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
3. restore datafile 3;
4. recover datafile 3;
5. sql 'alter tablespace
or
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 andrecovery 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 '
5. alter database open;
6. exit;
7. rman target /
8. restore datafile '
9. recover datafile '
10. sql 'alter tablespace
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
3. alter database create datafile '/user/oradata/u01/dbtst/newdata01.dbf;
4. exit
5. rman target /
6. recover tablespace
7. sql 'alter tablespace
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'
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
3. alter tablespace
'/user/oradata/u01/dbtst/user01.dbf' to '/user/oradata/u02/dbtst/user01.dbf';
4. rman target /
5. recover tablespace
6. sql 'alter tablespace
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:
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!
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
Post a Comment