In sweet memories of my ever loving brother "kutty thambi " ARUN KUMAR

Saturday, May 8, 2010

Complete Recovery With RMAN Backup.

BACKUP AND RECOVERY SCENARIOS Complete Recovery With RMAN Backup.
previous post i have posted a complete recovery with user-managed backup,
here we are going to see the complete recovery using rman backup.
you can perform complete recovery in the following 5 situations.
  RMAN Recovery Scenarios of complete recovery.
1. Complete Closed Database Recovery. System datafile is missing
2. Complete Open Database Recovery. Non system datafile is missing
3. Complete Open Database Recovery (when the database is initially closed). Non system datafile is missing
4. Recovery of a Datafile that has no backups.
5. Restore and Recovery of a Datafile to a different location.
1.Complete Closed Database Recovery. System Datafile is missing
   In this case complete recovery is performed, only the system datafile is missing,
   so the database can be opened without reseting the redologs.
       1.  rman target /
       2.  startup mount;
       3.  restore database or datafile file#;
       4.  recover database or datafile file#;
       5.  alter database open;
workshop1:
SQL> create user sweety identified by sweety;

User created.

SQL> grant dba to sweety;

Grant succeeded.

SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> host rm -rf /u01/app/oracle/oradata/testdb/system01.dbf

SQL> startup
ORACLE instance started.

Total System Global Area  444596224 bytes
Fixed Size                  1219904 bytes
Variable Size             130024128 bytes
Database Buffers          310378496 bytes
Redo Buffers                2973696 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: '/u01/app/oracle/oradata/testdb/system01.dbf'


SQL> shutdown immediate
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL>

[oracle@cdbs1 ~]$ rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Fri May 7 23:53:51 2010

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

connected to target database (not started)

RMAN> startup mount

Oracle instance started
database mounted

Total System Global Area     444596224 bytes

Fixed Size                     1219904 bytes
Variable Size                130024128 bytes
Database Buffers             310378496 bytes
Redo Buffers                   2973696 bytes

RMAN> RESTORE DATABASE;

Starting restore at 07-MAY-10
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK

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/app/oracle/oradata/testdb/system01.dbf
restoring datafile 00002 to /u01/app/oracle/oradata/testdb/undotbs01.dbf
restoring datafile 00003 to /u01/app/oracle/oradata/testdb/sysaux01.dbf
restoring datafile 00004 to /u01/app/oracle/oradata/testdb/users01.dbf
restoring datafile 00005 to /u03/oradata/test01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/TESTDB/backupset/2010_05_07/o1_mf_nnndf_TAG20100507T232259_5y8nvxt2_.bkp
channel ORA_DISK_1: restored backup piece 1
piece handle=/u01/app/oracle/flash_recovery_area/TESTDB/backupset/2010_05_07/o1_mf_nnndf_TAG20100507T232259_5y8nvxt2_.bkp tag=TAG20100507T232259
channel ORA_DISK_1: restore complete, elapsed time: 00:02:52
Finished restore at 07-MAY-10

RMAN> RECOVER DATABASE;

Starting recover at 07-MAY-10
using channel ORA_DISK_1

starting media recovery


RMAN> sql 'alter database open';

sql statement: alter database open

RMAN>
SQL> conn sys/oracle as sysdba;
Connected.
SQL> col name format a45
SQL> select name , status from v$datafile;

NAME                                          STATUS
--------------------------------------------- -------
/u01/app/oracle/oradata/testdb/system01.dbf   SYSTEM
/u01/app/oracle/oradata/testdb/undotbs01.dbf  ONLINE
/u01/app/oracle/oradata/testdb/sysaux01.dbf   ONLINE
/u01/app/oracle/oradata/testdb/users01.dbf    ONLINE
/u03/oradata/test01.dbf                       ONLINE

SQL> select username from dba_users
  2  where username='SWEETY';

USERNAME
------------------------------
SWEETY
2.Complete Open Database Recovery. Non system datafile is missing,
database is up
1. rman target /
2. sql 'alter tablespace offline immediate'; 
                                or
     sql 'alter database datafile file# offline;
3. restore datafile 3;
4. recover datafile 3;
5. sql 'alter tablespace online';
                          or

      sql 'alter database datafile file# online;
