TDE makes sure that the data is stored in encrypted form and when the data is accessed, it automatically decrypt it and it all happens transparently so it does not require even a single line of code at the application or database end.
setup Transparent Data Encryption:
First of all we need to define wallet location. Open your sqlnet.ora file and define the wallet location which can be any directory at the database server.
SQL> alter system set db_recovery_file_dest=’+’ SCOPE=spfile;
2. Ensure db_recovery_file_dest_size is set
SQL> alter system set db_recovery_file_dest_size=100G SCOPE=spfile;
3. Stop and start the database
sqlplus ‘/ as sysdba’
SQL> shutdown immediate;
SQL> startup mount;
If flashback to any previous point in time is required, then turn flashback on using the following command
SQL> alter database flashback on;
SQL> alter database open;
SQL> alter system set db_flashback_retention_target=2880;
NOTES
Set the db_recovery_file_dest to an appropriate location for the flashback recovery files.
Set the db_recovery_file_dest_size to an appropriate size for the amount and size of the testing required.
Set the db_flashback_retention_target to an appropriate time, in mins, to retain flashbackability.
Only run alter database flashback on; if there is a requirement to flashback to ANY previous point in time.
Determine if Flashback Database is Already Enabled.
sqlplus ‘/ as sysdba’
SQL> select flashback_on from v$database;
Creating and Using Flashback Restore points.
This worked example assumes the database is using ASM to manage its storage.
Createing a Restore point
Create a restore point whenever the database is at a state that it may needed to be flashed back to. Use the optional GUARANTEE FLASHBACK DATABASE clause to ensure that the restore point is not aged out of the flashback recovery area (FRA) as dictated by the db_flashback_retention_target parameter.
1. You may want to create the restore point in mount mode. If so, put the database into mount mode now.
2. Create a restore point
sqlplus ‘/ as sysdba’
SQL> create restore point [GUARANTEE FLASHBACK DATABASE];
Rolling Back to a Restore Point
1. Identify the Restore point
sqlplus ‘/ as sysdba’
SQL> select name, time,guarantee_flashback_databse from v$restore_point;
SQL> quit
2. For a non RAC environment use the following commands to flashback to a restore point.
sqlplus ‘/ as sysdba’
SQL> shutdown immediate;
SQL> startup mount;
SQL> flashback database to restore point ;
SQL> alter database open resetlogs;
3. For RAC instances use the following commands.
One one of the nodes run, srvctl stop database -d -o immediate
sqlplus ‘/ as sysdba’
SQL> startup mount;
SQL> flashback database to restore point ;
SQL> alter database open resetlogs;
SQL> shutdown immediate;
srvctl start database -d
Run crs_stat -t to confirm that the database is backup okay.
NOTES
Any tables created and updated without the LOGGING option will be suseptable to block curruption errors when the database is flashed back. These can be remedied by issuing the TRUNCATE TABLE command against the affected object(s).
Dropping a Restore Point
Restore points can be dropped with the database open using the following commands
sqlplus ‘/ as sysdba’
SQL> drop restore poijnt ;
Monitoring Flashback Logging
After enabling flashback logging, Oracle keeps track of the amount of logging generated. This can be queried from v$flashback_database_log, the estimate gets better with age. Note that this is the size of the flashback logs only and does not include space used by archive logs and RMAN backups.
sqlplus ‘/ as sysdba’
SQL> select estimated_flashback_size/1024/1024/1024 “EST_FLASHBACK_SIZE(GB)” from v$flashback_database_log;
Finding the Earliest Flashback Point
Querying V$flashback_database_log will show you the earliest point you can flashback your database to based on the size of the FRA and the currently available flashback logs.
sqlplus ‘/ as sysdba’
SQL> alter session set nls_date_format=’dd/mm/yy hh24:mi:ss’;
SQL> select oldest_flashback_scn,oldest_flashback_time from v$flashback_database_log;
Disabling Flashback Database
Full any previous point in time flashback can be disabled with the database open. Any unused Flashback logs will be automatically removed at this point and a message detailing the file deletion written to the alert log.
sqlplus ‘/ as sysdba’
SQL> ALTER DATABASE FLASHBACK OFF;
mostly comming Errors
ORA-38754: FLASHBACK DATABASE not started; required redo log is not available
Cause:-
Oracle needs to have the required archive logs in the archive destination at the time of flashback.
Very often DBAs are requested( by user or by force to optimze space requirement or performance) to re-org tables and indexes to get free space after a mass housekeeping of application or after some weeks of application run. In order to take back space from datafile/tablespace DBA can do one or many of few standard things:
1. move table to same or different tablespace. Rebuild of indexes is must after move of table as rowid of rows are changed.
2. do export and import of table. Need business outage.
3. rebuild only indexes(with/without online) to claim free space of indexes only.
4. alter table table_name shrink space cascade; This will try to put blocks togather starting from start of first free block in the datafile. Cascade option will take care of indexes, so no need to rebuild indexes in this case.
5. online re-org of tablespace/tables with exception for tables with data type long and raw.
The Oracle extents are mapped to OS data blocks. The mapping can be revealed by dba_free_space and dba_extents. We can check the actual layout of segments and free space in datafile by below command:
We can play around with above query to get information of each segment, related datafile etc.
There are 2 types of Fragmentation in Oracle:
1.Honeycomb fragmentation:
This is the fragmentation when free space is side by side. Honeycomb fragmentation donot bother you much until it is at the very beginning of the datafile. Mostly Shrink space cascade for the segment which is live before honeycomb fragmentation gets you rid of this fragmentation.
2.swiss cheese fragmentation:
This is the fragmentation when the free extents are separated by live segments.
If we query any tablespace with above shared script, we may get something like :
20 73400 73407 BHUSHAN00
20 73408 73415 free
20 73416 73423 BHUSHAN00
Mostly, we have combination of both fragmentation and thats make it challenging.
Often DBA fire any of the re-org command without analyzing the order in which we should do so as to take benefit to the max and get the re-org done completely.
In my scenario, I had 15 indexes on a datafile in a tablespace. The total size on segments was 500mb and total size of datafile was 15000mb. Still I was not able to claim any space to OS for other datafile. There was lot of fragmentation for sure which got revealed on running above mentioned query.
After my effort I could claim all 11000mb.
The best approach i found was:
find the order in which extents for segment is scattered in datafile. Start rebiuld index/shrink table from the bottom or top where you find more free blocks.
Oracle has several tools to help reclaim disk space:
alter database datafile xxx.dbf resize yym; – This will remove space that the physical “end” if the datafile, and the command will not work if any segments extend beyond your resize boundary.
alter tablespace xxx coalesce – This command will reclaim space from honeycomb fragmentation
Oracle leaves the high-water mark alone after rows are deleted, and you can reclaim space at the table level with these techniques, all of which lower the high water mark for the table, thereby freeing-up the space:
export-import – For a complete reorganization and space reclamation, export/import allows you to restructure your files and reclaim lost space.
dbms_redefinition – This procedure will reorganize a table while it remains online for updates.
alter table xxx shrink – If you were 10g and beyond you could use “alter table xxx shrink space compact;” syntax.
You can look at the coalesce table syntax. Unlike the “deallocate unused space” syntax which removes space above the high-water mark, “coalesce” puts together discontiguous fragmented extents. There are two type of space fragmentation in Oracle.
First is the honeycomb fragmentation, when the free extents are side by side, and the “Swiss Cheese” fragmentation, when the extents are separated by live segments.
alter table xxx coalesce;
You can also deallocate unused space. Oracle notes that the “deallocate unused space” clause is used to to explicitly deallocate unused space at “the end” of a segment and makes that space available for other segments within the tablespace.
alter table xxx deallocate unused space;
alter index xxx deallocate unused space;
Internally, Oracle deallocates unused space beginning from the end of the objects (allocated space) and moving downwards toward the beginning of the object, continuing down until it reaches the high water mark (HWM). For indexes, “deallocate unused space” coalesces all leaf blocks within same branch of b-tree, and quickly frees up index leaf blocks for use.
The below error may trigger when you hit with max sessions exceeded
ERROR at line 1:
ORA-01012: not logged on
What is the cause of this ORA-00020 error and how do I fix it?
Answer: The ORA-00020 is a serious production error because a user cannot connect.
The ORA-00020 is caused by two things:
1. Disconnected processes: Rogue “zombie” connections to Oracle that are idle (not working). To fix this, use the ALTER SYSTEM KILL command. You may also need to kill session at the OS level with the KILL -9 or the ORAKILL command.
2. Too few process buckets: Oracle limits the number of connected processes with the processes parameter, and you may get the ORA-00020 error as the natural result in growth of system usage.
To fix this, increase the processes parameter, usually doubling the value to allow for future growth.
The OERR command shows these details for the ORA-00020 error:
ORA-00020: maximum number of processes (string) exceeded
Cause: All process state objects are in use.
Action: Increase the value of the PROCESSES initialization parameter
SQL> Alter index SAI.TESTVALUE_IDX rebuild online;
Alter index SAI.TESTVALUE_IDX rebuild online *
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01450: maximum key length (3215) exceeded
Cause of the Problem
When creating an index the total length of the index cannot exceed a certain value. Primarily this value depends on DB_BLOCK_SIZE.
If 2K block size then maximum index key length=758
If 4K block size then maximum index key length=1578
If 8K block size then maximum index key length=3218
If 16K block size then maximum index key length=6498
How the maximum index key length is measured by?
Maximum index key length=Total index length (Sum of width of all indexed column + the number of indexed columns) +Length of the key (2 bytes) +ROWID (6 bytes) +the length of the rowid (1 byte)
The index key size is limited by the value of db_block_size, because a key value may not span multiple blocks. So, based on the size of the block size of index depends. In fact, it is required that any index block must contain at least TWO index entries per block.
So we can say that the maximum key length for an index will be less than half of the DB_BLOCK_SIZE. But we know that in a block there also needed space for PCTFREE, INITRANS and space for block overhead (Block Header, ROW Directory, Table Directory, etc.). After considering these bytes the actual space that can be used for the Index key is actually just over 1/3 of the DB_BLOCK_SIZE.
Solution of the Problem
1) Increase your database block size. Create a tablespace with bigger block size and create index on that tablespace.
2) If you have index on multiple columns then you can split index to single or 2 columns so that size does not extended over it can handle.
3) Rebuild the index without online clause. That is ALTER INDEX index_name REBUILD;
Because the online rebuild of the index creates a journal table and index. This internal journal IOT table contains more columns in its index. This is a feature of online rebuild. This time the error arises because that current value of the initialization parameter db_block_size is not large enough to create internal journal IOT.
The rows are split into more than one block is called fragmentation. it decrease the block accesses and it slow the database performance. fragmentation are two type one is table fragmentation and second is index fragmentation.
TABLE FRAGMENTATION:
Each table have the blocks and it stored in the data files. The data will store in the block level. When the data split into more than one blocks is called table fragmentation. The table get more update and delete statement means table will get fragmentation.
When table getting full table scan in the that time oracle will read the data upto HWM (HIGH WATER MARK LEVEL). HWM is using to find used block and free block in table.
ROW CHAINING:
When inserting the row, in that time the block does not have any space means oracle allocate the next block for that inserting its called the row chaining.
ROW MIGRATION:
When updating the row in block the update not fit into the existing block it go to another block is called row migration. it based on pctfree. Because pctfree it low means the block get row migration.
FINDING THE FRAGMENTATION IN TABLE:
Create one table and add 2 column in that table.
Insert max 1000000 records in that table and commit it.
Now do some delete operation in that table and analyze the table using estimate or compute statistics or dbms_space.space_usage package.
Now check the info in dba_tables using query
(SELECT chain_cnt FROM user_tables WHERE table_name = ‘ROW_MIG_CHAIN_DEMO’;)
Its show the num_rows and chain_cnt, if u get any values in chain_cnt means fragmentation is accured.
Using dbms_space.space_usage
Steps to Check and Remove Table Fragmentation:-
1. Gather table stats:
To check exact difference in table actual size (dba_segments) and stats size (dba_tables). The difference between these value will report actual fragmentation to DBA. So, We have to have updated stats on the table stored in dba_tables. Check LAST_ANALYZED value for table in dba_tables. If this value is recent you can skip this step. Other wise i would suggest to gather table stats to get updated stats.
((round((blocks*8),2)-round((num_rows*avg_row_len/1024),2))/round((blocks*8),2))*100 -10 “reclaimable space % ” from dba_tables where table_name =’&table_Name’ AND OWNER LIKE ‘&schema_name’
/Note: This query fetch data from dba_tables, so the accuracy of result depends on dba_table stats.
If you find reclaimable space % value more than 20% then we can expect fragmentation in the table. Suppose, DBA find 50% reclaimable space by above query, So he can proceed for removing fragmentation.
4. DEFRAGMENTATIONS (or)remove fragemenation
1. move table to another tablespace
2. export and import the table(exp/imp)
3. shrink command (fron oracle 10g)
(shrink command is only applicable for tables which are tablespace with auto segment space management)
4. CTAS method
5.online redefinition
option: 1 move table to another tablespace
alter table
enable row movement;
alter table
move tablespace ;
Now, get back table to old tablespaces using below command
alter table table_name move tablespace old_tablespace_name;
REBUILD ALL INDEXES:
We need to rebuild all the indexes on the table because of move command all the index goes into unusable state.
Here, value in status field may be valid or unusable.
SQL> select status,index_name from dba_indexes where table_name = ‘&table_name’;
STATUS INDEX_NAME
——– ——————————
UNUSABLE INDEX_NAME
Here UNUSABLE status going to valid
SQL> alter index rebuild online;
Index altered.
Here, value in status field must be valid.
SQL> select status,index_name from dba_indexes where table_name = ‘&table_name’;
((round((blocks*8),2)-round((num_rows*avg_row_len/1024),2))/round((blocks*8),2))*100 -10 “reclaimable space % ” from dba_tables where table_name =’&table_Name’ AND OWNER LIKE ‘&schema_name’
PRCR-1006 : Failed to add resource ora.rocratc1.db for rocratc1 PRCR-1071 : Failed to register or update resource ora.rocratc1.db CRS-0259: Owner of the resource does not belong to the group.
postgres 
