How to use DBMS_FLASHBACK?

In 10g using dbms_flashback one can flashback using timestamp or SCN. If a user accidentally deletes rows or updates rows one can use this to review the data without need to have flashback turned on in the database. Note: SYS can’t use dbms_flashback to flashback, Oracle will return “ORA-08185: Flashback not supported for user SYS” when SYS will try to enable flashback.

Example:
– shows flashback is off
SQL> select flashback_on from V$database;

FLASHBACK_ON
——————
NO

– declare a variable to store current SCN
SQL> variable current_scn number
– Get the current SCN and initialize current_scn
SQL> execute :current_scn := sys.dbms_flashback.get_system_change_number();

PL/SQL procedure successfully completed.

– print the current scn
SQL> print current_scn;

CURRENT_SCN
———–
389907

– query the table, shows no rows
SQL> select * FROM test;

no rows selected

– insert the row and commit
SQL> insert into test values ( 1 );

1 row created.

SQL> commit;

Commit complete.

– flashback to before insert was executed, so select should return no rows
SQL> execute dbms_flashback.enable_at_system_change_number(:current_scn);

PL/SQL procedure successfully completed.

– as expected select returned no rows
SQL> select * FROM test;

no rows selected

– disable flashback
SQL> execute dbms_flashback.disable;

PL/SQL procedure successfully completed.

Note: If there is a truncate executed on the table then data can’t be read from the table after enabling flashback Oracle will return “ORA-01466: unable to read data – table definition has changed” on truncated table.

How to fix Target Database Incarnation Is Not Current In Recovery Catalog RMAN-20011?

When doing a incomplete recovery (i.e. database is open with resetlogs) and when running RMAN to backup that database after incomplete recovery you will get the following error.

RMAN-03014: implicit resync of recovery catalog failed
RMAN-06004: ORACLE error from recovery catalog database: RMAN-20011: target database incarnation is not current in recovery catalog

To fix this issue by performing the following steps.

1) Connect to RMAN catalog via SQL*Plus
$ sqlplus @rmancatalog

SQL> SELECT name, DBID, RESETLOGS_TIME FROM rc_database WHERE dbid=9999999999;

NAME DBID RESETLOGS
——– ———- ———
TESTDB 9999999999 29-JUL-09

Check the latest incarnation
SQL> SELECT dbid, name, dbinc_key, resetlogs_change#, resetlogs_time FROM rc_database_incarnation WHERE dbid=9999999999 ORDER BY resetlogs_time;

SQL>SELECT db_key,DBID,name,current_incarnation FROM rc_database_incarnation WHERE dbid = 9999999999 order by 1;

DBID NAME DBINC_KEY RESETLOGS_CHANGE# TO_CHAR(RESETLOGS_TI
———- ——– ———- —————– ——————–
9999999999 TESTDB 473490 1 16-FEB-2006 08:59:37
9999999999 TESTDB 473484 565658 07-MAY-2007 10:15:58
9999999999 TESTDB 774712 52500357 29-JUL-2009 11:12:21

So then connect to the rman catalog
$ export NLS_DATE_FORMAT=’DD-MON-YYYY HH24:MI:SS’
$ rman catalog target /
RMAN> reset database to incarnation 774712; — From step 1 after running this step you should be able to run backup on the database on which incomplete recovery was done

RMAN> resync catalog;

– shows the current incarnation is the current one
RMAN> list incarnation;

List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
——- ——- ——– —————- — ———- ———-
473483 473490 TESTDB 9999999999 PARENT 1 16-FEB-2006 08:59:37
473483 473484 TESTDB 9999999999 PARENT 565658 07-MAY-2007 10:15:58
473483 774712 TESTDB 9999999999 CURRENT 52500357 29-JUL-2009 11:12:21

How to delete archive logs already archived to backup device?

If one is keeping archive logs available on disk and also have them archived to tape and would like to delete only the ones that have been archived to tape in the archive directory through RMAN, you can use the following command.

connect target /
connect catalog rmancatalog/rmancatalog@catalog;
# For Tape
run {
DELETE ARCHIVELOG LIKE '%' BACKED UP 1 TIMES TO DEVICE TYPE SBT_TAPE;
}

# For Disk
run {
DELETE ARCHIVELOG LIKE '%' BACKED UP 1 TIMES TO DEVICE TYPE DISK;
}