workshop2:

 

SQL> conn sweety/sweety;
Connected.
SQL> create table demo(id number);

Table created.

SQL> insert into demo values(123);

1 row created.

SQL> commit;

Commit complete.

SQL> conn sys/oracle as sysdba;
Connected.
SQL> select username,default_tablespace from dba_users   2  where username='SWEETY';

USERNAME                       DEFAULT_TABLESPACE
------------------------------ ------------------------------
SWEETY                         USERS

SQL> host rm -rf /u01/app/oracle/oradata/testdb/users01.dbf

SQL> conn sweety/sweety
Connected.
SQL> alter system flush buffer_cache;

System altered.

SQL> select * from demo;
select * from demo
              *
ERROR at line 1:
ORA-01116: error in opening database file 4
ORA-01110: data file 4: '/u01/app/oracle/oradata/testdb/users01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3

[oracle@cdbs1 ~]$ rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Sat May 8 01:35:09 2010

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

connected to target database: TESTDB (DBID=2501713962)

RMAN> sql 'alter database datafile 4 offline';

using target database control file instead of recovery catalog
sql statement: alter database datafile 4 offline

RMAN> restore datafile 4;

Starting restore at 08-MAY-10
using channel ORA_DISK_1
...
channel ORA_DISK_1: restore complete, elapsed time: 00:00:09
Finished restore at 08-MAY-10

RMAN> recover datafile 4;

Starting recover at 08-MAY-10
using channel ORA_DISK_1

starting media recovery
......
media recovery complete, elapsed time: 00:00:05
Finished recover at 08-MAY-10

RMAN> sql 'alter database datafile 4 online';

sql statement: alter database datafile 4 online

RMAN>exit

SQL> conn sweety/sweety;
Connected.
SQL> select * from demo;

        ID
----------
       123

SQL>


3.Complete Open Database Recovery (when the database is initially closed).
Non system datafile is missing
A user datafile is reported missing when trying to startup the database. The datafile can be turned offline and the database started up. Restore and
recovery are performed using Rman. After recovery is performed the datafile can be turned online again.
1. sqlplus /nolog
2. connect / as sysdba
3. startup mount
4. alter database datafile '' offline;
5. alter database open;
6. exit;
7. rman target /
8. restore datafile '';
9. recover datafile '';
10. sql 'alter tablespace online';



workshop3:
 

SQL> conn sweety/sweety;
Connected.

SQL> create table test ( testid number);

Table created.

SQL> insert into test values(54321);

1 row created.

SQL> commit;

Commit complete.

SQL> conn sys/oracle as sysdba;
Connected.
SQL>shu immediate
SQL> host rm -rf /u01/app/oracle/oradata/testdb/users01.dbf

SQL> startup
ORACLE instance started.

Total System Global Area  444596224 bytes
Fixed Size                  1219904 bytes
Variable Size             138412736 bytes
Database Buffers          301989888 bytes
Redo Buffers                2973696 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: '/u01/app/oracle/oradata/testdb/users01.dbf'

SQL> alter database datafile 4 offline;

Database altered.

SQL> alter database open;

Database altered.

[oracle@cdbs1 ~]$ rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Sat May 8 01:51:45 2010

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

connected to target database: TESTDB (DBID=2501713962)

RMAN> restore datafile 4;

Starting restore at 08-MAY-10
using target database control file instead of recovery catalog
.....
channel ORA_DISK_1: restore complete, elapsed time: 00:00:04
Finished restore at 08-MAY-10

RMAN> recover datafile 4;

Starting recover at 08-MAY-10
using channel ORA_DISK_1

starting media recovery
.....
media recovery complete, elapsed time: 00:00:08
Finished recover at 08-MAY-10

RMAN> exit

SQL> alter database datafile 4 online;

Database altered.

SQL> conn sweety/sweety;
Connected.
SQL> select * from test;

    TESTID
----------
     54321

