Thursday 28 November 2019

Recover Tablespace using Rman backup..


FIRST TAKE THE WHOLE DATABASE BACKUP USING RMAN THEN STARTS THE SCNARIO

C:\Users\svss3>sqlplus /nolog
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Apr 19 15:43:27 2012
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
sql>connect /as sysdba
SQL> Connected.
SQL>   select name from v$Datafile;
NAME
--------------------------------------------------------------------------------
E:\ORACLE\PRODUCT\10.2.0\ORADATA\AGILE2\SYSTEM01.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\AGILE2\UNDOTBS01.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\AGILE2\SYSAUX01.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\AGILE2\USERS01.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\AGILE2\EXAMPLE01.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\AGILE2\TEST.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\AGILE2\SVSS.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\AGILE2\SVSS01.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\AGILE2\RMAN.DBF
9 rows selected
(--BEFORE DROPPING TABLESPACE RMAN DATAFILEs  9 IS THERE SEE THE BOLD LETTER)

SQL>drop tablespace rman including contents and datafiles;
Tablespace dropped.
SQL>   select name from v$Datafile;
NAME
--------------------------------------------------------------------------------
E:\ORACLE\PRODUCT\10.2.0\ORADATA\AGILE2\SYSTEM01.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\AGILE2\UNDOTBS01.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\AGILE2\SYSAUX01.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\AGILE2\USERS01.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\AGILE2\EXAMPLE01.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\AGILE2\TEST.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\AGILE2\SVSS.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\AGILE2\SVSS01.DBF
8 rows selected.
(AFTER DROP RMAN DATAFILE IS REMOVED FROM PHYSICALLY)
SQL> EXIT
SQL> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
(Dropped tablespace is removed from database physically,so we go back to previous time when drop command issued by using the alert log file)
(Recover database using rman)
Shu down the database ,
Startup nomount.
e:\oracle\product\10.2.0\oradata\agile2>sqlplus /nolog
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Apr 19 15:50:41 2012

Copyright (c) 1982, 2005, Oracle.  All rights reserved.
SQL>connect  /as sysdba
SQL> Connected.
SQL>shutdown  immediate
SQL> Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>startup nomount
SQL>  ORACLE instance started.

Total System Global Area  293601280 bytes
Fixed Size                                1248600 bytes
Variable Size                        104858280 bytes
Database Buffers               180355072 bytes
Redo Buffers                          7139328 bytes
SQL> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
(Using rman restore the controlfile from previous backup innomount stage)
(Autobackup is on,so after drop command issued one controlfile backup is stored in autobackup area,if  we use that it will restore upto  dropped stage itself. So restore the controfile backup before the drop command issued by using alert log file time.)
e:\oracle\product\10.2.0\oradata\agile2>rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on Thu Apr 19 15:52:00 2012
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
connected to target database: agile2 (not mounted)

RMAN>restore controlfile from 'E:\oracle\product\10.2.0\db_1\flash_recovery_area\AGILE2\AUTOBACKUP\2012_04_19\O1_MF_N_781024853_7RZQZYG2_.BKP'
Starting restore at 19-APR-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=157 devtype=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
output filename=E:\ORACLE\PRODUCT\10.2.0\ORADATA\AGILE2\CONTROL01.CTL
output filename=E:\ORACLE\PRODUCT\10.2.0\ORADATA\AGILE2\CONTROL02.CTL
output filename=E:\ORACLE\PRODUCT\10.2.0\ORADATA\AGILE2\CONTROL03.CTL
Finished restore at 19-APR-12

RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1


RMAN> report schema;
Starting implicit crosscheck backup at 19-APR-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=154 devtype=DISK
Crosschecked 8 objects
Crosschecked 18 objects
Finished implicit crosscheck backup at 19-APR-12

Starting implicit crosscheck copy at 19-APR-12
using channel ORA_DISK_1
using channel ORA_DISK_2
Finished implicit crosscheck copy at 19-APR-12

