RMAN Tablespace Point in Time Recovery

A user deletes data from a table and commits it. How do you retrieve that data? If using a version of Oracle with flashback technology AND you are made aware of the error while the undo information is still retained that’s not so much of a problem. If running in noarchivelog mode, and given that you have a cold backup or export lying around, the recovery process is fairly cut and dry: restore the entire database or import the table from the export.
One check to make in the import approach is for a referential integrity action, that is, is there a on delete cascade” minefield waiting for you to step in? And don’t forget about triggers. In other words, more than one table may need to be recovered. Even a standby database can leave you in the hurt locker. If the transported redo has been applied, you now have the problem (i.e., the missing data) in two places.
One of the best situations to be in is running in archivelog mode and using RMAN as your backup mechanism or process. RMAN tablespace point in time recovery (TSPITR) can be used to restore the data.
Background
This is one of those critical skills where you will be glad you have put your hands on the keyboard and practiced this several times ahead of the time when you need to do this for real.
When it comes to backup and recovery, with the emphasis on recovery, Oracle documentation (to include notes on MetaLink) is full of sage advice. One such warning (going back as far as 8i) states the following:
Do not perform RMAN TSPITR for the first time on a production system or when you have a time constraint.
Another classic one is about not putting yourself into a situation worse than you already are in. Unintended change vectors to data are one thing; your mission is to prevent that from turning into a job change vector because you trashed the production database and cannot recover it.
If you use a recovery catalog, you have unlimited attempts to get things right. If not using one, you have one shot at getting the recovery point correct. Once recovered (but you didn’t go far enough back), the backup you were using cannot be used again for that tablespace.
The root process of an RMAN TSPITR is based on creating a clone of the production database. This is where some of the existing documentation gets murky. You’ll see references to a term called auxiliary set, which includes a backup control file, the system tablespace, datafiles containing rollback (or undo) segments, and optionally, a temporary tablespace. The lifespan of the clone is what separates how TSPITR can be done. And what about redo logs? How do they factor in?
In the official RMAN TSPITR process (fully automated), the clone exists but for a short time. Once it has served its purpose (as a temporary repository/instance used to restore/recover a tablespace to a point in time), it dies in place. In fact, Oracle kills it for you. In a variation of the official process, you create a clone database (using RMAN) whose end state is as of whatever point in time (obviously in the past) you desire. The clone lives on in this case. The official TSPITR process recovers the affected tablespace in its entirety. The other process creates the tablespace in a clone database, and from there, you can single out the affected table. From that point, export/import, CTAS, or insert into via selecting across a database link are three ways to get the table’s data restored.
Nailing Down the Setup Steps
Let’s create a 4-3-2-1 model for the setup steps. The steps pertain to older versions of Oracle, but will work in at least up to10gR2. Much of the setup is taken care of for you in 10g; just tell Oracle where the auxiliary instance work area is located. The steps include editing or identifying:
4 initialization parameters
3 tablespaces (possibly more)
2 Net8/Net Services configuration files
1 parameter file (and maybe one password file)
4 Initialization Parameters

The four init.ora parameters are lock_name_space, db_file_name_convert, log_file_name_convert, and control_files. The three tablespaces are the one you need to recover, System, and Rollback or Undo. The two Net Services files requiring editing are tnsnames.ora and listener.ora. The one copy of a parameter file is a copy of the production database’s initialization parameter file.

You use the same db_name value as what is in production. The lock_name_space parameter value is a name you can give the auxiliary instance (clone works well enough) and is what distinguishes the clone from production.

You do not need to copy a control file into the clone working directory. One will be created for you, but you must specify a different name than what production uses.

