Tuesday, December 14, 2010

RMAN Tablespace Point-in-Time Recovery and ORA-19602

Bug: 7016765 10.2.0.3.0 RDBMS 10.2.0.3.0 RMAN
Abstract: ORA-19602 WHEN RUNNING RMAN'S TSPITR
RMAN TSPITR does not always work using datafilecopies. Not an issue with backupsets.
This is fix in 10.2.0.5

Path 7016765 have been ported for Linux x86_64. You can also check on Metalink patch search for other platforms.

This bug also states for the 10.2.0.4 version and if you hit this there is no workaround until you apply 10.2.0.5.So in order to avoid this bug , you must manual duplicate the database to another instance and applying incomplete recovery and then do a transaportable tablespace set export/import to the target database for tbs you want to perform TSPITR.

My scenario is based on a 3 Node Oracle 10gR2 (10.2.0.4) RAC database.

A tablespace called MYTBS must be recovered back in time before an accidentally "drop user cascade" occurred. The user dropped at 14:00 and itried to use TSPITR but RMAN failed with ORA-19602 as stated in the beginning of the article.

The only valid workaround to this very annoying issue (believe me , i have fall into this bug on my production 24x7 system) is to use the duplicate database feature doing a whole database point in time recovery skipping all the tablespaces you except SYSTEM,SYSAUX,UNDO and the tablespace you want to recover.

In this example ORACLE_BASE=/u01/app/oracle

1) Create an AUX init.ora file like you do in manual TSPITR having the following entries.

# FILE: initAUX.ora
audit_file_dest =/u01/app/oracle/admin/AUX/adump
background_dump_dest =/u01/app/oracle/admin/AUX/bdump
core_dump_dest =/u01/app/oracle/admin/AUX/cdump
user_dump_dest =/u01/app/oracle/admin/AUX/udump
db_name =AUX
instance_name =AUX
db_unique_name=AUX
db_create_file_dest=/data/ttbs
db_recovery_file_dest=/data/ttbs
db_recovery_file_dest_size=100000M # Resize according to the size on the mount point you are duplicating
db_block_size = 8192
compatible=10.2.0.4.0
db_block_size=8192
db_files=1000
large_pool_size=1M
shared_pool_size=110M
remote_login_passwordfile=exclusive
undo_management=AUTO
undo_retention=900
undo_tablespace=UNDO2 # Must be the same as the target instance

I have placed my file in the [oracle@labdb01 AUX]$ORACLE_HOME/dbs in order sqlplus to use it by default.

2) Create the following directories and the password file
[oracle@labdb01 AUX]$ mkdir -p /data/ttbs/AUX
[oracle@labdb01 AUX]$ mkdir -p /u01/app/oracle/admin/AUX/adump
[oracle@labdb01 AUX]$ mkdir -p /u01/app/oracle/admin/AUX/bdump
[oracle@labdb01 AUX]$ mkdir -p /u01/app/oracle/admin/AUX/udump
[oracle@labdb01 AUX]$ mkdir -p /u01/app/oracle/admin/AUX/cdump

[oracle@labdb01 AUX]$ orapwd file=[oracle@labdb01 AUX]$ORACLE_HOME/dbs/orapwAUX password=pass

3) Startup nomount the AUX instance
[oracle@labdb01 AUX]$ export ORACLE_SID=AUX
[oracle@labdb01 AUX]$ sqlplus / as sysdba
SQL> create spfile from pfile;

File created.

SQL> startup nomount
ORACLE instance started.

Total System Global Area 205520896 bytes
Fixed Size 2082752 bytes
Variable Size 146802752 bytes
Database Buffers 50331648 bytes
Redo Buffers 6303744 bytes
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
[oracle@labdb01 AUX]$

4) Create an rman script for the duplication (dup.rcv)
Keep in mind that SYSTEM,SYSAUX and UNDO tablespaces can not be skipped.
Also the recovery set of tablespaces must consist of all dependable objects e.g table and indexes , tables/partitions, lob segments , lob indexes.
Check also the v$archived_log in order to find the exact time to use.

RUN
{
sql 'alter system archive log current';
allocate channel C1 device type disk;
allocate auxiliary channel C2 device type disk;
set until time "to_date('14/12/2010 13:20:00','DD/MM/YYYY HH24:MI:SS')";
DUPLICATE TARGET DATABASE TO AUX
SKIP TABLESPACE USERS;
}

5) Connect to rman and run the script
NOTE: if your target db is using a catalog then add the string catalog user/pass@catalog_sid

[oracle@labdb01 ~]$ rman target sys@testdb1 auxiliary /
RMAN>@dup.rcv

NOTE: You cannot skip the default temporary tablespace from the recovery set.

6) Begin transport tablespace procedure.

on AUX :

SQL>begin
SQL>sys.dbms_tts.transport_set_check('MYTBS',TRUE);
SQL>end;
SQL>/

PL/SQL procedure successfully completed.

SQL>select * from sys.transport_set_violations;

If this query returns rows then fix them before proceed.

SQL>alter tablespace mytbs read only;

SQL> create or replace directory exp_dir as '/data/ttbs/AUX';

Directory created.

SQL> grant read,write on directory exp_dir to system;

Grant succeeded.

SQL>exit;

[oracle@labdb01 ~]$ expdp system dumpfile=mytbs.dmp directory=EXP_DIR TRANSPORT_TABLESPACES=MYTBS TRANSPORT_FULL_CHECK=Y

