World of Oracle DBA

Whenever you connect to a database (rman version if different from source database version in fact rman version is higher than the source database) through RMAN using/without recovery catalog it fails with RMAN-06429: TARGET database is not compatible with this version of RMAN as below. C:\>rman target rman/rman@local2 catalog rman/rman@testdb Recovery Manager: Release 10.2.0.1.0 - Production on Tue Mar 24 05:07:14 2009 Copyright (c) 1982, 2005, Oracle. All rights reserved. Recovery Manager incompatible with TARGET database: RMAN 8.0.4.0 to 10.1.0.0 req uired RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-00554: initialization of internal recovery manager package failed RMAN-06429: TARGET database is not compatible with this version of RMAN Cause of the Problem You have higher RMAN version than the target data...

Overview A powerful feature of RMAN is the ability to duplicate (clone), a database from a backup. It is possible to create a duplicate database on: A remote server with the same file structure A remote server with a different file structure The local server with a different file structure A duplicate database is distinct from a standby database, although both types of databases are created with the DUPLICATE command. A standby database is a copy of the primary database that you can update continually or periodically by using archived logs from the primary database. If the primary database is damaged or destroyed, then you can perform failover to the standby database and effectively transform it into the new primary database. A duplicate database, on the other hand, cannot be used in this way: it is not intended for failover scenarios and does not support the various standby recovery and failover options. To prepare for database duplication, you must first create an auxiliary instance....

Problem Scenario You have cold backup of oracle database directory structure along with oracle software. Now your Windows OS got corrupted. So you reinstall your windows operating system and you try to restore the database. After installing software you set the ORACLE_SID and ORACLE_HOME environmental variable and whenever you create oracle service with oradim it fails with below message. C:\>oradim -new -sid orcl -intpwd orcl -startmode manual -pfile 'F:\oracle10g\pr oduct\10.2.0\db_1\database\initorcl.ora' Instance created. DIM-00019: create service error O/S-Error: (OS 2) The system cannot find the file specified. Solution of the Problem On linux environment you don't need these things. But in windows environment you need to do a lot of things more than restoring files. Whenever a windows OS gets corrupted you loose the Oracle Universal Installer repository and the regedit entries along with oracle home structure. So to solve the problem it is better to install new o...

I will demonstrate RMAN incremental database backup in this post. My requirement is as follows. 1)Database running is noarchivelog mode. As I wish to take backup online so database need to run in archivelog mode. 2)I will use flash recovery area to take backup for better management of backup data. 3)Take full database backup or level 0 incremental backup in a week and each day will take incremental backup cumulative. 4)As a repository I will use recovery catalog in order to store backup information. 5)I need to setup rman configuration like, -autobackup on(in order to back up the control file and spfile whenever the database structure metadata in the control file changes and whenever a backup record is added) , -backup optimization on (in order to skip logs that it has already backed up to the specified device). and, -recovery window to 7 days. (to ensure that you can recover the database to any point within the last week) 6)My recovery catalog database name is neptune, listener port 1...

Whenever I try to connect remotely to a database through rman it fails with message RMAN-00554, RMAN-04005, ORA-0103. In this example my source database is saturn and destination database is jupiter. From jupiter let me try to connect to saturn. Source database Before proceed let's see the parameter that we can use to connect to rman without tnsnames.ora. Our needed parameter is hostname/ipaddress, service_name and port number. These three can be found by issuing lsnrctl status in source machine. Note that you must have a password file setup in the source database. This requires the use of the "orapwd" command and the initialization parameter "REMOTE_LOGIN_PASSWORDFILE". bash-3.00$ hostname saturn SQL> show parameter remote_login NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ remote_login_passwordfile string EXCLUSIVE bash-3.00$ lsnrctl status LSNRCTL for S...

Whenever I try to connect remotely to a database through rman it fails with message RMAN-00554, RMAN-04005, ORA-0103. In this example my source database is saturn and destination database is jupiter. From jupiter let me try to connect to saturn. Source database Before proceed let's see the parameter that we can use to connect to rman without tnsnames.ora. Our needed parameter is hostname/ipaddress, service_name and port number. These three can be found by issuing lsnrctl status in source machine. Note that you must have a password file setup in the source database. This requires the use of the "orapwd" command and the initialization parameter "REMOTE_LOGIN_PASSWORDFILE". bash-3.00$ hostname saturn SQL> show parameter remote_login NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ remote_login_passwordfile string EXCLUSIVE bash-3.00$ lsnrctl status LSNRCTL for S...

Step by steps cloning operation is described below. The scenario is, -Source database shaik be cloned as shaikcl -Datafile named E:\ORACLE\PRODUCT\10.2.0\ORADATA\SHAIK\*.dbf will be cloned as F:\ORACLE\SHAIKCL\*.dbf -Controlfile named E:\ORACLE\PRODUCT\10.2.0\ORADATA\SHAIK\*.ctl will be cloned as F:\ORACLE\*.CTL -In cloned database adump,bdump,cdump,udump will be located in F:\ORACLE\*dump Step 01: In source database identify the datafile location and redo logfile location. On windows machine, C:\Documents and Settings\Queen>set ORACLE_SID=shaik On linux based machine, $export ORACLE_SID=shaik C:\Documents and Settings\Queen>sqlplus / as sysdba SQL*Plus: Release 10.2.0.1.0 - Production on Mon Oct 6 23:19:45 2008 Copyright (c) 1982, 2005, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options SQL> col file_name format a70 SQL> set linesize 160 SQL> select file_n...