4.Recovery of a Datafile that has no backups (database is up).
If a non system datafile that was not backed up since the last backup is missing,
recovery can be performed if all archived logs since the creation
of the missing datafile exist. Since the database is up you can check the tablespace name and put it offline. The option offline immediate is used
to avoid that the update of the datafile header.
Pre requisites: All relevant archived logs.
1. sqlplus '/ as sysdba'
2. alter tablespace offline immediate;
3. alter database create datafile '/user/oradata/u01/dbtst/newdata01.dbf;
4. exit
5. rman target /
6. recover tablespace ;
7. sql 'alter tablespace online';
If the create datafile command needs to be executed to place the datafile on a
location different than the original use:
alter database create datafile '/user/oradata/u01/dbtst/newdata01.dbf' as
'/user/oradata/u02/dbtst/newdata01.dbf'

restriction: controlfile creation time must be prior than datafile creation time.
for more reference refer previous blog post.(user-managed complete recovery).
workshop4:
SQL> create user john identified by john
  2  default tablespace testing;

User created.

SQL> grant dba to john;

Grant succeeded.

SQL> conn john/john;
Connected.
SQL> create table test_tb( testid number);

Table created.

SQL> insert into test_tb values(1001);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from test_tb;

    TESTID
----------
      1001

SQL> conn sys/oracle as sysdba;
Connected.
SQL> host rm -rf /u03/oradata/test01.dbf

SQL> alter system flush buffer_cache;

System altered.

SQL> conn john/john;
Connected.
SQL> select * from test_tb;
select * from test_tb
              *
ERROR at line 1:
ORA-01116: error in opening database file 5
ORA-01110: data file 5: '/u03/oradata/test01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
SQL> conn sys/oracle as sysdba;
Connected.
SQL> alter tablespace testing offline immediate;

Tablespace altered.
---if you want to create datafile in same location
SQL> alter database create datafile '/u03/oradata/test01.dbf';

Database altered.
---if you want to create a datafile in different location(disk).
SQL> alter database create datafile '/u03/oradata/test01.dbf' as '/u01/app/oracle/oradata/testdb/test01.dbf';

Database altered.
[oracle@cdbs1 ~]$ rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Sat May 8 02:15:28 2010

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

connected to target database: TESTDB (DBID=2501713962)

RMAN> recover tablespace testing;

Starting recover at 08-MAY-10
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=145 devtype=DISK

starting media recovery

SQL> alter tablespace testing online;

Tablespace altered.

SQL> conn john/john;
Connected.
SQL> select * from test_tb;

    TESTID
----------
      1001

5.Restore and Recovery of a Datafile to a different location. Database is up.
If a non system datafile is missing and its original location not available, restore can be made to a different location and recovery performed.
Pre requisites: All relevant archived logs, complete cold or hot backup.
1. Use OS commands to restore the missing or corrupted datafile to the new
location, ie:
cp -p /user/backup/uman/user01.dbf /user/oradata/u02/dbtst/user01.dbf
2. alter tablespace offline immediate;
3. alter tablespace rename datafile
'/user/oradata/u01/dbtst/user01.dbf' to '/user/oradata/u02/dbtst/user01.dbf';
4. rman target /
5. recover tablespace ;
6. sql 'alter tablespace online';

workshop5: 

follow the same example workshop4 for workshop 5 except creating new datafile, here you have to copy the recent backup file to the new disk location and perform recovery. thats it , rest of the procedures are same.

regards,
Rajeshkumar Govindarajan.

2 comments:

Anonymous said...

Awesome post! I was trying to test restoring a tablespace after moving all the datafiles elsewhere. When I went to offline the tablespace to do the restore, I was getting the ORA-01157 and ORA-01110 errors and couldn't get the tablespace offline because the database wasn't open. Noticed that in your example, you had to offline the datafile before being able to open the database, so offlined the datafiles first, then was able to open the database and offline the tablespace in order to do the restore. Thanks so much for sharing - this was very helpful!

Nidhika said...

Hi,
Could you please help me to clarify below mention doubt.

DECODE:-
SELECT DECODE (NULL,NULL,'TRUE','FALSE') AS VALUE FROM DUAL
OUTPUT:- TRUE

CASE:-
SELECT CASE NULL WHEN NULL THEN 'TRUE' ELSE 'FALSE' END AS CASE_TEST FROM DUAL

OUTPUT :- FALSE

Why above mention queries are giving different output and what is the difference between case and decode?
Please reply on my email-id 'nidhikavaidh@gmail.com'

Regards,
Nidhika

 
Share/Bookmark