World of Oracle DBA

How to take backup of oracle database from remote system ------------------------------------------------------------- In order to take oracle database backup from remote system you have to ensure the following things. 1)The database in which you will take backup is called the target database. The target database must be in mount or open state. 2)The target database listener must be running. 3)The user connected t the target database and eventually take backup must have SYSDBA privilege. So the user by which you connect that user entry must be in the password file. In the following section I will take backup of machine neptune and database name is dbase. I will take backup from another machine named saturn. Step 1: ------------------ Log on to neptune machine and see the listener service name and listener port number and ensure that listener is running. If you knew listener service name and port number previously and currently it is running then you can avoid this step. In machine nept

How to take backup of oracle database from remote system ------------------------------------------------------------- In order to take oracle database backup from remote system you have to ensure the following things. 1)The database in which you will take backup is called the target database. The target database must be in mount or open state. 2)The target database listener must be running. 3)The user connected t the target database and eventually take backup must have SYSDBA privilege. So the user by which you connect that user entry must be in the password file. In the following section I will take backup of machine neptune and database name is dbase. I will take backup from another machine named saturn. Step 1: ------------------ Log on to neptune machine and see the listener service name and listener port number and ensure that listener is running. If you knew listener service name and port number previously and currently it is running then you can avoid this step. In machine nept

Example of User Managed Hot Backup: ----------------------------------------- 1)Determine which data file you need to backup. SQL> select file_name ,tablespace_name from dba_data_files 2 / FILE_NAME TABLESPACE_NAME ---------------------------------------- ------------------------------ /oradata2/7.dbf USERS /oradata2/data1/dbase/users01.dbf USERS /oradata2/data1/dbase/sysaux01.dbf SYSAUX /oradata2/data1/dbase/undotbs01.dbf UNDOTBS1 /oradata2/data1/dbase/system01.dbf SYSTEM /oradata2/6.dbf USERS 6 rows selected. 2)Make the tablespace in backup mode one by one and copy the associated datafile to backup location. SQL> ALTER TABLESPACE USERS BEGIN BACKUP; Tablespace altered. SQL> !cp /oradata2/data1/dbase/users01.dbf /oradata2/shaik SQL> !cp /oradata2/7.dbf /oradata2/shaik SQL> ALTER TABLESPACE USERS END BACKUP; Tablespace altered. SQL> ALTER TABLESPACE SYSAUX BEGIN BACKUP; Tablespace altered. SQL> !cp /oradata2/data1/dbase/sysaux01.dbf /oradata2/shaik SQL> ALTER TA

Example of User Managed Hot Backup: ----------------------------------------- 1)Determine which data file you need to backup. SQL> select file_name ,tablespace_name from dba_data_files 2 / FILE_NAME TABLESPACE_NAME ---------------------------------------- ------------------------------ /oradata2/7.dbf USERS /oradata2/data1/dbase/users01.dbf USERS /oradata2/data1/dbase/sysaux01.dbf SYSAUX /oradata2/data1/dbase/undotbs01.dbf UNDOTBS1 /oradata2/data1/dbase/system01.dbf SYSTEM /oradata2/6.dbf USERS 6 rows selected. 2)Make the tablespace in backup mode one by one and copy the associated datafile to backup location. SQL> ALTER TABLESPACE USERS BEGIN BACKUP; Tablespace altered. SQL> !cp /oradata2/data1/dbase/users01.dbf /oradata2/shaik SQL> !cp /oradata2/7.dbf /oradata2/shaik SQL> ALTER TABLESPACE USERS END BACKUP; Tablespace altered. SQL> ALTER TABLESPACE SYSAUX BEGIN BACKUP; Tablespace altered. SQL> !cp /oradata2/data1/dbase/sysaux01.dbf /oradata2/shaik SQL> ALTER TA

Case A: Losing a Member of a Multiplex online Redo log group. ------------------------------------------------------------------------ In this case your redo log file is multiplex that means you have more than one log member within log groups. Now if you have at least one redo log member of the group exist then database continues functional as usual. In alert log error message will be written. In order to solve the problem, i)Identify the redo log file that is invalid. SQL> SELECT GROUP#, STATUS, MEMBER FROM V$LOGFILE WHERE STATUS='INVALID'; GROUP# STATUS MEMBER ------- ----------- --------------------- 0002 INVALID /oracle/shaik/redo02.log ii)Drop the damaged member. ALTER DATABASE DROP LOGFILE MEMBER '/oracle/shaik/redo02.log/shaik/redo02.log' iii)Add a new member to the group. ALTER DATABASE ADD LOGFILE MEMBER '/oracle/shaik/redo02b.log' TO GROUP 1; --FOr example to group 1. If the file you want to add already exists, you must specify REUSE. For e

Case A: Losing a Member of a Multiplex online Redo log group. ------------------------------------------------------------------------ In this case your redo log file is multiplex that means you have more than one log member within log groups. Now if you have at least one redo log member of the group exist then database continues functional as usual. In alert log error message will be written. In order to solve the problem, i)Identify the redo log file that is invalid. SQL> SELECT GROUP#, STATUS, MEMBER FROM V$LOGFILE WHERE STATUS='INVALID'; GROUP# STATUS MEMBER ------- ----------- --------------------- 0002 INVALID /oracle/shaik/redo02.log ii)Drop the damaged member. ALTER DATABASE DROP LOGFILE MEMBER '/oracle/shaik/redo02.log/shaik/redo02.log' iii)Add a new member to the group. ALTER DATABASE ADD LOGFILE MEMBER '/oracle/shaik/redo02b.log' TO GROUP 1; --FOr example to group 1. If the file you want to add already exists, you must specify REUSE. For e