If the expdp fails then :

SQL>select reason,count(*) from sys.pluggable_set_check where ts1_name like '%MYTBS%' group by reason;

And resolve each reason !!!

Copy the dmp file and the datafile(s) of the tabelspace to the desired target db directory.
[oracle@labdb01 ~]$ cp /data/ttbs/AUX/mytbs.dmp /data/ttbs/testdb/
[oracle@labdb01 ~]$ cp /data/ttbs/AUX/datafile/o1_mf_mytbs_6jgv6obd_.dbf /data/ttbs/testdb/

IF you are recovering form a user error like our scenario drop the tabelspace from the target !!
Re create the dropped user. Remember to grant his roles and give him his object grants.
My user had the dba role granted so.



[oracle@labdb01 ~]$ sqlplus sys@testdb as sysdba
SQL> drop tablespace mytbs including contents and datafiles cascade constraints;
SQL> grant dba to agis identified by agis;
SQL> alter user agis default tablespace users;
SQL> exit;

Go to the directory you copied the files:

[oracle@labdb01 ~]$ cd /data/ttbs/testdb/

I have a directory object TTS_DIR that points to the correct O/S directory. If not create one as in previous section
[oracle@labdb01 testdb]$ export ORACLE_SID=testdb
[oracle@labdb01 testdb]$ impdp system dumpfile=mytbs.dmp logfile=imp.log DIRECTORY=TTS_DIR TRANSPORT_DATAFILES='/data/ttbs/testdb/o1_mf_mytbs_6jgv6obd_.dbf'

If you want you can omit to gather statistics again using : EXCLUDE=TABLE_STATISTICS EXCLUDE=INDEX_STATISTICS on the impdp

At this point the tablespace mytbs has been restored bur its read only and unknown to the control file.


[oracle@labdb01 testdb]$ sqlplus agis/agis

SQL*Plus: Release 10.2.0.4.0 - Production on Tue Dec 14 15:40:17 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

SQL> select table_name from user_tables;

TABLE_NAME
------------------------------
TEST

SQL> select count(*) from test;

COUNT(*)
----------
49643

BUT !!!

[oracle@labdb01 testdb]$ rman target /

Recovery Manager: Release 10.2.0.4.0 - Production on Tue Dec 14 15:41:02 2010

Copyright (c) 1982, 2007, Oracle. All rights reserved.

connected to target database: TESTDB (DBID=2520745547)

RMAN> report schema;

using target database control file instead of recovery catalog
Report of database schema

List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 490 SYSTEM *** /data/ttbs/testdb/system01.dbf
2 90 UNDOTBS1 *** /data/ttbs/testdb/undotbs01.dbf
3 360 SYSAUX *** /data/ttbs/testdb/sysaux01.dbf
4 5 USERS *** /data/ttbs/testdb/users01.dbf
5 25 UNDOTBS2 *** /data/ttbs/testdb/undotbs02.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 166 TEMP 32767 /data/ttbs/testdb/temp01.dbf


Now its the time to rename the datafile if you want or make it writable in order to inform the controlfile.

On the target instance
[oracle@labdb01 testdb]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Tue Dec 14 15:43:34 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

SQL> select file_id,file_name from dba_data_files where tablespace_name='MYTBS';

FILE_ID
----------
FILE_NAME
--------------------------------------------------------------------------------
6
/data/ttbs/testdb/o1_mf_mytbs_6jgv6obd_.dbf


SQL> alter tablespace mytbs offline;
SQL> !mv /data/ttbs/testdb/o1_mf_mytbs_6jgv6obd_.dbf /data/ttbs/testdb/mytbs.dbf
SQL> alter tablespace mytbs rename datafile '/data/ttbs/testdb/o1_mf_mytbs_6jgv6obd_.dbf' to '/data/ttbs/testdb/mytbs.dbf';

Tablespace altered.

SQL> select file_id,file_name from dba_data_files where tablespace_name='MYTBS';

FILE_ID
----------
FILE_NAME
--------------------------------------------------------------------------------
6
/data/ttbs/testdb/mytbs.dbf


SQL> alter tablespace mytbs online;

Tablespace altered.

SQL> alter tablespace mytbs read write;

Tablespace altered.

SQL> exit

Backup your tabelspace imemdiate after the restore !!!

7) Drop the AUX database
[oracle@labdb01 ~]$ export ORACLE_SID=AUX
[oracle@labdb01 ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Tue Dec 14 15:56:22 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

SQL> shutdown abort;
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
[oracle@labdb01 ~]$ rm -fr /data/ttbs/AUX
[oracle@labdb01 ~]$


We keep the password file and the init file in order to use the same configuration for other point int time recovery scenarios.

If you database uses ASM you must put the tabelspace in ASM.

From sqlplus
SQL> alter database tablespace mytbs offline;

Use RMAN to transfer the tablespace to ASM

rman target /
RMAN>
RMAN> backup as copy tablespace mytbs format '+DATA';
RMAN> switch tablespace mytbs to copy;
RMAN> recover tablespace mytbs;

From sqlplus

SQL> alter database tablespace mytbs online;

Database altered.

And you are done !!!

References:

Metalink Documnet IDs:
340848.1
368029.1
153981.1
388424.1
371556.1

No comments:

Post a Comment