Oracle World: 12/21/08

Friday, December 26, 2008

How to exclude a tablespace from whole database backup

Everyday you take your backup whole database. Suppose in a tablespace the data don't change open or the tablespace contains test data only. Then you might change will backup strategy of your database to exclude certain tablespace. In order to exclude a tablespace you have to issue,

RMAN> CONFIGURE EXCLUDE FOR TABLESPACE users;

Now if you backup your database by,

RMAN> BACKUP DATABASE;

then then RMAN backs up all tablespaces in the database except users tablespace.

You can see which tablespace is excluded from your backup strategy by issue,

RMAN> SHOW EXCLUDE;

In order to skip two tablespaces or more issue command in RMAN twice or more like,
RMAN> CONFIGURE EXCLUDE FOR TABLESPACE DATA01;
RMAN> CONFIGURE EXCLUDE FOR TABLESPACE DATA02;

Here tablespace DATA01, DATA02 will be excluded from backup.

You can override this exclusion feature by explicitly issuing keyword NOEXCLUDE in order to take whole database backup.

RMAN>BACKUP DATABASE NOEXCLUDE;

You can disable the exclusion feature for users tablespace as follows:
RMAN>CONFIGURE EXCLUDE FOR TABLESPACE users CLEAR;

In order to skip READONLY and OFFLINE tablespace you can issue backup database command as,

RMAN>BACKUP DATABASE SKIP READONLY, SKIP OFFLINE;

How to exclude a tablespace from whole database backup

How to Restore spfile from backup

1.Connect to target Database with RMAN.

i)If you have lost your spfile while your database is running then,

RMAN>CONNECT TARGET /

ii)If your database is not up and you don't use recovery catalog then use,
RMAN>CONNECT TARGET /
RMAN>SET DBID 3386862614

2)Start the instance with dummy parameter file.
RMAN>STARTUP FORCE NOMOUNT

3)Restore server parameter file.
To restore in default location,
RMAN> RESTORE SPFILE FROM AUTOBACKUP;
To restore in another location,
RMAN> RESTORE SPFILE TO 'new_location' FROM AUTOBACKUP;

If you want to restore to a pfile then use,
RMAN> RESTORE SPFILE TO PFILE '/tmp/initTEMP.ora';

4)Start the instance.
RMAN>STARTUP;

How to Restore spfile from backup

Restore and Recover database to a new host

In this post I will show how I can restore and recover a database to another host. In this case DBID of the database will be the same as of original database. But whenever your purpose is to create a new copy of the database then use RMAN DUPLICATE; this will change the DBID of the new database.

In this example my database name dbase1 and the database dbase1 is running on neptune machine. I like to take a backup on neptune machine, transfer the backup to saturn machine and perform restore and recover in saturn machine.

1)In neptune machine(Source)

RMAN> backup database;

Starting backup at 06-MAY-08
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/oradata2/data1/dbase1/system01.dbf
input datafile fno=00003 name=/oradata2/data1/dbase1/sysaux01.dbf
input datafile fno=00005 name=/oradata2/DBASE1/datafile/o1_mf_tbs2_41vyzfrq_.dbf
input datafile fno=00006 name=/oradata2/DBASE1/datafile/o1_mf_after_on_420r4f9h_.dbf
input datafile fno=00002 name=/oradata2/data1/dbase1/undotbs01.dbf
input datafile fno=00004 name=/oradata2/data1/dbase1/users01.dbf
input datafile fno=00007 name=/oradata2/DBASE1/datafile/o1_mf_after_on_420r675z_.dbf
input datafile fno=00008 name=/oradata2/DBASE1/datafile/o1_mf_after_on_420x2yw8_.dbf
channel ORA_DISK_1: starting piece 1 at 06-MAY-08
channel ORA_DISK_1: finished piece 1 at 06-MAY-08
piece handle=/oradata2/flash_recovery_area/DBASE1/backupset/2008_05_06/
o1_mf_nnndf_TAG20080506T150716_421c355f_.bkp tag=TAG20080506T150716 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:35
Finished backup at 06-MAY-08

Starting Control File and SPFILE Autobackup at 06-MAY-08
piece handle=/oradata2/flash_recovery_area/DBASE1/autobackup/2008_05_06/
o1_mf_s_654016132_421c64vl_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 06-MAY-08

2)Transfer this two backup pieces to target machine(From Neptune)
bash-3.00$ scp /oradata2/flash_recovery_area/DBASE1/backupset/2008_05_06/
o1_mf_nnndf_TAG20080506T150716_421c355f_.bkp oracle@saturn:/oradata2/
Password:
o1_mf_nnndf_TAG20080 100% |***********************************************| 525 MB 00:59
bash-3.00$ scp /oradata2/flash_recovery_area/DBASE1/autobackup/2008_05_06/
o1_mf_s_654016132_421c64vl_.bkp oracle@saturn:/oradata2/
Password:
o1_mf_s_654016132_42 100% |***********************************************| 6976 KB 00:00

3)Determine the DBID of source machine(Neptune here)

SQL> select dbid from v$database;
DBID
----------
3386862614

4)Now perform task on target machine(Saturn here).
First set ORACLE_SID,
-bash-3.00$export ORACLE_SID=dbase1
Then connect to rman,
-bash-3.00$ rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on Tue May 6 05:22:39 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database (not started)

