World of Oracle DBA

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 Determin...

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 Determin...

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.

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.

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 FLASHB...

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 FLASHB...

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 thr...