R e l a t i o n a l D a t a b a s e C o n s u l t i n g Page 1

Database Partial Restore

PRE-IMPLEMENTATION STEPS

1) Make sure you have a backup of the System, Sysaux, Undo and the data Tablespace

2) Make sure you have all the archive logs from the time the backup was taken to the time you want to restore to.

3) Make sure you have enough space to restore the above tablespaces

R e l a t i o n a l D a t a b a s e C o n s u l t i n g Page 2

IMPLEMENTATION STEPS

Step 1. Connect to the database and check the table SIZE, TABLESPACE and OWNER as follows: SQL> select OWNER, SEGMENT_NAME, SEGMENT_TYPE, TABLESPACE_NAME , bytes/1024 from dba_segments where owner =’TABLE_OWNER’ and SEGMENT_NAME=’TABLE_NAME’ ; In our case we are going to export a table name 'JAMES_DEBUG' and the owner of the table is 'XSL' SQL> select OWNER, SEGMENT_NAME, SEGMENT_TYPE , TABLESPACE_NAME, bytes/1024 from dba_segments where owner ='XSL' and SEGMENT_NAME='JAMES_DEBUG' ; OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME BYTES/1024 -------------- ------------------------- ----------------------------- ---------------- -------------- XSL JAMES_DEBUG TABLE XSL_DATA 1,572,864 Step 2. Check which datafiles the table resides on, by querying the dba_extents view. select distinct FILE_ID from dba_extents where SEGMENT_NAME='JAMES_DEBUG' and owner= 'XSL' ; FILE_ID ---------- 17 16 18 As a bonus let’s check how many datafiles make up this tablespace: select FILE_NAME , FILE_ID, TABLESPACE_NAME , BYTES/1024/1024 from dba_data_files where TABLESPACE_NAME='XSL_DATA'

FILE_NAME FILE_ID TABLESPACE_NAME BYTES/1024/1024 ------------------------------------------------------------- ------------------ ---------------------------- ----------------- +DATA/cfmsp2/datafile/xsl_data.259.777231375 16 XSL_DATA 15000 +DATA/cfmsp2/datafile/xsl_data.260.777231375 17 XSL_DATA 15000 +DATA/cfmsp2/datafile/xsl_data.261.777231721 18 XSL_DATA 15000 +DATA/cfmsp2/datafile/xsl_data.262.777231795 24 XSL_DATA 15000

R e l a t i o n a l D a t a b a s e C o n s u l t i n g Page 3

So rather than restore the whole tablespace we are going to restore 3 of the 4 datafiles that make the tablespace. So datafile, FILe_ID 24 will not be restored. Step 3. Partial Database Restore To do a partial database restore you need to restore the mandatory files for a database to run which are:

1. Spfile 2. Controlfile 3. System tablespace 4. Sysaux tablespace (also required by the exp/imp or expdp/impdp) 5. Undo tablespace

Then lastly the datafile/s that contain the table/s that we want to export! In our case I will only restore datafiles 16, 17 and 18 only.

i.) Restore the spfile: connect target connect rcvcat rman11203/rman11203@recovery_catalogDB; set dbid 896914363 ; run { startup force nomount ; set until time "to_date('04-DEC-2013 07:00:00','DD-MON-YYYY HH24:MI:SS')"; allocate channel t1 type 'SBT_TAPE' PARMS='ENV=(NB_ORA_CLIENT=XXXXXXX)'; restore spfile to pfile ’/app/oracle/local/initsid.ora ; release channel t1; }

I got this ERROR Message repeatedly!

RMAN> startup nomount startup failed: ORA-01078: failure in processing system parameters LRM-00109: could not open parameter file '/app/oracle/product/11.2.0.3/db_1/dbs/initcfmsp2.ora' starting Oracle instance without parameter file for retrieval of spfile RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of startup command at 12/04/2013 15:44:42 RMAN-04014: startup failed: ORA-04031: unable to allocate 3981064 bytes of shared memory ("shared pool","unknown object","sga heap(1,0)","FileOpenBlock") RMAN> exit This error message is caused by, I quote: “It means that we should increase database shared memory. To do this, we need to exit from RMAN and define environment variable ORA_RMAN_SGA_TARGET:” Thus to resolve this problem we simply export this variable:

R e l a t i o n a l D a t a b a s e C o n s u l t i n g Page 4

$ export ORA_RMAN_SGA_TARGET=450 Thanks to this link below that came up with the solution! http://www.iliachemodanov.ru/en/blog-en/21-databases/50-oracle-backup-and-

