Wednesday, March 10, 2010

Oracle Flashback Drop and Recycle bin

The Oracle 10g provides the ability to reinstating an accidentally dropped table, which is called Flashback Drop.

When a table is dropped, the database does not immediately remove the space associated with that table. Instead, the table is renamed to a system-defined name and placed in the Recycle bin. The flashback drop operations recovers from recycle bin.

SQL> DROP TABLE employee_tbl;

You can see the system-defined name of the table.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BIN$gXxxELu7aQ/gQAoKd5l2Hg==$0 TABLE

You can see the dropped table in the recycle bin using
SQL> show Recyclebin
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
-------------- ------------------------------ ------------ ----------------
EMPLOYEE_TBL BIN$gXxxELu7aQ/gQAoKd5l2Hg==$0 TABLE 2010-03-01:09:10:00

Recover the dropped table from Recycle bin using
SQL> FLASHBACK TABLE employee_tbl TO BEFORE DROP;

Recover the dropped table from Recycle bin with new name using
SQL> FLASHBACK TABLE employee_tbl TO BEFORE DROP RENAME TO employee_tmp;

You can also drop a table completely with out needing a flashback using
SQL> DROP TABLE employee_tbl PURGE;

The tablespace will not free up space until the table will remain until recycle bin is purged. You can purge table explicitly from recycle bin using
SQL> PURGE TABLE employee_tbl;

Purge all tables in recycle bin using
SQL> PURGE RECYCLEBIN;

As a DBA can purge all the objects in any tablespace using
SQL> PURGE DBA_RECYCLEBIN;

Purge all the objects in recycle bin in a tablespace using
PURGE TABLESPACE employee_tbs;

Purge all the objects in recycle bin in a tablespace specific to a user only using
PURGE TABLESPACE employee_tbs USER emp_user;

Regards,
Satishbabu Gunukula
http://www.oracleracexpert.com

Flashback Table Feature in Oracle 10g

In Oracle 9i Database, we have concept of Flashback Query option to retrieve data from a point in time in the past. The Oracle 10g provides the ability to recover a table or set of tables to a specified point in time in the past, this concept is called “Flashback table”.

Oracle Flashback Table operation is very quick and you do not have to restore any data from backups, and the rest of your database remains available while the Flashback Table operation is being performed.

The Flashback table depends on Undo information retained in the undo tablespace. If you set UNDO_RETENTION=1 hr, Oracle will not overwritten the data in undo tablespace until 1 hr. User can recover from their mistakes until specified time only.

Flashback table feature has some prerequisites:
•Row movement must be enabled on the table.
SQL> ALTER TABLE table_name ENABLE ROW MOVEMENT;
•You must have SELECT, INSERT, DELETE, and ALTER privileges on the table.
•You must have FLASHBACK ANY TABLE privilege or the FLASHBACK object privilege on the table.

Use below commands to restore the table to its state when the database was at the time specified by SCN or point in time.
SQL> FLASHBACK TABLE employee_tbl TO SCN 786;
or
SQL> FLASHBACK TABLE employee_tbl TO TIMESTAMP TO_TIMESTAMP ('2010-03-01 09:00:00', 'YYYY-MM-DD HH:MI:SS')

Regards
Satishbabu Gunukula
http://www.oracleracexpert.com

Tuesday, March 9, 2010

Unable to start RAC instance after applying a patch

Many users experienced “ORA-07445” error after applying any patch on 10.2.0.2 version and unable to start the RAC database instances on NODE2 or remote NODE’S (if more than 2 nodes). This also leads to dumps or internal errors on the remote nodes.

ORA-07445: exception encountered: core dump [kkxsyn()+584] [SIGSEGV]


The issues is a known bug 5128575 and the effected version are >=10.2.0.2

Check for possible Error messages in ASM/Database alert.log and trace files on NODE2 or Remote nodes. If you find the below SYMPTOMS then you are hitting a BUG 5128575.

ASM alert.log errors:
Errors in file /oracle/v10202/admin/+ASM/udump/+asm2_ora_30841.trc:
ORA-07445: exception encountered: core dump [kkxsyn()+584] [SIGSEGV] [Address not mapped to object] [0x000000168] [] []

Trace file errors:
Exception signal: 11 (SIGSEGV), code: 1 (Address not mapped to object), addr: 0x168, PC: [0x3f06748, kkxsyn()+584]
ksedmp: internal or fatal error
ORA-07445: exception encountered: core dump [kkxsyn()+584] [SIGSEGV] [Address not mapped to object] [0x000000168] [] []
No current SQL statement being executed.

Database errors:
Startup database fails with below errors
SQL> startup database
ORA-01078: failure in processing system parameters
ORA-01565: error in identifying file '+DATA/testdb/spfiletestdb.ora'
ORA-17503: ksfdopn:2 Failed to open file +DATA/testdb/spfiletestdb.ora
ORA-03113: end-of-file on communication channel

