| This guide will prepare you to run a simple RMAN shell script to perform a hot backup of the full database. In addition, you will learn few useful commands on how to list the RMAN binary backup files and on how to crosscheck if the backed up files are physically present on disk. |
- Loss of Controlfile, Redo & Datafiles
- Loss of a Non-System datafile
- Loss of System datafile
- Point In Time Recovery (PITR) using RMAN ('UNTIL TIME')
- Point In Time Recovery (PITR) using user-managed hot backup ('UNTIL TIME')
- Point In Time Recovery (PITR) using RMAN ('UNTIL SCN')
- Point In Time Recovery (PITR) using user-managed hot backup ('UNTIL CHANGE')
- Point In Time Recovery (PITR) using RMAN ('UNTIL SEQUENCE')
- Point In Time Recovery (PITR) using user-managed hot backup ('UNTIL CANCEL')
- Loss of Undo datafile
- Loss of Online Redo log file
- Loss of one Controlfile when Controlfiles are multiplexed
- Loss of all Controlfiles
- Recreating Controlfile from trace
|
| |
| 1. Loss of Controlfile, Redo & Datafiles |
| |
| First, let's take a RMAN hotbackup and user-managed hotbackup of the database before creating a scenario. |
| |
| |
| i) Let's do RMAN backup using the simple script as shown below: |
| |
[ ----------------------------------< code - expandable on mouseover >----------------------------------- ]
#!/bin/ksh
export ORACLE_HOME=/oracle/product/10.2.0/db_1
export ORACLE_SID=dbuat01
/oracle/product/10.2.0/db_1/bin/rman nocatalog <<EOF
connect target /
run
{
configure backup optimization on;
configure controlfile autobackup on;
configure controlfile autobackup format for device type disk to '/u02/oradata/backup/dbuat01/rman/dbuat01_control_%F';
delete noprompt obsolete redundancy 1;
allocate channel bkp1 device type disk;
allocate channel bkp2 device type disk;
change archivelog all crosscheck;
backup as compressed backupset format '/u02/oradata/backup/dbuat01/rman/%d_rman_%T_%t_%u_%s_%p' database plus archivelog;
backup current controlfile;
sql "alter database backup controlfile to trace as ''/u02/oradata/backup/dbuat01/rman/dbuat01_controlfile_trace.txt'' reuse";
release channel bkp1;
release channel bkp2;
delete noprompt obsolete redundancy 1;
}
exit
EOF
cp -f $ORACLE_HOME/dbs/initdbuat01.ora /u02/oradata/backup/dbuat01/rman
cp -f $ORACLE_HOME/dbs/spfiledbuat01.ora /u02/oradata/backup/dbuat01/rman
cp -f $ORACLE_HOME/dbs/orapwdbuat01 /u02/oradata/backup/dbuat01/rman
cp -f $ORACLE_HOME/network/admin/listener.ora /u02/oradata/backup/dbuat01/rman
cp -f $ORACLE_HOME/network/admin/tnsnames.ora /u02/oradata/backup/dbuat01/rman |
| |
| ii) Let's do a user-managed hotbackup as shown below: |
| |
$ export ORACLE_SID=dbuat01
$ sqlplus / as sysdba
|
SQL> alter database begin backup;
|
$ cp /u01/oradata/dbuat01/data/*dbf /u02/oradata/backup/dbuat01/hotbkp/
$ cp /u01/oradata/dbuat01/data/*ctl /u02/oradata/backup/dbuat01/hotbkp/
$ sqlplus / as sysdba
|
SQL> alter database end backup;
SQL> alter system archive log current;
|
| |
| iii) In order to create a scenario, we will manually delete all controlfiles, redologs and datafiles when the database is up and running. |
| |
$ rm -f /u01/oradata/dbuat01/data/*dbf
$ rm -f /u01/oradata/dbuat01/control/*ctl
$ rm -f /u01/oradata/dbuat01/redo/*log
|
| |
| iv) Now, let's try connecting to the database. You must see something as shown below when you query V$DATABASE. |
| |
$ sqlplus / as sysdba
SQL> select name from v$database;
select name from v$database
*
ERROR at line 1:
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/u01/oradata/dbuat01/control/control01.ctl'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
SQL> shutdown abort
|
| |
| v) Now, let's try connecting to the database. You must see something as shown below when you query V$DATABASE. |
| |
|
[ ----------------------------------< code - expandable on mouseover >----------------------------------- ]
RMAN> startup nomount;
RMAN> restore controlfile from '/u02/oradata/backup/dbuat01/rman/dbuat01_control_c-2691765368-20120709-00';
Starting restore at 09-JUL-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=761 devtype=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
output filename=/u01/oradata/dbuat01/control/control01.ctl
output filename=/u01/oradata/dbuat01/control/control02.ctl
output filename=/u01/oradata/dbuat01/control/control03.ctl
Finished restore at 09-JUL-12
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
RMAN> restore database;
Starting restore at 09-JUL-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=761 devtype=DISK
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00003 to /u01/oradata/dbuat01/data/sysaux1.dbf
channel ORA_DISK_1: reading from backup piece /u02/oradata/backup/dbuat01/rman/DBUAT01_rman_20120709_788201636_04nfm054_4_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/u02/oradata/backup/dbuat01/rman/DBUAT01_rman_20120709_788201636_04nfm054_4_1 tag=TAG20120709T165356
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u01/oradata/dbuat01/data/system1.dbf
restoring datafile 00002 to /u01/oradata/dbuat01/data/undotbs1.dbf
channel ORA_DISK_1: reading from backup piece /u02/oradata/backup/dbuat01/rman/DBUAT01_rman_20120709_788201636_03nfm054_3_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/u02/oradata/backup/dbuat01/rman/DBUAT01_rman_20120709_788201636_03nfm054_3_1 tag=TAG20120709T165356
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
Finished restore at 09-JUL-12
RMAN> recover database until logseq 15;
Starting recover at 09-JUL-12
using channel ORA_DISK_1
starting media recovery
archive log thread 1 sequence 11 is already on disk as file /u01/oradata/dbuat01/archive/dbuat01_1_11_788199864.arc
archive log filename=/u01/oradata/dbuat01/archive/dbuat01_1_11_788199864.arc thread=1 sequence=11
archive log filename=/u01/oradata/dbuat01/archive/dbuat01_1_12_788199864.arc thread=1 sequence=12
archive log filename=/u01/oradata/dbuat01/archive/dbuat01_1_13_788199864.arc thread=1 sequence=13
archive log filename=/u01/oradata/dbuat01/archive/dbuat01_1_14_788199864.arc thread=1 sequence=14
media recovery complete, elapsed time: 00:00:03
Finished recover at 09-JUL-12
RMAN> alter database open resetlogs;
Database opened.
|
| |
| vi) Alternatively, the recovery can be performed using user-managed hotbackup: |
| |
$ cd /u01/oradata/dbuat01/control/; cp /u02/oradata/backup/dbuat01/hotbkp/*ctl .
$ cd /u01/oradata/dbuat01/data/; cp /u02/oradata/backup/dbuat01/hotbkp/*dbf .
$ sqlplus / as sysdba
|
SQL> startup mount;
SQL> alter database recover automatic using backup controlfile until cancel;
alter database recover automatic using backup controlfile until cancel
*
ERROR at line 1:
ORA-00279: change 152214 generated at 07/09/2012 18:05:41 needed for thread 1
ORA-00289: suggestion : /u01/oradata/dbuat01/archive/dbuat01_1_7_788205231.arc
ORA-00280: change 152214 for thread 1 is in sequence #7
ORA-00278: log file '/u01/oradata/dbuat01/archive/dbuat01_1_7_788205231.arc' no longer needed for this recovery
ORA-00308: cannot open archived log '/u01/oradata/dbuat01/archive/dbuat01_1_7_788205231.arc'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
SQL> recover cancel;
Media recovery complete.
|
| |
| [or] |
| |
| Alternatively, when the instance is in mount stage use the V$LOG view to know the FIRST_CHANGE# number of the last redo logfile and use the following method. |
| |
SQL> alter database recover automatic using backup controlfile until change 386320;
SQL> alter database open resetlogs;
Database altered.
|
| |
| |
| 2. Loss of a Non-System datafile |
| |
| i) First, let's populate some data into 'USERS' tablespace and don't forget to force few log switches thereafter. |
| |
| ii) In order to create a scenario, let's delete the datafile 'users1.dbf' beloging to 'USERS' tablespace. |
| |
$ rm -f /u01/oradata/dbuat01/data/users1.dbf
$ sqlplus scott/tiger
|
SQL> select * from test;
select * from test
*
ERROR at line 1:
ORA-01116: error in opening database file 4
ORA-01110: data file 4: '/u01/oradata/dbuat01/data/users1.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
SQL> conn / as sysdba
SQL> alter database datafile '/u01/oradata/dbuat01/data/users1.dbf' offline;
Database altered.
|
| |
| iii) Let's look at how we can recover the missing datafile (using RMAN): |
| |
|
[ ----------------------------------< code - expandable on mouseover >----------------------------------- ]
RMAN> restore datafile 4;
Starting restore at 09-JUL-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=750 devtype=DISK
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00004 to /u01/oradata/dbuat01/data/users1.dbf
channel ORA_DISK_1: reading from backup piece /u02/oradata/backup/dbuat01/rman/DBUAT01_rman_20120709_788207368_0fnfm5o8_15_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/u02/oradata/backup/dbuat01/rman/DBUAT01_rman_20120709_788207368_0fnfm5o8_15_1 tag=TAG20120709T182927
channel ORA_DISK_1: restore complete, elapsed time: 00:00:04
Finished restore at 09-JUL-12
RMAN> recover datafile 4;
Starting recover at 09-JUL-12
using channel ORA_DISK_1
starting media recovery
archive log thread 1 sequence 9 is already on disk as file /u01/oradata/dbuat01/archive/dbuat01_1_9_788207051.arc
archive log thread 1 sequence 10 is already on disk as file /u01/oradata/dbuat01/archive/dbuat01_1_10_788207051.arc
archive log thread 1 sequence 11 is already on disk as file /u01/oradata/dbuat01/archive/dbuat01_1_11_788207051.arc
archive log thread 1 sequence 12 is already on disk as file /u01/oradata/dbuat01/archive/dbuat01_1_12_788207051.arc
archive log filename=/u01/oradata/dbuat01/archive/dbuat01_1_9_788207051.arc thread=1 sequence=9
archive log filename=/u01/oradata/dbuat01/archive/dbuat01_1_10_788207051.arc thread=1 sequence=10
media recovery complete, elapsed time: 00:00:02
Finished recover at 09-JUL-12
|
|
SQL> alter database datafile '/u01/oradata/dbuat01/data/users1.dbf' online;
Database altered.
|
| |
| iv) How about using user-managed hotbackup: |
| |
$ cd /u01/oradata/dbuat01/data; cp /u02/oradata/backup/dbuat01/backup/users1.dbf .
$ sqlplus / as sysdba
|
SQL> recover datafile 4;
ORA-00279: change 151699 generated at 07/09/2012 19:10:07 needed for thread 1
ORA-00289: suggestion : /u01/oradata/dbuat01/archive/dbuat01_1_1_788209720.arc
ORA-00280: change 151699 for thread 1 is in sequence #1
Specify log: {=suggested | filename | AUTO | CANCEL}
ORA-00279: change 151749 generated at 07/09/2012 19:11:59 needed for thread 1
ORA-00289: suggestion : /u01/oradata/dbuat01/archive/dbuat01_1_2_788209720.arc
ORA-00280: change 151749 for thread 1 is in sequence #2
ORA-00278: log file '/u01/oradata/dbuat01/archive/dbuat01_1_1_788209720.arc' no longer needed for this recovery
Specify log: {=suggested | filename | AUTO | CANCEL}
Log applied.
Media recovery complete.
SQL> alter database datafile '/u01/oradata/dbuat01/data/users1.dbf' online;
Database altered.
|
| |
| |
| 3. Loss of System datafile |
| |
| Following is a simple RMAN shell script to backup full database including archivelogs using one day retention. |
| |
| i) Populate some data inside system tablespace and force a few logswitches. |
| |
| ii) In order to create a scenario, let's delete the datafile 'tbs_system_01.dbf' beloging to 'system' tablespace. |
| |
$ cd /u01/oradata/dbuat01/data
$ rm -f tbs_system_01.dbf
$ sqlplus / as sysdba
|
SQL> select * from test1;
select * from test1
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01116: error in opening database file 1
ORA-01110: data file 1: '/u02/oradata/dbuat01/data/tbs_system_01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
SQL> shut abort
|
| |
| iii) Recovery using RMAN: |
| |
[ ----------------------------------< code - expandable on mouseover >----------------------------------- ]
RMAN> startup mount;
RMAN> restore datafile 1;
Starting restore at 10-JUL-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=133 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 /u02/oradata/dbuat01/data/tbs_system_01.dbf
channel ORA_DISK_1: reading from backup piece /u02/oradata/backup/dbuat01/rman/DBUAT01_rman_bkp_20120710_788261650_3onfnqoi_120_1
channel ORA_DISK_1: piece handle=/u02/oradata/backup/dbuat01/rman/DBUAT01_rman_bkp_20120710_788261650_3onfnqoi_120_1 tag=TAG20120710T093409
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
Finished restore at 10-JUL-12
RMAN> recover database;
Starting recover at 10-JUL-12
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 58 is already on disk as file /u02/oradata/dbuat01/archived_logs/dbuat01_1_58_786023887.arc
archived log for thread 1 with sequence 59 is already on disk as file /u02/oradata/dbuat01/archived_logs/dbuat01_1_59_786023887.arc
archived log for thread 1 with sequence 60 is already on disk as file /u02/oradata/dbuat01/archived_logs/dbuat01_1_60_786023887.arc
archived log file name=/u02/oradata/dbuat01/archived_logs/dbuat01_1_58_786023887.arc thread=1 sequence=58
media recovery complete, elapsed time: 00:00:01
Finished recover at 10-JUL-12
RMAN> alter database open;
database opened
|
| |
| iv) Recovery using user-managed hotbackup: |
| |
$ cd /u02/oradata/dbuat01/data; cp /u02/oradata/backup/dbuat01/hotbkp/tbs_system_01.dbf .
$ sqlplus / as sysdba
|
SQL> startup mount;
SQL> recover database;
Media recovery complete.
SQL> alter database open;
Database altered.
|
| |
| |
| 4. Point In Time Recovery (PITR) using RMAN ('UNTIL TIME') |
| |
| Following is a simple RMAN shell script to backup full database including archivelogs using one day retention. |
| |
| i) Let's first populate some user data ('SCOTT.EMPLOYEES'): |
| |
SQL> create table employees (
2 emp_id varchar2(4 byte) not null,
3 emp_f_name varchar2(10 byte),
4 emp_l_name varchar2(10 byte),
5 emp_doj date,
6 emp_salary number(8,2),
7 emp_city varchar2(10 byte)
)
/
SQL> insert into employees values (01,'jason','martin','25-jul-1996',1234.56,'mumbai');
SQL> insert into employees values (02,'mathews','alison','21-mar-1976',4322.55,'chennai');
SQL> insert into employees values (03,'smith','james','12-dec-1978',7897.78,'kolkata');
SQL> insert into employees values (04,'rice','celia','30-jul-1987',6544.65,'bangalore');
SQL> insert into employees values (05,'larry','david','17-sep-1996',1232.35,'noida');
SQL> commit;
SQL> select * from employees;
EMP_ EMP_F_NAME EMP_L_NAME EMP_DOJ EMP_SALARY EMP_CITY
---- ---------- ---------- -------------------- ---------- ----------
1 jason martin 25-jul-1996 00:00:00 1234.56 mumbai
2 mathews alison 21-mar-1976 00:00:00 4322.55 chennai
3 smith james 12-dec-1978 00:00:00 7897.78 kolkata
4 rice celia 30-jul-1987 00:00:00 6544.65 bangalore
5 larry david 17-sep-1996 00:00:00 1232.35 noida
|
| |
| ii) Let's now grab the V$LOG |
| |
SQL> conn / as sysdba
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
-------- ---------- ---------- ---------- ---------- --- ------------- ------------- --------------------
1 1 1 52428800 2 NO CURRENT 183093 10-jul-2012 17:09:25
2 1 0 52428800 2 YES UNUSED 0
3 1 0 52428800 2 YES UNUSED 0
|
| |
| The view shows that the populated 'SCOTT.EMPLOYEES' redo data is held in the current logseq# 1. |
| |
| iii) Now, after a couple of log switches, it should now look as below: |
| |
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
-------- ---------- ---------- ---------- ---------- --- ------------- ------------- --------------------
1 1 1 52428800 2 YES ACTIVE 183093 10-jul-2012 17:09:25
2 1 2 52428800 2 YES ACTIVE 184123 10-jul-2012 17:36:59
3 1 3 52428800 2 NO CURRENT 184133 10-jul-2012 17:37:24
|
| |
| iv) Now, let's perform RMAN backup (refer my other posts on how to perform RMAN backup). after the RMAN backup, V$LOG might look like this: |
| |
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
--------- ---------- ---------- ---------- ---------- --- -------------- ------------- --------------------
1 1 4 52428800 2 YES INACTIVE 184453 10-jul-2012 17:47:36
2 1 5 52428800 2 NO CURRENT 184464 10-jul-2012 17:47:48
3 1 3 52428800 2 YES INACTIVE 184133 10-jul-2012 17:37:24
|
| |
| RMAN would have backed up till logseq# 4 along with the database backup. To crosscheck, use: |
| |
RMAN> list backup of archivelog all;
using target database control file instead of recovery catalog
List of Backup Sets
===================
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
54 3.00K DISK 00:00:01 10-JUL-12
BP Key: 54 Status: AVAILABLE Compressed: YES Tag: TAG20120710T174748
Piece Name: /u02/oradata/backup/dbuat01/rman/DBUAT01_rman_20120710_788291268_20nfonm4_64_1
List of Archived Logs in backup set 54
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 4 184453 10-JUL-12 184464 10-JUL-12
|
| |
| v) Now, let's create a scenario: |
| |
SQL> conn scott/tiger
Connected.
SQL> select * from employees;
EMP_ EMP_F_NAME EMP_L_NAME EMP_DOJ EMP_SALARY EMP_CITY
---- ---------- ---------- -------------------- ---------- ----------
1 jason martin 25-jul-1996 00:00:00 1234.56 mumbai
2 mathews alison 21-mar-1976 00:00:00 4322.55 chennai
3 smith james 12-dec-1978 00:00:00 7897.78 kolkata
4 rice celia 30-jul-1987 00:00:00 6544.65 bangalore
5 larry david 17-sep-1996 00:00:00 1232.35 noida
SQL> delete employees;
5 rows deleted.
SQL> commit;
Commit complete.
SQL> conn / as sysdba
SQL> alter system archive log current;
System altered.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ------------- ------------- --------------------
1 1 4 52428800 2 YES INACTIVE 184453 10-jul-2012 17:47:36
2 1 5 52428800 2 YES ACTIVE 184464 10-jul-2012 17:47:48
3 1 6 52428800 2 NO CURRENT 185087 10-jul-2012 18:13:30
|
| |
| Note: Remember that logseq# 5 will have vector change of SQL> DELETE EMPLOYEES;. Now, let's make some more changes in the 'SCOTT.EMPLOYEES' table. of course, let's insert some fresh data. it now looks like this: |
| |
SQL> select * from employees;
EMP_ EMP_F_NAME EMP_L_NAME EMP_DOJ EMP_SALARY EMP_CITY
---- ---------- ---------- -------------------- ---------- ----------
6 arun shanmug 17-feb-2011 00:00:00 1232.35 chennai
7 aryan arun 17-jan-2012 00:00:00 1232.35 mumbai
|
| |
| Now, let's grab V$LOG status. |
| |
SQL> conn / as sysdba
Connected.
SQL> alter system archive log current;
System altered.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
--------- ---------- ---------- ---------- ---------- --- -------------- ------------- --------------------
1 1 7 52428800 2 NO CURRENT 185210 10-jul-2012 18:19:28
2 1 5 52428800 2 YES INACTIVE 184464 10-jul-2012 17:47:48
3 1 6 52428800 2 YES ACTIVE 185087 10-jul-2012 18:13:30
|
| |
| Note: Remember that logseq# 6 will have vector change of the two fresh records inserted. Now, let's make some more changes in the 'SCOTT.EMPLOYEES' table. this time let's drop the table itself. |
| |
SQL> conn scott/tiger
Connected.
SQL> drop table employees;
Table dropped.
SQL> conn / as sysdba
SQL> alter system archive log current;
System altered.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
--------- ---------- ---------- ---------- ---------- --- -------------- ------------- --------------------
1 1 7 52428800 2 YES ACTIVE 185210 10-jul-2012 18:19:28
2 1 8 52428800 2 NO CURRENT 185272 10-jul-2012 18:21:47
3 1 6 52428800 2 YES ACTIVE 185087 10-jul-2012 18:13:30
|
| |
| Note: Logseq# 7 will have vector change of the SQL> DROP TABLE EMPLOYEES;. Now, let's prepare for recovery to ensure that we recover the two records inserted a short while ago (that means, we must recover UNTIL TIME '10-jul-2012 18:19:28') just before dropping the table using RMAN backup and available archivelogs. |
| |
| Few things to recall before crashing the database: |
- In the RMAN backup, only the logseq# 4 is available along with the database backup.
- In the actual archive destination (/u01/oradata/dbuat01/archive), log sequences between logseq# 4 and logseq# 7 are still available.
|
$ cd /u01/oradata/dbuat01/archive
$ ls -lrt
-rw-r----- 1 ora10g dba 2048 Jul 10 17:47 dbuat01_1_4_788288965.arc
-rw-r----- 1 ora10g dba 80896 Jul 10 18:13 dbuat01_1_5_788288965.arc
-rw-r----- 1 ora10g dba 7680 Jul 10 18:19 dbuat01_1_6_788288965.arc
-rw-r----- 1 ora10g dba 9728 Jul 10 18:21 dbuat01_1_7_788288965.arc
|
|
$ rman target /
RMAN> set DBID=2691765368
executing command: SET DBID
RMAN> startup nomount;
RMAN> restore controlfile from '/u02/oradata/backup/dbuat01/rman/dbuat01_control_c-2691765368-20120710-07';
Starting restore at 10-JUL-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=761 devtype=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
output filename=/u01/oradata/dbuat01/control/control01.ctl
output filename=/u01/oradata/dbuat01/control/control02.ctl
output filename=/u01/oradata/dbuat01/control/control03.ctl
Finished restore at 10-JUL-12
RMAN> alter database mount;
|
| |
| Note: After mounting the database, if you query V$LOG it might show as: |
| |
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
--------- ---------- ---------- ---------- ---------- --- -------------- ------------- --------------------
1 1 4 52428800 2 YES INACTIVE 184453 10-jul-2012 17:47:36
3 1 3 52428800 2 YES INACTIVE 184133 10-jul-2012 17:37:24
2 1 5 52428800 2 NO CURRENT 184464 10-jul-2012 17:47:48
|
| |
| Note: After mounting the database, if you query V$LOG it might show as: |
| |
[ ----------------------------------< code - expandable on mouseover >----------------------------------- ]
RMAN> run {
2> set until time = "to_date('10-jul-2012 18:19:28','dd-mon-yyyy hh24:mi:ss')";
3> restore database;
4> recover database;
5> }
executing command: SET until clause
Starting restore at 10-JUL-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=760 devtype=DISK
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00003 to /u01/oradata/dbuat01/data/sysaux1.dbf
restoring datafile 00004 to /u01/oradata/dbuat01/data/users1.dbf
channel ORA_DISK_1: reading from backup piece /u02/oradata/backup/dbuat01/rman/DBUAT01_rman_20120710_788291261_1vnfonlt_63_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/u02/oradata/backup/dbuat01/rman/DBUAT01_rman_20120710_788291261_1vnfonlt_63_1 tag=TAG20120710T174741
channel ORA_DISK_1: restore complete, elapsed time: 00:00:08
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u01/oradata/dbuat01/data/system1.dbf
restoring datafile 00002 to /u01/oradata/dbuat01/data/undotbs1.dbf
channel ORA_DISK_1: reading from backup piece /u02/oradata/backup/dbuat01/rman/DBUAT01_rman_20120710_788291261_1unfonlt_62_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/u02/oradata/backup/dbuat01/rman/DBUAT01_rman_20120710_788291261_1unfonlt_62_1 tag=TAG20120710T174741
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
Finished restore at 10-JUL-12
Starting recover at 10-JUL-12
using channel ORA_DISK_1
starting media recovery
archive log thread 1 sequence 4 is already on disk as file /u01/oradata/dbuat01/archive/dbuat01_1_4_788288965.arc
archive log filename=/u01/oradata/dbuat01/archive/dbuat01_1_4_788288965.arc thread=1 sequence=4
archive log filename=/u01/oradata/dbuat01/archive/dbuat01_1_5_788288965.arc thread=1 sequence=5
archive log filename=/u01/oradata/dbuat01/archive/dbuat01_1_6_788288965.arc thread=1 sequence=6
media recovery complete, elapsed time: 00:00:02
Finished recover at 10-JUL-12
|
| |
| As you can see, RMAN stopped archivelog apply after logseq# 6, because the timestamp '10-JUL-2012 18:19:28' is the first change_time of logseq# 7. |
| |
RMAN> alter database open resetlogs;
Database opened.
|
| |
| After, 'RESETLOGS' logseq# is reset to 1: |
| |
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ------------- ------------- --------------------
1 1 1 52428800 2 NO CURRENT 185211 10-jul-2012 18:42:56
2 1 0 52428800 2 YES UNUSED 0
3 1 0 52428800 2 YES UNUSED 0
|
| |
| We aren't done yet! Now finally, let's verify 'SCOTT.EMPLOYEES' table |
| |
SQL> conn scott/tiger
Connected.
SQL> select * from employees;
EMP_ EMP_F_NAME EMP_L_NAME EMP_DOJ EMP_SALARY EMP_CITY
---- ---------- ---------- -------------------- ---------- ----------
6 arun shanmug 17-feb-2011 00:00:00 1232.35 chennai
7 aryan arun 17-jan-2012 00:00:00 1232.35 mumbai
|
| |
| Voila! we did it!!! |
| |
| |
| 5. Point In Time Recovery (PITR) using user-managed hot backup ('UNTIL TIME') |
| |
| i) Let's first populate some user data ('SCOTT.EMPLOYEES'): |
| |
SQL> create table employees (
2 emp_id varchar2(4 byte) not null,
3 emp_f_name varchar2(10 byte),
4 emp_l_name varchar2(10 byte),
5 emp_doj date,
6 emp_salary number(8,2),
7 emp_city varchar2(10 byte)
)
/
SQL> insert into employees values (01,'jason','martin','25-jul-1996',1234.56,'mumbai');
SQL> insert into employees values (02,'mathews','alison','21-mar-1976',4322.55,'chennai');
SQL> insert into employees values (03,'smith','james','12-dec-1978',7897.78,'kolkata');
SQL> insert into employees values (04,'rice','celia','30-jul-1987',6544.65,'bangalore');
SQL> insert into employees values (05,'larry','david','17-sep-1996',1232.35,'noida');
SQL> commit;
SQL> select * from employees;
EMP_ EMP_F_NAME EMP_L_NAME EMP_DOJ EMP_SALARY EMP_CITY
---- ---------- ---------- -------------------- ---------- ----------
1 jason martin 25-jul-1996 00:00:00 1234.56 mumbai
2 mathews alison 21-mar-1976 00:00:00 4322.55 chennai
3 smith james 12-dec-1978 00:00:00 7897.78 kolkata
4 rice celia 30-jul-1987 00:00:00 6544.65 bangalore
5 larry david 17-sep-1996 00:00:00 1232.35 noida
|
| |
| ii) Let's now grab the V$LOG |
| |
SQL> conn / as sysdba
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
-------- ---------- ---------- ---------- ---------- --- -------------- ------------- --------------------
1 1 1 52428800 2 NO CURRENT 187163 10-jul-2012 19:57:32
2 1 0 52428800 2 YES UNUSED 0
3 1 0 52428800 2 YES UNUSED 0
|
| |
| The view shows that the populated 'SCOTT.EMPLOYEES' redo data resides in the current logseq# 1. |
| |
| iii) Now, after a couple of log switches, it should now look as below: |
| |
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
--------- ---------- ---------- ---------- ---------- --- ------------- ------------- --------------------
1 1 1 52428800 2 YES ACTIVE 187163 10-jul-2012 19:57:32
2 1 2 52428800 2 YES ACTIVE 221183 11-jul-2012 19:02:22
3 1 3 52428800 2 NO CURRENT 221189 11-jul-2012 19:02:34
|
| |
| |
| iv) Now, let's perform user-managed hotbackup (refer my other posts on how to perform user-managed hotbackup). |
| |
| |
| v) Vow, let's create a scenario: |
| |
SQL> conn scott/tiger
Connected.
SQL> select * from employees;
EMP_ EMP_F_NAME EMP_L_NAME EMP_DOJ EMP_SALARY EMP_CITY
---- ---------- ---------- -------------------- ---------- ----------
1 jason martin 25-jul-1996 00:00:00 1234.56 mumbai
2 mathews alison 21-mar-1976 00:00:00 4322.55 chennai
3 smith james 12-dec-1978 00:00:00 7897.78 kolkata
4 rice celia 30-jul-1987 00:00:00 6544.65 bangalore
5 larry david 17-sep-1996 00:00:00 1232.35 noida
SQL> delete employees;
5 rows deleted.
SQL> commit;
Commit complete.
SQL> conn / as sysdba
SQL> alter system archive log current;
System altered.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
-------- ---------- ---------- ---------- ---------- --- -------------- ------------- --------------------
1 1 4 52428800 2 NO CURRENT 221411 11-jul-2012 19:12:54
2 1 2 52428800 2 YES INACTIVE 221183 11-jul-2012 19:02:22
3 1 3 52428800 2 YES ACTIVE 221189 11-jul-2012 19:02:34
|
| |
| Note: Remember that logseq# 3 will have vector change of 'SQL> DELETE EMPLOYEES;'. Now, let's make some more changes in the 'SCOTT.EMPLOYEES' table. Of course, let's insert some fresh data. It now looks like this: |
| |
SQL> select * from employees;
EMP_ EMP_F_NAME EMP_L_NAME EMP_DOJ EMP_SALARY EMP_CITY
---- ---------- ---------- -------------------- ---------- ----------
6 arun shanmug 17-feb-2011 00:00:00 1232.35 chennai
7 aryan arun 17-jan-2012 00:00:00 2132.53 mumbai
|
| |
| Now, let's grab V$LOG status. |
| |
SQL> conn / as sysdba
Connected.
SQL> alter system archive log current;
System altered.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
-------- ---------- ---------- ---------- ---------- --- ------------- ------------- --------------------
1 1 4 52428800 2 YES ACTIVE 221411 11-jul-2012 19:12:54
2 1 5 52428800 2 NO CURRENT 221518 11-jul-2012 19:18:00
3 1 3 52428800 2 YES INACTIVE 221189 11-jul-2012 19:02:34
|
| |
| Note: Remember that logseq# 4 will have vector change of the two fresh records inserted. Now, let's make some more changes in the 'SCOTT.EMPLOYEES' table. This time let's drop the table itself. |
| |
SQL> conn scott/tiger
Connected.
SQL> drop table employees;
Table dropped.
SQL> conn / as sysdba
SQL> alter system archive log current;
System altered.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
-------- ---------- ---------- ---------- ---------- --- -------------- ------------- --------------------
1 1 4 52428800 2 YES ACTIVE 221411 11-jul-2012 19:12:54
2 1 5 52428800 2 YES ACTIVE 221518 11-jul-2012 19:18:00
3 1 6 52428800 2 NO CURRENT 221561 11-jul-2012 19:19:22
|
| |
| Note: logseq# 5 will have vector change of the SQL> DROP TABLE EMPLOYEES;. now, let's prepare for recovery to ensure that we recover the two records inserted a short while ago (that means, we must recover UNTIL TIME '11-JUL-2012 19:18:00') just before dropping the table using the user-managed backup and available archivelogs. |
| |
| Few things to recall before crashing the database: |
- In the actual archive destination (/u01/oradata/dbuat01/archive), ensure that log sequences between logseq# 1 and logseq# 5 are still available.
|
$ cd /u01/oradata/dbuat01/archive
$ ls -lrt
-rw-r----- 1 ora10g dba 23560704 Jul 11 19:02 dbuat01_1_1_788299052.arc
-rw-r----- 1 ora10g dba 1024 Jul 11 19:02 dbuat01_1_2_788299052.arc
-rw-r----- 1 ora10g dba 47104 Jul 11 19:12 dbuat01_1_3_788299052.arc
-rw-r----- 1 ora10g dba 5120 Jul 11 19:18 dbuat01_1_4_788299052.arc
-rw-r----- 1 ora10g dba 9728 Jul 11 19:19 dbuat01_1_5_788299052.arc
|
|
$ cd /u01/oradata/dbuat01/data; rm -f *dbf
$ cd /u01/oradata/dbuat01/control; rm -f *ctl
$ cd /u01/oradata/dbuat01/redo; rm -f *log
$ cd /u01/oradata/dbuat01/data; cp /u02/oradata/backup/dbuat01/hotbkp/*dbf .
$ cd /u01/oradata/dbuat01/control; cp /u02/oradata/backup/dbuat01/hotbkp/*ctl .
|
SQL> startup mount;
SQL> recover database until time '2012-jul-11:19:18:00' using backup controlfile;
ORA-00279: change 221242 generated at 07/11/2012 19:05:12 needed for thread 1
ORA-00289: suggestion : /u01/oradata/dbuat01/archive/dbuat01_1_3_788299052.arc
ORA-00280: change 221242 for thread 1 is in sequence #3
Specify log: {=suggested | filename | AUTO | CANCEL}
ORA-00279: change 221411 generated at 07/11/2012 19:12:54 needed for thread 1
ORA-00289: suggestion : /u01/oradata/dbuat01/archive/dbuat01_1_4_788299052.arc
ORA-00280: change 221411 for thread 1 is in sequence #4
ORA-00278: log file '/u01/oradata/dbuat01/archive/dbuat01_1_3_788299052.arc' no longer needed for this recovery
Specify log: {=suggested | filename | AUTO | CANCEL}
Log applied.
Media recovery complete.
SQL> alter database open resetlogs;
Database altered.
|
| |
| Now, SCOTT.EMPLOYEES must have the two records inserted just before dropping the table itself. Let's check out. |
| |
SQL> conn scott/tiger
Connected.
SQL> select * from employees;
EMP_ EMP_F_NAME EMP_L_NAME EMP_DOJ EMP_SALARY EMP_CITY
---- ---------- ---------- -------------------- ---------- ----------
6 arun shanmug 17-feb-2011 00:00:00 1232.35 chennai
7 aryan arun 17-jan-2012 00:00:00 2132.53 mumbai
|
| |
| |
| 6. Point In Time Recovery (PITR) using RMAN ('UNTIL SCN') |
| |
| |
| i) Let's first populate some user data ('SCOTT.EMPLOYEES'): |
| |
SQL> create table employees (
2 emp_id varchar2(4 byte) not null,
3 emp_f_name varchar2(10 byte),
4 emp_l_name varchar2(10 byte),
5 emp_doj date,
6 emp_salary number(8,2),
7 emp_city varchar2(10 byte)
)
/
SQL> insert into employees values (01,'jason','martin','25-jul-1996',1234.56,'mumbai');
SQL> insert into employees values (02,'mathews','alison','21-mar-1976',4322.55,'chennai');
SQL> insert into employees values (03,'smith','james','12-dec-1978',7897.78,'kolkata');
SQL> insert into employees values (04,'rice','celia','30-jul-1987',6544.65,'bangalore');
SQL> insert into employees values (05,'larry','david','17-sep-1996',1232.35,'noida');
SQL> commit;
SQL> select * from employees;
EMP_ EMP_F_NAME EMP_L_NAME EMP_DOJ EMP_SALARY EMP_CITY
---- ---------- ---------- -------------------- ---------- ----------
1 jason martin 25-jul-1996 00:00:00 1234.56 mumbai
2 mathews alison 21-mar-1976 00:00:00 4322.55 chennai
3 smith james 12-dec-1978 00:00:00 7897.78 kolkata
4 rice celia 30-jul-1987 00:00:00 6544.65 bangalore
5 larry david 17-sep-1996 00:00:00 1232.35 noida
|
| |
| |
| ii) let's now grab the V$LOG |
| |
SQL> conn / as sysdba
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
-------- ---------- ---------- ---------- ---------- --- -------------- ------------- --------------------
1 1 1 52428800 2 NO CURRENT 185211 10-jul-2012 18:42:56
2 1 0 52428800 2 YES UNUSED 0
3 1 0 52428800 2 YES UNUSED 0
|
| |
| The view shows that the populated 'SCOTT.EMPLOYEES' redo data resides in the current logseq# 1. |
| |
| |
| iii) now, after a couple of switches, it now looks as below: |
| |
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
-------- ---------- ---------- ---------- ---------- --- -------------- ------------- --------------------
1 1 1 52428800 2 YES ACTIVE 185211 10-jul-2012 18:42:56
2 1 2 52428800 2 YES ACTIVE 185763 10-jul-2012 18:54:20
3 1 3 52428800 2 NO CURRENT 185768 10-jul-2012 18:54:29
|
| |
| |
| iv) Now, let's perform RMAN backup (refer my other posts on how to perfor RMAN backup). After the RMAN backup, V$LOG might look like this: |
| |
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
-------- ---------- ---------- ---------- ---------- --- -------------- ------------- --------------------
1 1 4 52428800 2 YES INACTIVE 185821 10-jul-2012 18:55:08
2 1 5 52428800 2 NO CURRENT 185832 10-jul-2012 18:55:23
3 1 3 52428800 2 YES INACTIVE 185768 10-jul-2012 18:54:29
|
| |
| RMAN would have backed up till Next SCN# 185832 (logseq# 4) along with the database backup. To crosscheck, use: |
| |
RMAN> list backup of archivelog all;
List of Backup Sets
===================
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
62 2.00K DISK 00:00:01 10-JUL-12
BP Key: 62 Status: AVAILABLE Compressed: YES Tag: TAG20120710T185523
Piece Name: /u02/oradata/backup/dbuat01/rman/DBUAT01_rman_20120710_788295323_2anforkr_74_1
List of Archived Logs in backup set 62
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 4 185821 10-JUL-12 185832 10-JUL-12
|
| |
| |
| v) Now, let's create a scenario: |
| |
SQL> conn scott/tiger
Connected.
SQL> select * from employees;
EMP_ EMP_F_NAME EMP_L_NAME EMP_DOJ EMP_SALARY EMP_CITY
---- ---------- ---------- -------------------- ---------- ----------
1 jason martin 25-jul-1996 00:00:00 1234.56 mumbai
2 mathews alison 21-mar-1976 00:00:00 4322.55 chennai
3 smith james 12-dec-1978 00:00:00 7897.78 kolkata
4 rice celia 30-jul-1987 00:00:00 6544.65 bangalore
5 larry david 17-sep-1996 00:00:00 1232.35 noida
SQL> delete employees;
5 rows deleted.
SQL> commit;
Commit complete.
SQL> conn / as sysdba
SQL> alter system archive log current;
System altered.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
-------- ---------- ---------- ---------- ---------- --- -------------- ------------- --------------------
1 1 4 52428800 2 YES INACTIVE 185821 10-jul-2012 18:55:08
2 1 5 52428800 2 YES ACTIVE 185832 10-jul-2012 18:55:23
3 1 6 52428800 2 NO CURRENT 185999 10-jul-2012 19:00:21
|
| |
| Note: Remember that logseq# 5 will have vector change of SQL> DELETE EMPLOYEES;. Now, let's make some more changes in the 'SCOTT.EMPLOYEES' table. Of course, let's insert some fresh data. It should now looks like this: |
| |
SQL> select * from employees;
EMP_ EMP_F_NAME EMP_L_NAME EMP_DOJ EMP_SALARY EMP_CITY
---- ---------- ---------- -------------------- ---------- ----------
6 arun shanmug 17-feb-2011 00:00:00 1232.35 chennai
7 aryan arun 17-jan-2012 00:00:00 1232.35 mumbai
|
| |
| Now, let's grab V$LOG status. |
| |
SQL> alter system archive log current;
System altered.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
-------- ---------- ---------- ---------- ---------- --- -------------- ------------- --------------------
1 1 7 52428800 2 NO CURRENT 186149 10-jul-2012 19:04:11
2 1 5 52428800 2 YES ACTIVE 185832 10-jul-2012 18:55:23
3 1 6 52428800 2 YES ACTIVE 185999 10-jul-2012 19:00:21
|
| |
| Note: Remember that logseq# 6 will have vector change of the two fresh records. Now, let's make some more changes in the 'EMPLOYEES' table. This time, let's drop the table itself. |
| |
SQL> conn scott/tiger
Connected.
SQL> drop table employees;
Table dropped.
SQL> conn / as sysdba
SQL> alter system archive log current;
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
-------- ---------- ---------- ---------- ---------- --- -------------- ------------- --------------------
1 1 7 52428800 2 YES ACTIVE 186149 10-jul-2012 19:04:11
2 1 8 52428800 2 NO CURRENT 186211 10-jul-2012 19:06:29
3 1 6 52428800 2 YES ACTIVE 185999 10-jul-2012 19:00:21
|
| |
| Note: Logseq# 7 will have vector change of the SQL> DROP TABLE EMPLOYEES;. Now, let's prepare for recovery to ensure that we recover the two records inserted a short while ago (that means, we must recover UNTIL CHANGE# '186149') just before dropping the table using RMAN backup and archivelogs. |
| |
| Few things to recall before crashing the database: |
- In the RMAN backup, only the logseq# 4 (NEXT SCN 185832) is available.
- In the actual archive destination, from logseq# 4 to logseq# 7 is available as show below:
|
$ cd /u01/oradata/dbuat01/archive
$ ls -lrt
-rw-r----- 1 ora10g dba 1024 Jul 10 18:55 dbuat01_1_4_788294576.arc
-rw-r----- 1 ora10g dba 5120 Jul 10 19:00 dbuat01_1_5_788294576.arc
-rw-r----- 1 ora10g dba 1503232 Jul 10 19:04 dbuat01_1_6_788294576.arc
-rw-r----- 1 ora10g dba 11264 Jul 10 19:06 dbuat01_1_7_788294576.arc
|
|
$ rman target /
RMAN> set DBID=2691765368
executing command: SET DBID
RMAN> startup nomount;
RMAN> restore controlfile from '/u02/oradata/backup/dbuat01/rman/dbuat01_control_c-2691765368-20120710-09';
Starting restore at 10-JUL-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=761 devtype=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
output filename=/u01/oradata/dbuat01/control/control01.ctl
output filename=/u01/oradata/dbuat01/control/control02.ctl
output filename=/u01/oradata/dbuat01/control/control03.ctl
Finished restore at 10-JUL-12
RMAN> alter database mount;
|
| |
| Note: After mounting the database, if you query V$LOG it might show as: |
| |
SQL> select status from v$instance;
STATUS
------------
MOUNTED
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
-------- ---------- ---------- ---------- ---------- --- -------------- ------------- --------------------
1 1 4 52428800 2 YES INACTIVE 185821 10-jul-2012 18:55:08
3 1 3 52428800 2 YES INACTIVE 185768 10-jul-2012 18:54:29
2 1 5 52428800 2 NO CURRENT 185832 10-jul-2012 18:55:23
|
[ ----------------------------------< code - expandable on mouseover >----------------------------------- ]
RMAN> run {
2> set until scn 186149;
3> restore database;
4> recover database;
5> }
executing command: SET until clause
Starting restore at 10-JUL-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=760 devtype=DISK
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00003 to /u01/oradata/dbuat01/data/sysaux1.dbf
restoring datafile 00004 to /u01/oradata/dbuat01/data/users1.dbf
channel ORA_DISK_1: reading from backup piece /u02/oradata/backup/dbuat01/rman/DBUAT01_rman_20120710_788295312_29nforkg_73_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/u02/oradata/backup/dbuat01/rman/DBUAT01_rman_20120710_788295312_29nforkg_73_1 tag=TAG20120710T185512
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u01/oradata/dbuat01/data/system1.dbf
restoring datafile 00002 to /u01/oradata/dbuat01/data/undotbs1.dbf
channel ORA_DISK_1: reading from backup piece /u02/oradata/backup/dbuat01/rman/DBUAT01_rman_20120710_788295312_28nforkg_72_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/u02/oradata/backup/dbuat01/rman/DBUAT01_rman_20120710_788295312_28nforkg_72_1 tag=TAG20120710T185512
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
Finished restore at 10-JUL-12
Starting recover at 10-JUL-12
using channel ORA_DISK_1
starting media recovery
archive log thread 1 sequence 4 is already on disk as file /u01/oradata/dbuat01/archive/dbuat01_1_4_788294576.arc
archive log filename=/u01/oradata/dbuat01/archive/dbuat01_1_4_788294576.arc thread=1 sequence=4
archive log filename=/u01/oradata/dbuat01/archive/dbuat01_1_5_788294576.arc thread=1 sequence=5
archive log filename=/u01/oradata/dbuat01/archive/dbuat01_1_6_788294576.arc thread=1 sequence=6
media recovery complete, elapsed time: 00:00:02
Finished recover at 10-JUL-12
|
| |
| Note: As you can see, RMAN applied until logseq# 6, because the timestamp SCN '186149' is the first change_time of logseq# 7. |
| |
RMAN> alter database open resetlogs;
Database opened.
|
| |
| Let's query V$LOG after using RESETLOGS: |
| |
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
-------- ---------- ---------- ---------- ---------- --- -------------- ------------- --------------------
1 1 1 52428800 2 NO CURRENT 186212 10-jul-2012 19:19:04
2 1 0 52428800 2 YES UNUSED 0
3 1 0 52428800 2 YES UNUSED 0
|
| |
| We aren't done yet! Now finally, let's verify SCOTT.EMPLOYEES table |
| |
SQL> conn scott/tiger
Connected.
SQL> select * from employees;
EMP_ EMP_F_NAME EMP_L_NAME EMP_DOJ EMP_SALARY EMP_CITY
---- ---------- ---------- -------------------- ---------- ----------
6 arun shanmug 17-feb-2011 00:00:00 1232.35 chennai
7 aryan arun 17-jan-2012 00:00:00 1232.35 mumbai
|
| |
| Voila! Those two records recovered!!! |
| |
| |
| 7. Point In Time Recovery (PITR) using user-managed hot backup ('UNTIL CHANGE') |
| |
| i) Let's first populate some user data ('SCOTT.EMPLOYEES'): |
| |
SQL> create table employees (
2 emp_id varchar2(4 byte) not null,
3 emp_f_name varchar2(10 byte),
4 emp_l_name varchar2(10 byte),
5 emp_doj date,
6 emp_salary number(8,2),
7 emp_city varchar2(10 byte)
)
/
SQL> insert into employees values (01,'jason','martin','25-jul-1996',1234.56,'mumbai');
SQL> insert into employees values (02,'mathews','alison','21-mar-1976',4322.55,'chennai');
SQL> insert into employees values (03,'smith','james','12-dec-1978',7897.78,'kolkata');
SQL> insert into employees values (04,'rice','celia','30-jul-1987',6544.65,'bangalore');
SQL> insert into employees values (05,'larry','david','17-sep-1996',1232.35,'noida');
SQL> commit;
SQL> select * from employees;
EMP_ EMP_F_NAME EMP_L_NAME EMP_DOJ EMP_SALARY EMP_CITY
---- ---------- ---------- -------------------- ---------- ----------
1 jason martin 25-jul-1996 00:00:00 1234.56 mumbai
2 mathews alison 21-mar-1976 00:00:00 4322.55 chennai
3 smith james 12-dec-1978 00:00:00 7897.78 kolkata
4 rice celia 30-jul-1987 00:00:00 6544.65 bangalore
5 larry david 17-sep-1996 00:00:00 1232.35 noida
|
| |
| |
| ii) Let's now grab the V$LOG |
| |
SQL> conn / as sysdba
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
-------- ---------- ---------- ---------- ---------- --- -------------- ------------- --------------------
1 1 1 52428800 2 NO CURRENT 186953 12-jul-2012 12:41:30
2 1 0 52428800 2 YES UNUSED 0
3 1 0 52428800 2 YES UNUSED 0
|
| |
| The view shows that the populated 'SCOTT.EMPLOYEES' redo data resides in the current logseq# 1. |
| |
| |
| iii) Now, after a couple of switches, it now looks as below: |
| |
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
-------- ---------- ---------- ---------- ---------- --- -------------- ------------- --------------------
1 1 1 52428800 2 YES ACTIVE 186953 12-jul-2012 12:41:30
2 1 2 52428800 2 YES ACTIVE 187115 12-jul-2012 12:44:05
3 1 3 52428800 2 NO CURRENT 187118 12-jul-2012 12:44:07
|
| |
| |
| iv) Now, let's perform user-managed hotbackup (refer my other posts on how to perform user-managed hotbackup). |
| |
| |
| v) Now, let's create a scenario: |
| |
SQL> conn scott/tiger
Connected.
SQL> select * from employees;
EMP_ EMP_F_NAME EMP_L_NAME EMP_DOJ EMP_SALARY EMP_CITY
---- ---------- ---------- -------------------- ---------- ----------
1 jason martin 25-jul-1996 00:00:00 1234.56 mumbai
2 mathews alison 21-mar-1976 00:00:00 4322.55 chennai
3 smith james 12-dec-1978 00:00:00 7897.78 kolkata
4 rice celia 30-jul-1987 00:00:00 6544.65 bangalore
5 larry david 17-sep-1996 00:00:00 1232.35 noida
SQL> delete employees;
5 rows deleted.
SQL> commit;
Commit complete.
SQL> conn / as sysdba
SQL> alter system archive log current;
System altered.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
-------- ---------- ---------- ---------- ---------- --- -------------- ------------- --------------------
1 1 4 52428800 2 NO CURRENT 187191 12-jul-2012 12:47:02
2 1 2 52428800 2 YES INACTIVE 187115 12-jul-2012 12:44:05
3 1 3 52428800 2 YES ACTIVE 187118 12-jul-2012 12:44:07
|
| |
| Note: Remember that logseq# 3 will have vector change of SQL> DELETE EMPLOYEES;. now, let's make some more changes in the 'SCOTT.EMPLOYEES' table. Of course, let's insert some fresh data. It now looks like this: |
| |
SQL> select * from employees;
EMP_ EMP_F_NAME EMP_L_NAME EMP_DOJ EMP_SALARY EMP_CITY
---- ---------- ---------- -------------------- ---------- ----------
6 arun shanmug 17-feb-2011 00:00:00 1232.35 chennai
7 aryan arun 26-nov-2008 00:00:00 2226.35 nagercoil
|
| |
| Now, let's grab V$LOG status. |
| |
SQL> conn / as sysdba
Connected.
SQL> alter system archive log current;
System altered.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
-------- ---------- ---------- ---------- ---------- --- -------------- ------------- --------------------
1 1 4 52428800 2 YES ACTIVE 187191 12-jul-2012 12:47:02
2 1 5 52428800 2 NO CURRENT 187243 12-jul-2012 12:49:24
3 1 3 52428800 2 YES ACTIVE 187118 12-jul-2012 12:44:07
|
| |
| Note: Remember that logseq# 4 will have vector change of the two fresh records inserted. Now, let's make some more changes in the 'SCOTT.EMPLOYEES' table. This time let's drop the table itself. |
| |
SQL> conn scott/tiger
Connected.
SQL> drop table employees;
Table dropped.
SQL> conn / as sysdba
SQL> alter system archive log current;
System altered.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
-------- ---------- ---------- ---------- ---------- --- -------------- ------------- --------------------
1 1 4 52428800 2 YES INACTIVE 187191 12-jul-2012 12:47:02
2 1 5 52428800 2 YES ACTIVE 187243 12-jul-2012 12:49:24
3 1 6 52428800 2 NO CURRENT 187281 12-jul-2012 12:50:33
|
| |
| Note: logseq# 5 will have vector change of the SQL> DROP TABLE EMPLOYEES;. now, let's prepare for recovery to ensure that we recover the two records inserted a short while ago (that means, we must recover UNTIL CHANGE# 187243) just before dropping the table using the user-managed backup and available archivelogs. |
| |
| Few things to recall before crashing the database: |
- In the actual archive destination (/u01/oradata/dbuat01/archive), log sequences between logseq# 1 and logseq# 5 are still available.
|
$ cd /u01/oradata/dbuat01/archive
$ ls -lrt
-rw-r----- 1 ora10g dba 58880 Jul 12 12:44 dbuat01_1_1_788445690.arc
-rw-r----- 1 ora10g dba 1024 Jul 12 12:44 dbuat01_1_2_788445690.arc
-rw-r----- 1 ora10g dba 40960 Jul 12 12:47 dbuat01_1_3_788445690.arc
-rw-r----- 1 ora10g dba 3072 Jul 12 12:49 dbuat01_1_4_788445690.arc
-rw-r----- 1 ora10g dba 10752 Jul 12 12:50 dbuat01_1_5_788445690.arc
|
|
$ cd /u01/oradata/dbuat01/data; rm -f *dbf
$ cd /u01/oradata/dbuat01/control; rm -f *ctl
$ cd /u01/oradata/dbuat01/redo; rm -f *log
$ cd /u01/oradata/dbuat01/data; cp /u02/oradata/backup/dbuat01/hotbkp/*dbf .
$ cd /u01/oradata/dbuat01/control; cp /u02/oradata/backup/dbuat01/hotbkp/*ctl .
|
SQL> startup mount;
SQL> recover database until change 187243 using backup controlfile;
ORA-00279: change 187130 generated at 07/12/2012 12:44:43 needed for thread 1
ORA-00289: suggestion : /u01/oradata/dbuat01/archive/dbuat01_1_3_788445690.arc
ORA-00280: change 187130 for thread 1 is in sequence #3
Specify log: {=suggested | filename | AUTO | CANCEL}
ORA-00279: change 187191 generated at 07/12/2012 12:47:02 needed for thread 1
ORA-00289: suggestion : /u01/oradata/dbuat01/archive/dbuat01_1_4_788445690.arc
ORA-00280: change 187191 for thread 1 is in sequence #4
ORA-00278: log file '/u01/oradata/dbuat01/archive/dbuat01_1_3_788445690.arc' no longer needed for this recovery
Specify log: {=suggested | filename | AUTO | CANCEL}
Log applied.
Media recovery complete.
SQL> alter database open resetlogs;
Database altered.
|
| |
| Now, SCOTT.EMPLOYEES must have the two records inserted just before dropping the table itself. |
| |
SQL> conn scott/tiger
Connected.
SQL> select * from employees;
EMP_ EMP_F_NAME EMP_L_NAME EMP_DOJ EMP_SALARY EMP_CITY
---- ---------- ---------- -------------------- ---------- ----------
6 arun shanmug 17-feb-2011 00:00:00 1232.35 chennai
7 aryan arun 26-nov-2008 00:00:00 2226.35 nagercoil
|
| |
| Voila! Those two records!!! |
| |
| |
| 8. Point In Time Recovery (PITR) using RMAN ('UNTIL SEQUENCE') |
| |
| i) Let's first populate some user data |
| |
SQL> create table employees (
2 emp_id VARCHAR2(4 BYTE) NOT NULL,
3 emp_f_name VARCHAR2(10 BYTE),
4 emp_l_name VARCHAR2(10 BYTE),
5 emp_doj DATE,
6 emp_salary Number(8,2),
7 emp_city VARCHAR2(10 BYTE)
)
/
SQL> insert into employees values (01,'jason','martin','25-jul-1996',1234.56,'mumbai');
SQL> insert into employees values (02,'mathews','alison','21-mar-1976',4322.55,'chennai');
SQL> insert into employees values (03,'smith','james','12-dec-1978',7897.78,'kolkata');
SQL> insert into employees values (04,'rice','celia','30-jul-1987',6544.65,'bangalore');
SQL> insert into employees values (05,'larry','david','17-sep-1996',1232.35,'noida');
SQL> commit;
SQL> select * from employees;
EMP_ EMP_F_NAME EMP_L_NAME EMP_DOJ EMP_SALARY EMP_CITY
---- ---------- ---------- -------------------- ---------- ----------
1 jason martin 25-jul-1996 00:00:00 1234.56 mumbai
2 mathews alison 21-mar-1976 00:00:00 4322.55 chennai
3 smith james 12-dec-1978 00:00:00 7897.78 kolkata
4 rice celia 30-jul-1987 00:00:00 6544.65 bangalore
5 larry david 17-sep-1996 00:00:00 1232.35 noida
|
| |
| |
| ii) Let's now grab the V$LOG |
| |
SQL> conn / as sysdba
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
-------- ---------- ---------- ---------- ---------- --- -------------- ------------- --------------------
1 1 1 52428800 2 NO CURRENT 186212 10-jul-2012 19:19:04
2 1 0 52428800 2 YES UNUSED 0
3 1 0 52428800 2 YES UNUSED 0
|
| |
| The view indicates that the populated sample data must reside in logseq# 1 |
| |
| |
| iii) Now, after a couple of switches, it now looks as below: |
| |
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
-------- ---------- ---------- ---------- ---------- --- -------------- ------------- --------------------
1 1 1 52428800 2 YES ACTIVE 186212 10-jul-2012 19:19:04
2 1 2 52428800 2 YES ACTIVE 186869 10-jul-2012 19:37:14
3 1 3 52428800 2 NO CURRENT 186874 10-jul-2012 19:37:24
|
| |
| |
| iv) Now, let's take RMAN backup. So, after the RMAN backup, it might look like this: |
| |
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
-------- ---------- ---------- ---------- ---------- --- -------------- ------------- --------------------
1 1 4 52428800 2 YES INACTIVE 186941 10-jul-2012 19:38:47
2 1 5 52428800 2 NO CURRENT 186952 10-jul-2012 19:38:59
3 1 3 52428800 2 YES INACTIVE 186874 10-jul-2012 19:37:24
|
| |
| RMAN would have backed up till logseq# 4. To crosscheck, use: |
| |
RMAN> list backup of archivelog all;
List of Backup Sets
===================
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
70 2.00K DISK 00:00:01 10-JUL-12
BP Key: 70 Status: AVAILABLE Compressed: YES Tag: TAG20120710T193859
Piece Name: /u02/oradata/backup/dbuat01/rman/DBUAT01_rman_20120710_788297939_2knfou6j_84_1
List of Archived Logs in backup set 70
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 4 186941 10-JUL-12 186952 10-JUL-12
|
| |
| |
| v) Now, let's create a scenario: |
| |
SQL> conn scott/tiger
Connected.
SQL> select * from employees;
EMP_ EMP_F_NAME EMP_L_NAME EMP_DOJ EMP_SALARY EMP_CITY
---- ---------- ---------- -------------------- ---------- ----------
1 jason martin 25-jul-1996 00:00:00 1234.56 mumbai
2 mathews alison 21-mar-1976 00:00:00 4322.55 chennai
3 smith james 12-dec-1978 00:00:00 7897.78 kolkata
4 rice celia 30-jul-1987 00:00:00 6544.65 bangalore
5 larry david 17-sep-1996 00:00:00 1232.35 noida
SQL> delete employees;
5 rows deleted.
SQL> commit;
Commit complete.
SQL> conn / as sysdba
SQL> alter system archive log current;
System altered.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
-------- ---------- ---------- ---------- ---------- --- -------------- ------------- --------------------
1 1 4 52428800 2 YES INACTIVE 186941 10-jul-2012 19:38:47
2 1 5 52428800 2 YES ACTIVE 186952 10-jul-2012 19:38:59
3 1 6 52428800 2 NO CURRENT 187125 10-jul-2012 19:42:49
|
| |
| Note: Remember that logseq# 5 will have vector change of SQL> DELETE EMPLOYEES;. Now, let's make some more changes in the 'EMPLOYEES' table. Of course, some fresh data. So, it now looks like this: |
| |
SQL> select * from employees;
EMP_ EMP_F_NAME EMP_L_NAME EMP_DOJ EMP_SALARY EMP_CITY
---- ---------- ---------- -------------------- ---------- ----------
6 arun shanmug 17-feb-2011 00:00:00 1232.35 chennai
7 aryan arun 17-jan-2012 00:00:00 1232.35 mumbai
|
| |
| Now, let's grab V$LOG status. |
| |
SQL> alter system archive log current;
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
-------- ---------- ---------- ---------- ---------- --- -------------- ------------- --------------------
1 1 7 52428800 2 NO CURRENT 187162 10-jul-2012 19:44:32
2 1 5 52428800 2 YES ACTIVE 186952 10-jul-2012 19:38:59
3 1 6 52428800 2 YES ACTIVE 187125 10-jul-2012 19:42:49
|
| |
| Note: Remember that logseq# 6 will have vector change of the two fresh records. Now, let's make some more changes in the 'SCOTT.EMPLOYEES' table. This time, let's drop the table itself. |
| |
SQL> conn scott/tiger
Connected.
SQL> drop table employees;
Table dropped.
SQL> conn / as sysdba
SQL> alter system archive log current;
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
-------- ---------- ---------- ---------- ---------- --- -------------- ------------- --------------------
1 1 7 52428800 2 YES ACTIVE 187162 10-jul-2012 19:44:32
2 1 8 52428800 2 NO CURRENT 187202 10-jul-2012 19:45:41
3 1 6 52428800 2 YES ACTIVE 187125 10-jul-2012 19:42:49
|
| |
| Note: Logseq# 7 will have vector change of the SQL> DROP TABLE EMPLOYEES;. Now, let's prepare for recovery to ensure that we recover the two records inserted a short while ago (that means, we must recover UNTIL SEQUENCE 7) just before dropping the table using RMAN backup and available archivelogs. |
| |
| Few things to recall before crashing the database: |
- In the RMAN backup, only the logseq# 4 (NEXT SCN 185832) is available.
- In the actual archive destination, from logseq# 4 to logseq# 7 is available as show below:
|
$ cd /u01/oradata/dbuat01/archive
$ ls -lrt
-rw-r----- 1 ora10g dba 1024 Jul 10 19:38 dbuat01_1_4_788296744.arc
-rw-r----- 1 ora10g dba 363520 Jul 10 19:42 dbuat01_1_5_788296744.arc
-rw-r----- 1 ora10g dba 2560 Jul 10 19:44 dbuat01_1_6_788296744.arc
-rw-r----- 1 ora10g dba 13312 Jul 10 19:45 dbuat01_1_7_788296744.arc
|
|
|
RMAN> set DBID=2691765368
RMAN> startup nomount;
RMAN> restore controlfile from '/u02/oradata/backup/dbuat01/rman/dbuat01_control_c-2691765368-20120710-0b';
Starting restore at 10-JUL-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=761 devtype=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
output filename=/u01/oradata/dbuat01/control/control01.ctl
output filename=/u01/oradata/dbuat01/control/control02.ctl
output filename=/u01/oradata/dbuat01/control/control03.ctl
Finished restore at 10-JUL-12
RMAN> alter database mount;
|
| |
| Note: After mounting the database, if you query V$LOG it might show as: |
| |
SQL> select status from v$instance;
STATUS
------------
MOUNTED
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
-------- ---------- ---------- ---------- ---------- --- -------------- ------------- --------------------
1 1 4 52428800 2 YES INACTIVE 186941 10-jul-2012 19:38:47
3 1 3 52428800 2 YES INACTIVE 186874 10-jul-2012 19:37:24
2 1 5 52428800 2 NO CURRENT 186952 10-jul-2012 19:38:59
|
[ ----------------------------------< code - expandable on mouseover >----------------------------------- ]
RMAN> run {
2> set until sequence = 7;
3> restore database;
4> recover database;
5> }
executing command: SET until clause
Starting restore at 10-JUL-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=760 devtype=DISK
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00003 to /u01/oradata/dbuat01/data/sysaux1.dbf
restoring datafile 00004 to /u01/oradata/dbuat01/data/users1.dbf
channel ORA_DISK_1: reading from backup piece /u02/oradata/backup/dbuat01/rman/DBUAT01_rman_20120710_788297932_2jnfou6c_83_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/u02/oradata/backup/dbuat01/rman/DBUAT01_rman_20120710_788297932_2jnfou6c_83_1 tag=TAG20120710T193852
channel ORA_DISK_1: restore complete, elapsed time: 00:00:08
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u01/oradata/dbuat01/data/system1.dbf
restoring datafile 00002 to /u01/oradata/dbuat01/data/undotbs1.dbf
channel ORA_DISK_1: reading from backup piece /u02/oradata/backup/dbuat01/rman/DBUAT01_rman_20120710_788297932_2infou6c_82_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/u02/oradata/backup/dbuat01/rman/DBUAT01_rman_20120710_788297932_2infou6c_82_1 tag=TAG20120710T193852
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
Finished restore at 10-JUL-12
Starting recover at 10-JUL-12
using channel ORA_DISK_1
starting media recovery
archive log thread 1 sequence 4 is already on disk as file /u01/oradata/dbuat01/archive/dbuat01_1_4_788296744.arc
archive log thread 1 sequence 6 is already on disk as file /u01/oradata/dbuat01/redo/redo_g3_m2.log
archive log filename=/u01/oradata/dbuat01/archive/dbuat01_1_4_788296744.arc thread=1 sequence=4
archive log filename=/u01/oradata/dbuat01/archive/dbuat01_1_5_788296744.arc thread=1 sequence=5
archive log filename=/u01/oradata/dbuat01/redo/redo_g3_m2.log thread=1 sequence=6
media recovery complete, elapsed time: 00:00:01
Finished recover at 10-JUL-12
|
| |
| Note: As you can see, RMAN applied till logseq# 6. |
| |
RMAN> alter database open resetlogs;
Database opened.
|
| |
| Let's query V$LOG after using RESETLOGS: |
| |
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
-------- ---------- ---------- ---------- ---------- --- -------------- ------------- --------------------
1 1 1 52428800 2 NO CURRENT 187163 10-jul-2012 19:57:32
2 1 0 52428800 2 YES UNUSED 0
3 1 0 52428800 2 YES UNUSED 0
|
| |
| We aren't done yet! Now finally, let's verify SCOTT.EMPLOYEES table |
| |
SQL> conn scott/tiger
Connected.
SQL> select * from employees;
EMP_ EMP_F_NAME EMP_L_NAME EMP_DOJ EMP_SALARY EMP_CITY
---- ---------- ---------- -------------------- ---------- ----------
6 arun shanmug 17-feb-2011 00:00:00 1232.35 chennai
7 aryan arun 17-jan-2012 00:00:00 1232.35 mumbai
|
| |
| Voila! We did it!!! |
| |
| |
| 9. Point In Time Recovery (PITR) using user-managed hot backup ('UNTIL CANCEL') |
| |
| i) Let's first populate some user data |
| |
SQL> create table employees (
2 emp_id VARCHAR2(4 BYTE) NOT NULL,
3 emp_f_name VARCHAR2(10 BYTE),
4 emp_l_name VARCHAR2(10 BYTE),
5 emp_doj DATE,
6 emp_salary Number(8,2),
7 emp_city VARCHAR2(10 BYTE)
)
/
SQL> insert into employees values (01,'jason','martin','25-jul-1996',1234.56,'mumbai');
SQL> insert into employees values (02,'mathews','alison','21-mar-1976',4322.55,'chennai');
SQL> insert into employees values (03,'smith','james','12-dec-1978',7897.78,'kolkata');
SQL> insert into employees values (04,'rice','celia','30-jul-1987',6544.65,'bangalore');
SQL> insert into employees values (05,'larry','david','17-sep-1996',1232.35,'noida');
SQL> commit;
SQL> select * from employees;
EMP_ EMP_F_NAME EMP_L_NAME EMP_DOJ EMP_SALARY EMP_CITY
---- ---------- ---------- -------------------- ---------- ----------
1 jason martin 25-jul-1996 00:00:00 1234.56 mumbai
2 mathews alison 21-mar-1976 00:00:00 4322.55 chennai
3 smith james 12-dec-1978 00:00:00 7897.78 kolkata
4 rice celia 30-jul-1987 00:00:00 6544.65 bangalore
5 larry david 17-sep-1996 00:00:00 1232.35 noida
|
| |
| |
| ii) let's now grab the V$LOG |
| |
SQL> conn / as sysdba
SQL> select group#, sequence#, bytes, members, archived, status, first_change#, first_time, next_change#,
next_time from v$log;
GROUP# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
------- ---------- ---------- ---------- --- -------- ------------- -------------------- ------------ -----------
1 1 104857600 2 NO CURRENT 363193 12-jul-2012 17:48:30 2.8147E+14
2 0 104857600 2 YES UNUSED 0 0
3 0 104857600 2 YES UNUSED 0 0
|
| |
| The view indicates that the populated sample data must reside in logseq# 1 |
| |
| |
| iii) Now, after a couple of switches, it now looks as below: |
| |
SQL> select group#, sequence#, bytes, members, archived, status, first_change#, first_time, next_change#,
next_time from v$log;
GROUP# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
------- ---------- ---------- ---------- --- -------- ------------- -------------------- ------------ --------------------
1 4 104857600 2 NO CURRENT 363540 12-jul-2012 17:57:12 2.8147E+14
2 2 104857600 2 YES INACTIVE 363532 12-jul-2012 17:57:11 363536 12-jul-2012 17:57:11
3 3 104857600 2 YES INACTIVE 363536 12-jul-2012 17:57:11 363540 12-jul-2012 17:57:12
|
| |
| |
| iv) Now, let's perform user-managed hotbackup (refer my other posts on how to perform user-managed hotbackup). |
| |
| |
| v) Now, let's create a scenario: |
| |
SQL> conn scott/tiger
Connected.
SQL> select * from employees;
EMP_ EMP_F_NAME EMP_L_NAME EMP_DOJ EMP_SALARY EMP_CITY
---- ---------- ---------- -------------------- ---------- ----------
1 jason martin 25-jul-1996 00:00:00 1234.56 mumbai
2 mathews alison 21-mar-1976 00:00:00 4322.55 chennai
3 smith james 12-dec-1978 00:00:00 7897.78 kolkata
4 rice celia 30-jul-1987 00:00:00 6544.65 bangalore
5 larry david 17-sep-1996 00:00:00 1232.35 noida
SQL> delete employees;
5 rows deleted.
SQL> commit;
Commit complete.
SQL> conn / as sysdba
SQL> alter system archive log current;
System altered.
SQL> select group#, sequence#, bytes, members, archived, status, first_change#, first_time, next_change#,
next_time from v$log;
GROUP# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
------- ---------- ---------- ---------- --- -------------------- ------------- -------------------- ------------ --------------------
1 4 104857600 2 YES ACTIVE 363540 12-jul-2012 17:57:12 363838 12-jul-2012 18:03:17
2 5 104857600 2 YES ACTIVE 363838 12-jul-2012 18:03:17 363860 12-jul-2012 18:03:57
3 6 104857600 2 NO CURRENT 363860 12-jul-2012 18:03:57 2.8147E+14
|
| |
| Note: Remember that logseq# 5 will have vector change of SQL> DELETE EMPLOYEES;. Now, let's make some more changes in the 'SCOTT.EMPLOYEES' table. Of course, let's insert some fresh data. It now looks like this: |
| |
SQL> select * from employees;
EMP_ EMP_F_NAME EMP_L_NAME EMP_DOJ EMP_SALARY EMP_CITY
---- ---------- ---------- -------------------- ---------- ----------
6 arun shanmug 17-feb-2011 00:00:00 1232.35 chennai
7 aryan arun 26-nov-2008 00:00:00 2226.35 nagercoil
|
| |
| Now, let's grab V$LOG status. |
| |
SQL> conn / as sysdba
Connected.
SQL> alter system archive log current;
System altered.
SQL> select group#, sequence#, bytes, members, archived, status, first_change#, first_time, next_change#,
next_time from v$log;
GROUP# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
------- ---------- ---------- ---------- --- ---------------- ------------- -------------------- ------------ --------------------
1 7 104857600 2 NO CURRENT 364270 12-jul-2012 18:06:39 2.8147E+14
2 5 104857600 2 YES ACTIVE 363838 12-jul-2012 18:03:17 363860 12-jul-2012 18:03:57
3 6 104857600 2 YES ACTIVE 363860 12-jul-2012 18:03:57 364270 12-jul-2012 18:06:39
|
| |
| Note: Remember that logseq# 6 will have vector change of the two fresh records inserted. Now, let's make some more changes in the 'SCOTT.EMPLOYEES' table. This time let's drop the table itself. |
| |
SQL> conn scott/tiger
Connected.
SQL> drop table employees;
Table dropped.
SQL> conn / as sysdba
SQL> alter system archive log current;
System altered.
SQL> select group#, sequence#, bytes, members, archived, status, first_change#, first_time, next_change#,
next_time from v$log;
GROUP# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
------- ---------- ---------- ---------- --- -------------- ------------- -------------------- ------------ --------------------
1 7 104857600 2 YES ACTIVE 364270 12-jul-2012 18:06:39 364325 12-jul-2012 18:08:24
2 8 104857600 2 NO CURRENT 364325 12-jul-2012 18:08:24 2.8147E+14
3 6 104857600 2 YES ACTIVE 363860 12-jul-2012 18:03:57 364270 12-jul-2012 18:06:39
|
| |
| Note: logseq# 7 will have vector change of the SQL> DROP TABLE EMPLOYEES;. now, let's prepare for recovery to ensure that we recover the two records inserted a short while ago (that means, we must recover UNTIL SEQUENCE# 7) just before dropping the table using the user-managed backup and available archivelogs. |
| |
| |
| Few things to recall before crashing the database: |
- In the actual archive destination (/u01/oradata/dbuat01/archive), ensure log sequences between logseq# 1 and logseq# 7 are still available.
|
| |
$ cd /u01/oradata/dbuat01/archive
$ ls -lrt
-rw-r----- 1 ora11g oinstall 103424 Jul 12 17:57 dbuat01_1_1_788464110.arc
-rw-r----- 1 ora11g oinstall 1024 Jul 12 17:57 dbuat01_1_2_788464110.arc
-rw-r----- 1 ora11g oinstall 2048 Jul 12 17:57 dbuat01_1_3_788464110.arc
-rw-r----- 1 ora11g oinstall 837120 Jul 12 18:03 dbuat01_1_4_788464110.arc
-rw-r----- 1 ora11g oinstall 6144 Jul 12 18:03 dbuat01_1_5_788464110.arc
-rw-r----- 1 ora11g oinstall 311296 Jul 12 18:06 dbuat01_1_6_788464110.arc
-rw-r----- 1 ora11g oinstall 13824 Jul 12 18:08 dbuat01_1_7_788464110.arc
|
|
$ cd /u01/oradata/dbuat01/data; rm -f *dbf
$ cd /u01/oradata/dbuat01/control; rm -f *ctl
$ cd /u01/oradata/dbuat01/redo; rm -f *log
$ cd /u01/oradata/dbuat01/data; cp /u02/oradata/backup/dbuat01/hotbkp/*dbf .
$ cd /u01/oradata/dbuat01/control; cp /u02/oradata/backup/dbuat01/hotbkp/*ctl .
|
[ ----------------------------------< code - expandable on mouseover >----------------------------------- ]
SQL> startup mount;
ORACLE instance started.
Total System Global Area 536870912 bytes
Fixed Size 2085360 bytes
Variable Size 150998544 bytes
Database Buffers 377487360 bytes
Redo Buffers 6299648 bytes
Database mounted.
SQL> recover database until cancel using backup controlfile;
ORA-00279: change 363762 generated at 07/12/2012 18:00:21 needed for thread 1
ORA-00289: suggestion : /u02/oradata/dbuat01/archived_logs/dbuat01_1_4_788464110.arc
ORA-00280: change 363762 for thread 1 is in sequence #4
Specify log: {=suggested | filename | AUTO | CANCEL}
ORA-00279: change 363838 generated at 07/12/2012 18:03:17 needed for thread 1
ORA-00289: suggestion : /u02/oradata/dbuat01/archived_logs/dbuat01_1_5_788464110.arc
ORA-00280: change 363838 for thread 1 is in sequence #5
ORA-00278: log file '/u02/oradata/dbuat01/archived_logs/dbuat01_1_4_788464110.arc' no longer needed for this recovery
Specify log: {=suggested | filename | AUTO | CANCEL}
ORA-00279: change 363860 generated at 07/12/2012 18:03:57 needed for thread 1
ORA-00289: suggestion : /u02/oradata/dbuat01/archived_logs/dbuat01_1_6_788464110.arc
ORA-00280: change 363860 for thread 1 is in sequence #6
ORA-00278: log file '/u02/oradata/dbuat01/archived_logs/dbuat01_1_5_788464110.arc' no longer needed for this recovery
Specify log: {=suggested | filename | AUTO | CANCEL}
ORA-00279: change 364270 generated at 07/12/2012 18:06:39 needed for thread 1
ORA-00289: suggestion : /u02/oradata/dbuat01/archived_logs/dbuat01_1_7_788464110.arc
ORA-00280: change 364270 for thread 1 is in sequence #7
ORA-00278: log file '/u02/oradata/dbuat01/archived_logs/dbuat01_1_6_788464110.arc' no longer needed for this recovery
Specify log: {=suggested | filename | AUTO | CANCEL}
CANCEL
Media recovery cancelled.
SQL> alter database open resetlogs;
Database altered.
|
| |
| Now, SCOTT.EMPLOYEES must have the two records inserted just before dropping the table itself. |
| |
SQL> conn scott/tiger
Connected.
Session altered.
SQL> select * from employees;
EMP_ EMP_F_NAME EMP_L_NAME EMP_DOJ EMP_SALARY EMP_CITY
---- ---------- ---------- -------------------- ---------- ----------
6 arun shanmug 17-feb-2011 00:00:00 1232.35 chennai
7 aryan arun 26-nov-2008 00:00:00 2226.12 nagercoil
|
| |
| Voila! We are done now!!! |
| |
| |
| 10. Loss of Undo datafile |
| |
| i) Let's populate some user data: |
| |
SQL> create table employees (
2 emp_id varchar2(4 byte) not null,
3 emp_f_name varchar2(10 byte),
4 emp_l_name varchar2(10 byte),
5 emp_doj date,
6 emp_salary number(8,2),
7 emp_city varchar2(10 byte)
)
/
SQL> insert into employees values (01,'jason','martin','25-jul-1996',1234.56,'mumbai');
SQL> insert into employees values (02,'mathews','alison','21-mar-1976',4322.55,'chennai');
SQL> insert into employees values (03,'smith','james','12-dec-1978',7897.78,'kolkata');
SQL> insert into employees values (04,'rice','celia','30-jul-1987',6544.65,'bangalore');
SQL> insert into employees values (05,'larry','david','17-sep-1996',1232.35,'noida');
SQL> commit;
SQL> quit
|
$ rm -f /u02/oradata/dbuat01/data/tbs_undotbs01_01.dbf
$ sqlplus / as sysdba
|
SQL> conn scott/tiger
SQL> delete from employees where emp_id in (4,5);
SQL> rollback;
rollback
*
ERROR at line 1:
ORA-00603: ORACLE server session terminated by fatal error
ORA-00376: file 3 cannot be read at this time
ORA-01110: data file 3: '/u02/oradata/dbuat01/data/tbs_undotbs01_01.dbf'
ORA-00376: file 3 cannot be read at this time
ORA-01110: data file 3: '/u02/oradata/dbuat01/data/tbs_undotbs01_01.dbf'
Process ID: 4486
Session ID: 10 Serial number: 28
SQL> conn / as sysdba
SQL> select file#, online_status, error from v$recover_file;
FILE# ONLINE_ ERROR
---------- ------- ------------------------------
3 OFFLINE FILE NOT FOUND
SQL> select file#, name, status from v$datafile;
FILE# NAME STATUS
---------- ------------------------------------------------------------ -------
3 /u02/oradata/dbuat01/data/tbs_undotbs01_01.dbf RECOVER
SQL> shut abort;
SQL> startup mount;
SQL> select file#, online_status, error from v$recover_file;
FILE# ONLINE_ ERROR
---------- ------- ------------------------------
3 OFFLINE FILE NOT FOUND
|
|
RMAN> restore datafile 3;
Starting restore at 11-JUL-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=10 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 00003 to /u02/oradata/dbuat01/data/tbs_undotbs01_01.dbf
channel ORA_DISK_1: reading from backup piece /u02/oradata/backup/dbuat01/rman/DBUAT01_rman_bkp_20120711_788348439_4mnfqfgn_150_1
channel ORA_DISK_1: piece handle=/u02/oradata/backup/dbuat01/rman/DBUAT01_rman_bkp_20120711_788348439_4mnfqfgn_150_1 tag=TAG20120711T094039
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
Finished restore at 11-JUL-12
RMAN> recover datafile 3;
Starting recover at 11-JUL-12
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at 11-JUL-12
RMAN> alter database open;
database opened
|
|
SQL> select file#, online_status, error from v$recover_file;
no rows selected
SQL> select file#, name, status from v$datafile where file# = 3;
FILE# NAME STATUS
---------- ------------------------------------------------------------ -------
3 /u02/oradata/dbuat01/data/tbs_undotbs01_01.dbf OFFLINE
SQL> alter database datafile 3 online;
Database altered.
SQL> select file#, name, status from v$datafile where file# = 3;
FILE# NAME STATUS
---------- ------------------------------------------------------------ -------
3 /u02/oradata/dbuat01/data/tbs_undotbs01_01.dbf ONLINE
SQL> conn scott/tiger
SQL> select * from employees;
EMP_ EMP_F_NAME EMP_L_NAME EMP_DOJ EMP_SALARY EMP_CITY
---- ---------- ---------- -------------------- ---------- ----------
1 jason martin 25-jul-1996 00:00:00 1234.56 mumbai
2 mathews alison 21-mar-1976 00:00:00 4322.55 chennai
3 smith james 12-dec-1978 00:00:00 7897.78 kolkata
4 rice celia 30-jul-1987 00:00:00 6544.65 bangalore
5 larry david 17-sep-1996 00:00:00 1232.35 noida
|
| |
| Note: As you can see, inspite of the recovery, SQL> DELETE FROM EMPLOYEES WHERE EMP_ID IN (4,5); statement didn't succeed, because the undo segment was already lost ('tbs_undotbs_01.dbf' physical file was lost) before this statement was executed. hence the sql delete statement couldn't be recovered. |
| |
| |
| 11. Loss of Online Redo log file |
| |
| |
| i) let's grab the V$LOGFILE & V$LOG |
| |
SQL> select * from v$log;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- ------------------------------------------------------------ ---
1 ONLINE /u02/oradata/dbuat01/redo/redo_g1_m1.log NO
1 ONLINE /u02/oradata/dbuat01/redo/redo_g1_m2.log NO
2 ONLINE /u02/oradata/dbuat01/redo/redo_g2_m1.log NO
2 ONLINE /u02/oradata/dbuat01/redo/redo_g2_m2.log NO
3 ONLINE /u02/oradata/dbuat01/redo/redo_g3_m1.log NO
3 ONLINE /u02/oradata/dbuat01/redo/redo_g3_m2.log NO
SQL> select group#, sequence#, members, status, first_change#, next_change# from v$log;
GROUP# SEQUENCE# MEMBERS STATUS FIRST_CHANGE# NEXT_CHANGE#
---------- ---------- ---------- ---------------- ------------- ------------
1 19 2 INACTIVE 363178 363192
2 20 2 CURRENT 363192 2.8147E+14
3 18 2 INACTIVE 363142 363178
|
| |
| Note: GROUP# 2 is currently active. |
| |
| |
| ii) Let's create a scenario: |
| |
SQL> conn scott/tiger
SQL> select * from employees;
EMP_ EMP_F_NAME EMP_L_NAME EMP_DOJ EMP_SALARY EMP_CITY
---- ---------- ---------- -------------------- ---------- ----------
1 jason martin 25-jul-1996 00:00:00 1234.56 mumbai
2 mathews alison 21-mar-1976 00:00:00 4322.55 chennai
3 smith james 12-dec-1978 00:00:00 7897.78 kolkata
5 ashok kanna 01-jan-2009 00:00:00 5000.35 madurai
4 arun shanmug 01-feb-2011 00:00:00 3000.33 chennai
SQL> delete from employees where emp_id in (4,5);
2 rows deleted.
SQL> commit;
Commit complete.
|
| |
| No problem until here. Now, let's delete the GROUP# 3 log members, so that the next log switch will run into trouble here. |
| |
$ cd /u02/oradata/dbuat01/redo
$ rm -rf redo_g3_m1.log redo_g3_m2.log
|
SQL> conn / as sysdba
SQL> alter system archive log current;
System altered.
SQL> select group#, sequence#, members, status, first_change#, next_change# from v$log;
GROUP# SEQUENCE# MEMBERS STATUS FIRST_CHANGE# NEXT_CHANGE#
---------- ---------- ---------- ---------------- ------------- ------------
1 19 2 INACTIVE 363178 363192
2 20 2 ACTIVE 363192 363400
3 21 2 CURRENT 363400 2.8147E+14
|
| |
| Good! Now, we should face some problem here after another switch. |
| |
SQL> alter system archive log current;
alter system archive log current
*
ERROR at line 1:
ORA-16038: log 3 sequence# 21 cannot be archived
ORA-00312: online log 3 thread 1: '/u02/oradata/dbuat01/redo/redo_g3_m1.log'
ORA-00312: online log 3 thread 1: '/u02/oradata/dbuat01/redo/redo_g3_m2.log'
SQL> shut abort
|
|
[ ----------------------------------< code - expandable on mouseover >----------------------------------- ]
RMAN> startup nomount;
RMAN> restore controlfile from '/u02/oradata/backup/dbuat01/rman/dbuat01_c-2689589391-20120711-03';
Starting restore at 11-JUL-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=134 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u02/oradata/dbuat01/control/control01.ctl
output file name=/u02/oradata/dbuat01/control/control02.ctl
output file name=/u02/oradata/dbuat01/control/control03.ctl
Finished restore at 11-JUL-12
RMAN> alter database mount;
RMAN> run {
2> restore database;
3> recover database;
4> }
Starting restore at 11-JUL-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=134 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 00004 to /u02/oradata/dbuat01/data/tbs_users_01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /u02/oradata/dbuat01/data/ts_arun_01.dbf
channel ORA_DISK_1: reading from backup piece /u02/oradata/backup/dbuat01/rman/DBUAT01_rman_bkp_20120711_788357184_4unfqo20_158_1
channel ORA_DISK_1: piece handle=/u02/oradata/backup/dbuat01/rman/DBUAT01_rman_bkp_20120711_788357184_4unfqo20_158_1 tag=TAG20120711T120623
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
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 /u02/oradata/dbuat01/data/tbs_system_01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /u02/oradata/dbuat01/data/tbs_sysaux_01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u02/oradata/dbuat01/data/tbs_undotbs01_01.dbf
channel ORA_DISK_1: reading from backup piece /u02/oradata/backup/dbuat01/rman/DBUAT01_rman_bkp_20120711_788357184_4vnfqo20_159_1
channel ORA_DISK_1: piece handle=/u02/oradata/backup/dbuat01/rman/DBUAT01_rman_bkp_20120711_788357184_4vnfqo20_159_1 tag=TAG20120711T120623
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:35
Finished restore at 11-JUL-12
Starting recover at 11-JUL-12
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 19 is already on disk as file /u02/oradata/dbuat01/archived_logs/dbuat01_1_19_788356652.arc
archived log for thread 1 with sequence 20 is already on disk as file /u02/oradata/dbuat01/redo/redo_g2_m1.log
archived log for thread 1 with sequence 22 is already on disk as file /u02/oradata/dbuat01/redo/redo_g1_m1.log
archived log file name=/u02/oradata/dbuat01/archived_logs/dbuat01_1_19_788356652.arc thread=1 sequence=19
archived log file name=/u02/oradata/dbuat01/redo/redo_g2_m1.log thread=1 sequence=20
unable to find archived log
archived log thread=1 sequence=21
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 07/11/2012 12:21:16
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 21 and starting SCN of 363400
|
| |
| Note: As you can see, log group# 3 not applied anywhere during the recovery. Because, log group# 3 is not physically present. It will be recreated only while opening the database using RESETLOGS. |
| |
RMAN> alter database open resetlogs;
Database altered.
|
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- -------------------------------------------------- ---
1 ONLINE /u02/oradata/dbuat01/redo/redo_g1_m1.log NO
1 ONLINE /u02/oradata/dbuat01/redo/redo_g1_m2.log NO
2 ONLINE /u02/oradata/dbuat01/redo/redo_g2_m1.log NO
2 ONLINE /u02/oradata/dbuat01/redo/redo_g2_m2.log NO
3 ONLINE /u02/oradata/dbuat01/redo/redo_g3_m1.log NO
3 ONLINE /u02/oradata/dbuat01/redo/redo_g3_m2.log NO
SQL> select group#, sequence#, members, status, first_change#, next_change# from v$log;
GROUP# SEQUENCE# MEMBERS STATUS FIRST_CHANGE# NEXT_CHANGE#
---------- ---------- ---------- ---------------- ------------- ------------
1 1 2 CURRENT 363401 2.8147E+14
2 0 2 UNUSED 0 0
3 0 2 UNUSED 0 0
|
| |
| |
| 12. Loss of one Controlfile when Controlfiles are multiplexed |
| |
| |
| i) Let's grab V$CONTROLFILE; |
| |
SQL> select * from v$controlfile;
STATUS NAME IS_ BLOCK_SIZE FILE_SIZE_BLKS
------- ------------------------------------------------------- --- ---------- --------------
/u02/oradata/dbuat01/control/control01.ctl NO 16384 608
/u02/oradata/dbuat01/control/control02.ctl NO 16384 608
/u02/oradata/dbuat01/control/control03.ctl NO 16384 608
|
| |
| |
| ii) Let's delete one of the multiplexed copies. |
| |
$ rm -f /u02/oradata/dbuat01/control/control02.ctl
|
SQL> select * from v$datafile;
select * from v$datafile
*
ERROR at line 1:
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/u02/oradata/dbuat01/control/control02.ctl'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
|
| |
| |
| iii) Let's make a copy from the multiplexed copies |
| |
$ cp -p /u02/oradata/dbuat01/control/control01.ctl /u02/oradata/dbuat01/control/control02.ctl
|
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u02/oradata/dbuat01/data/tbs_system_01.dbf
/u02/oradata/dbuat01/data/tbs_sysaux_01.dbf
/u02/oradata/dbuat01/data/tbs_undotbs01_01.dbf
/u02/oradata/dbuat01/data/tbs_users_01.dbf
/u02/oradata/dbuat01/data/ts_arun_01.dbf
|
| |
| It should be fine now! |
| |
| |
| 13. Loss of all Controlfiles |
| |
| |
| i) Let's grab V$CONTROLFILE; |
| |
SQL> select * from v$controlfile;
STATUS NAME IS_ BLOCK_SIZE FILE_SIZE_BLKS
------- ------------------------------------------------------- --- ---------- --------------
/u02/oradata/dbuat01/control/control01.ctl NO 16384 608
/u02/oradata/dbuat01/control/control02.ctl NO 16384 608
/u02/oradata/dbuat01/control/control03.ctl NO 16384 608
|
| |
| |
| ii) Let's delete all the multiplexed copies. |
| |
$ rm -f /u02/oradata/dbuat01/control/*ctl
|
SQL> select * from v$datafile;
select * from v$datafile
*
ERROR at line 1:
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/u02/oradata/dbuat01/control/control02.ctl'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
|
|
[ ----------------------------------< code - expandable on mouseover >----------------------------------- ]
RMAN> startup nomount;
RMAN> restore controlfile from '/u02/oradata/backup/dbuat01/rman/dbuat01_c-2689589391-20120711-04';
Starting restore at 11-JUL-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=134 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u02/oradata/dbuat01/control/control01.ctl
output file name=/u02/oradata/dbuat01/control/control02.ctl
output file name=/u02/oradata/dbuat01/control/control03.ctl
Finished restore at 11-JUL-12
RMAN> run {
2> alter database mount;
3> restore database;
4> recover database;
5> }
database mounted
released channel: ORA_DISK_1
Starting restore at 11-JUL-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=134 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 00004 to /u02/oradata/dbuat01/data/tbs_users_01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /u02/oradata/dbuat01/data/ts_arun_01.dbf
channel ORA_DISK_1: reading from backup piece /u02/oradata/backup/dbuat01/rman/DBUAT01_rman_bkp_20120711_788357184_4unfqo20_158_1
channel ORA_DISK_1: piece handle=/u02/oradata/backup/dbuat01/rman/DBUAT01_rman_bkp_20120711_788357184_4unfqo20_158_1 tag=TAG20120711T120623
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
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 /u02/oradata/dbuat01/data/tbs_system_01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /u02/oradata/dbuat01/data/tbs_sysaux_01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u02/oradata/dbuat01/data/tbs_undotbs01_01.dbf
channel ORA_DISK_1: reading from backup piece /u02/oradata/backup/dbuat01/rman/DBUAT01_rman_bkp_20120711_788357184_4vnfqo20_159_1
channel ORA_DISK_1: piece handle=/u02/oradata/backup/dbuat01/rman/DBUAT01_rman_bkp_20120711_788357184_4vnfqo20_159_1 tag=TAG20120711T120623
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:35
Finished restore at 11-JUL-12
Starting recover at 11-JUL-12
using channel ORA_DISK_1
starting media recovery
archived log file name=/u02/oradata/dbuat01/archived_logs/dbuat01_1_33_788358144.arc thread=1 sequence=33
archived log file name=/u02/oradata/dbuat01/archived_logs/dbuat01_1_34_788358144.arc thread=1 sequence=34
archived log file name=/u02/oradata/dbuat01/archived_logs/dbuat01_1_35_788358144.arc thread=1 sequence=35
archived log file name=/u02/oradata/dbuat01/redo/redo_g3_m1.log thread=1 sequence=36
archived log file name=/u02/oradata/dbuat01/redo/redo_g1_m1.log thread=1 sequence=37
archived log file name=/u02/oradata/dbuat01/redo/redo_g2_m1.log thread=1 sequence=38
media recovery complete, elapsed time: 00:00:06
Finished recover at 11-JUL-12
RMAN> alter database open resetlogs;
Database opened.
|
| |
| |
| 14. Recreating Controlfile from trace |
| |
| Controlfiles may be recreated only under two situations. One is when you have lost all your controlfiles and no backup is available. And the second is to rename a database (DB_NAME). Now, let's create a controlfile trace. |
| |
| i. Creating a controlfile trace file |
| |
|
SQL> alter database backup controlfile to trace '/u02/oradata/backup/dbuat01/rman/dbuat01_controlfile_trace.sql';
Database altered.
|
| |
| |
| Now, we must make some changes in the trace file using 'vi' editor. it should look like the one shown below after removing all unwanted comments on the top and bottom of the trace file: |
| |
CREATE CONTROLFILE REUSE DATABASE "DBUAT01" RESETLOGS ARCHIVELOG
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXDATAFILES 100
MAXINSTANCES 1
MAXLOGHISTORY 292
LOGFILE
GROUP 1 (
'/u02/oradata/dbuat01/redo/redo_g1_m1.log',
'/u02/oradata/dbuat01/redo/redo_g1_m2.log'
) SIZE 100M BLOCKSIZE 512,
GROUP 2 (
'/u02/oradata/dbuat01/redo/redo_g2_m1.log',
'/u02/oradata/dbuat01/redo/redo_g2_m2.log'
) SIZE 100M BLOCKSIZE 512,
GROUP 3 (
'/u02/oradata/dbuat01/redo/redo_g3_m1.log',
'/u02/oradata/dbuat01/redo/redo_g3_m2.log'
) SIZE 100M BLOCKSIZE 512
DATAFILE
'/u02/oradata/dbuat01/data/tbs_system_01.dbf',
'/u02/oradata/dbuat01/data/tbs_sysaux_01.dbf',
'/u02/oradata/dbuat01/data/tbs_undotbs01_01.dbf',
'/u02/oradata/dbuat01/data/tbs_users_01.dbf',
'/u02/oradata/dbuat01/data/ts_arun_01.dbf'
CHARACTER SET US7ASCII
;
|
| |
| |
| ii) Let's create a scenario by forcefully removing all the controlfiles. |
| |
$ rm -f /u02/oradata/dbuat01/control/*ctl
|
| |
| |
| iii) Let's include the following undocumented parameter in the init.ora. Otherwise, you will not be able to open the database using 'RESETLOGS' |
| |
_allow_resetlogs_corruption=true
|
| |
| |
| iv) Shut abort the database and startup again using the init.ora |
| |
SQL> shut abort
SQL> startup nomount pfile=$ORACLE_HOME/dbs/initdbuat01.ora
SQL> @/u02/oradata/backup/dbuat01/rman/dbuat01_controlfile_trace.sql
Control file created.
SQL> alter database open resetlogs;
Database altered.
|
| |
| Shut down the database and restart again. Take a full backup!
|
| |
| |
| [ Top ] |
| |
| Last modified: Jul 05, 2012 11:45 IST |