The name of the production database used in this example is db10, and the name of the auxiliary instance/database is clone. Suppose your SYSTEM datafile lives in this path and is named:
D:\oracle\product\10.2.0\oradata\db10\system01.dbf
Let’s translate db10 into clone, so that the to-be-restored datafile is:
D:\oracle\product\10.2.0\oradata\clone\system01.dbf
How is this translation accomplished? One way is to use the DB_FILE_NAME_CONVERT parameter, and it would be specified by:
db_file_name_convert=(‘db10’,’clone’)
Wherever Oracle finds db10 in a path to a datafile, it will translate db10 into clone. This is where using optimal flexible architecture pays off. Suppose your SYSTEM datafiles are spread out like so:
/u001/oradata/db10/system_01.dbf
/u002/oradata/db10/system_02.dbf
/u003/oradata/db10/system_03.dbf
Create corresponding directories for clone and when all goes well, the clone will have its system datafiles automatically created for you as:
/u001/oradata/clone/system_01.dbf
/u002/oradata/clone/system_02.dbf
/u003/oradata/clone/system_03.dbf
The same thing will take place for rollback/undo and for the errant tablespace’s files. A similar parameter takes care of the redo log files:
log_file_name_convert=(‘db10’,’clone’)
Log files can also be specified/created in the RMAN run block. However, log files have to be specified somewhere, because even if using Oracle managed files in the auxiliary instance, they will not be created for you.
Other parameters such as dump locations and memory settings can be changed as well. The file renaming can be done other ways, either using a paired old versus new path in the convert parameters, or by explicitly setting a new name in the RMAN run block.
To summarize the parameters going from db10 to clone:
db_name='db10'
lock_name_space='clone'
db_file_name_convert=("db10","clone")
log_file_name_convert=("db10","clone")
control_files='D:\oracle\product\10.2.0\oradata\clone\clone_control01.ctl'
3 Tablespaces
The minimum set is the one you need to recover, plus system and rollback/undo. As mentioned, system and rollback/undo can be handled for you. If you want to restore the datafiles elsewhere other than under a translated path, use the set newname clause in the RMAN run block. You can explicitly identify the target tablespace’s datafiles when renaming them, or use their respective file IDs. The order of precedence (i.e., the file name conversion versus using newname) is listed in the documentation as:
1. SET NEWNAME
2. CONFIGURE AUXNAME
3. DB_FILE_NAME_CONVERT
4. AUXILIARY DESTINATION argument to RECOVER TABLESPACE
In line with this identification, it is handy to have a listing of the file ID numbers and the filenames. Error messages such as the one below, are not hard to decipher as far as the affected tablespace is concerned, but what if the file is an Oracle managed file named O1_MF_USERS_3PCS61ON_.DBF?

SQL> select * from emp;
select * from emp
*
ERROR at line 1:
ORA-00376: file 4 cannot be read at this time
ORA-01110: data file 4: 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\DB10\USERS01.DBF'
We got lucky with this one because the tablespace name is clear, but that may not be the case. Oracle will use the first eight characters of the tablespace name. Given there are two tablespaces, one named EIGHTLONG1 and the other EIGHTLONG2, which file (shown below) belongs to which tablespace?

Are other tablespaces needed? Run a query against SYS.TS_PITR_CHECK using the specific columns or select all columns like so:
select *
from sys.ts_pitr_check
where (ts1_name = 'USERS' and ts2_name != 'USERS')
or (ts1_name != 'USERS' and ts2_name = 'USERS');
Resolve dependencies as needed. Indexes, for example, can be dropped and rebuilt, so you don't necessarily need to take along an index tablespace. Be sure to get the DDL for re-creating them, and also for any constraints that may be dropped.
2 Net8/Net Services configuration files
This step is quite easy add entries into the tnsnames.ora and listener.ora files for the auxiliary instance. If sqlnet.ora has names.default_domain in it, then the entry in tnsnames.ora must account for that parameter.
1 parameter file (and maybe one password file)
Already covered from the 4”part, but you need an editable version of the parameter file. The production database only needs to be mounted in order to create a pfile from spfile. Of course, verify beforehand how the production database was started to begin with. What’s in the spfile may not be what’s in the pfile, and vice versa. Overwriting the pfile via the create pfile from spfile command may cause recent/needed parameters to be removed.

There is no need to copy a control file from production; RMAN is going to create one for you. Create a new password file for the clone, or copy/rename one from production. RMAN always expects you’re connecting as SYS, but when starting the clone (as in SQL*Plus), connecting as sys as sysdba needs to be authenticated.

Almost ready...

Since RMAN is being used, two or three connections need to be made. One is to target (i.e., production) and the other is to the auxiliary instance. The auxiliary or clone is started using NOMOUNT, and when connecting to auxiliary, that state will be reflected in RMAN.
RMAN> connect auxiliary sys/oracle@clone

connected to auxiliary database: DB10 (not mounted)
The third connection is based on using a recovery catalog. If using one, that connection needs to be made. If not using one, then connecting to target and auxiliary is all that is needed.

For more details on RMAN you can view on http://oracleDbaSupport.co.uk

Article Source: http://www.articleviral.com

Please Rate this Article

5 out of 54 out of 53 out of 52 out of 51 out of 5 

Not yet Rated