Suppose inside rman environment I got the following output. RMAN> list backup; using target database control file instead of recovery catalog List of Backup Sets =================== BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 1 Full 558.80M DISK 00:01:14 08-SEP-08 BP Key: 1 Status: AVAILABLE Compressed: NO Tag: TAG20080908T035835 Piece Name: /oracle/app/oracle/product/10.2.0/db_1/flash_recovery_area/SHAIK/backupset/ 2008_09_08/o1_mf_nnndf_TAG20080908T035835_4d9pscwz_.bkp List of Datafiles in backup set 1 File LV Type Ckp SCN Ckp Time Name ---- -- ---- ---------- --------- ---- 1 Full 1030282 08-SEP-08 /oradata2/shaikdba/shaikdba/shaik/system01.dbf 2 Full 1030282 08-SEP-08 /oradata2/shaikdba/shaikdba/shaik/undotbs01.dbf 3 Full 1030282 08-SEP-08 /oradata2/shaikdba/shaikdba/shaik/sysaux01.dbf 4 Full 1030282 08-SEP-08 ...

In many cases in RMAN we need to debug the session to find and investigate the session about what is happening there. Also to obtain and check the correct diagnostic evidence we sometimes need the debug the rman session. The most common use of debugging RMAN session is whenever we fail RMAN operation. In fact if any failure in RMAN operation RMAN log and debug files are NOT generated by default and we explicitly need to enable it. We can log and debug session whichever operation we do in RMAN. Like Backup, Restore, TSPITR, DBPITR, Duplicate Database, Restore to a new host etc. The commnad is simple, The syntax is, $ rman target [un/pwd@target_db] catalog [un/pwd@catalog_db] debug trace rman.trc log rman.log RMAN>[RMAN Commands Here] Where un indicates username and pwd indicates password. In my system I used debugging and logging RMAN session by, $ rman target / log=/backup03/webkey/rmanlog.txt trace=/backup03/webkey/rmantrace.log RMAN> debug on RMAN> @/backup03/webkey/rman_scr...