Cause:
Installing the 10.2.0.2 Patch Set in RAC on any Unix platform does not correctly update the libknlopt.a file on all remote nodes. The local node where the installer is run does update libknlopt.a file and remote nodes do not get the updated file.

Workaround:
Step 1: Shutdown ASM and all Database instances
Step 2: Manually copy $ORACLE_HOME/rdbms/lib/libknlopt.a from the local node to NODE2 or all remote nodes (if more than 2 nodes)
$ scp libknlopt.a oracle@testrac2:$ORACLE_HOME/rdbms/lib/libknlopt.a
Step 3: Re-link Oracle on all remote nodes.
3. cd $ORACLE_HOME/rdbms/lib
make -f ins_rdbms.mk ioracle

Regards,
Satishbabu Gunukula
http://www.oracleracexpert.com

Friday, March 5, 2010

Flash Recovery Area (FRA) in Oracle 10g

Flash recovery area is a disk location in which the database can store and manage files related to Backup and Recovery. To setup a flash recovery area, you must choose a directory location or Automatic Storage Management disk group to hold the files. FRA cannot be stored on a raw file system.

Flash recovery area simplifies the administration of your database by automatically naming recovery-related files, retaining the files as long as they are needed for restore and recovery activities, and deleting the files when they are no longer needed to restore your database and space is needed for some other backup and recovery-related purpose.

To Setup Flash Recovery Area (FRA), you just need to specify below two parameters.
1. DB_RECOVERY_FILE_DEST_SIZE (Specifies max space to use for FRA)
2. DB_RECOVERY_FILE_DEST (Location of FRA)

The DB_RECOVERY_FILE_DEST_SIZE must be set before DB_RECOVERY_FILE_DEST.

You can initialize these parameters by adding them in “init.ora” file or setting them using ALTER SYSTEM SET command or DBCA (Database Configuration Assistant).
For ex:-
SQL> ALTER SYSTEM SET db_recovery_file_dest_size=10G scope=both
SQL> ALTER SYSTEM SET db_recovery_file_dest='/oradata/FRA;
For example, If FRA is a Automatic Storage Management (ASM) disk group
SQL> ALTER SYSTEM SET db_recovery_file_dest='+FLASH’sid='*’ ( sid=’*’, if using RAC)

In a RAC database, all database instances MUST have the same values for these parameters.

Disable the Flash Recovery Area:
To disable FRA set the DB_RECOVERY_FILE_DEST initialization parameter to a null string.
SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST = '' scope=both;

Monitor the Flash recovery area using Dynamic performance views
To find out the current flash recovery area location, disk quota and current usage in flash recovery area query V$RECOVERY_FILE_DEST.

SQL> SELECT * FROM V$RECOVERY_FILE_DEST;
NAME SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES
------ ----------- ----------- ----------------- -------------
FLASH 10737418240 5368709120 256000 28

The $FLASH_RECOVERY_AREA_USAGE views shows the percentage of the total disk quota used by different types files, and how much space for each type of file can be reclaimed by deleting files that are obsolete, redundant, or already backed up to tape.

SQL> SELECT * FROM V$FLASH_RECOVERY_AREA_USAGE;
FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
------------ ------------------ ------------------------- ---------------
CONTROLFILE 2 0 4
ONLINELOG 6 0 8
ARCHIVELOG 8.05 1.11 20
BACKUPPIECE 33.94 5.46 32
IMAGECOPY 5.64 1.23 10
FLASHBACKLOG .06 0 1

Files Stored in the Flash Recovery Area
PERMANENT Files - Multiplexed copies of Control files and online redo log files
TRANSIENT Files - Archived redo logs not yet backed up, Datafile copies, Incremental backups, Contolfile auto backups (which include spfile)

Only TRANSIENT files will be deleted from flash recovery area based up on following rules:
•Files those are obsolete under the configured RMAN retention policy.
•Transient files that are copied to tape.
•In a Data Guard environment, archived redo log files can be deleted based up on archived redo log deletion policy.

Errors:
ORA-19809: limit exceeded for recovery files
Cause: The limit for recovery files specified by the DB_RECOVERY_FILE_DEST_SIZE was exceeded.
Action: The error is accompanied by 19804. See message 19804 for further details

ORA-19804: cannot reclaim nnnnn bytes disk space from mmmmm limit
Cause: Oracle cannot reclaim disk space of specified bytes from the DB_RECOVERY_FILE_DEST_SIZE limit.
Action: There are five possible solutions:
1) Take frequent backup of recovery area using RMAN.
2) Consider changing RMAN retention policy.
3) Consider changing RMAN archivelog deletion policy.
4) Add disk space and increase DB_RECOVERY_FILE_DEST_SIZE.
5) Delete files from recovery area using RMAN.

ORA-38786: Flash recovery area is not enabled.
Cause: An attempt was made to perform a command that requires Flash recovery area to be enabled.
Action: Set DB_RECOVERY_FILE_DEST to an appropriate location and retry

Regards,
Satishbabu Gunukula
http://www.oracleracexpert.com