5)Set DBID and restore spfile to pfile.
RMAN> set dbid 3386862614
executing command: SET DBID

RMAN> startup nomount
startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/oracle/app/oracle/product/10.2.0/db_1/dbs/initdbase1.ora'

starting Oracle instance without parameter file for retrival of spfile
Oracle instance started

Total System Global Area 159383552 bytes
Fixed Size 2019224 bytes
Variable Size 67108968 bytes
Database Buffers 83886080 bytes
Redo Buffers 6369280 bytes

RMAN> restore spfile to pfile '/oracle/app/oracle/product/10.2.0/db_1/dbs/initdbase1.ora' from '/oradata2/o1_mf_s_654016132_421c64vl_.bkp';

Starting restore at 06-MAY-08
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=36 devtype=DISK

channel ORA_DISK_1: autobackup found: /oradata2/o1_mf_s_654016132_421c64vl_.bkp
channel ORA_DISK_1: SPFILE restore from autobackup complete
Finished restore at 06-MAY-08

Open the pfile with an editor file and if you wish change the location
6)start the instance with pfile.
RMAN> STARTUP FORCE NOMOUNT PFILE='/oracle/app/oracle/product/10.2.0/db_1/dbs/initdbase1.ora';

Oracle instance started

Total System Global Area 209715200 bytes

Fixed Size 2019608 bytes
Variable Size 109055720 bytes
Database Buffers 92274688 bytes
Redo Buffers 6365184 bytes

7)Restore controlfile and mount the database.
RMAN> RESTORE CONTROLFILE FROM '/oradata2/o1_mf_s_654016132_421c64vl_.bkp';

Starting restore at 06-MAY-08
using channel ORA_DISK_1

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
output filename=/oradata2/DBase1/control01.ctl
output filename=/oradata2/DBase1/control02.ctl
output filename=/oradata2/DBase1/control03.ctl
Finished restore at 06-MAY-08

RMAN> ALTER DATABASE MOUNT;

database mounted
released channel: ORA_DISK_1

8)From SQL*Plus determine the data file and redo log file name.
SQL> COLUMN NAME FORMAT a70
SQL> 1 SELECT FILE# AS "File/Grp#", NAME FROM V$DATAFILE
2 UNION
3* SELECT GROUP#,MEMBER FROM V$LOGFILE

File/Grp# NAME
---------- ------------------------------------------------------------
1 /oradata2/data1/dbase1/redo01.log
1 /oradata2/data1/dbase1/system01.dbf
2 /oradata2/data1/dbase1/redo02.log
2 /oradata2/data1/dbase1/undotbs01.dbf
3 /oradata2/data1/dbase1/redo03.log
3 /oradata2/data1/dbase1/sysaux01.dbf
4 /oradata2/data1/dbase1/users01.dbf
5 /oradata2/DBASE1/datafile/o1_mf_tbs2_41vyzfrq_.dbf
6 /oradata2/DBASE1/datafile/o1_mf_after_on_420r4f9h_.dbf
7 /oradata2/DBASE1/datafile/o1_mf_after_on_420r675z_.dbf
8 /oradata2/DBASE1/datafile/o1_mf_after_on_420x2yw8_.dbf

11 rows selected.

9)Catalog your backuppiece.
RMAN> catalog backuppiece '/oradata2/o1_mf_nnndf_TAG20080506T150716_421c355f_.bkp';

cataloged backuppiece
backup piece handle=/oradata2/o1_mf_nnndf_TAG20080506T150716_421c355f_.bkp
recid=33 stamp=65398295
RMAN> list backup;

List of Backup Sets
===================

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
32 Full 525.67M DISK 00:01:31 06-MAY-08
BP Key: 33 Status: AVAILABLE Compressed: NO Tag: TAG20080506T150716
Piece Name: /oradata2/o1_mf_nnndf_TAG20080506T150716_421c355f_.bkp
List of Datafiles in backup set 32
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 745212 06-MAY-08 /oradata2/data1/dbase1/system01.dbf
2 Full 745212 06-MAY-08 /oradata2/data1/dbase1/undotbs01.dbf
3 Full 745212 06-MAY-08 /oradata2/data1/dbase1/sysaux01.dbf
4 Full 745212 06-MAY-08 /oradata2/data1/dbase1/users01.dbf
5 Full 745212 06-MAY-08 /oradata2/DBASE1/datafile/o1_mf_tbs2_41vyzfrq_.dbf
6 Full 745212 06-MAY-08 /oradata2/DBASE1/datafile/o1_mf_after_on_420r4f9h_.dbf
7 Full 745212 06-MAY-08 /oradata2/DBASE1/datafile/o1_mf_after_on_420r675z_.dbf
8 Full 745212 06-MAY-08 /oradata2/DBASE1/datafile/o1_mf_after_on_420x2yw8_.dbf

10)Make a script by issuing SET NEWNAME if you want different file name other than source.

In the script issue SET UNTIL clause and restore and recover database.
RMAN> @/export/home/oracle/rman