Some older operating system limits the size of individual files. In that case if your datafile size is 10G stored in raw device and your operating system supports only 4GB files on the file system then it is not possible to take backup of the image copy to the file system. The CONFIGURE MAXSETSIZE command limits the size of backup sets created on file system on a specific channel. If I use 100M then maximum backup set size will be 100M per channel. You can also control the size of a backup set piece or the entire backup itself with the MAXPIECESIZE parameter. If your database size is 330M and you set MAXPIECESIZE to 100M then it will be divided in 4 backup pieces containing 100M, 100M, 100M and 30M. An example: -------------- RMAN> run{ 2> ALLOcate CHANNEL a DEVICE TYPE DISK MAXPIECESIZE 100M; 3> backup database; 4> } released channel: ORA_DISK_1 allocated channel: a channel a: sid=155 devtype=DISK Starting backup at 11-MAY-08 channel a: starting full datafile backupset cha

Some older operating system limits the size of individual files. In that case if your datafile size is 10G stored in raw device and your operating system supports only 4GB files on the file system then it is not possible to take backup of the image copy to the file system. The CONFIGURE MAXSETSIZE command limits the size of backup sets created on file system on a specific channel. If I use 100M then maximum backup set size will be 100M per channel. You can also control the size of a backup set piece or the entire backup itself with the MAXPIECESIZE parameter. If your database size is 330M and you set MAXPIECESIZE to 100M then it will be divided in 4 backup pieces containing 100M, 100M, 100M and 30M. An example: -------------- RMAN> run{ 2> ALLOcate CHANNEL a DEVICE TYPE DISK MAXPIECESIZE 100M; 3> backup database; 4> } released channel: ORA_DISK_1 allocated channel: a channel a: sid=155 devtype=DISK Starting backup at 11-MAY-08 channel a: starting full datafile backupset cha

Error Stuck: -------------------- While running duplicating database the following error returns. RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of Duplicate Db command at 05/13/2008 04:40:56 RMAN-03015: error occurred in stored script Memory Script RMAN-06053: unable to perform media recovery because of missing log RMAN-06025: no backup of log thread 1 seq 5 lowscn 977621 found to restore Cause of The problem: ------------------------ The problem is that RMAN is not able to apply all the archived logs needed for complete recovery. For example, if you only backed up logs through sequence 4, but the most recent archived log is sequence 5, then DUPLICATE fails. Solution of The Problem: ------------------------------ When creating the duplication script, use the SET UNTIL command to specify a log sequen

Error Stuck: -------------------- While running duplicating database the following error returns. RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of Duplicate Db command at 05/13/2008 04:40:56 RMAN-03015: error occurred in stored script Memory Script RMAN-06053: unable to perform media recovery because of missing log RMAN-06025: no backup of log thread 1 seq 5 lowscn 977621 found to restore Cause of The problem: ------------------------ The problem is that RMAN is not able to apply all the archived logs needed for complete recovery. For example, if you only backed up logs through sequence 4, but the most recent archived log is sequence 5, then DUPLICATE fails. Solution of The Problem: ------------------------------ When creating the duplication script, use the SET UNTIL command to specify a log sequen

Used managed backup means you take backup without any oracle feature. Suppose if you take backup by Operating system then it is called user managed backup. And the term hot backup means taking your backup whenever your database is at open state. To take full database backup follow the following steps. 1)Before proceed remember you can take online/hot backup whenever your database is in Archivelog mode. If your database run on noarchivelog mode then you must take consistent backup that is after cleanly shutdown. In order to determine the archival mode, issue the query, SQL> SELECT LOG_MODE FROM V$DATABASE; LOG_MODE ------------ ARCHIVELOG If you see ARCHIVELOG then you can proceed further. In order to take backup while you are in noarhivelog mode follow other post on my blog. 2)Determine the files that you need to take backup. Whenever you decide to take your database backup then take backup of data files , online redo log files ,control files, spfile. In order to decide which files

Used managed backup means you take backup without any oracle feature. Suppose if you take backup by Operating system then it is called user managed backup. And the term hot backup means taking your backup whenever your database is at open state. To take full database backup follow the following steps. 1)Before proceed remember you can take online/hot backup whenever your database is in Archivelog mode. If your database run on noarchivelog mode then you must take consistent backup that is after cleanly shutdown. In order to determine the archival mode, issue the query, SQL> SELECT LOG_MODE FROM V$DATABASE; LOG_MODE ------------ ARCHIVELOG If you see ARCHIVELOG then you can proceed further. In order to take backup while you are in noarhivelog mode follow other post on my blog. 2)Determine the files that you need to take backup. Whenever you decide to take your database backup then take backup of data files , online redo log files ,control files, spfile. In order to decide which files

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

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

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;

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;

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

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

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 recove

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 recove

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 databa

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 databa

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'