searching for all files in the recovery area
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: E:\ORACLE\PRODUCT\10.2.0\DB_1\FLASH_RECOVERY_AREA\AGILE2\AUTOBACKUP\2012_04_19\O1_MF_N_781024853_7RZQZYG2_.BKP
File Name: E:\ORACLE\PRODUCT\10.2.0\DB_1\FLASH_RECOVERY_AREA\AGILE2\AUTOBACKUP\2012_04_19\O1_MF_N_781026571_7RZSONJD_.BKP

RMAN-06139: WARNING: control file is not current for REPORT SCHEMA
Report of database schema

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    480      SYSTEM               ***     E:\ORACLE\PRODUCT\10.2.0\ORADATA\AGILE2\SYSTEM01.DBF
2    30       UNDOTBS1             ***     E:\ORACLE\PRODUCT\10.2.0\ORADATA\AGILE2\UNDOTBS01.DBF
3    250      SYSAUX               ***     E:\ORACLE\PRODUCT\10.2.0\ORADATA\AGILE2\SYSAUX01.DBF
4    5        USERS                ***     E:\ORACLE\PRODUCT\10.2.0\ORADATA\AGILE2\USERS01.DBF
5    100      EXAMPLE              ***     E:\ORACLE\PRODUCT\10.2.0\ORADATA\AGILE2\EXAMPLE01.DBF
6    5        TEST                 ***     E:\ORACLE\PRODUCT\10.2.0\ORADATA\AGILE2\TEST.DBF
7    50       SVSS                 ***     E:\ORACLE\PRODUCT\10.2.0\ORADATA\AGILE2\SVSS.DBF
8    5        SVSS                 ***     E:\ORACLE\PRODUCT\10.2.0\ORADATA\AGILE2\SVSS01.DBF
9    0        RMAN                 ***     E:\ORACLE\PRODUCT\10.2.0\ORADATA\AGILE2\RMAN.DBF

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    20       TEMP                 32767       E:\ORACLE\PRODUCT\10.2.0\ORADATA\AGILE2\TEMP01.DBF

RMAN>run{
2>set until time "to_date('19-apr-2012 15:49:00','dd-mon-yyyy hh24:mi:ss')";
 3>restore database;
 4>recover database;
5> }
executing command: SET until clause

Starting restore at 19-APR-12
using channel ORA_DISK_1
using channel ORA_DISK_2

channel ORA_DISK_2: starting datafile backupset restore
channel ORA_DISK_2: specifying datafile(s) to restore from backup set
restoring datafile 00001 to E:\ORACLE\PRODUCT\10.2.0\ORADATA\AGILE2\SYSTEM01.DBF
restoring datafile 00006 to E:\ORACLE\PRODUCT\10.2.0\ORADATA\AGILE2\TEST.DBF
restoring datafile 00008 to E:\ORACLE\PRODUCT\10.2.0\ORADATA\AGILE2\SVSS01.DBF
restoring datafile 00009 to E:\ORACLE\PRODUCT\10.2.0\ORADATA\AGILE2\RMAN.DBF
channel ORA_DISK_2: reading from backup piece E:\RMAN\TODAY\20120419_DATA_598.BKP
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00002 to E:\ORACLE\PRODUCT\10.2.0\ORADATA\AGILE2\UNDOTBS01.DBF
restoring datafile 00003 to E:\ORACLE\PRODUCT\10.2.0\ORADATA\AGILE2\SYSAUX01.DBF
restoring datafile 00004 to E:\ORACLE\PRODUCT\10.2.0\ORADATA\AGILE2\USERS01.DBF
restoring datafile 00005 to E:\ORACLE\PRODUCT\10.2.0\ORADATA\AGILE2\EXAMPLE01.DBF
restoring datafile 00007 to E:\ORACLE\PRODUCT\10.2.0\ORADATA\AGILE2\SVSS.DBF
channel ORA_DISK_1: reading from backup piece E:\RMAN\TODAY\20120419_DATA_599.BKP
channel ORA_DISK_1: restored backup piece 1
piece handle=E:\RMAN\TODAY\20120419_DATA_599.BKP tag=TAG20120419T152018
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
channel ORA_DISK_2: restored backup piece 1
piece handle=E:\RMAN\TODAY\20120419_DATA_598.BKP tag=TAG20120419T152018
channel ORA_DISK_2: restore complete, elapsed time: 00:00:22
Finished restore at 19-APR-12

