Partial restore of a database ~ Marko Sutic's Database Blog

Before database migration to new version it is always wise to test how will applications work on this new version.

But what if you have in production very big database and your test storage is limited - then partial restore comes as handy solution.

In most cases you will probably have few big interval-date range partitioned tables in production database. To fit in limited storage you can expel some tables or partitions. Using partial restored database you can still successfully test functionality of application.

Note:
After partial restore is finished it is probably important to recreate few tables or to fix some other errors - but this depends from application to application.

In this demo case I will concentrate only on partial restore.

OS: Red Hat Enterprise Linux Server release 5.4 (Tikanga) 32bit
DB: Oracle 10.2.0.4

First to list tablespaces of my database:

SQL> set lines 200
SQL> set pages 999
SQL> col tablespace_name for a30
SQL> col file_name for a55
SQL> select tablespace_name, file_name from dba_data_files;

TABLESPACE_NAME                FILE_NAME
------------------------------ -------------------------------------------------------
SYSTEM                         /oratest/oradata/datafiles/system01.dbf
UNDOTBS1                       /oratest/oradata/datafiles/undotbs101.dbf
SYSAUX                         /oratest/oradata/datafiles/sysaux01.dbf
APP_DATA                       /oratest/oradata/datafiles/app_data01.dbf
USERS                          /oratest/oradata/datafiles/users01.dbf
BILL_DATA                      /oratest/oradata/datafiles/bill_data01.dbf
OT_DATA_01                     /oratest/oradata/datafiles/ot_data_01.dbf
OT_DATA_02                     /oratest/oradata/datafiles/ot_data_02.dbf
OT_DATA_03                     /oratest/oradata/datafiles/ot_data_03.dbf
OT_DATA_04                     /oratest/oradata/datafiles/ot_data_04.dbf
OT_DATA_05                     /oratest/oradata/datafiles/ot_data_05.dbf
OT_DATA_06                     /oratest/oradata/datafiles/ot_data_06.dbf
OT_DATA_07                     /oratest/oradata/datafiles/ot_data_07.dbf

Before anything I will backup this database using script below:

RMAN> run
2> {
3> allocate channel c1 type disk;
4> backup as compressed backupset incremental level 0 database format '/home/oratest/rmanbkp/FULL_%d_%u_%s_%T';
5> BACKUP CURRENT CONTROLFILE FORMAT '/home/oratest/rmanbkp/controlf_%d_%u_%s_%T';
6> BACKUP SPFILE FORMAT '/home/oratest/rmanbkp/spfile_%d_%u_%s_%T';
7> release channel c1;
8> }

Now to create control file script:

SQL> alter database backup controlfile to trace as '/home/oratest/ctlfile_scr.sql';

Database altered.

What tablespaces are mandatory for partial restore:
- SYSTEM tablespace
- SYSAUX tablespace (if my database is 10g or higher)
- Other tablespaces that hold dictionary information
select distinct tablespace_name from dba_segments where owner in ('SYS', 'SYSTEM');
- Tablespace with rollback segments

Optional:
- All tablespaces with user data that I want to use for restore

In this case i will use this tablespaces for partial restore:

SYSTEM /oratest/oradata/datafiles/system01.dbf
SYSAUX /oratest/oradata/datafiles/sysaux01.dbf
UNDOTBS1 /oratest/oradata/datafiles/undotbs101.dbf
APP_DATA /oratest/oradata/datafiles/app_data01.dbf
USERS /oratest/oradata/datafiles/users01.dbf
BILL_DATA /oratest/oradata/datafiles/bill_data01.dbf
OT_DATA_01 /oratest/oradata/datafiles/ot_data_01.dbf

I've expelled OT_DATA "old" parts...

Edit control file script including only tablespaces that I want to restore:

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "TESTDB" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 (
    '/oratest/oradata/TESTDB/redo/redo01a.rdo',
    '/oratest/oradata/TESTDB/redo/redo01b.rdo'
  ) SIZE 100M,
  GROUP 2 (
    '/oratest/oradata/TESTDB/redo/redo02a.rdo',
    '/oratest/oradata/TESTDB/redo/redo02b.rdo'
  ) SIZE 100M,
  GROUP 3 (
    '/oratest/oradata/TESTDB/redo/redo03a.rdo',
    '/oratest/oradata/TESTDB/redo/redo03b.rdo'
  ) SIZE 100M