How to keep 2 days of archive log on disk and still have back to tape?

Using the following rman command one can setup to keep 2 days of archive log on disk and also archive them to tape/disk the ones that haven’t been sent to.

connect target /
connect rmancatalog/rmancatalog@catalog

run {
# tells to backup all archive logs that haven’t been backed up one time which prevents from backing up more than once to
backup archivelog all not backed up 1 times;
# deletes archive log more than 2 days old, as the 2nd command runs after the first one so if some reason first one fails the delete step won’t run
delete archivelog until time ‘SYSDATE-2′;
}

There is another way of doing which is:
# set the following parameter in the rman parameters
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE SBT_TAPE TO 1;
run {
delete archivelog all completed before ’sysdate-2′;
}

How to find objects that are performing unrecoverable/nologgging option?

Using the steps below one can find the object that is unrecoverable from UNRECOVERABLE_CHANGE# in the v$datafile, one would use this to find the object that is unrecoverable since the last backup.

In this scenario datafile# 5 had unrecoverable/nologging as when we query v$datafile it shows and UNRECOVERABLE_TIME was greater than when the last backup was done.

SQL> column UNRECOVERABLE_CHANGE# format 99999999999999

SQL> select file#, UNRECOVERABLE_CHANGE# from v$datafile where file# = 5;

FILE# UNRECOVERABLE_CHANGE#
———- ———————
5 37640948153

– find the archive log that contains the sequence#
SQL> select sequence#, name from v$archived_log where 37640948153 >= first_change# and 37640948153 < next_change#;

SEQUENCE# NAME
———- ——————————————————————————–
2108 /u01/TESTDB/arch/arch_1_2108.arc

– This will create file in user_dump
SQL> alter system dump logfile '/u01/TESTDB/arch/arch_1_2108.arc' layer 19;

In 8i the object id is not included but in the 10g trace file the object id is included in the trace file
/u01/admin/TESTDB/udump $ grep DBA ora_168168_testdb.trc

…
CHANGE #1 INVLD AFN:5 DBA:0×01401e0a BLKS:0×0020 SCN:0×0008.c39349b9 SEQ: 1 OP:19.2
…