RMAN> run{
2> set newname for datafile 1 to '/oradata2/DBase1/system01.dbf';
3> set newname for datafile 2 to '/oradata2/DBase1/undotbs01.dbf';
4> set newname for datafile 3 to '/oradata2/DBase1/sysaux01.dbf';
5> set newname for datafile 4 to '/oradata2/DBase1/users01.dbf';
6> set newname for datafile 5 to '/oradata2/DBase1/tbs201.dbf';
7> set newname for datafile 6 to '/oradata2/DBase1/after_01.dbf';
8> set newname for datafile 7 to '/oradata2/DBase1/after_02.dbf';
9> set newname for datafile 8 to '/oradata2/DBase1/after_03.dbf';
10> set newname for datafile 1 to '/oradata2/DBase1/system01.dbf';
11>
12> SET UNTIL SCN 745212;
13> RESTORE DATABASE;
14> SWITCH DATAFILE ALL;
15> RECOVER DATABASE;
16> }

11)Open the Database resetlogs option.
RMAN> alter database open resetlogs;

database opened.

Restore and Recover database to a new host

How to create recovery catalog and use it

Recovery Catalog Concepts:
----------------------------------

Recovery catalog holds the RMAN repository information(i.e backup information) in a separate database schema in addition to control files. Though you can use target database as a recovery catalog database(The database where recovery catalog resides) but you will not do that because recovery catalog must be protected in the event of the loss of target database.

Before proceed it is good to understand about recovery catalog that recovery catalog is nothing but a schema that owns a list of tables. SYS user can't be owner of recovery catalog.

How to Create Recovery Catalog:
--------------------------------------------
Creating a recovery catalog is a three steps process.They are,

A)Configure Recovery Catalog Database.
B)Create the Recovery Catalog owner.
C)Create Recovery Catalog itself.

A)Configure Recovery Catalog Database:
-------------------------------------------
1)Choose any database to select as recovery catalog database rather than target database(where you will perform backup).

2)Create a tablespace where recovery catalog information will be populated.


SQL> SELECT NAME,DBID FROM V$DATABASE;
NAME            DBID
--------- ----------
ARJU    2869417476

SQL> CREATE TABLESPACE catalog_tbs DATAFILE '/oradata2/catalog01.dbf' SIZE 100M;
Tablespace created.

B)Create the Recovery Catalog owner.
---------------------------------------------------------
1)Create a user in the recovery catalog database who actually owns the recovery catalog schema. Also assign default tablespace catalog_tbs to this user.



SQL> SELECT NAME,DBID FROM V$DATABASE;
NAME            DBID
--------- ----------
ARJU    2869417476

SQL> CREATE USER catalog_user IDENTIFIED BY catalog_pwd TEMPORARY TABLESPACE TEMP DEFAULT TABLESPACE catalog_tbs QUOTA UNLIMITED ON catalog_tbs;

User created.

2)Grant the RECOVERY_CATALOG_OWNER role to the schema owner. This role provides the user with all privileges required to maintain and query the recovery catalog.

SQL> GRANT RECOVERY_CATALOG_OWNER TO catalog_user;
Grant succeeded.

C)Create Recovery Catalog itself:
-----------------------------------------
At this stage you have only a tablespace and a user in recovery catalog database. There is no objects in the recovery catalog schema.


SQL> SELECT NAME,DBID FROM V$DATABASE;
NAME            DBID
--------- ----------
ARJU    2869417476

SQL> conn catalog_user/catalog_pwd
Connected.

SQL> select * from user_tables;
no rows selected

Now you have to populate tables in the schema. To do this,

1)Through RMAN connect to the database that will contain the catalog as the catalog owner.

SQL> !rman CATALOG catalog_user/catalog_pwd

Recovery Manager: Release 10.2.0.1.0 - Production on Wed May 7 05:51:44 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to recovery catalog database

2)Run the CREATE CATALOG command to create the catalog.

RMAN>CREATE CATALOG;
recovery catalog created

You can also create catalog in other tablespace if you specify that,

RMAN>CREATE CATALOG TABLESPACE USERS;

3)Check the recovery catalog tables, objects, view, package etc.

SQL> SELECT COUNT(*) FROM USER_OBJECTS;
COUNT(*)
----------
195

How to create recovery catalog and use it


SQL> SELECT NAME,DBID FROM V$DATABASE;
NAME            DBID
--------- ----------
ARJU    2869417476



SQL> SELECT NAME,DBID FROM V$DATABASE;
NAME            DBID
--------- ----------
ARJU    2869417476


SQL> SELECT NAME,DBID FROM V$DATABASE;
NAME            DBID
--------- ----------
ARJU    2869417476

How to Use Recovery Catalog

You have already created recovery catalog described in How to Create Recovery Catalog. Now you want this recovery catalog for your target database.

To do so you have to follow the following steps.

1)-Make sure that the recovery catalog database is open,
-Connect RMAN to the target database and recovery catalog database.

For example in target Database dbase , you want to use recovery catalog created of ARJU database.

SQL> SELECT NAME, DBID FROM V$DATABASE;
NAME DBID
--------- ----------
DBASE 1509380669

SQL> !rman target / catalog catalog_user/catalog_pwd@saturn:1521/ARJU.SATURN.ARJUBD.COM

Recovery Manager: Release 10.2.0.1.0 - Production on Wed May 7 06:16:05 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: DBASE (DBID=1509380669)
connected to recovery catalog database

2)Register the target database in the connected recovery catalog:

By REGISTER DATABASE RMAN creates rows in the catalog tables to contain information about the target database, then copies all pertinent data about the target database from the control file into the catalog, synchronizing the catalog with the control file.