-- STANDBY LOGFILE

DATAFILE
  '/oratest/oradata/datafiles/system01.dbf',
  '/oratest/oradata/datafiles/undotbs101.dbf',
  '/oratest/oradata/datafiles/sysaux01.dbf',
  '/oratest/oradata/datafiles/app_data01.dbf',
  '/oratest/oradata/datafiles/users01.dbf',
  '/oratest/oradata/datafiles/bill_data01.dbf',
  '/oratest/oradata/datafiles/ot_data_01.dbf'
CHARACTER SET AL32UTF8
;

It is time to test this partial restore.

First to restore control file and mount database:

RMAN> startup nomount;

RMAN> run
2> {
3> allocate channel c1 device type disk;
4> restore controlfile from '/home/oratest/rmanbkp/controlf_TESTDB_i5l38viu_1605_20100112';
5> release channel c1;
6> }

RMAN> alter database mount;
database mounted

To check File# of datafiles that I want to restore:

SQL> SET PAGES 200
SQL> COLUMN NAME FORMAT a60
SQL> SELECT FILE#, NAME FROM V$DATAFILE;

     FILE# NAME
---------- ------------------------------------------------------------
         1 /oratest/oradata/datafiles/system01.dbf
         2 /oratest/oradata/datafiles/undotbs101.dbf
         3 /oratest/oradata/datafiles/sysaux01.dbf
         4 /oratest/oradata/datafiles/app_data01.dbf
         5 /oratest/oradata/datafiles/users01.dbf
         6 /oratest/oradata/datafiles/bill_data01.dbf
         7 /oratest/oradata/datafiles/ot_data_01.dbf
         8 /oratest/oradata/datafiles/ot_data_02.dbf
         9 /oratest/oradata/datafiles/ot_data_03.dbf
        10 /oratest/oradata/datafiles/ot_data_04.dbf
        11 /oratest/oradata/datafiles/ot_data_05.dbf
        12 /oratest/oradata/datafiles/ot_data_06.dbf
        13 /oratest/oradata/datafiles/ot_data_07.dbf

Restore specified datafiles:

RMAN> run
2> {
3> restore datafile 1;
4> restore datafile 2;
5> restore datafile 3;
6> restore datafile 4;
7> restore datafile 5;
8> restore datafile 6;
9> restore datafile 7;
10> }

Create new control file using script above and mount database:

$ sqlplus "/as sysdba"

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> @/home/oratest/ctlfile_scr.sql
ORACLE instance started.

Total System Global Area 1342177280 bytes
Fixed Size                  1267380 bytes
Variable Size            1191184716 bytes
Database Buffers          134217728 bytes
Redo Buffers               15507456 bytes

Control file created.

I will cancel media recovery in this test case:

SQL> recover database until cancel using backup controlfile;
ORA-00279: change 36022694011 generated at 01/12/2010 09:48:08 needed for
thread 1
ORA-00289: suggestion : /oratest/oradata/archlogs/1_18_706354584.dbf
ORA-00280: change 36022694011 for thread 1 is in sequence #18


Specify log: {=suggested | filename | AUTO | CANCEL}
CANCEL
Media recovery cancelled.

Open database using resetlogs command:

SQL> alter database open resetlogs;

Database altered.

Create temporary tablespace:

SQL> ALTER TABLESPACE TMP ADD TEMPFILE '/oratest/oradata/datafiles/tmp01.dbf'
  2  SIZE 50M REUSE AUTOEXTEND ON NEXT 25M  MAXSIZE 2048M;


Tablespace altered.

Excerpt from alert log:

