Subject: Perform tablespace point-in-time recovery using transportable tablespace Type: BULLETIN Status: PUBLISHED PURPOSE Tablespace point-in-time recovery (TSPITR) is simplified using the transportable tablespace feature in 8.1.x version. SCOPE & APPLICATION For users requiring to recover a tablespace from a backup. RELATED DOCUMENTS Oracle8i Administrator's Guide [NOTE:77523.1] Transportable Tablespaces -- An Example to setup and use [NOTE:100693.1] Getting Started with Transportable Tablespaces This method is similar to the O/S TSPITR, except you use the transportable tablespace feature to move recovered tablespaces from the secondary database to the primary database. Advantages: The major difference between O/S TSPITR and TSPITR via transportable tablespaces is that for the former you must follow special procedures to create clone init.ora files and mount the clone database. O/S TSPITR assumes that the user may place the clone database on the same computer as the primary database; the special clone database commands provide error checks to prevent corruption of the primary database on the same computer while recovering the clone database. Performing TSPITR via transportable tablespaces relaxes this requirement. If you restore backups to a different computer separate from the primary database, you can start the secondary database as if it were the primary database, using the normal database MOUNT command instead of the clone database MOUNT command. If you restore backups on the same computer as the primary database, it is however recommended to follow special procedures to create the secondary database as described in O/S TSPITR, since this procedure helps prevent accidental corruption of the primary database while recovering the secondary database on the same computer. TSPITR via transportable tablespaces provides basically the same functionality as O/S TSPITR, but is more flexible since: 1. You can recover dropped tablespaces 2. You can transport the recovered tablespaces from the secondary database to a different database to test the procedure before making permanent changes to the primary database. *** -------------------------------------------------------------- *** Technical Method and Explanation *** -------------------------------------------------------------- 1/ Restore the backup to construct the secondary database. 2/ Recover the secondary database to the specified point in time. 3/ Open the secondary database with the RESETLOGS option. 4/ Make the tablespaces in the recovery set read-only by issuing the ALTER TABLESPACE READ ONLY command. 5/ Generate the transportable set by running EXPORT. Include all recovered tablespaces in the recovery set. 6/ In the primary database, drop the tablespaces of the recovery set via the DROP TABLESPACE command. 7/ Copy the datafiles of the recovered tablespaces and the export file to the primary database site. 8/ Plug in the transportable set into the primary database by running IMPORT. 9/ If necessary, make the recovered tablespaces read write by issuing the ALTER TABLESPACE READ WRITE command. *** --------------------------------------------------------------- *** Sample testcase *** --------------------------------------------------------------- ------------------------------------------------------------------- -- Make a full backup of the primary database to a backup directory -- Simulate a user error (DELETE/COMMIT) ------------------------------------------------------------------- SQL> CREATE TABLE test (c1 number) TABLESPACE users; Table created. SQL> INSERT INTO test VALUES (1); 1 row created. SQL> COMMIT; Commit complete. SQL> ALTER SYSTEM SWITCH LOGFILE; System altered. SQL> !date mar avr 11 12:36:09 CEST 2000 SQL> DELETE FROM test; 1 row deleted. SQL> COMMIT; Commit complete. SQL> EXECUTE SYS.DBMS_TTS.TRANSPORT_SET_CHECK('USERS',FALSE); PL/SQL procedure successfully completed. SQL> SELECT * FROM sys.transport_set_violations; no rows selected ------------------------------------------------------------------- -- Restore the full backup of the primary database to build a -- secondary database and recover until the user error ------------------------------------------------------------------- SVRMGR> connect internal Connected. SVRMGR> startup ORACLE instance started. Total System Global Area 7769488 bytes Fixed Size 64912 bytes Variable Size 7122944 bytes Database Buffers 409600 bytes Redo Buffers 172032 bytes Database mounted. SVRMGR> SVRMGR> RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL TIME '2000-04-11:12:36:09'; ORA-00279: change 622112 generated at 04/11/00 12:36:01 needed for thread 1 ORA-00289: suggestion : /oracle/apps/u02/rdbms/arch/1_2410.dbf ORA-00280: change 622112 for thread 1 is in sequence #2410 Specify log: {=suggested | filename | AUTO | CANCEL} Log applied. Media recovery complete. SVRMGR> alter database open resetlogs; Statement processed. SVRMGR> select * from system.test; C1 ---------- 1 1 row selected. ------------------------------------------------------------------- -- Make the recovered transportable tablespace READ ONLY ------------------------------------------------------------------- SVRMGR> ALTER TABLESPACE users READ ONLY; Statement processed. ------------------------------------------------------------------- -- Export the metadata of the recovered transportable tablespace ------------------------------------------------------------------- $ exp sys/manager transport_tablespace=y tablespaces=users file=V815 Export: Release 8.1.5.0.0 - Production on Tue Apr 11 13:35:34 2000 (c) Copyright 1999 Oracle Corporation. All rights reserved. Connected to: Oracle8i Enterprise Edition Release 8.1.5.0.0 - Production With the Partitioning and Java options PL/SQL Release 8.1.5.0.0 - Production Export done in US7ASCII character set and JA16EUC NCHAR character set Note: table data (rows) will not be exported About to export transportable tablespace metadata... For tablespace USERS ... . exporting cluster definitions . exporting table definitions . . exporting table TEST . . exporting table WWV_DOCUMENT$ . . exporting table WWV_DOCUMENTPART . . exporting table WWV_SYS_PRIVS$ . . exporting table WWV_SYS_COMPONENT_SCHEMAS$ . . exporting table WWV_APP_OWNER$ . . exporting table WWV_JAVA_SCRIPTS$ . . exporting table WWV_HELP$ . . exporting table WWV_HELP_RELATED_TOPICS$ . . exporting table WWV_HELP_INDEX$ . . exporting table WWV_HELP_CONTEXT_SENSITIVE$ . . exporting table WWV_HELP_MUTAB . . exporting table CTX_TEMP_WWV_HELP . . exporting table WWV_LOG_INTERVAL$ . . exporting table WWV_ACTIVITY_LOG1$ . . exporting table WWV_ACTIVITY_LOG2$ . . exporting table WWV_SYS_DRIVER_PARAMETERS$ . . exporting table WWV_SYS_MESSAGES$ . . exporting table DUAL100 . . exporting table WWV_SYS_MENUS$ . . exporting table WWV_USR_MENUS$ . . exporting table WWV_SYS_LANGUAGES$ . . exporting table WWV_SYS_LABELS$ . . exporting table WWV_SYS_LABEL_USAGES$ . . exporting table WWV_SYS_TEXT_BLOCKS$ . . exporting table WWV_SYS_TEXT_BLOCK_USAGES$ . . exporting table WWV_USER_LABELS$ . . exporting table WWV_USER_LABEL_USAGES$ . . exporting table WWV_USER_TEXT_BLOCKS$ . . exporting table WWV_USER_TEXT_BLOCK_USAGES$ . . exporting table WWV_SYS_CAT_LOV$ . . exporting table WWV_SYS_STATIC_LOV$ . . exporting table WWV_SYS_DYNAMIC_LOV$ . . exporting table WWV_USR_CAT_LOV$ . . exporting table WWV_USR_STATIC_LOV$ . . exporting table WWV_USR_DYNAMIC_LOV$ . . exporting table WWV_SYS_IMAGE_TYPES$ . . exporting table WWV_SYS_IMAGES$ . . exporting table WWV_USR_IMAGE_TYPES$ . . exporting table WWV_USR_IMAGES$ . . exporting table WWV_SYS_PADMIN$ . . exporting table WWV_SYS_SURVEY_LOC$ . . exporting table WWV_SYS_JAVA_SCRIPTS$ . . exporting table WWV_USR_JAVA_SCRIPTS$ . . exporting table WWV_ERRORS$ . . exporting table WWV_SYS_MODULE_GROUPS$ . . exporting table WWV_SYS_MODULE_TYPES$ . . exporting table WWV_SYS_BUILDER_DETAILS$ . . exporting table WWV_MODULES$ . . exporting table WWV_MODULE_DETAILS$ . . exporting table WWV_SYS_APPLICATION_TEMPLATES$ . . exporting table WWV_USR_APPLICATION_TEMPLATES$ . . exporting table WWV_SYS_APP_TEMPLATE_DETAILS$ . . exporting table WWV_USR_APP_TEMPLATE_DETAILS$ . . exporting table WWV_WIZARD_VALUES$ . . exporting table WWV_SITEBUILDER_INSTALL$ . . exporting table WWV_SITEBUILDER_DDL$ . . exporting table WWV_WIZARD_EXECUTION_STATUS$ . . exporting table WWV_WIZARD_ERROR_LOG$ . . exporting table WWV_WIZARD_ABORT_STATUS$ . . exporting table WWV_FONTS$ . . exporting table WWV_COLORS$ . . exporting table WWV_PREFERENCES$ . . exporting table WWV_SYS_PREFERENCES$ . . exporting table WWV_PARM_SAVES$ . . exporting table WWV_BATCH_SUBMISSIONS$ . . exporting table WWV_BATCH_RESULTS$ . . exporting table WWV_SYS_DEBUG . . exporting table F_EMP . exporting referential integrity constraints . exporting triggers . end transportable tablespace metadata export Export terminated successfully without warnings. $ $ ls -l V815* -rw-r--r-- 1 oracle oracle 98304 avr 11 13:39 V815.dmp SVRMGR> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SVRMGR> ------------------------------------------------------------------- -- Drop the tablespace from the primary database ------------------------------------------------------------------- SVRMGR> DROP TABLESPACE users INCLUDING CONTENTS; Statement processed. ------------------------------------------------------------------- -- Copy the datafiles of the transportable tablespace and the -- export file ------------------------------------------------------------------- $ cd /oracle/apps/u01/rdbms/8.1.5 $ cp /oracle/apps/u02/dj/V815.dmp . $ cp /oracle/apps/u02/dj/users01.dbf . ------------------------------------------------------------------- -- Import the metadata of the recovered tablespace ------------------------------------------------------------------- $ imp sys/manager file=V815.dmp transport_tablespace=y tablespaces=users datafiles='/oracle/apps/u01/rdbms/8.1.5/users01.dbf' Import: Release 8.1.5.0.0 - Production on Tue Apr 11 13:56:54 2000 (c) Copyright 1999 Oracle Corporation. All rights reserved. Connected to: Oracle8i Enterprise Edition Release 8.1.5.0.0 - Production With the Partitioning and Java options PL/SQL Release 8.1.5.0.0 - Production Export file created by EXPORT:V08.01.05 via conventional path About to import transportable tablespace(s) metadata... import done in US7ASCII character set and JA16EUC NCHAR character set . importing SYS's objects into SYS . importing SYSTEM's objects into SYSTEM . . importing table "TEST" . importing WEBDB's objects into WEBDB . . importing table "WWV_DOCUMENT$" . . importing table "WWV_DOCUMENTPART" . . importing table "WWV_SYS_PRIVS$" . . importing table "WWV_SYS_COMPONENT_SCHEMAS$" . . importing table "WWV_APP_OWNER$" . . importing table "WWV_JAVA_SCRIPTS$" . . importing table "WWV_HELP$" . . importing table "WWV_HELP_RELATED_TOPICS$" . . importing table "WWV_HELP_INDEX$" . . importing table "WWV_HELP_CONTEXT_SENSITIVE$" . . importing table "WWV_HELP_MUTAB" . . importing table "CTX_TEMP_WWV_HELP" . . importing table "WWV_LOG_INTERVAL$" . . importing table "WWV_ACTIVITY_LOG1$" . . importing table "WWV_ACTIVITY_LOG2$" . . importing table "WWV_SYS_DRIVER_PARAMETERS$" . . importing table "WWV_SYS_MESSAGES$" . . importing table "DUAL100" . . importing table "WWV_SYS_MENUS$" . . importing table "WWV_USR_MENUS$" . . importing table "WWV_SYS_LANGUAGES$" . . importing table "WWV_SYS_LABELS$" . . importing table "WWV_SYS_LABEL_USAGES$" . . importing table "WWV_SYS_TEXT_BLOCKS$" . . importing table "WWV_SYS_TEXT_BLOCK_USAGES$" . . importing table "WWV_USER_LABELS$" . . importing table "WWV_USER_LABEL_USAGES$" . . importing table "WWV_USER_TEXT_BLOCKS$" . . importing table "WWV_USER_TEXT_BLOCK_USAGES$" . . importing table "WWV_SYS_CAT_LOV$" . . importing table "WWV_SYS_STATIC_LOV$" . . importing table "WWV_SYS_DYNAMIC_LOV$" . . importing table "WWV_USR_CAT_LOV$" . . importing table "WWV_USR_STATIC_LOV$" . . importing table "WWV_USR_DYNAMIC_LOV$" . . importing table "WWV_SYS_IMAGE_TYPES$" . . importing table "WWV_SYS_IMAGES$" . . importing table "WWV_USR_IMAGE_TYPES$" . . importing table "WWV_USR_IMAGES$" . . importing table "WWV_SYS_PADMIN$" . . importing table "WWV_SYS_SURVEY_LOC$" . . importing table "WWV_SYS_JAVA_SCRIPTS$" . . importing table "WWV_USR_JAVA_SCRIPTS$" . . importing table "WWV_ERRORS$" . . importing table "WWV_SYS_MODULE_GROUPS$" . . importing table "WWV_SYS_MODULE_TYPES$" . . importing table "WWV_SYS_BUILDER_DETAILS$" . . importing table "WWV_MODULES$" . . importing table "WWV_MODULE_DETAILS$" . . importing table "WWV_SYS_APPLICATION_TEMPLATES$" . . importing table "WWV_USR_APPLICATION_TEMPLATES$" . . importing table "WWV_SYS_APP_TEMPLATE_DETAILS$" . . importing table "WWV_USR_APP_TEMPLATE_DETAILS$" . . importing table "WWV_WIZARD_VALUES$" . . importing table "WWV_SITEBUILDER_INSTALL$" . . importing table "WWV_SITEBUILDER_DDL$" . . importing table "WWV_WIZARD_EXECUTION_STATUS$" . . importing table "WWV_WIZARD_ERROR_LOG$" . . importing table "WWV_WIZARD_ABORT_STATUS$" . . importing table "WWV_FONTS$" . . importing table "WWV_COLORS$" . . importing table "WWV_PREFERENCES$" . . importing table "WWV_SYS_PREFERENCES$" . . importing table "WWV_PARM_SAVES$" . . importing table "WWV_BATCH_SUBMISSIONS$" . . importing table "WWV_BATCH_RESULTS$" . . importing table "WWV_SYS_DEBUG" . importing TESTRM's objects into TESTRM . . importing table "F_EMP" . importing WEBDB's objects into WEBDB About to enable constraints... Import terminated successfully without warnings. $ ------------------------------------------------------------------- -- Check that the deleted rows are retrieved ------------------------------------------------------------------- SQL> select * from test; C1 ---------- 1 ------------------------------------------------------------------- -- Reset the recovered tablespace to READ WRITE ------------------------------------------------------------------- SQL> SELECT status FROM dba_tablespaces 2 WHERE tablespace_name='USERS'; STATUS --------- READ ONLY SQL> ALTER TABLESPACE users READ WRITE; Tablespace altered.