RMAN> REGISTER DATABASE;
database registered in recovery catalog
starting full resync of recovery catalog
full resync complete

c)Be sure that you are in RECOVERY CATALOG schema.

Some commands need to be in recovery catalog schema like,

RMAN> report schema at time 'SYSDATE-1';

Report of database schema
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 470 SYSTEM YES /oradata2/data1/dbase/system01.dbf
2 25 UNDOTBS1 YES /oradata2/data1/dbase/undotbs01.dbf
3 240 SYSAUX YES /oradata2/data1/dbase/sysaux01.dbf
4 5 USERS YES /oradata2/data1/dbase/users01.dbf

How to Use Recovery Catalog

Working with Recovery Catalog.

Registering Multiple Databases in a Recovery Catalog
-------------------------------------------------------------
You can register multiple databases in a recovery catalog; that means you can keep multiple database repository information in a single recovery catalog. But one restriction is each database DBID must be different as RMAN distinguish one database from another by DBID.

So whenever you just copy one database with user managed copy or by RMAN restore and recover then both database DBID is same. So they can't be register in same recovery catalog. In that case if you want to do so you have to change DBID. The method of changing DBID is described here Change DBID.

Unregistering a Target Database from the Recovery Catalog
-------------------------------------------------------------------
To unregister a target database from the recovery catalog, just follow the procedure.
1)Connect to Recovery Catalog database.

-bash-3.00$ rman target / CATALOG catalog_user/catalog_pwd@saturn:1521/ARJU.SATURN.ARJUBD.COM
Recovery Manager: Release 10.2.0.1.0 - Production on Wed May 7 23:53:51 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: DBASE (DBID=1509380669)
connected to recovery catalog database

2)issue UNREGISTER DATABASE command.

RMAN> UNREGISTER DATABASE;
database name is "DBASE" and DBID is 1509380669

Do you really want to unregister the database (enter YES or NO)? YES
database unregistered from the recovery catalog

But remember when a database is unregistered from the recovery catalog, all RMAN repository records in the recovery catalog are lost. The database can be registered again, but the recovery catalog records for that database are then based on the contents of the control file at the
time of re-registration. Records older than the CONTROLFILE_RECORD_KEEP_TIME setting in the target database control file are lost.

Resynchronizing the Recovery Catalog
---------------------------------------
RMAN performs resynchronizations automatically as needed when you execute certain commands, including BACKUP. If you want to manually resynchronize issue RESYNC CATALOG command. In case of resynchronization RMAN compares the recovery catalog to either the current control file of the target database or a backup control file and updates the recovery catalog with information that is missing or changed.

RMAN> RESYNC CATALOG;
starting full resync of recovery catalog
full resync complete

RMAN stored scripts in Recovery Catalog

Within RMAN you can save commands and execute it whenever you wish. Stored scripts bring this facility where we should not bother about OS scripts whether RMAN client has proper permission on it or not.

Stored Scripts can be two types.

1)Global Stored Scripts:A global stored script can be run against any database registered in the recovery catalog, if the RMAN client is connected to the recovery catalog and a target database.

2)Local Stored Scripts:A local stored script is associated with the target database to which RMAN is connected when the script is created, and can only be executed when you are connected to that target database.

How to Created Stored Script:
---------------------------------
To create local stored script.
CREATE SCRIPT query_backup
{
SHOW ALL;
REPORT NEED BACKUP;
REPORT OBSOLETE;
}

To create global stored with a comment added to it,

CREATE GLOBAL SCRIPT global_query_backup
COMMENT 'This is a sample global script which returns some query'
{
SHOW ALL;
REPORT NEED BACKUP;
REPORT OBSOLETE;
}

Alternatively you can create script from a text file. To create local script from text file in '/oradata2' just use,

CREATE SCRIPT test_script FROM FILE '/oradata2/my_script_file.txt';

How to run Stored Scripts:
------------------------------
To run stored script use,
RUN{
EXECUTE SCRIPT query_backup;
}
It will first search for local stored script with name query_backup. If not found then will check global stored script with the name. If you have same name in both local and global script then to run global script explicitly use,

RUN{
EXECUTE GLOBAL SCRIPT global_query_backup;
}

Displaying a Stored Script:
----------------------------------
PRINT SCRIPT query_backup;
PRINT GLOBAL SCRIPT global_query_backup;

RMAN> PRINT GLOBAL SCRIPT global_query_backup;

printing stored global script: global_query_backup
{SHOW ALL;
REPORT NEED BACKUP;
REPORT OBSOLETE;
}

Listing Stored Scripts:
-----------------------------
To view both global and local, for the currently connected target database use,

LIST SCRIPT NAMES;

To view only global script names use,

LIST GLOBAL SCRIPT NAMES;

To view the names of all scripts stored in the current recovery catalog, including global scripts and local scripts for all target databases registered in the recovery catalog, use,

LIST ALL SCRIPT NAMES;

Remember that to run LIST SCRIPT NAMES RMAN must be connected to target database.

To run LIST GLOBAL SCRIPT NAMES and LIST ALL SCRIPT NAMES RMAN need not to be connected to target database.

Example:
---------------
-bash-3.00$ rman CATALOG catalog_user/catalog_pwd@saturn:1521/ARJU.SATURN.ARJUBD.COM

Recovery Manager: Release 10.2.0.1.0 - Production on Thu May 8 01:14:36 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to recovery catalog database