restore-en

Output from the spfile restore command: startup failed: ORA-01078: failure in processing system parameters LRM-00109: could not open parameter file '/app/oracle/product/11.2.0.3/db_1/dbs/initcfmsp2.ora' starting Oracle instance without parameter file for retrieval of spfile Oracle instance started Total System Global Area 367439872 bytes Fixed Size 2221600 bytes Variable Size 197134816 bytes Database Buffers 163577856 bytes Redo Buffers 4505600 bytes connected to recovery catalog database executing command: SET DBID database name is "CFMSP2" and DBID is 896914363 executing command: SET until clause allocated channel: t1 channel t1: SID=266 device type=SBT_TAPE channel t1: Veritas NetBackup for Oracle - Release 7.0 (2010070805) Starting restore at 04-DEC-13 channel t1: starting datafile backup set restore channel t1: restoring SPFILE output file name=/app/oracle/product/11.2.0.3/db_1/dbs/spfilecfmsp2.ora channel t1: reading from backup piece bk_240819_1_833249285 channel t1: piece handle=bk_240819_1_833249285 tag=HOT_DB_BK_LEVEL0 channel t1: restored backup piece 1 channel t1: restore complete, elapsed time: 00:01:15 Finished restore at 04-DEC-13 released channel: t1 Recovery Manager complete.

R e l a t i o n a l D a t a b a s e C o n s u l t i n g Page 5

ii.) Restore the controlfile

Startup the database nomount using the just restored spfile. SQL> startup force nomount; Restore the controlfile as follows: connect rcvcat rman11203/rman11203@recovery_catalogDB; set dbid 896914363 ; connect target run { set until time "to_date('04-DEC-2013 07:00:00','DD-MON-YYYY HH24:MI:SS')"; allocate channel t1 type 'SBT_TAPE'parms'ENV=(NB_ORA_CLIENT=XXXXXXXX)'; restore controlfile; release channel t1; }

Mount the database

R e l a t i o n a l D a t a b a s e C o n s u l t i n g Page 6

iii.) Restore the datafiles: Script: connect target run { set until time "to_date('04-DEC-2013 07:00:00','DD-MON-YYYY HH24:MI:SS')"; allocate channel t1 type 'SBT_TAPE'parms'ENV=(NB_ORA_CLIENT=XXXXXXXX)'; allocate channel t2 type 'SBT_TAPE'parms'ENV=(NB_ORA_CLIENT=XXXXXXXX)'; restore datafile 1,2,3,23,53,16,17,18 ; release channel t1; release channel t2; }

Output from running the above restore script:

R e l a t i o n a l D a t a b a s e C o n s u l t i n g Page 7

R e l a t i o n a l D a t a b a s e C o n s u l t i n g Page 8

Once the datafiles restore is completed, there are two options to recover and open the database: a.) Rman, (automatic recovery) b.) Sqlplus (manual user managed)

Each of these has its own pros and cons. I will start with showing how to do it using rman then sqlplus.

iv.) Using rman to recover the database. If you offline drop the datafiles that where not restored, then use rman to recover the database you will get this error message: RMAN-06094: datafile XX must be restored

To get around this problem you have to backup the controlfile to trace then edit the controlfile trace to remove the unwanted datafiles (un-restored files) To backup the controlfile to trace do: SQL> alter database backup controlfile to trace as ‘/tmp/controlf.trc’ ; Edit the controlfile trace and only leave the files that were restored. See sample below. STARTUP NOMOUNT set echo on CREATE CONTROLFILE REUSE DATABASE "CFMSP2" RESETLOGS ARCHIVELOG MAXLOGFILES 30 MAXLOGMEMBERS 5 MAXDATAFILES 500 MAXINSTANCES 1

R e l a t i o n a l D a t a b a s e C o n s u l t i n g Page 9