...
Dictionary check beginning
Tablespace 'TMP' #7 found in data dictionary,
but not in the controlfile. Adding to controlfile.
Tablespace 'OT_DATA_02' #9 found in data dictionary,
but not in the controlfile. Adding to controlfile.
Tablespace 'OT_DATA_03' #10 found in data dictionary,
but not in the controlfile. Adding to controlfile.
Tablespace 'OT_DATA_04' #11 found in data dictionary,
but not in the controlfile. Adding to controlfile.
Tablespace 'OT_DATA_05' #12 found in data dictionary,
but not in the controlfile. Adding to controlfile.
Tablespace 'OT_DATA_06' #13 found in data dictionary,
but not in the controlfile. Adding to controlfile.
Tablespace 'OT_DATA_07' #14 found in data dictionary,
but not in the controlfile. Adding to controlfile.
File #8 found in data dictionary but not in controlfile.
Creating OFFLINE file 'MISSING00008' in the controlfile.
This file can no longer be recovered so it must be dropped.
File #9 found in data dictionary but not in controlfile.
Creating OFFLINE file 'MISSING00009' in the controlfile.
This file can no longer be recovered so it must be dropped.
File #10 found in data dictionary but not in controlfile.
Creating OFFLINE file 'MISSING00010' in the controlfile.
This file can no longer be recovered so it must be dropped.
File #11 found in data dictionary but not in controlfile.
Creating OFFLINE file 'MISSING00011' in the controlfile.
This file can no longer be recovered so it must be dropped.
File #12 found in data dictionary but not in controlfile.
Creating OFFLINE file 'MISSING00012' in the controlfile.
This file can no longer be recovered so it must be dropped.
File #13 found in data dictionary but not in controlfile.
Creating OFFLINE file 'MISSING00013' in the controlfile.
This file can no longer be recovered so it must be dropped.
Dictionary check complete
...

Tablespace check:

SQL> set lines 200
SQL> set pages 999
SQL> col tablespace_name for a30
SQL> col file_name for a55
SQL> select tablespace_name, file_name from dba_data_files
  2  union all
  3  select tablespace_name, file_name from dba_temp_files;

TABLESPACE_NAME                FILE_NAME
------------------------------ -------------------------------------------------------
BILL_DATA                      /oratest/oradata/datafiles/bill_data01.dbf
USERS                          /oratest/oradata/datafiles/users01.dbf
APP_DATA                       /oratest/oradata/datafiles/app_data01.dbf
SYSAUX                         /oratest/oradata/datafiles/sysaux01.dbf
UNDOTBS1                       /oratest/oradata/datafiles/undotbs101.dbf
SYSTEM                         /oratest/oradata/datafiles/system01.dbf
OT_DATA_01                     /oratest/oradata/datafiles/ot_data_01.dbf
OT_DATA_02                     /oratest/product/10.2.0/dbs/MISSING00008
OT_DATA_03                     /oratest/product/10.2.0/dbs/MISSING00009
OT_DATA_04                     /oratest/product/10.2.0/dbs/MISSING00010
OT_DATA_05                     /oratest/product/10.2.0/dbs/MISSING00011
OT_DATA_06                     /oratest/product/10.2.0/dbs/MISSING00012
OT_DATA_07                     /oratest/product/10.2.0/dbs/MISSING00013
TMP                            /oratest/oradata/datafiles/tmp01.dbf

14 rows selected.

Drop tablespaces with missing datafiles:

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 1342177280 bytes
Fixed Size                  1267380 bytes
Variable Size            1191184716 bytes
Database Buffers          134217728 bytes
Redo Buffers               15507456 bytes
Database mounted.
SQL> alter database datafile '/oratest/product/10.2.0/dbs/MISSING00008' offline drop;

Database altered.

SQL> alter database datafile '/oratest/product/10.2.0/dbs/MISSING00009' offline drop;

Database altered.

SQL> alter database datafile '/oratest/product/10.2.0/dbs/MISSING00010' offline drop;

Database altered.

SQL> alter database datafile '/oratest/product/10.2.0/dbs/MISSING00011' offline drop;

Database altered.

SQL> alter database datafile '/oratest/product/10.2.0/dbs/MISSING00012' offline drop;

Database altered.

SQL> alter database datafile '/oratest/product/10.2.0/dbs/MISSING00013' offline drop;

Database altered.



SQL> alter database open;

Database altered.