RMAN> LIST GLOBAL SCRIPT NAMES;
List of Stored Scripts in Recovery Catalog
Global Scripts
Script Name
Description
-----------------------------------------------------------------------
global_query_backup
This is a sample global script which returns some query

Updating Stored Scripts:
-------------------------
To update a script use,
REPLACE [GLOBAL]SCRIPT
{
BACKUP DATABASE PLUS ARCHIVELOG;
}
Here like,
REPLACE SCRIPT query_backup
{
BACKUP DATABASE PLUS ARCHIVELOG;
}
If the script does not already exist, then RMAN creates it.

Deleting Stored Scripts
--------------------------------
DELETE SCRIPT query_bakcup;
DELETE GLOBAL SCRIPT global_query_backup;

If you use DELETE SCRIPT without GLOBAL, and there is no stored script for the target database with the specified name, RMAN will look for a global stored script by the specified name and delete the global script if it exists.

Working with Recovery Catalog.

RMAN stored scripts in Recovery Catalog

how many database register in Recovery Catalog Views

All of the information that we can see in RMAN prompt by SHOW, LIST or REPORT command can also be easily seen by query recovery catalog views. Recovery catalog views are prefixed by RC_.

Most of the catalog views have a corresponding dynamic performance view (or V$view) in the database server. For example, RC_BACKUP_PIECE corresponds to V$BACKUP_PIECE. The primary difference between the recovery catalog view and corresponding server view is that each catalog view contains information about all the databases registered in the catalog, whereas the database server view contains information only about itself.

How to Find DB_KEY and DBID from Recovery Catalog View?
------------------------------------------------------------
SQL> conn catalog_user/catalog_pwd@netservice_name_of_recovery_catalog
Connected.
SQL> SELECT DB_KEY,DBID,NAME,RESETLOGS_TIME FROM RC_DATABASE;

DB_KEY DBID NAME RESETLOGS
---------- ---------- -------- ---------
88 1509380669 DBASE 06-MAY-08

Determining the Schema Version of the Recovery Catalog
------------------------------------------------------------
SQL> SELECT * FROM rcver;

VERSION
------------
10.02.00.00

You can query RC_BACKUP_SET,RC_BACKUP_CONTROLFILE,RC_BACKUP_DATAFILE,RC_BACKUP_CORRUPTION,
RC_STORED_SCRIPT,RC_BACKUP_FILES views as of your interest.

how many database register in Recovery Catalog Views

When to use RMAN TSPITR?

RMAN tablespace point-in-time recovery (abbreviated as TSPITR) enables you to quickly recover one or more tablespace in an oracle database to a previous time, without affecting the rest of the tablespaces in your database.

When you can use TSPITR
---------------------------
We need to remember that with RMAN TSPITR the whole data set inside the tablespace will be recover to an earlier time rather than single object.

In the following cases you might choose to use TSPITR.

1)Erroneously TRUNCATE TABLE statement is execute on a table.
2)Wrong update is done on a table and committed.
3)In a database there is several schema each in different tablespace. Now I want to get back a single schema to an earlier time.

It is good to remember that if a database run on a NOARCHIVELOG mode then TSPITR can't be performed. In other word I can say if I don't have archived redo logs then TSPITR can't be performed.

When to use RMAN TSPITR?

Performing Flashback Database

Before going any production upgrade to database we can make a guaranteed restore point to database and if any wrong then we can get back to the restore point state. Guaranteed restore point always ensure that we can get back data to our restore point creation time.

Suppose before upgradation to database I have made an guaranteed restore point to database like,

SQL> create restore point before_upgrade guarantee flashback database;
Restore point created.

SQL> conn shaik/shaik
Connected.

SQL> select count(*) from user_tables;
COUNT(*)
----------
4

SQL> create table after_restore_point (col1 number);
Table created.

SQL> desc after_restore_point;
Name Null? Type
----------------------------------------- -------- ----------------------------
COL1 NUMBER

Now I want to perform flashback database to get back database at the time of creating restore point. To do it follow the following steps,

1. Determine the desired SCN, restore point or point in time for the FLASHBACK DATABASE command.

SQL> select NAME,SCN,GUARANTEE_FLASHBACK_DATABASE,DATABASE_INCARNATION# from v$restore_point;

NAME SCN GUA DATABASE_INCARNATION#
------------------------------ ---------- --- ---------------------
BEFORE_UPGRADE 787027 YES 2

2.Shutdown the database cleanly and mount it.

SQL>SHUTDOWN IMMEDIATE;
SQL>STARTUP MOUNT;

3.Determine the current window for flashback database.

When flashback logging is enabled, the earliest SCN in the flashback database window can be determined by querying V$FLASHBACK_DATABASE_LOG.

SELECT OLDEST_FLASHBACK_SCN, OLDEST_FLASHBACK_TIME
FROM V$FLASHBACK_DATABASE_LOG;

If you attempt FLASHBACK DATABASE and your target SCN is now outside the flashback window, then FLASHBACK DATABASE will fail with an ORA-38729 error.

So, you can't get back to state to an SCN before OLDEST_FLASHBACK_SCN.

4.Run your RMAN flashback command your restore point or to the desired SCN or to your desired time.

In this case I ran,

SQL> FLASHBACK DATABASE TO RESTORE POINT before_upgrade;
Flashback complete.