Starting recover at 19-APR-12
using channel ORA_DISK_1
using channel ORA_DISK_2

starting media recovery

archive log thread 1 sequence 23 is already on disk as file E:\ORACLE\PRODUCT\10.2.0\DB_1\FLASH_RECOVERY_AREA\AGILE2\ARCHIVELOG\2012_04_19\O1_MF_1_23_7RZQWMW5_.ARC
archive log thread 1 sequence 1 is already on disk as file E:\ORACLE\PRODUCT\10.2.0\DB_1\FLASH_RECOVERY_AREA\AGILE2\ARCHIVELOG\2012_04_19\O1_MF_1_1_7RZQZLO6_.ARC
archive log thread 1 sequence 2 is already on disk as file E:\ORACLE\PRODUCT\10.2.0\DB_1\FLASH_RECOVERY_AREA\AGILE2\ARCHIVELOG\2012_04_19\O1_MF_1_2_7RZQZM7Y_.ARC
archive log thread 1 sequence 3 is already on disk as file E:\ORACLE\PRODUCT\10.2.0\ORADATA\AGILE2\REDO03.LOG
archive log filename=E:\ORACLE\PRODUCT\10.2.0\DB_1\FLASH_RECOVERY_AREA\AGILE2\ARCHIVELOG\2012_04_19\O1_MF_1_1_7RZQZLO6_.ARC thread=1 sequence=1
archive log filename=E:\ORACLE\PRODUCT\10.2.0\DB_1\FLASH_RECOVERY_AREA\AGILE2\ARCHIVELOG\2012_04_19\O1_MF_1_2_7RZQZM7Y_.ARC thread=1 sequence=2
archive log filename=E:\ORACLE\PRODUCT\10.2.0\ORADATA\AGILE2\REDO03.LOG thread=1 sequence=3
media recovery complete, elapsed time: 00:00:04
Finished recover at 19-APR-12

RMAN>

RMAN> exit
Recovery Manager complete.
e:\oracle\product\10.2.0\oradata\agile2>sqlplus /nolog
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Apr 19 16:03:28 2012
Copyright (c) 1982, 2005, Oracle.  All rights reserved.

SQL> Connected.
(Before give resetlogs we recover the database upto correct time tablespace is available,for this we open the database read only mode then check it tablespace is there able to access the table in that tablespace.)

SQL> alter database open read only;
Database altered.
SQL> select name from V$datafile; 
NAME
--------------------------------------------------------------------------------
E:\ORACLE\PRODUCT\10.2.0\ORADATA\AGILE2\SYSTEM01.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\AGILE2\UNDOTBS01.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\AGILE2\SYSAUX01.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\AGILE2\USERS01.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\AGILE2\EXAMPLE01.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\AGILE2\TEST.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\AGILE2\SVSS.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\AGILE2\SVSS01.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\AGILE2\RMAN.DBF

9 rows selected.
(See dropped tablespace rman is recovered)
SQL>select  * from rman1.recover;
                ID
----------
                12
(That particular table belongs to tablespace also accessible)
SQL>shu immediate
SQL> Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>startup mount
SQL> ORACLE instance started.

Total System Global Area  293601280 bytes
Fixed Size                                1248600 bytes
Variable Size                        104858280 bytes
Database Buffers               180355072 bytes
Redo Buffers                          7139328 bytes
Database mounted.
SQL> alter database open resetlogs
Database altered.

SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
E:\ORACLE\PRODUCT\10.2.0\ORADATA\AGILE2\SYSTEM01.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\AGILE2\UNDOTBS01.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\AGILE2\SYSAUX01.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\AGILE2\USERS01.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\AGILE2\EXAMPLE01.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\AGILE2\TEST.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\AGILE2\SVSS.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\AGILE2\SVSS01.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\AGILE2\RMAN.DBF

9 rows selected.

SQL> select * from rman1.recover
                ID
----------
                12

SQL> exit
SQL> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
(After recover the tablespace compulsory take the full database backup..)

at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)