SQL> drop tablespace OT_DATA_02 including contents;

Tablespace dropped.

SQL> drop tablespace OT_DATA_03 including contents;

Tablespace dropped.

SQL> drop tablespace OT_DATA_04 including contents;

Tablespace dropped.

SQL> drop tablespace OT_DATA_05 including contents;

Tablespace dropped.

SQL> drop tablespace OT_DATA_06 including contents;

Tablespace dropped.

SQL> drop tablespace OT_DATA_07 including contents;

Tablespace dropped.

Final check:

SQL> select tablespace_name, file_name from dba_data_files;

TABLESPACE_NAME                FILE_NAME
------------------------------ -------------------------------------------------------
OT_DATA_01                     /oratest/oradata/datafiles/ot_data_01.dbf
BILL_DATA                      /oratest/oradata/datafiles/bill_data01.dbf
USERS                          /oratest/oradata/datafiles/users01.dbf
APP_DATA                       /oratest/oradata/datafiles/app_data01.dbf
SYSAUX                         /oratest/oradata/datafiles/sysaux01.dbf
UNDOTBS1                       /oratest/oradata/datafiles/undotbs101.dbf
SYSTEM                         /oratest/oradata/datafiles/system01.dbf

12 rows selected.

Update:
I want to thank Damir Vadas for noticing unnecessary cataloging of backup pieces in this demo case.

References:
Metalink ID 102786.1

10 comments:

Damir VadasJanuary 13, 2010 at 4:58 AM
Marko,

this is really nice. A work for respect and honor.

Question to clear up:
why did you need to "restore controlfile from '/home/oratest/rmanbkp/controlf_TESTDB_i5l38viu_1605_20100112';
"
and then to catalogize it:
"catalog start with '/home/oratest/rmanbkp';"
?

THX
Marko SuticJanuary 13, 2010 at 11:16 AM
Hi Damir,

you are correct about restore of control file in my script. It would be enough just to catalogize it before restore.

Another enhancement to RMAN backup script that could speed up restore process a little is adding filesperset 1, or backup as copy datafile. Intention is to speed up RMAN seeking time for pieces.

Thank you for commenting.

Cheers,
Marko
Damir VadasJanuary 13, 2010 at 6:30 PM
Marko,
Frankly I do not find too much benefits in cataloging that. Have you any numbers on that ...percentage... or like that. Was cataloging necessary? This where I have lost your point a little bit...

ThX
Damir Vadas
http://damir-vadas.blogspot.com
Marko SuticJanuary 13, 2010 at 9:11 PM
Hi Damir,

I don't have any numbers or percentages and I can agree with you about gained benefits.
This was just first thing that came on my mind while I was performing demo case.

Thank you for pointing that out.

I will play little more with partial restores and test other solutions...

Regards,
Marko
Damir VadasJanuary 13, 2010 at 9:54 PM
Marko,

what i wanted to ay is to put only necessary information that explain the problem-all other may confuse or hide the geniality of the solution.
Which this one is!
Regards,
Damir Vadas
http://damir-vadas.blogspot.com
Marko SuticJanuary 13, 2010 at 11:04 PM
Hi Damir,

I've (finally) made little corrections to the blog post... cataloging wasn't necessary in this case so I deleted those lines to avoid confusion.

Thanks for being persistent :)

Regards,
Marko
StorageCraftJanuary 24, 2010 at 10:51 AM
The programing for the data storage looks quite a complex for me but i will try and understand it as extracting data for me is very essential. Thanks for the program.
AnonymousJuly 1, 2011 at 6:57 PM
Marko,

Thank you for sharing the demo. It's very helpful.

- Frank
Marko SuticJuly 2, 2011 at 11:46 AM
Hi Frank,

thank you for the feedback.

Best Regards,
Marko
AWS RDS SQL Server Restore BackupFebruary 28, 2018 at 5:30 AM
AWS RDS SQL server restore backup is very important and this blog explain very well. Thanks for sharing useful info.

Menu

Marko Sutic's Database Blog

Tuesday, January 12, 2010

Partial restore of a database

10 comments:

CATEGORIES

Blogroll

Recent Comments

About