You can also use TO SCN Clause and TO TIMESTAMP Clause with FLASHBACK DATABASE.

5. You can verify that you have returned the database to the desired state, by opening the
database read-only and performing some queries to inspect the database contents.Like I perform some operations,

SQL> alter database open read only;
Database altered.

SQL> select open_mode from v$database;
OPEN_MODE
----------
READ ONLY

SQL> conn shaik/shaik
Connected.
SQL> select count(*) from tabs;

COUNT(*)
----------
4

SQL> desc after_restore_point;
ERROR:
ORA-04043: object after_restore_point does not exist

6) If you are satisfied with the state of your database about flashback then you have two choices.

Choice a) Make the database available for updates by open resetlogs, Once you perform this OPEN RESETLOGS operation, all changes to the database after the target SCN for FLASHBACK DATABASE are abandoned.

SQL>SHUTDOWN IMMEDIATE;
SQL>STARTUP MOUNT;
SQL>alter database open RESETLOGS;

Choice b) Use the export/data pump export utility to export the objects whose state was corrupted. Then, recover the database to the present time:

RMAN> RECOVER DATABASE;

This step undoes the effect of the Flashback Database, by re-applying all changes in the redo logs to the database, returning it to the most recent SCN.

After re-opening the database read-write, you can import the exported objects using the import utility corresponding to the export utility used earlier.

Performing Flashback Database

Why RMAN tool is used to take backup

We know that backup of a database can be taken through OS command or by using RMAN. Most people want to use RMAN or feel more flexible to use RMAN. The features that make RMAN better than other backup methodology are as follows.

1)RMAN came with oracle and it is free to use.
2)RMAN becomes simpler day by day. The simply BACKUP DATABASE will backup the database.
3)When use RMAN you are 100% sure that database is backed up.
4)With RMAN you can validate a database before backup and also validate a database after taking backup.
5)RMAN maintains a central repository where backup information is kept. So we manually get rid to remember the backup information of database.
6)RMAN is the only tool that supports incremental backup.
7)RMAN improve performance in time while taking backup and restore database.
8)RMAN encrypt data and also use compression of backup.
9)Can take backup parallelly and also restore operation can be done by parallel.
10)Various reports and querying facility through RMAN.
11)No extra redo generated when backup is taken..compared to online backup without RMAN which
results in saving of space in hard disk.
12)RMAN is an intelligent tool which help us to decide various query like which files need to take backup, which backup is obsolete, which backup is expired etc.

Why RMAN tool is used to take backup

Backup Retention Policy of RMAN

Setting the RETENTION POLICY in RMAN keep track about the backup files and tells the report whether they are obsoleted and whether it is needed to backup the database or datafile.

•If you have flash recovery configured then the database automatically deletes unnecessary files from the flash recovery area based on its internal disk quota rules. The disk quota rules are distinct from the backup retention policy rules, but the database will never delete files in violation of the retention policy to satisfy the disk quota.

•If you don't have flash recovery configured then the database does not delete any file even they are obsolete.

•The configuration parameter RETENTION POLICY can be set by CONFIGURE RETENTION POLICY ....

•A backup becomes obsolete based on retention policy, that it is not needed for recovery.A backup becomes expired only when RMAN perform CROSSCHECK and can't find the file. (A most common is file is deleted by OS). Obsolete means "not needed," whereas expired means "not found."

•There is two mutually exclusive options for setting a retention policy; redundancy and recovery window.

To set Recovery Window of 3 days,
RMAN>CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 3 DAYS;

To set Recovery Window of redundancy 2 copies,
RMAN> CONFIGURE RETENTION POLICY TO REDUNDANCY 2;

Whenever retention policy is set to recovery window of 3 days then rman retains all information and backup data though which it can go to any point within 3 days from current date. The backup data which is not needed to go back any point within 3 days from current date are termed as obsolete. Suppose before 5 days ago I took an incremental level 0 backup and then I took incremental level 1 backup, now the backup before 5 days ago level 0 backup is not obsolete. Because this is base backup and needed if I want to back any point within 3 days. However, if I took a backup right 4 days ago then backup before 5 days ago become obsolete.

Whenever retention policy is set to redundancy of 2 copies the rman at least retains latest 2 copies of each datafile. If I took 3rd backup of datafile 3 then 1st backup of datafile 3 become obsolete.

•The default retention policy is REDUNDANCY = 1

•You can disable the retention policy by setting,

CONFIGURE RETENTION POLICY TO NONE;

If the retention policy is configured to NONE, then REPORT OBSOLETE and DELETE OBSOLETE do not consider any backups to be obsolete.

Backup Retention Policy of RMAN

RMAN channels

•An RMAN channel corresponds to one server session and it represents one stream of data to a device type.

•When you run a command that requires to use any server session then RMAN automatically allocates channel with the option specified in CONFIGURE command, if you don't explicitly allocate any channel.

•In the settings for which RMAN automatically allocate channel are.

CONFIGURE DEVICE TYPE ... PARALLELISM
CONFIGURE DEFAULT DEVICE TYPE
CONFIGURE CHANNEL

•You can override automatically allocated channel by explicitly specifying ALLOCATE CHANNEL within run block as follows,

RUN
{
ALLOCATE CHANNEL c1 DEVICE TYPE sbt;
BACKUP DATABASE;
}