# to find the file# and block
SET SERVEROUTPUT ON
DECLARE
l_dba NUMBER := TO_NUMBER (‘01401e0a’,'XXXXXXXX’);
l_file NUMBER := DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE (l_dba);
l_block NUMBER := DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK (l_dba);
BEGIN
DBMS_OUTPUT.PUT_LINE (‘File : ‘||l_file);
DBMS_OUTPUT.PUT_LINE (‘Block : ‘||l_block);
END;
/

Output:
File : 5
Block : 7690

SQL> SELECT owner, segment_name FROM dba_extents where file_id = 5 and block_id = 7690;

OWNER SEGMENT_NAME
—————————— —————-
SCOTT TEST_TABLE

How to find if there are datafiles that have objects that are not recoverable since last backup?

Using the following query one can find the datafiles that had nologging turned on, direct load or unrecoverable option was used on a datafile since the last successful backup.

SELECT df.name, to_char(df.unrecoverable_time, ‘DD-MON-YYYY HH24:MI:SS’) unrecover_time
FROM v$datafile df,
(SELECT bd.file#, max(completion_time) completion_time
FROM v$backup_datafile bd GROUP BY bd.file#) bd
WHERE bd.file# (+) = df.file#
AND df.unrecoverable_time > bd.completion_time;

If it returns no rows it means no unrecoverable/nologging options were used since the last backup.

NAME UNRECOVER_TIME
—————————————————————- ———————-
/u01/oradata/TESTDB/INDX_01.dbf 27-APR-2009 13:31:52
/u03/oradata/TESTDB/DATA_01.dbf 27-APR-2009 03:52:59

How to restore archive logs from backup piece?

If one needs to restore archive logs to disk and not apply them, for example in case when cloning a database manually and one is missing the archive logs from the source database and you need to restore the archive logs on the source machine. So using the following command one can restore the archive logs.

connect target /
connect rcvcat rmancatalog/rmancatalog@catdb

run {
restore archivelog from logseq=36747 until logseq=36753 thread=1;
}
from logseq – is the starting log sequence #
until logseq – is the end log sequence #

Remove Old Backup Log Files

This script will delete files modified more than 14 days ago and have file name that begin with daily_backup , it will do recursive search within directories that exists in the directory /work/dba/backup/logs/

find /work/dba/backup/logs/ -name "daily_backup*" -mtime +14 -exec rm -f {} ;

Format for parameter LOG_ARCHIVE_FORMAT?

This parameter controls the format of the archive log file name.  This parameter can’t be changed on the fly therefore requires a restart of the instance.  This parameter can be changed with scope=spfile if spfile is used.  

If the format defined in the parameter log_archive_format is invalid the database will startup but archiver will fail to archive logs which will cause database to hang and in the alert log the following message would be reported “ORA-00294: invalid archivelog format specifier..” or you will see an error message when you try to archive the current redo log by running “ALTER SYSTEM ARCHIVE LOG CURRENT;”, so if you change this parameter a quick test can be done by running the above SQL to make sure oracle is able to archive the redo log.

Format options available on 9i:

%s – log sequence number
%S – log sequence number, zero filled
%t – thread number, needed when running RAC as each node creates it’s own archivelog
%T thread number, zero filled, needed when running RAC as each node creates it’s own archivelog

Format options available on 10g

%s – log sequence number
%S – log sequence number, zero filled
%t – thread number, needed when running RAC as each node creates it’s own archivelog
%T – thread number, zero filled, needed when running RAC as each node creates it’s own archivelog
%a – activation ID
%d – database ID
%r – resetlogs ID that ensures unique names are constructed for the archived log files across multiple incarnations of the database.

In 10g, %s, %t, %r are required to be present in the parameter, if it doesn’t the database fail to start with the error ORA-19905: log_archive_format must contain %s, %t and %r.  Using this format makes it the archive log filename unique for that instance.

The following article has information how to start database in archivelog mode

How to clone a database manually?

Using the following steps one can clone a database manually. In the following example PRD represents the source and DEV represents target/new database.

1) Get list of datafiles on the PRD database. In the following example this database has 3 datafiles.
SQL> SELECT name FROM v$datafile;
NAME
——————————————————————————–
/u03/oradata/PRD/system01.dbf
/u03/oradata/PRD/undotbs01.dbf
/u03/oradata/PRD/sysaux01.dbf

3 rows selected.

2) On the PRD (source) database, run the following query to find the last archived logs, the archive logs created after backup begin is run needs to be copied, so make a note of sequence # shown needs to be copied on the DEV (target) database to restore.
SQL> SELECT sequence#, TO_CHAR(next_time, ‘DD-MON-YYYY HH24:MI:SS’)
FROM (SELECT sequence#, next_time FROM V$archived_log ORDER BY next_time DESC)
WHERE rownum < 2;

SEQUENCE# TO_CHAR(NEXT_TIME,’D
———- ——————–
6 12-JAN-2009 20:40:15

1 row selected.

3) Execute the following SQL to make the datafiles in backup mode.  This command is supported in 10g for 9i, each tablespace would need to placed in backup mode by using “ALTER TABLESPACE name BEGIN BACKUP;”
SQL> ALTER DATABASE BEGIN BACKUP;

Database altered.

4) Now the datafiles can be copied from PRD (source) to DEV (target) machine from the list created in step (1)
$ cd /u03/oradata/DEV
$ cp -pi /u03/oradata/PRD/system01.dbf .
$ cp -pi /u03/oradata/PRD/undotbs01.dbf .
$ cp -pi /u03/oradata/PRD/sysaux01.dbf .

4) Execute the following SQL to make the datafiles out of backup mode.
SQL> ALTER DATABASE END BACKUP;

Database altered.

5) Perform some logswitches on the PRD (source) database, this step will create archive logs on the source database.
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;

System altered.

SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;

System altered.

SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;

System altered.

6) On the PRD (source) database, run the SQL in step (2) again to get the current sequence# of archived logs. The archived log files created for the sequence# between step (2) and step (6) would need to copied to the DEV (target) machine. In this example archive logs with sequence# 6-12 would need to be copied on the target host.

SEQUENCE# TO_CHAR(NEXT_TIME,’D
———- ——————–
14 12-JAN-2009 20:43:05

1 row selected.

7) Modify the pfile by copying the pfile from PRD (source) to target or if the source is using spfile, run the following SQL to create the pfile “CREATE PFILE=’/tmp/initDEV.ora’ TO SPFILE;” The parameters in the pfile needs to be modified for the target database. In the following example the following parameters where modified where “PRD” was replaced with “DEV”.

