Database Point-In-Time Recovery

 STEP 1: Note the current database clock time

SQL> select to_char(sysdate,'DD-MON-YY:HH24:MI:SS') from dual;

TO_CHAR(SYSDATE,'DD-MON-

------------------------

20-FEB-15:17:45:55

STEP 2: Drop a table

We will now drop the table MYOBJECTS and then perform an RMAN point-in-time recovery to restore the database to a point in time just before the table was dropped. Note the number of rows in the table.

SQL> select count(*) from myobjects;

  COUNT(*)

----------

     13420

SQL> drop table myobjects;

Table dropped.

STEP 3: Shutdown and then mount the database in preparation for restore and recovery.

SQL> conn / as sysdba

Connected.

SQL> shutdown abort

ORACLE instance shut down.

SQL> startup mount;

[oracle@NVMBD1BZY150D00 CLONEDB]$ rman target / catalog rman/rman@rmanp

Recovery Manager: Release 11.2.0.3.0 - Production on Fri Feb 20 17:49:27 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: CLONEDB (DBID=958582256, not open)

connected to recovery catalog database

Note – the incremental backup since available is restored before applying the archive log files. The archive log files that will be applied are those that have been generated after the Level 1 incremental backup.

Since we are restoring the database to a point in the past, we will have to open the database with the RESETLOGS option.

RMAN> run {

2> set until time "to_date('20-FEB-15:17:45:55','dd-MON-yy hh24:mi:ss')";

3> restore database;

4> recover database;

5> }

executing command: SET until clause

Starting restore at 20-FEB-15

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=176 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_DISK_1: restoring datafile 00001 to /data/oracle/app/oracle/oradata/CLONEDB/system.dbf

channel ORA_DISK_1: restoring datafile 00002 to /data/oracle/app/oracle/oradata/CLONEDB/user04.dbf

channel ORA_DISK_1: restoring datafile 00003 to /data/oracle/app/oracle/oradata/CLONEDB/sysaux.dbf

channel ORA_DISK_1: restoring datafile 00004 to /data/oracle/app/oracle/oradata/CLONEDB/undo.dbf

channel ORA_DISK_1: restoring datafile 00005 to /data/oracle/app/oracle/oradata/CLONEDB/test1_tmp.dbf

channel ORA_DISK_1: restoring datafile 00006 to /data/oracle/app/oracle/oradata/CLONEDB/test_corrupt.dbf

channel ORA_DISK_1: restoring datafile 00007 to /data/oracle/app/oracle/oradata/CLONEDB/users_01.dbf

channel ORA_DISK_1: reading from backup piece /data1/backup/clonedb/bkp.38pvodgm_1_1

channel ORA_DISK_1: piece handle=/data1/backup/clonedb/bkp.38pvodgm_1_1 tag=LEVEL 0

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:01:25

Finished restore at 20-FEB-15

Starting recover at 20-FEB-15

using channel ORA_DISK_1

channel ORA_DISK_1: starting incremental datafile backup set restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

destination for restore of datafile 00001: /data/oracle/app/oracle/oradata/CLONEDB/system.dbf

destination for restore of datafile 00002: /data/oracle/app/oracle/oradata/CLONEDB/user04.dbf

destination for restore of datafile 00003: /data/oracle/app/oracle/oradata/CLONEDB/sysaux.dbf

destination for restore of datafile 00004: /data/oracle/app/oracle/oradata/CLONEDB/undo.dbf

destination for restore of datafile 00005: /data/oracle/app/oracle/oradata/CLONEDB/test1_tmp.dbf

destination for restore of datafile 00006: /data/oracle/app/oracle/oradata/CLONEDB/test_corrupt.dbf

destination for restore of datafile 00007: /data/oracle/app/oracle/oradata/CLONEDB/users_01.dbf

channel ORA_DISK_1: reading from backup piece /data1/backup/clonedb/bkp.3cpvog7a_1_1

channel ORA_DISK_1: piece handle=/data1/backup/clonedb/bkp.3cpvog7a_1_1 tag=LEVEL 1 BACKUP

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

starting media recovery

archived log for thread 1 with sequence 6 is already on disk as file /data1/clonedb/archive/1_6_872166459.dbf

archived log for thread 1 with sequence 7 is already on disk as file /data1/clonedb/archive/1_7_872166459.dbf

archived log for thread 1 with sequence 8 is already on disk as file /data1/clonedb/archive/1_8_872166459.dbf

archived log for thread 1 with sequence 9 is already on disk as file /data1/clonedb/archive/1_9_872166459.dbf

archived log for thread 1 with sequence 10 is already on disk as file /data1/clonedb/archive/1_10_872166459.dbf

archived log for thread 1 with sequence 11 is already on disk as file /data1/clonedb/archive/1_11_872166459.dbf

archived log for thread 1 with sequence 12 is already on disk as file /data1/clonedb/archive/1_12_872166459.dbf

archived log file name=/data1/clonedb/archive/1_6_872166459.dbf thread=1 sequence=6

archived log file name=/data1/clonedb/archive/1_7_872166459.dbf thread=1 sequence=7

archived log file name=/data1/clonedb/archive/1_8_872166459.dbf thread=1 sequence=8

archived log file name=/data1/clonedb/archive/1_9_872166459.dbf thread=1 sequence=9

archived log file name=/data1/clonedb/archive/1_10_872166459.dbf thread=1 sequence=10

media recovery complete, elapsed time: 00:00:01

Finished recover at 20-FEB-15

STEP 4: Start the database with resetlogs option.

RMAN> sql 'alter database open resetlogs';

sql statement: alter database open resetlogs

STEP 5: Connect to database and confirm that table is present.

We will now connect to the database to confirm that our table which we had earlier dropped has been restored as well as the record count in the table matches that taken before the table drop.

[oracle@NVMBD1BZY150D00 CLONEDB]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Fri Feb 20 17:59:52 2015

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select count(*) from myobjects;

  COUNT(*)

----------

     13420

STEP 6: Reset the database in recovery catalog.

Because we have done a RESETLOGS we need to reset the database in the recovery catalog.

[oracle@NVMBD1BZY150D00 CLONEDB]$ rman target / catalog rman/rman@rmanp

RMAN> reset database;

new incarnation of database registered in recovery catalog

starting full resync of recovery catalog

full resync complete

STEP 7: Standard Practice take full fresh backup of database.

We will now take a fresh level 0 incremental backup

RMAN> backup incremental level 0 database;

I hope this article helped you. Your suggestions/feedback are most welcome.

Keep learning... Have a great day!!!

RECOVER LOSS OF ALL CONTROLFILES (NO CATALOG)

In this session will be discussing scenario of recovering database from loss of all controlfile using rman.

STEP1: CREATE A SCENARIO FOR TEST PURPOSE.

[oracle@NVMBD1BZY150D00 clonedb]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Thu Feb 19 15:50:38 2015

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> conn oracle/oracle

Connected.

SQL>CREATE TABLE myobjects AS SELECT * FROM DBA_OBJECTS;

SQL> select table_name from user_tables;

TABLE_NAME

------------------------------

MYOBJECTS

SQL> insert into myobjects select * from myobjects;

919664 rows created.

SQL> commit;

Commit complete.

SQL> select count(*) from myobjects;

  COUNT(*)

----------

   1839328   >>>> need to check this record count after recovery

SQL> archive log list

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     5

Next log sequence to archive   7

Current log sequence           7

Note - current log sequence is 7 - not archived but contains the last committed changes that we made

Note - archive logs will not be found in $ARCV area, but in the flashback location

STEP2: STIMULATE A FAILURE

SQL> select name from v$controlfile;

NAME

-------------------------------------------------------------------------------

/u01/ORACLE/testdb/control01.ctl

/u01/ORACLE/testdb/control02.ctl

/u01/ORACLE/testdb/control03.ctl

SQL> !rm /u01/ORACLE/testdb/*.ctl

SQL> alter tablespace users online;

alter tablespace users online

*

ERROR at line 1:

ORA-00603: ORACLE server session terminated by fatal error.

STEP3: SHUT DONW THE DATABASE AND STARTUP IN NOMOUNT STAGE TO RECOVER CONTROL FILE.

SQL> shutdown abort

ORACLE instance shut down.

SQL> startup nomount;

ORACLE instance started.

Total System Global Area  893386752 bytes

Fixed Size                  2076816 bytes

Variable Size             432017264 bytes

Database Buffers          452984832 bytes

Redo Buffers                6307840 bytes

Since we are not using a RMAN catalog we need to set the DBID

RMAN> set dbid=2415549446;

executing command: SET DBID

STEP4: RESTORE THE CONTROLFILE.

RMAN> run {

2> restore controlfile from autobackup;

3> }

Starting restore at 18-SEP-14

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=156 devtype=DISK

recovery area destination: /u01/ORACLE/flash_recovery_area

database name (or database unique name) used for search: TESTDB

channel ORA_DISK_1: autobackup found in the recovery area

channel ORA_DISK_1: autobackup found: /u01/ORACLE/flash_recovery_area/TESTDB/autobackup/2007_09_18/o1_mf_s_633601094_3gynd74g_.bkp

channel ORA_DISK_1: control file restore from autobackup complete

output filename=/u01/ORACLE/testdb/control01.ctl

output filename=/u01/ORACLE/testdb/control02.ctl

output filename=/u01/ORACLE/testdb/control03.ctl

Finished restore at 18-SEP-07

STEP5: MOUNT AND RECOVER THE DATABASE;

RMAN> alter database mount;

database mounted

released channel: ORA_DISK_1

RMAN> recover database;

Starting recover at 18-SEP-07

Starting implicit crosscheck backup at 18-SEP-07

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=156 devtype=DISK

Crosschecked 1 objects

Finished implicit crosscheck backup at 18-SEP-07

Starting implicit crosscheck copy at 18-SEP-07

using channel ORA_DISK_1

Finished implicit crosscheck copy at 18-SEP-07

searching for all files in the recovery area

cataloging files...

cataloging done

List of Cataloged Files

=======================

File Name: /u01/ORACLE/flash_recovery_area/TESTDB/autobackup/2007_09_18/o1_mf_s_633601094_3gynd74g_.bkp

using channel ORA_DISK_1

allocated channel: ORA_SBT_TAPE_1

channel ORA_SBT_TAPE_1: sid=155 devtype=SBT_TAPE

channel ORA_SBT_TAPE_1: Data Protection for Oracle: version 5.2.4.0

datafile 4 not processed because file is offline

starting media recovery

archive log thread 1 sequence 6 is already on disk as file /u01/ORACLE/flash_recovery_area/TESTDB/archivelog/2007_09_18/o1_mf_1_6_3gyn7vnk_.arc

archive log thread 1 sequence 7 is already on disk as file /u01/ORACLE/testdb/redo03.log

archive log filename=/u01/ORACLE/flash_recovery_area/TESTDB/archivelog/2007_09_18/o1_mf_1_6_3gyn7vnk_.arc thread=1 sequence=6

archive log filename=/u01/ORACLE/testdb/redo03.log thread=1 sequence=7  >>>> current redo log with committed but unarchived changes applied

media recovery complete, elapsed time: 00:00:09

Finished recover at 18-SEP-07

STEP6: OPEN DATABASE WITH RESETLOGS OPTION.

SQL> alter database open resetlogs;

Database altered.

SQL> conn ORACLE/ORACLE

Connected.

SQL> select count(*) from myobjects;

  COUNT(*)

----------

   1839328

I hope this article helped you. Your suggestions/feedback are most welcome.

Keep learning... Have a great day!!!

Manually corrupting the data block in Linux and recovering it using BLOCKRECOVER command of RMAN

In order to test the RMAN’s  BLOCKRECOVER command, we need to corrupt the specific data block and recover it for testing purpose. To do it in Linux, use dd command. In the following example, let’s create a table and corrupt it manually

1. For the purpose of this practice, will create a separate tablespace and a new schema user.

a. Create a Tablespace say “test_corrupt”

[oracle@NVMBD1BZY150D00 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Fri Dec 19 18:09:36 2014

 

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create tablespace test_corrupt datafile '/data/oracle/app/oracle/oradata/TESTDB/test_corrupt.dbf' size 10m;

Tablespace created.

b. Create an user say “test” and assign quota to the “test_corrupt” tablespace

SQL> create user test identified by test123 default tablespace test_corrupt

quota unlimited on test_corrupt;

User created.

c. Grant the necessary privileges

SQL> grant create session, resource to test;

Grant succeeded.

 

2. Create a table as test user

SQL> conn test/test123

Connected.

SQL> create table emp(eno number(7)) tablespace test_corrupt;

Table created.

 

3. Insert some dummy records into the table by writing a simple ‘for loop’ block as shown:

SQL> begin

for i in 1..10000
loop
insert into emp values(i);
end loop;
end;
/

PL/SQL procedure successfully completed.

SQL> select count(*) from emp;

COUNT(*)

———-
10000

 

4. Take the RMAN backup, either full database or tablespace level backup before corrupting the block.

Connect to the RMAN

[oracle@NVMBD1BZY150D00 ~]$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Fri Dec 19 18:14:36 2014

 

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

 

connected to target database: TESTDB (DBID=2649358339)

Report the schema to know about the target database

RMAN> report schema;

using target database control file instead of recovery catalog

Report of database schema for database with db_unique_name TESTDB

 

List of Permanent Datafiles

===========================

File Size(MB) Tablespace           RB segs Datafile Name

---- -------- -------------------- ------- ------------------------

1    100      SYSTEM               ***     /data/oracle/app/oracle/oradata/TESTDB/system.dbf

2    100      SYSTEM               ***     /data/oracle/app/oracle/oradata/TESTDB/user04.dbf

3    100      SYSAUX               ***     /data/oracle/app/oracle/oradata/TESTDB/sysaux.dbf

4    500      UNDOTBS              ***     /data/oracle/app/oracle/oradata/TESTDB/undo.dbf

5    1024     TEST                 ***     /data/oracle/app/oracle/oradata/TESTDB/test1_tmp.dbf

6    10       TEST_CORRUPT         ***     /data/oracle/app/oracle/oradata/TESTDB/test_corrupt.dbf

 

List of Temporary Files

=======================

File Size(MB) Tablespace           Maxsize(MB) Tempfile Name

---- -------- -------------------- ----------- --------------------

1    1000     TEMP                 1000        /data/oracle/app/oracle/oradata/TESTDB/temp1.dbf

Take the backup of the tablespace

RMAN> backup tablespace test_corrupt;

Starting backup at 19-DEC-14

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=190 device type=DISK

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00006 name=/data/oracle/app/oracle/oradata/TESTDB/tes

channel ORA_DISK_1: starting piece 1 at 19-DEC-14

channel ORA_DISK_1: finished piece 1 at 19-DEC-14

piece handle=/data/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/0cpqitp8_1_1 ta

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

Finished backup at 19-DEC-14

 

RMAN>

Recovery Manager complete.

5. Manually corrupting datablock.

[oracle@NVMBD1BZY150D00 ~]$ ssqlplus / as sysdba

 

SQL*Plus: Release 11.2.0.3.0 Production on Fri Dec 19 18:16:14 2014

 

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

 

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

 

SQL> SELECT header_block FROM dba_segments WHERE segment_name='EMP';

HEADER_BLOCK

------------

130

So the header of segment (table) is block 130, so if the block 131,132,133,… get some noise it will eventually end up as a datafile with corrupted blocks

[oracle@NVMBD1BZY150D00 ~]$  dd of=/data/oracle/app/oracle/oradata/TESTDB/test_corrupt.dbf bs=8192 conv=notrunc seek=131 << EOF

>  > testing corruption

> > EOF

>

> -bash: warning: here-document at line 18 delimited by end-of-file (wanted `EOF')

0+1 records in

0+1 records out

29 bytes (29 B) copied, 3.1915e-05 s, 909 kB/s

 

6. To query the data, connect to the “test” user and query the table “emp”

SQL> ALTER SYSTEM FLUSH BUFFER_CACHE;

System altered.

 SQL> conn test/test123

Connected.

SQL> select * from emp;

select * from emp

              *

ERROR at line 1:

ORA-01578: ORACLE data block corrupted (file # 6, block # 131)

ORA-01110: data file 6:

'/data/oracle/app/oracle/oradata/TESTDB/test_corrupt.dbf'

The query returns complains of block corruption in file 6, and the block numbered 131 is being reported as corrupt.

This is the methods to be followed in order to corrupt the Oracle data blocks.

Lets us see, how to identify the corrupted blocks and recover them.

7. Let us see what are the blocks are corrupted in “test_corrupt01.dbf” datafile by running dbv (DBVERIFY) utility.

 [oracle@NVMBD1BZY150D00 ~]$ dbv file=/data/oracle/app/oracle/oradata/TESTDB/test_corrupt.dbf blocksize=8192

DBVERIFY: Release 11.2.0.3.0 - Production on Fri Dec 19 18:19:10 2014

 

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

DBVERIFY - Verification starting : FILE = /data/oracle/app/oracle/oradata/TESTDB/test_corrupt.dbf

Page 131 is marked corrupt

Corrupt block relative dba: 0x01800083 (file 6, block 131)

Bad header found during dbv:

Data in bad block:

 type: 32 format: 6 rdba: 0x69747365

 last change scn: 0x726f.6320676e seq: 0x72 flg: 0x75

 spare1: 0x20 spare2: 0x74 spare3: 0x6f69

 consistency value in tail: 0x5b5c0601

 check value in block header: 0x7470

 computed block checksum: 0xeb9b

 

 

DBVERIFY - Verification complete

 

Total Pages Examined         : 1280

Total Pages Processed (Data) : 19

Total Pages Failing   (Data) : 0

Total Pages Processed (Index): 0

Total Pages Failing   (Index): 0

Total Pages Processed (Other): 131

Total Pages Processed (Seg)  : 0

Total Pages Failing   (Seg)  : 0

Total Pages Empty            : 1129

Total Pages Marked Corrupt   : 1

Total Pages Influx           : 0

Total Pages Encrypted        : 0

Highest block SCN            : 678748 (0.678748)

This utility scans all the blocks in a given datafile and outputs the corrupt blocks. In this case, there was only one block marked as corrupt. Make a note of all the corrupted blocks as we need to recover them to previous state.

8. Start RMAN session and recover all the corrupted blocks.

The beauty of RMAN is that it recovers only corrupted blocks and we need to recover only those corrupt blocks instead of entire datafile.

[oracle@NVMBD1BZY150D00 ~]$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Fri Dec 19 18:22:41 2014

 

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

 

connected to target database: TESTDB (DBID=2649358339)

Report the Schema to know the status of target database, gathers the information from control file

RMAN> report schema;

using target database control file instead of recovery catalog

Report of database schema for database with db_unique_name TESTDB

 

List of Permanent Datafiles

===========================

File Size(MB) Tablespace           RB segs Datafile Name

---- -------- -------------------- ------- ------------------------

1    100      SYSTEM               ***     /data/oracle/app/oracle/oradata/TESTDB/system.dbf

2    100      SYSTEM               ***     /data/oracle/app/oracle/oradata/TESTDB/user04.dbf

3    100      SYSAUX               ***     /data/oracle/app/oracle/oradata/TESTDB/sysaux.dbf

4    500      UNDOTBS              ***     /data/oracle/app/oracle/oradata/TESTDB/undo.dbf

5    1024     TEST                 ***     /data/oracle/app/oracle/oradata/TESTDB/test1_tmp.dbf

6    10       TEST_CORRUPT         ***     /data/oracle/app/oracle/oradata/TESTDB/test_corrupt.dbf

 

List of Temporary Files

=======================

File Size(MB) Tablespace           Maxsize(MB) Tempfile Name

---- -------- -------------------- ----------- --------------------

1    1000     TEMP                 1000        /data/oracle/app/oracle/oradata/TESTDB/temp1.dbf

9. Recover the corrupted blocks using recover command

RMAN>  BLOCKRECOVER DATAFILE 6 BLOCK 131;

Starting recover at 19-DEC-14

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=197 device type=DISK

 

channel ORA_DISK_1: restoring block(s)

channel ORA_DISK_1: specifying block(s) to restore from backup set

restoring blocks of datafile 00006

channel ORA_DISK_1: reading from backup piece /data/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/0cpqitp8_1_1

channel ORA_DISK_1: piece handle=/data/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/0cpqitp8_1_1 tag=TAG20141219T181504

channel ORA_DISK_1: restored block(s) from backup piece 1

channel ORA_DISK_1: block restore complete, elapsed time: 00:00:01

 

starting media recovery

media recovery complete, elapsed time: 00:00:01

 

Finished recover at 19-DEC-14

RMAN>

Recovery Manager complete.

10. Will query the table again by logging in to the SQL*Plus:

SQL> conn test/test123

Connected.

SQL> select * from emp order by eno;

ENO

———-

1

2

3

…

…

…

9997

9998

9999

10000

10000 rows selected.

 

SQL> select count(*) from emp;

COUNT(*)

———-

10000

This will executes successfully and the records will be restored back to the table.

I hope this article helped you. Your suggestions/feedback are most welcome.

Keep learning... Have a great day!!!