MAXLOGHISTORY 37392 LOGFILE GROUP 1 ( '+DATA/cfmsp2/onlinelog/group_1.307.777413909', '+DATA/cfmsp2/onlinelog/group_1.359.825675081' ) SIZE 256M BLOCKSIZE 512, GROUP 2 ( '+DATA/cfmsp2/onlinelog/group_2.308.777413975', '+DATA/cfmsp2/onlinelog/group_2.358.825675085' ) SIZE 256M BLOCKSIZE 512, GROUP 3 ( '+DATA/cfmsp2/onlinelog/group_3.309.777413985', '+DATA/cfmsp2/onlinelog/group_3.357.825675091' ) SIZE 256M BLOCKSIZE 512, GROUP 4 ( '+DATA/cfmsp2/onlinelog/group_4.340.825674989', '+DATA/cfmsp2/onlinelog/group_4.360.825675017' ) SIZE 100M BLOCKSIZE 512, GROUP 5 ( '+DATA/cfmsp2/onlinelog/group_5.305.777413729', '+DATA/cfmsp2/onlinelog/group_5.356.825675093' ) SIZE 256M BLOCKSIZE 512 -- STANDBY LOGFILE

DATAFILE '+DATA/cfmsp2/datafile/system.291.833377993', '+DATA/cfmsp2/datafile/undots.292.833377991', '+DATA/cfmsp2/datafile/sysaux.295.833374645', '+DATA/cfmsp2/datafile/xsl_data.294.833374645', '+DATA/cfmsp2/datafile/xsl_data.296.833374643', '+DATA/cfmsp2/datafile/xsl_data.278.833376423', '+DATA/cfmsp2/datafile/undots.293.833377919', '+DATA/cfmsp2/datafile/undots.290.833380071' CHARACTER SET AL32UTF8 ;

Shutdown the oracle database and then execute the above script. SQL> shutdown immediate ORA-01109: database not open Database dismounted. ORACLE instance shut down.

SQL>@/tmp/controlf.trc Controlfile created. SQL> exit Now that the controlfile has been created, it’s time to restore the archive logs manually. This is so because the recently created control file does not any info about backups of archivelogs generated prior to now! Otherwise if you try to recover the database you will get this error message:

RMAN-06054: media recovery requesting unknown archived log for

thread 1 with sequence 282016 and starting SCN of 13166164699339

R e l a t i o n a l D a t a b a s e C o n s u l t i n g Page 10

connect target run { allocate channel t1 type 'SBT_TAPE'; allocate channel t2 type 'SBT_TAPE'; restore archivelog from logseq XXXXXX until logseq XXXXXX thread 1; release channel t1; release channel t2; }

Once the restore of the archivelogs is completed, catalog the archive logs and then you can go ahead and recover the database using the below recovery script: connect target run { set until time "to_date('04-DEC-2013 02:35:00','DD-MON-YYYY HH24:MI:SS')"; allocate channel t1 type 'SBT_TAPE'parms'ENV=(NB_ORA_CLIENT=XXXXXXXX)'; allocate channel t2 type 'SBT_TAPE'parms'ENV=(NB_ORA_CLIENT=XXXXXXXX)'; recover database DELETE ARCHIVELOG MAXSIZE 10G ; release channel t1; release channel t2; }

When recovery is complete you can open resetlogs your database!

Recovering Database using sqlplus.

To recover the database using sqlplus, you need to first restore the archivelogs as shown above using rman. Once the archive logs are restored then offline drop all the datafiles that were not restored as shown below:

alter database datafile 4 offline drop ; alter database datafile 5 offline drop ; alter database datafile 6 offline drop ; alter database datafile 7 offline drop ; alter database datafile 8 offline drop ; alter database datafile 9 offline drop ; alter database datafile 10 offline drop ; alter database datafile 11 offline drop ; alter database datafile …………………………….. ;

Recover the database in sqlplus as follows: SQL> recover database using backup controlfile until cancel ;

R e l a t i o n a l D a t a b a s e C o n s u l t i n g Page 11

ORA-00279: change 13166141511000 generated at 12/03/2013 01:30:16 needed for thread 1 ORA-00289: suggestion : /app/oracle/arch/cfmsp2/log/1_0000281991_748556524.cfmsp2 ORA-00280: change 13166141511000 for thread 1 is in sequence #281991 ORA-00278: log file '/app/oracle/arch/cfmsp2/log/1_0000281991_748556524.cfmsp2' no longer needed for this recovery

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

v.) Opening the Database Resetlogs

Now that the recovery is done, the database can be opened resetlogs. SQL> alter database open resetlogs; You are almost done! Time to create a directory for datapump SQL> create directory exp_dir as ‘/app/oracle/export’ ;

Table Export:

R e l a t i o n a l D a t a b a s e C o n s u l t i n g Page 12

Finally this is how the database datafiles look like!

R e l a t i o n a l D a t a b a s e C o n s u l t i n g Page 13

R e l a t i o n a l D a t a b a s e C o n s u l t i n g Page 14

This is how a partial database restore is done.