*.audit_file_dest=’/u01/app/oracle/admin/DEV/adump’
*.background_dump_dest=’/u01/app/oracle/admin/DEV/bdump’
*.user_dump_dest=’/u01/app/oracle/admin/DEV/udump’
*.core_dump_dest=’/u01/app/oracle/admin/DEV/cdump’
*.control_files=’/u03/oradata/DEV/control01.ctl’,'/u03/oradata/DEV/control02.ctl’,'/u03/oradata/DEV/control03.ctl’
*.db_name=’SMOXY’
*.log_archive_format=’DEV_%t_%s_%r.arc’
*.log_archive_dest_1=’LOCATION=/u03/oradata/DEV/arch’

8 ) On the PRD (source) instance create the backup control file to trace using the following SQL
SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

Database Altered

Modify the control file creation script in udump folder, section where the resetlogs and change REUSE TO SET, modify the name PRD to DEV, change the path of the datafiles if needed, only the following section would needs to be run.
CREATE CONTROLFILE SET DATABASE “DEV” RESETLOGS ARCHIVELOG
MAXLOGFILES 50
MAXLOGMEMBERS 5
MAXDATAFILES 500
MAXINSTANCES 1
MAXLOGHISTORY 2045
LOGFILE
GROUP 1 (
‘/u03/oradata/DEV/redo01a.log’,
‘/u03/oradata/DEV/redo01b.log’
) SIZE 100M,
GROUP 2 (
‘/u03/oradata/DEV/redo02a.log’,
‘/u03/oradata/DEV/redo02b.log’
) SIZE 100M,
GROUP 3 (
‘/u03/oradata/DEV/redo03a.log’,
‘/u03/oradata/DEV/redo03b.log’
) SIZE 100M,
GROUP 4 (
‘/u03/oradata/DEV/redo04a.log’,
‘/u03/oradata/DEV/redo04b.log’
) SIZE 100M
DATAFILE
‘/u03/oradata/DEV/system01.dbf’,
‘/u03/oradata/DEV/undotbs01.dbf’,
‘/u03/oradata/DEV/sysaux01.dbf’
CHARACTER SET WE8ISO8859P1;

9) On the target machine, and start the instance in nomount, make sure it will use the pfile created in step (7)
export ORACLE_SID=DEV
SQL> startup nomount

10) Using the script create the control file created in step ( 8 )

11) Now database can be recovered, at this step Oracle will prompt for the archive logs copied in step (2) and (6) or a specific point in time can be specified. When all the archive logs are applied, type CANCEL
SQL> RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL;

ORA-00279: change 9603 generated at 01/12/2009 20:40:43 needed for thread 1
ORA-00289: suggestion : /u03/oradata/DEV/arch/DEV_1_9_675981354.arc
ORA-00280: change 9603 for thread 1 is in sequence #9

21:07:48 Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u03/oradata/DEV/arch/DEV_1_9_675981354.arc
ORA-00279: change 9614 generated at 01/12/2009 20:40:56 needed for thread 1
ORA-00289: suggestion : /u03/oradata/DEV/arch/DEV_1_10_675981354.arc
ORA-00280: change 9614 for thread 1 is in sequence #10
ORA-00278: log file ‘/u03/oradata/DEV/arch/DEV_1_9_675981354.arc’ no longer needed for this recovery

……..

21:11:25 Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
CANCEL

Media recovery cancelled.

Or (for point in time recovery)
SQL> RECOVER DATABASE UNTIL TIME ‘2009-01-11:15:14:30′ USING BACKUP CONTROLFILE;
…
Media recovery complete

12) Now the database can be opened with the reset logs option.
ALTER DATABASE OPEN RESETLOGS;

Database altered.

13) Add datafile to temporary tablespace, the size of the datafile can be adjusted as needed.
SQL> ALTER TABLESPACE TEMP ADD TEMPFILE ‘/u03/oradata/DEV/temp01.dbf’ SIZE 100M REUSE AUTOEXTEND OFF;

14) Change global name of the cloned database
SQL> ALTER DATABASE RENAME global_name TO DEV;

SQL> SELECT * FROM global_name;

GLOBAL_NAME
——————————————————————————–
DEV