Error Description And Symptoms I am performing disaster recovery operation. The new host path are different than from original host. So for the datafile I used SET NEWNAME .. clause. And for the online redo log file I used SQL "ALTER DATABASE RENAME FILE .. " clause to rename the online logfiles to a valid location on to a new host. I have already ran the restore srcipt in previous but it fails to rename third online redo logfiles due to invalid location. And after fixing location whenever I ran the script again it fails with serveral RMAN and Oracle error as below. RMAN> @/backup03/webkey/rman_script RMAN> run{ 2> set newname for datafile 1 to '/backup03/webkey/system01.dbf'; 3> set newname for datafile 2 to '/backup03/webkey/undotbs01.dbf'; 4> set newname for datafile 3 to '/backup03/webkey/sysaux01.dbf'; 5> set newname for datafile 4 to '/backup03/webkey/users01.dbf'; 6> set newname for datafile 5 to '/back...

In many cases in RMAN we need to debug the session to find and investigate the session about what is happening there. Also to obtain and check the correct diagnostic evidence we sometimes need the debug the rman session. The most common use of debugging RMAN session is whenever we fail RMAN operation. In fact if any failure in RMAN operation RMAN log and debug files are NOT generated by default and we explicitly need to enable it. We can log and debug session whichever operation we do in RMAN. Like Backup, Restore, TSPITR, DBPITR, Duplicate Database, Restore to a new host etc. The commnad is simple, The syntax is, $ rman target [un/pwd@target_db] catalog [un/pwd@catalog_db] debug trace rman.trc log rman.log RMAN>[RMAN Commands Here] Where un indicates username and pwd indicates password. In my system I used debugging and logging RMAN session by, $ rman target / log=/backup03/webkey/rmanlog.txt trace=/backup03/webkey/rmantrace.log RMAN> debug on RMAN> @/backup03/webkey/rman_scr...

This example is based on, You have lost all your current and backup of controlfiles. You have avaiable your current data files. You have lost all your online active , current redo log files. You have lost your spfile and pfile. In the scenario, I will show the procedure of how to recover database if you lose your all copies of controlfiles and all copies of redo log groups and members are lost. 1. Let's start by deleting online redo log files and controlfile of my running database. SQL> select MEMBER from v$logfile; MEMBER -------------------------------------------------------------------------------- /oradata2/shaikdba/shaikdba/redo02.log /oradata2/shaikdba/shaikdba/redo01.log /oradata2/shaikdba/shaikdba/redo03.log SQL> select name from v$controlfile; NAME -------------------------------------------------------------------------------- /oradata2/shaikdba/shaikdba/control01.ctl /oradata2/shaikdba/shaikdba/control02.ctl /oradata2/shaikdba/shaikdba/control03.ctl 2.Delete all c...

Error Description: While performing disaster recovery I get the error RMAN-06172 as below. RMAN> restore spfile to pfile '/oracle/app/oracle/product/10.2.0/db_1/dbs/initSHAIK.ora' from '/backup1/snap/june/ctl_sp_bak_c-448149146-20080607-00'; Starting restore at 27-AUG-08 allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=36 devtype=DISK RMAN-00571: ==================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: ==================================================== RMAN-03002: failure of restore command at 08/27/2008 00:42:41 RMAN-06172: no autobackup found or specified handle is not a valid copy or piece Cause of The Error To perform restore operation to a new host you must have at least one autobackup of the controlfile. If you don't have autobackup of the controlfile then you would not be able to perform disaster recovery. For example if you have snapshot of controlfile backup then you w...

Whenever we issue in RMAN, RMAN> restore spfile from autobackup; then restoring from autobackup stops at seven days and then it arises message channel ORA_DISK_1: no autobackup in 7 days found. For example, RMAN> restore spfile from autobackup; Starting restore at 31-AUG-08 using channel ORA_DISK_1 channel ORA_DISK_1: looking for autobackup on day: 20080831 channel ORA_DISK_1: looking for autobackup on day: 20080830 channel ORA_DISK_1: looking for autobackup on day: 20080829 channel ORA_DISK_1: looking for autobackup on day: 20080828 channel ORA_DISK_1: looking for autobackup on day: 20080827 channel ORA_DISK_1: looking for autobackup on day: 20080826 channel ORA_DISK_1: looking for autobackup on day: 20080825 channel ORA_DISK_1: no autobackup in 7 days found RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03...

Error Description I give the full path of rman executable file location and I am getting error RMAN .msb not found as below. -bash-3.1$ /oradata2/bin/rman target / Message file RMAN .msb not found Verify that ORACLE_HOME is set properly Solution of The problem Believe me, as I still got you have not set ORACLE_HOME properly. So set it. To know your current settings of ORACLE_HOME, issue, -bash-3.1$ echo $ORACLE_HOME /oradata2/bin/ Here we see it is set, it may either unset. Though it is set wrong. The ORACLE_HOME path is before the bin directory. So here ORACLE_HOME will be /oradata2 instead of /oradata2/bin/ On unix set the value for the current session by, -bash-3.1$ export ORACLE_HOME=/oradata2/ On Windows environment you have to set by set ORACLE_HOME=C:\oracle or like that. In order to set it permanently edit your profile. On linux like, ~/.bash_profile or on unix edit the file .profile on home directory and make an entry of ORACLE_HOME. After setting correct ORACLE_HOME now try ...

Error Description Whenever you try to create recovery catalog in rman with a user other than RECOVERY_CATALOG_OWNER role owner it fails with RMAN-06426: RECOVERY_CATALOG_OWNER role must be granted to user SHAIK. -bash-3.00$ rman target sys/@moon:/SHAIK.MOON.SHAIK.NET catalog shaik/a Recovery Manager: Release 10.2.0.1.0 - Production on Mon Aug 4 05:15:32 2008 Copyright (c) 1982, 2005, Oracle. All rights reserved. target database Password: connected to target database: SHAIK(DBID=2855033675) connected to recovery catalog database RMAN> create catalog; RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-06426: RECOVERY_CATALOG_OWNER role must be granted to user SHAIK Cause of The Problem The userid that was supplied in the CATALOG connect string does not have the RECOVERY_CATALOG_OWNER role granted as a DEFAULT role. S...

The information about Cloning and Refreshing a Database process available over web widely or has already been discussed. Here, in this post, I would like to explain and provide the information on the following Questions about Cloning and Refreshing of a Database with my simple terms. Terms used in this post: Source System - the system to be cloned - Production Target System - the newly created (or cloned) system – Non Production Production Database – PROD Test Database – TEST Development Database - DEV What is a Database Clone? * A database clone is an activity/procedure which is performed by every DBA on regular basis or when there is a requirement or request to do so from the different departments i.e. Test/Development teams. * Cloning is nothing but creating a copy of production system in to a test or development environment. i.e. Having an exact image of production database in test area. * Cloning is a procedure for preparing and creating a test or development ...

The information about Cloning and Refreshing a Database process available over web widely or has already been discussed. Here, in this post, I would like to explain and provide the information on the following Questions about Cloning and Refreshing of a Database with my simple terms. Terms used in this post: Source System - the system to be cloned - Production Target System - the newly created (or cloned) system – Non Production Production Database – PROD Test Database – TEST Development Database - DEV What is a Database Clone? * A database clone is an activity/procedure which is performed by every DBA on regular basis or when there is a requirement or request to do so from the different departments i.e. Test/Development teams. * Cloning is nothing but creating a copy of production system in to a test or development environment. i.e. Having an exact image of production database in test area. * Cloning is a procedure for preparing and creating a test or development ...