•If you have any three above types of CONFIGURE settings then RMAN automatically allocates channel when you perform BACKUP, RESTORE, DELETE command or command within RUN block.

•The number of channels allocation is done by CONFIGURE DEVICE TYPE ... PARALLELISM is based on parallelism settings. If setttings is
CONFIGURE DEVICE TYPE DISK PARALLELISM 3;
then RMAN allocate 3 channels.

•Channel name is defined by ORA_devicetype_n, where

devicetype refers to the user's device type (such as DISK or sbt_tape) and
n refers to the channel number.

The thing need to know that RMAN output always displays sbt_tape whether the input is sbt or sbt_tape. If I have CONFIGURE DEVICE TYPE sbt PARALLELISM 2; then channel 1 will be named as ORA_SBT_TAPE_1 and channel 2 will be named as ORA_SBT_TAPE_2.

•Channel name prefix by ORA_ are reserved for RMAN. You cannot manually allocate a channel with a name that begins with ORA_.

RMAN channels

RMAN Restore & Recovery

•If we simply use RESTORE command then RMAN directs a server session to restore the file to default location that is overwrite the existing file.

•If we use RESTORE command with SET NEWNAME option then RMAN restore the file with specified location.

Example: RUN {
SET NEWNAME FOR DATAFILE '/oradata/datafile/1.dbf' TO '/tmp/1.dbf';
RESTORE DATAFILE '/oradata/datafile/1.dbf';
}

In this case, RMAN restore /oradata/datafile/1.dbf to /tmp/1.dbf
•RMAN always restore data file as image copy.

•After restoring a datafile to a new location if you want to use that then use the SWITCH command in order to update control file. RMAN SWITCH command is equivalent to SQL statement ALTER DATABASE RENAME FILE command.

Example: SWITCH DATAFILE '/oradata/datafile/1.dbf' TO DATAFILECOPY '/tmp/1.dbf';

•RMAN always select the most recent backup while restoring. If two backups are in the same point then RMAN prefers to use image copies over backup sets because RMAN can restore more quickly from image copies than from backup sets.

•During RESTORE operation if RMAN fails the restore one copy then i automatically searches other usable copy. If there is no backup exist then RMAN try to re-create the datafile.

•RMAN uses restore optimization to avoid restoring datafiles from backup when possible. If a datafile is already present in the correct location and its header contains the expected information, then RMAN does not restore the datafile from backup. However you can override this behavior by FORCE option of RESTORE command.

•If RMAN has a choice between archived redo logs and incremental backups then RMAN always choice incremental backups during recovery.If overlapping levels of incremental backups are available then RMAN choose the longest covering one.

RMAN Restore & Recovery

What will be the Archived Redo Log Destination in Oracle

As a sysdba privilege issue "archive log list" to see your present archival destination settings.

SQL> ARCHIVE LOG LIST
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 1
Next log sequence to archive 2
Current log sequence 2

•Based on the destination you can see the physical destination of your archived redo log file in the v$parameter. You can use,

SQL> SHOW PARAMETER DB_RECOVERY_FILE_DEST

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /oradata2/flash_recovery_area
db_recovery_file_dest_size big integer 2G

Now a question is for which parameter settings my archived destination affects. In the following I will try to clear the idea.

•If you set LOG_ARCHIVE_DEST parameter then this parameter is used to locate the archived redo log destination.

•If you set DB_RECOVERY_FILE_DEST and and no LOG_ARCHIVE_DEST_n is specified, then LOG_ARCHIVE_DEST_10 is implicitly set to archival destination. However if you set LOG_ARCHIVE_DEST_10 explicitly empty then this behavior changed. Like,

SQL> select DEST_NAME,DESTINATION from v$archive_dest where dest_name='LOG_ARCHIVE_DEST_10';

DEST_NAME DESTINATION
------------------------------ ------------------------------
LOG_ARCHIVE_DEST_10 USE_DB_RECOVERY_FILE_DEST

Though you can implicitly change DB_RECOVERY_FILE_DEST to another destination like LOG_ARCHIVE_DEST_9.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_9='LOCATION=USE_DB_RECOVERY_FILE_DEST';
System altered.

SQL> select DEST_NAME,DESTINATION from v$archive_dest where dest_name='LOG_ARCHIVE_DEST_10';
DEST_NAME DESTINATION
------------------------------ ------------------------------
LOG_ARCHIVE_DEST_10

SQL> select DEST_NAME,DESTINATION from v$archive_dest where dest_name='LOG_ARCHIVE_DEST_9';
DEST_NAME DESTINATION
------------------------------ ------------------------------
LOG_ARCHIVE_DEST_9 USE_DB_RECOVERY_FILE_DEST

•If you set any local destinations for LOG_ARCHIVE_DEST_n, then archived redo logs are stored only in the destinations you specify using those parameters. In this case, redo log files are not archived in the flash recovery area by default. Though if you want to archive redo log to flash recover area then use LOCATION=USE_DB_RECOVERY_FILE_DEST as in previous example.

•If you do not set any value for LOG_ARCHIVE_DEST, LOG_ARCHIVE_DEST_n, or DB_RECOVERY_FILE_DEST, then the redo logs are archived to a default location that is platform-specific. On Solaris, for example, the default is ?/dbs/arch.

SQL> alter system set log_archive_dest_9='';
System altered.

SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination ?/dbs/arch
Oldest online log sequence 1
Next log sequence to archive 2
Current log sequence 2

What will be the Archived Redo Log Destination in Oracle

Wednesday, December 24, 2008

Set up a Database for RMAN Backup

To simplify ongoing use of RMAN for backup and recovery, the RMAN lets you set a number of persistent configuration settings for each target database. To view these persistent configuration settings use SHOW ALL within RMAN command prompt.

To change these persistent configuration settings use CONFIGURE ... command.

If you want to clear anytime these settings to default mode then use, CONFIGURE... CLEAR command.

Whenever you set any parameter by CONFIGURE ... these settings is used while backup and recovery strategy whenever you do not specify any other format within Run block or at RMAN prompt. However you can override these settings while taking backup. T
he order of precedence is,

1.Set within Run Block.
2.Set at RMAN prompt.
3.Set with CONFIGURE

Some Examples:
--------------------
This example changes the default backup destination from disk to tape and then again backup to default.

RMAN> show default device type;
using target database control file instead of recovery catalog
RMAN configuration parameters are:
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default

RMAN> CONFIGURE DEFAULT DEVICE TYPE TO 'SBT_TAPE';
old RMAN configuration parameters:
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
new RMAN configuration parameters:
CONFIGURE DEFAULT DEVICE TYPE TO 'SBT_TAPE';
new RMAN configuration parameters are successfully stored

RMAN> CONFIGURE DEFAULT DEVICE TYPE CLEAR;
old RMAN configuration parameters:
CONFIGURE DEFAULT DEVICE TYPE TO 'SBT_TAPE';
RMAN configuration parameters are successfully reset to default value

RMAN> show default device type;
RMAN configuration parameters are:
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default

RMAN> show backup optimization;
RMAN configuration parameters are:
CONFIGURE BACKUP OPTIMIZATION OFF; # default

RMAN> CONFIGURE BACKUP OPTIMIZATION ON;
new RMAN configuration parameters:
CONFIGURE BACKUP OPTIMIZATION ON;
new RMAN configuration parameters are successfully stored

RMAN> show backup optimization;
RMAN configuration parameters are:
CONFIGURE BACKUP OPTIMIZATION ON;

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;
old RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP OFF;
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored

By default, the format of the autobackup file for all configured devices is the substitution variable %F. This variable format translates into c-IIIIIIIIII-YYYYMMDD-QQ,

where:IIIIIIIIII stands for the DBID.

YYYYMMDD is a time stamp of the day the backup is generated

QQ is the hex sequence that starts with 00 and has a maximum of FF

Set up a Database for RMAN Backup

RMAN to Validate Before and After taking backup

If you want to verify whether there any physical or logical corruptions in database file before taking backup you can use the VALIDATE option of the BACKUP command.When performing a BACKUP... VALIDATE, RMAN reads the files to be backed up in their entirety, as it would during a real backup. It does not, however, actually produce any backup sets or image copies.If the backup validation discovers corrupt blocks, then RMAN updates the V$DATABASE_BLOCK_CORRUPTION view with rows describing the corruptions.Also with RMAN RESTORE ... VALIDATE before restore database or any datafile you can verify whether database can successfully restored or not.An example:--------------Before Performing Backup Check Any Physical or Logical Corruption:---------------------------------------------------------------------------------SQL> !rman target /Recovery Manager: Release 10.2.0.1.0 - Production on Wed Apr 23 17:15:24 2008Copyright (c) 1982, 2005, Oracle. All rights reserved.connected to target database: DATA1 (DBID=2547250380)RMAN> backup validate database;Starting backup at 23-APR-08using target database control file instead of recovery catalogallocated channel: ORA_DISK_1...including current control file in backupsetincluding current SPFILE in backupsetchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:02Finished backup at 23-APR-08After Backup Check if they can be Restored Successfully-------------------------------------------------------------Using Validate BackupSet--------------------------RMAN> validate backupset 4;using channel ORA_DISK_1channel ORA_DISK_1: starting validation of datafile backupsetchannel ORA_DISK_1: reading from backup piece /oracle/app/oracle/product/10.2.0/db_1/dbs/05jei8qh_1_1channel ORA_DISK_1: restored backup piece 1piece handle=/oracle/app/oracle/product/10.2.0/db_1/dbs/05jei8qh_1_1 tag=TAG20080423T165857channel ORA_DISK_1: validation complete, elapsed time: 00:00:25Using RESTORE .. VALIDATE----------------------------RMAN> restore database validate;Starting restore at 23-APR-08using channel ORA_DISK_1channel ORA_DISK_1: starting validation of datafile backupsetchannel ORA_DISK_1: reading from backup piece /oracle/app/oracle/product/10.2.0/db_1/dbs/0bjeiams_1_1channel ORA_DISK_1: restored backup piece 1piece handle=/oracle/app/oracle/product/10.2.0/db_1/dbs/0bjeiams_1_1 tag=TAG20080423T173108channel ORA_DISK_1: validation complete, elapsed time: 00:00:04Finished restore at 23-APR-08You can also validate single tablespace or datafile or any other like,RMAN> restore datafile 1 validate;RMAN> restore tablespace system validate;RMAN> restore controlfile validate;RMAN> restore spfile validate;

Friday, December 26, 2008

Wednesday, December 24, 2008

Blog Archive