ORA-16191: Primary log shipping client not logged on standby

On primary:

SQL> alter system switch logfile;
System altered.

SQL>select status,error from v$archive_dest where dest_id=2;
STATUS    ERROR
--------- -----------------------------------------------------------------
ERROR     ORA-16191: Primary log shipping client not logged on standby

There was password file and both primary and standby databases and they had same passwords still I received above error and I noticed primary database was not shipping archive log files to standby nodes.

I took below steps at both nodes:

1. deleted cureent password files of both primary and standby database

2. created password files for both databases with same option as below:
orapwd file=orapwtestdb password=sys entries=100 ignorecase=Y
orapwd file=orapwstdby password=sys entries=100 ignorecase=Y

3. chang init parameter on both databases to make passwords case insensitive:

alter system set sec_case_sensitive_logon=false;
Earlier I received below obvious error as I forgot to create password file for standby database and password is a must for log shipping from 10g.

SQL> alter system switch logfile;
System altered.
SQL> select status,error from v$archive_dest where dest_id=2;
STATUS    ERROR
--------- -----------------------------------------------------------------
ERROR     ORA-01031: insufficient privileges

 

create standby database in Oracle Standard edition

Oracle standard edition can not have data guard i.e it can not have the recovery managed mode standby database but it can have manually refreshed standby database.

Steps for creating manually refreshed standby databases is same as that for creating recovery managed standby database

Standby database in Standard edition has two main differences over data guard which are :

1) Primary databases can not perform log shipping i.e it can not copy/send the archive log file from to standby database's archive location.  So you will need to write ftp/sftp or scp shell script or batch script which will copy primary's archive log files to standby node

2) Standby database will be refreshed with below command:

SQL> RECOVER STANDBY DATABASE UNTIL CANCEL;

Optional : You can configure  new parameter introduced from 10g LOG_ARCHIVE_TARGET at the priamry database to the time(in minutes) interval at which you want archive logs to be automatically generated by log switch.

What you do not need:

• You do not need to set log_archive_dest_2 etc parameter in Primary nor in Standby database
• You do not need to set DB_UNIQUE_NAME
• You do not need to set parameter LOG_ARCHIVE_CONFIG

What do you need actually is : How to refresh standby database :

#script to refresh standby database-
sqlplus -s  / as sysdba <
set autorecovery on
alter database recover automatic standby database until cancel;
exit
EOF

You can invoke this script periodically after copying of archive logs from primary databases at time interval set by LOG_ARCHIVE_TARGET or at any interval.
 
To find out the last archived log applied on this standby:

you can use this query on mount stage.
SQL>select max(fhrba_Seq) from x$kcvfh;

Other steps are common:

That is you need to physically copy the database files (except redo logfiles) from primary to standby node and you also need to generate standby control file at primary database and copy to standby database node. You can perform this step via RMAN also.

Alternate way of creating standby database:

is to restore the backup of primary database on standby node and mount it and then refresh using below command. In this case you do not need to create standby control file.

Alter database recover automatic database using backup controlfile until cancel;

Additional Stuff:

If you want to failover to this new standby which was created with alternate way, for disaster recovery)
  ++ Apply maximum archive log from primary to standby. ++ Optionally we can copy the Online Redo Logs (ORL) from primary, provided they are accessible and recover so as to increase the point of recoverability     SQL>recover database using backup controlfile until cancel 
  ++ alter database open resetlogs.
NB:
 1) If you create a new tablespace in primary you may get the following errors while applying the corresponding logs in
   reporting server.
   ORA-00283: recovery session canceled due to errors
   ORA-01244: unnamed datafile(s) added to controlfile by media recovery
   ORA-01110: data file 5: '/home/jobin/tes1.dbf'


   At this stage you need to run a command like this to create an empty datafile in standby.

   a) SQL>select name from v$datafile where name like '%UNNAMED%';
   b) SQL> alter database create datafile '/home/oracle/product/ora101/dbs/UNNAMED00005'
            as '/oradata/dummy/test01.dbf';     Where /oradata/dummy is location for datafiles in standby.
       Now you can restart the recovery process.

RMAN-05541: no archived logs found in target database

RMAN> DUPLICATE TARGET DATABASE
   FOR STANDBY
   FROM ACTIVE DATABASE
   DORECOVER
   SPFILE
     SET "db_unique_name"="ORCL"
NOFILENAMECHECK;2> 3> 4> 5> 6> 7>
Starting Duplicate Db at 30-JUL-13
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=19 device type=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 07/30/2013 02:45:30
RMAN-05541: no archived logs found in target database

REMEDY is generate archive logs at Primary database

Flashing Back a Failed Primary Database into a Physical Standby Database

Flashing Back a Failed Primary Database into a Physical Standby Database

 

The following steps assume that a failover has been performed to a physical standby database and that Flashback Database was enabled on the old primary database at the time of the failover. This procedure brings the old primary database back into the Data Guard configuration as a physical standby database.


Step 1 Determine the SCN at which the old standby database became the primary database.

On the new primary database, issue the following query to determine the SCN at which the old standby database became the new primary database:


SQL> SELECT TO_CHAR(STANDBY_BECAME_PRIMARY_SCN) FROM V$DATABASE;

Step 2 Flash back the failed primary database.

Shut down the old primary database (if necessary), mount it, and flash it back to the value for STANDBY_BECAME_PRIMARY_SCN that was determined in Step 1.

SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;

SQL> FLASHBACK DATABASE TO SCN standby_became_primary_scn;

Step 3 Convert the database to a physical standby database.

Perform the following steps on the old primary database:

1.Issue the following statement on the old primary database:

SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;

This statement will dismount the database after successfully converting the control file to a standby control file.

2.Shut down and restart the database:

SQL> SHUTDOWN IMMEDIATE;

SQL> STARTUP MOUNT;

Step 4 Start transporting redo to the new physical standby database.

Perform the following steps on the new primary database:

1.Issue the following query to see the current state of the archive destinations:

SQL> SELECT DEST_ID, DEST_NAME, STATUS, PROTECTION_MODE, DESTINATION, -
> ERROR,SRL FROM V$ARCHIVE_DEST_STATUS;

2.If necessary, enable the destination:

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_n=ENABLE;

3.Perform a log switch to ensure the standby database begins receiving redo data from the new primary database, and verify it was sent successfully. Issue the following SQL statements on the new primary database:

SQL> ALTER SYSTEM SWITCH LOGFILE;

SQL> SELECT DEST_ID, DEST_NAME, STATUS, PROTECTION_MODE, DESTINATION,-
> ERROR,SRL FROM V$ARCHIVE_DEST_STATUS;

On the new standby database, you may also need to change the LOG_ARCHIVE_DEST_n initialization parameters so that redo transport services do not transmit redo data to other databases.

Step 5 Start Redo Apply on the new physical standby database.

Issue the following SQL statement on the new physical standby database:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE -
> USING CURRENT LOGFILE DISCONNECT;

Redo Apply automatically stops each time it encounters a redo record that is generated as the result of a role transition, so Redo Apply will need to be restarted one or more times until it has applied beyond the SCN at which the new primary database became the primary database. Once the failed primary database is restored and is running in the standby role, you can optionally perform a switchover to transition the databases to their original (pre-failure) roles. See upcoming post "Perfoming a Switchover to a Physical Standby Database" for more information.

convert failed primary database into physical standby database

Converting a Failed Primary into a Physical Standby Database Using RMAN Backups

 

To convert a failed primary database, Oracle recommends that you enable the Flashback Database feature on the primary and follow the procedure described in either Section 13.2.1 or Section 13.2.2. The procedures in those sections describe the fastest ways to convert a failed primary into either a physical or logical standby. However, if Flashback Database was not enabled on the failed primary, you can still convert the failed primary into either a physical or logical standby using a local backup of the failed primary, as described in the following sections:

The steps in this section describe how to convert a failed primary into a physical standby by using RMAN backups. This procedure requires that the COMPATIBLE initialization parameter of the old primary be set to at least 11.0.0.



Step 1 Determine the SCN at which the old standby database became the primary database.

On the new primary database, issue the following query to determine the SCN at which the old standby database became the new primary database:

SQL> SELECT TO_CHAR(STANDBY_BECAME_PRIMARY_SCN) FROM V$DATABASE;

Step 2 Restore and recover the entire database.

Restore the database with a backup taken before the old primary had reached the SCN at which the standby became the new primary (standby_became_primary_scn). Then, perform a point-in-time recovery to recover the old primary to that same point.

Issue the following RMAN commands:

RMAN> RUN
{
SET UNTIL SCN ;
RESTORE DATABASE;
RECOVER DATABASE;
}

 [ With user-managed recovery, you can first restore the database manually. Typically, a backup taken a couple of hours before the failover would be old enough. You can then recover the failed primary using the following command:

SQL> RECOVER DATABASE USIING BACKUP CONTROLFILE UNTIL CHANGE -

> ;

Unlike a reinstantiation that uses Flashback Database, this procedure adds one to standby_became_primary_scn. For datafiles, flashing back to an SCN is equivalent to recovering up until that SCN plus one. ]


Step 3 Convert the database to a physical standby database.

Perform the following steps on the old primary database:

1.Issue the following statement on the old primary database:
SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;

This statement will dismount the database after successfully converting the control file to a standby control file.

2.Shut down and restart the database:
SQL> SHUTDOWN IMMEDIATE;

SQL> STARTUP MOUNT;

Step 4 Open the database as read-only.

Issue the following command:

SQL> ALTER DATABASE OPEN READ ONLY;

The goal of this step is to synchronize the control file with the database by using a dictionary check. After this command, check the alert log for any actions suggested by the dictionary check. Typically, no user action is needed if the old primary was not in the middle of adding or dropping datafiles during the failover.

Step 5 (Optional) Mount the standby again, if desired

If you have purchased a license for the Active Data Guard option and would like to operate your physical standby database in active query mode, skip this step. Otherwise, bring your standby database to the mount state.

For example:

SQL> SHUTDOWN IMMEDIATE;

SQL> STARTUP MOUNT;

Step 6 Restart transporting redo to the new physical standby database.

Before the new standby database was created, the new primary database probably stopped transmitting redo to the remote destination. To restart redo transport services, perform the following steps on the new primary database:

1.Issue the following query to see the current state of the archive destinations:

SQL> SELECT DEST_ID, DEST_NAME, STATUS, PROTECTION_MODE, DESTINATION, -
> ERROR,SRL FROM V$ARCHIVE_DEST_STATUS;

2.If necessary, enable the destination:

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_n=ENABLE;

3.Perform a log switch to ensure the standby database begins receiving redo data from the new primary database, and verify it was sent successfully.

Note: This is an important step in order for the old primary to become a new standby following the new primary. If this step is not done, the old primary may recover to an incorrect database branch. The only way to correct the problem then is to convert the old primary again.

At the SQL prompt, enter the following statements:

SQL> ALTER SYSTEM SWITCH LOGFILE;

SQL> SELECT DEST_ID, DEST_NAME, STATUS, PROTECTION_MODE, DESTINATION, -
> ERROR,SRL FROM V$ARCHIVE_DEST_STATUS;

On the new standby database, you may also need to change the LOG_ARCHIVE_DEST_n initialization parameters so that redo transport services do not transmit redo data to other databases. This step can be skipped if both the primary and standby database roles were set up with the VALID_FOR attribute in one server parameter file (SPFILE). By doing this, the Data Guard configuration operates properly after a role transition.

Step 7 Start Redo Apply.

Start Redo Apply on the new physical standby database, as follows:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE -
> USING CURRENT LOGFILE DISCONNECT;

Once the failed primary database is restored and is running in the standby role, you can optionally perform a switchover to transition the databases to their original (pre-failure) roles. See Section 8.2.1, "Performing a Switchover to a Physical Standby Database" for more information.

DB lost write and standby database

Recovering From Lost-Write Errors on a Primary Database

During media recovery in a Data Guard configuration, a physical standby database can be used to detect lost-write data corruption errors on the primary database. This is done by comparing SCNs of blocks stored in the redo log on the primary database to SCNs of blocks on the physical standby database. If the SCN of the block on the primary database is lower than the SCN on the standby database, then there was a lost-write error on the primary database.

Note: Because lost-write errors are detected only when a block is read into the cache by a primary and the corresponding redo is later compared to the block on the standby, there may be undetected stale blocks on both the primary and the standby that have not yet been read and verified. These stale blocks do not affect operation of the current database because until those blocks are read, all blocks that have been used up to the SCN of the currently applied redo on the standby to do queries or updates were verified by the standby.

When a primary lost-write error is detected on the standby, one or more block error messages similar to the following for each stale block are printed in the alert file of the standby database:

Tue Dec 12 19:09:48 2006
STANDBY REDO APPLICATION HAS DETECTED THAT THE PRIMARY DATABASE
LOST A DISK WRITE OF BLOCK 26, FILE 7
NO REDO AT OR AFTER SCN 389667 CAN BE USED FOR RECOVERY.
.
.
.
The alert file then shows that an ORA-00752 error is raised on the standby database and the managed recovery is cancelled:
Slave exiting with ORA-752 exception
Errors in file /oracle/log/diag/rdbms/dgstwrite2/stwrite2/trace/stwrite2_pr00_23532.trc:
ORA-00752: recovery detected a lost write of a data block
ORA-10567: Redo is inconsistent with data block (file# 7, block# 26)
ORA-10564: tablespace TBS_2
ORA-01110: data file 7: '/oracle/dbs/btbs_21.f'
ORA-10561: block type 'TRANSACTION MANAGED DATA BLOCK', data object# 57503
.
.
.
The standby database is then recovered to a consistent state, without any corruption to its datafiles caused by this error, at the SCN printed in the alert file:

Recovery interrupted!
Recovered data files to a consistent state at change 389569

This last message may appear significantly later in the alert file and it may have a lower SCN than the block error messages. Also, the primary database may operate without visible errors even though its datafiles may already be corrupted.

The recommended procedure to recover from such errors is a failover to the physical standby, as described in the following steps.

Steps to Failover to a Physical Standby After Lost-Writes Are Detected on the Primary

1.Shut down the primary database. All data at or after SCN printed in the block error messages will be lost.
2.Issue the following SQL statement on the standby database to convert it to a primary:
SQL> ALTER DATABASE ACTIVATE STANDBY DATABASE;

oracle standby file management

Actions Required on a Standby Database After Changes to a Primary Database

Renaming a Datafile in the Primary Database

When you rename one or more datafiles in the primary database, the change is not propagated to the standby database. Therefore, if you want to rename the same datafiles on the standby database, you must manually make the equivalent modifications on the standby database because the modifications are not performed automatically, even if the STANDBY_FILE_MANAGEMENT initialization parameter is set to AUTO. The following describe how to rename a datafile in the primary database and manually propagate the changes to the standby database. If you do not want the standby database to have the same physical structure as the primary database, then these steps are not required.

Rename the datafile in the standby control file. Note that the STANDBY_FILE_MANAGEMENT initialization parameter must be set to MANUAL.

1. rename datafile at Primary
2. rename datafuke at standby at mount mode
SQL> ALTER DATABASE RENAME FILE '/disk1/oracle/oradata/payroll/tbs_4.dbf'
  2> TO '/disk1/oracle/oradata/payroll/tbs_x.dbf';

If you do not rename the corresponding datafile at the standby system, and then try to refresh the standby database control file, the standby database will attempt to use the renamed datafile, but it will not find it. Consequently, you will see error messages similar to the following in the alert log:

ORA-00283: recovery session canceled due to errors
ORA-01157: cannot identify/lock datafile 4 - see DBWR trace file
ORA-01110: datafile 4: '/Disk1/oracle/oradata/payroll/tbs_x.dbf'

Adding or Dropping Online Redo Log Files

Changing the size and number of the online redo log files is sometimes done to tune the database. You can add online redo log file groups or members to the primary database without affecting the standby database. Similarly, you can drop log file groups or members from the primary database without affecting your standby database. However, these changes do affect the performance of the standby database after switchover.

--------------------------------------------------------------------------------
Caution: Whenever you add an online redo log file to the primary database, you must add a corresponding standby redo log file to the standby database.
--------------------------------------------------------------------------------

For example, if the primary database has 10 online redo log files and the standby database has 2, and then you switch over to the standby database so that it functions as the new primary database, the new primary database is forced to archive more frequently than the original primary database. Consequently, when you add or drop an online redo log file at the primary site, it is important that you synchronize the changes in the standby database by following these steps:


If Redo Apply is running, you must cancel Redo Apply before you can change the log files.

If the STANDBY_FILE_MANAGEMENT initialization parameter is set to AUTO, change the value to MANUAL.

Add or drop an online redo log file:

To add an online redo log file, use a SQL statement such as this:

SQL> ALTER DATABASE ADD LOGFILE
'/disk1/oracle/oradata/payroll/prmy3.log' SIZE 100M;

To drop an online redo log file, use a SQL statement such as this:

SQL> ALTER DATABASE DROP LOGFILE
'/disk1/oracle/oradata/payroll/prmy3.log';

Repeat the statement you used in Step 3 on each standby database.  Restore the STANDBY_FILE_MANAGEMENT initialization parameter and the Redo Apply options to their original states.

Using Transportable Tablespaces with a Physical Standby Database

You can use the Oracle transportable tablespaces feature to move a subset of an Oracle database and plug it in to another Oracle database, essentially moving tablespaces between the databases.To move or copy a set of tablespaces when a physical standby is being used, perform the following steps:

Generate a transportable tablespace set that consists of datafiles for the set of tablespaces being transported and an export file containing structural information for the set of tablespaces.

Transport the tablespace set:

Copy the datafiles and the export file to the primary database.

Copy the datafiles to the standby database.

The datafiles must be to the directory defined by the DB_FILE_NAME_CONVERT initialization parameter. If DB_FILE_NAME_CONVERT is not defined, then issue the ALTER DATABASE RENAME FILE statement to modify the standby control file after the redo data containing the transportable tablespace was applied and failed. The STANDBY_FILE_MANAGEMENT initialization parameter must be set to AUTO.

Plug in the tablespace.

Invoke the Data Pump utility to plug the set of tablespaces into the primary database. Redo data will be generated and applied at the standby site to plug the tablespace into the standby database

Altering the Primary Database Control

FileUsing the SQL CREATE CONTROLFILE statement with the RESETLOGS option on your primary database will force the primary database to reset the online redo log file the next time the primary database is opened, thereby invalidating the standby database.

If you invalidated the control file for the standby database, re-create the file. If you invalidated the standby database, you must re-create the standby database.

NOLOGGING or Unrecoverable Operations

When you perform a DML or DDL operation using the NOLOGGING or UNRECOVERABLE clause, the standby database is invalidated and might require substantial DBA administrative activities to repair. You can specify the SQL ALTER DATABASE or SQL ALTER TABLESPACE statement with the FORCELOGGING clause to override the NOLOGGING setting. However, this statement will not repair an already invalidated database.

If you perform an unrecoverable operation (such as a direct path load), you will see a performance improvement on the primary database; but there is no corresponding recovery process performance improvement on the standby database, and you will have to move the data manually to the standby database

When the archived redo log file is copied to the standby site and applied to the physical standby database, a portion of the datafile is unusable and is marked as being unrecoverable. When you either fail over to the physical standby database, or open the standby database for read-only access, and attempt to read the range of blocks that are marked as UNRECOVERABLE, you will see error messages similar to the following:


ORA-01578: ORACLE data block corrupted (file # 1, block # 2521)
ORA-01110: data file 1: '/oracle/dbs/stdby/tbs_1.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option

To recover after the NOLOGGING clause is specified, you need to copy the datafile that contains the unjournaled data from the primary site to the physical standby site. Perform the following steps:

Step 1 Determine which datafiles should be copied.
Follow these steps:

Query the primary database:

SQL> SELECT NAME, UNRECOVERABLE_CHANGE# FROM V$DATAFILE;

NAME UNRECOVERABLE
----------------------------------------------------- -------------
/oracle/dbs/tbs_1.dbf 5216
/oracle/dbs/tbs_2.dbf 0
/oracle/dbs/tbs_3.dbf 0
/oracle/dbs/tbs_4.dbf 0

4 rows selected.

Query the standby database:

SQL> SELECT NAME, UNRECOVERABLE_CHANGE# FROM V$DATAFILE;

NAME UNRECOVERABLE
----------------------------------------------------- -------------
/oracle/dbs/stdby/tbs_1.dbf 5186
/oracle/dbs/stdby/tbs_2.dbf 0
/oracle/dbs/stdby/tbs_3.dbf 0
/oracle/dbs/stdby/tbs_4.dbf 0

4 rows selected.

Compare the query results of the primary and standby databases.

Compare the value of the UNRECOVERABLE_CHANGE# column in both query results. If the value of the UNRECOVERABLE_CHANGE# column in the primary database is greater than the same column in the standby database, then the datafile needs to be copied from the primary site to the standby site.

In this example, the value of the UNRECOVERABLE_CHANGE# in the primary database for the tbs_1.dbf datafile is greater, so you need to copy the tbs_1.dbf datafile to the standby site.

Step 2 On the primary site, back up the datafile you need to copy to the standby site.
Issue the following SQL statements:

SQL> ALTER TABLESPACE system BEGIN BACKUP;

% cp tbs_1.dbf /backup

SQL> ALTER TABLESPACE system END BACKUP;

Step 3 On the standby database, restart Redo Apply.

Issue the following SQL statement:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

You might get the following error messages (possibly in the alert log) when you try to restart Redo Apply:

ORA-00308: cannot open archived log 'standby1'
ORA-27037: unable to obtain file status
SVR4 Error: 2: No such file or directory
Additional information: 3
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: '/oracle/dbs/stdby/tbs_1.dbf'

If you get the ORA-00308 error and Redo Apply does not terminate automatically, you can cancel recovery by issuing the following statement from another terminal window:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

These error messages are returned when one or more log files in the archive gap have not been successfully applied. If you receive these errors, manually resolve the gaps, and repeat Step 3. See Section 5.8.4 for information about manually resolving an archive gap.

Determining If a Backup Is Required After Unrecoverable Operations

If you performed unrecoverable operations on your primary database, determine if a new backup operation is required by following these steps:

Query the V$DATAFILE view on the primary database to determine the system change number (SCN) or the time at which the Oracle database generated the most recent invalidated redo data.
Issue the following SQL statement on the primary database to determine if you need to perform another backup:

SELECT UNRECOVERABLE_CHANGE#,
TO_CHAR(UNRECOVERABLE_TIME, 'mm-dd-yyyy hh:mi:ss')
FROM V$DATAFILE;

If the query in the previous step reports an unrecoverable time for a datafile that is more recent than the time when the datafile was last backed up, then make another backup of the datafile in question.

Clone the database and make standby database using RMAN in Oracle 11g

CLONE DATABSE(duplicate database): Use the DUPLICATE command to create a copy of a source database. A duplicate database, is a copy of the source database with a unique DBID which RMAN generated. Because a duplicate database has a unique DBID, it is independent of the source database and can be registered in the same recovery catalog.  Following are steps of an example where sorce(target) database and clone(duplicate or auxilary instace) are on different machines.

#1. create the oracle password file in the clone database oracle_home

orapwd file=$ORACLE_HOME/dbs/prod

#2.  Take the backup of source(target) database including control file auto backup

#3 Make tns entry for source and clone (say auxilary or target or duplicate ) instance.

#4. Prepare init file of auxilary instance

# Minimum parameters file in $ORACLE_HOME/dbs/initprod at clone oracle home.

DB_NAME=prod

If source and clone db has to be in different location then optional parameters are:
DB_FILE_NAME_CONVERT = ("+SOURCEDSK","+DUPDSK")
LOG_FILE_NAME_CONVERT = ("+SOURCEDSK","+DUPDSK")

#5. create all directories required to start the clone or duplicate database

    like directory for diag_dest,audit_file_dest etc

#6. ftp or scp copy the backups taken at step# 2  to same locations on duplicate(clone) database node.

#7. startup the clone (auxilary) instance in nomount mode at clone db node

# Backup files are in matching location to that on the source server.
# Duplicate database to TARGET's current state.
DUPLICATE TARGET DATABASE TO prod
SPFILE
NOFILENAMECHECK;

# Duplicate database to TARGET's state 4 days ago.
DUPLICATE TARGET DATABASE TO DB11G
  UNTIL TIME 'SYSDATE-4'
  SPFILE
  NOFILENAMECHECK;

# Backup files are in a different location to that on the source server.
# Duplicate database to the most recent state possible using the provided backups.
# Works with just an AUXILIARY connection only.
DUPLICATE DATABASE TO DB11G
  SPFILE
  BACKUP LOCATION '/source/app/oracle/fast_recovery_area/DB11G'
  NOFILENAMECHECK;

STANDBY DATABASE:  A standby database, which is a special copy of the source database (called a primary database in a Data Guard environment) that is updated by applying archived redo logs from the primary database. A standby database does not get a new DBID.

steps for creating standby database using RMAN are similar to steps for creating duplicate(clone) database.

To create a standby database with the DUPLICATE command you must connect as target to the primary database and specify the FOR STANDBY option.

A database in a Data Guard environment is uniquely identified by means of the DB_UNIQUE_NAME parameter in the initialization parameter file. TheDB_UNIQUE_NAME must be unique across all the databases with the same DBID for RMAN to work correctly in a Data Guard environment.

Recovery of a Standby Databas By default, RMAN does not recover the standby database after creating it. RMAN leavesthe standby database mounted, but does not place the standby database in manual or managed recovery mode.

Use the DORECOVER option of the DUPLICATE command to specify that RMAN should recover the standby database. RMAN performs the following steps after creating the standby database files: 

1.RMAN begins media recovery. If recovery requires archived redo log files, and if the log files are not already on disk, then RMAN attempts to restore backups.

2. RMAN recovers the standby database to the specified time, system change number (SCN), or log file sequence number, or to the latest archived redo log file generated if none of the preceding are specified.

3. RMAN leaves the standby database mounted after media recovery is complete, but does not place the standby database in manual or managed recovery mode. 

simple example : DUPLICATE TARGET DATABASE FOR STANDBY DO RECOVER
           NOFILENAMECHECK;

NOFILENAMECHECK: prevents RMAN from checking whether the datafiles and online redo logs files of the source database are in use when the source database files share the same names as the duplicate database files (see Example 2-73). You are responsible for determining that the duplicate operation will not overwrite useful data. This option is necessary when you are creating a duplicate database in a different host that has the same disk configuration, directory structure, and filenames as the host of the source database. For example, assume that you have a small database located in the /dbs directory of host1:
/oracle/dbs/system_prod1.dbf
/oracle/dbs/users_prod1.dbf
/oracle/dbs/rbs_prod1.dbf
Assume that you want to duplicate this database to host2, which has the same file system /oracle/dbs/*, and you want to use the same filenames in the duplicate database as in the source database. In this case, specify the NOFILENAMECHECK option to avoid an error message. Because RMAN is not aware of the different hosts, RMAN cannot determine automatically that it should not check the filenames.

If duplicating a database on the same host as the source database, then make sure that NOFILENAMECHECK is not set. Otherwise, RMAN may signal the error.

Active Database Duplication: Beginning from Oracle 11g it has ability to create duplicate databases directly without the need for a backup,known as active database duplication. Its requirement is source database have to be in ARCHIVELOG mode..Both the source and destination database servers require a "tnsnames.ora" entry for the destination database.

Using the DUPLICATE Command to Create a Standby Database:

1. Creating a Standby Database with Active Database Duplication  (available from 11g)

DUPLICATE TARGET DATABASE

  FOR STANDBY
  FROM ACTIVE DATABASE
  DORECOVER
  SPFILE
    SET "db_unique_name"="foou" COMMENT ''Is a duplicate''
    SET LOG_ARCHIVE_DEST_2="service=inst3 ASYNC REGISTER
     VALID_FOR=(online_logfile,primary_role)"
    SET FAL_CLIENT="inst3" COMMENT "Is standby"
    SET FAL_SERVER="inst1" COMMENT "Is primary"

  NOFILENAMECHECK;

2. Creating a Standby Database with Backup-Based Duplication

DUPLICATE TARGET DATABASE
  FOR STANDBY
  DORECOVER
  SPFILE
    SET "db_unique_name"="foou" COMMENT ''Is a duplicate''
    SET LOG_ARCHIVE_DEST_2="service=inst3 ASYNC REGISTER
     VALID_FOR=(online_logfile,primary_role)"
    SET FAL_CLIENT="inst3" COMMENT "Is standby"
    SET FAL_SERVER="inst1" COMMENT "Is primary"
  NOFILENAMECHECK;

Other examples:

Copying the Server Parameter File in Active Database Duplication

RMAN> CONNECT TARGET SYS@prod

target database Password: password
connected to target database: PROD1 (DBID=39525561)

RMAN> CONNECT AUXILIARY SYS@dup

auxiliary database Password: password
connected to auxiliary database: DUP1 (not mounted)

RMAN> DUPLICATE TARGET DATABASE TO dup
2> FROM ACTIVE DATABASE
3> PASSWORD FILE
4> SPFILE;

Setting New Filenames Manually for Duplication:

DUPLICATE TARGET DATABASE TO dup
  FOR STANDBY
  FROM ACTIVE DATABASE
  PASSWORD FILE
  SPFILE 
    PARAMETER_VALUE_CONVERT '/disk1', '/disk2'
    SET DB_FILE_NAME_CONVERT '/disk1','/disk2'
    SET LOG_FILE_NAME_CONVERT '/disk1','/disk2'
    SET SGA_MAX_SIZE 200M
    SET SGA_TARGET 125M;

references: oracle doc1
                 oracle doc2
                 oracle base
                      

setting up 10g physical standby database

STEPS for creating 10g dataguard

prerequisite : 9i dataguard setup knowledge

Step1 : Prepare initSID.ora file for primary and standby databases as follow.

** STANDBY setup parameters are given in bold and highlighted

part A)

**** Production database primary file ****

prod.__db_cache_size=125829120
prod.__java_pool_size=4194304
prod.__large_pool_size=4194304
prod.__shared_pool_size=79691776
prod.__streams_pool_size=0
*.audit_file_dest='C:\oracle\product\10.2.0\admin\prod\adump'
*.background_dump_dest='C:\oracle\product\10.2.0\admin\prod\bdump'
*.compatible='10.2.0.3.0'
*.control_files='Q:\oradata\prod\control01.ctl','Q:\oradata\prod\control02.ctl','Q:\oradata\prod\control03.ctl'
*.core_dump_dest='C:\oracle\product\10.2.0\admin\prod\cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='prod'
*.db_recovery_file_dest='C:\oracle\product\10.2.0\flash_recovery_area'
*.db_recovery_file_dest_size=2147483648

*.instance_name='prod'
*.job_queue_processes=10

*.db_unique_name='prod'
*.fal_client='prod'
*.fal_server='prod_stdby'
*.log_archive_config='DG_CONFIG=(prod,stdby)'
*.log_archive_dest_1='LOCATION=Q:\oradata\prod\arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=prod'
*.log_archive_dest_2='SERVICE=prod_stdby VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=stdby LGWR ASYNC REOPEN=10'
*.remote_login_passwordfile='EXCLUSIVE'

*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_format='%t%s%r.arc'
*.open_cursors=300
*.pga_aggregate_target=72351744
*.processes=150
*.service_names='prod'
*.sga_target=218103808
*.standby_archive_dest='q:\arch'
*.standby_file_management='AUTO'
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='C:\oracle\product\10.2.0\admin\prod\udump'

Part B)

**** standby database primary file ****

*.compatible='10.2.0.3.0'
*.control_files='C:\oradata\prod\stdby.ctl'
*.instance_name='stdby'
*.db_name='prod'
*.db_unique_name='stdby'
*.fal_client='prod_stdby'
*.fal_server='prod'
*.log_archive_config='DG_CONFIG=(prod,stdby)'
*.log_archive_dest_1='LOCATION=C:\oradata\prod\stdby\arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=stdby'
*.log_archive_dest_2='SERVICE=prod VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=prod LGWR ASYNC REOPEN=10'
*.db_file_name_convert='Q:\','C:\'
*.log_file_name_convert='Q:\','C:\'
*.remote_login_passwordfile='EXCLUSIVE'

*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_format='%t_%s_%r'
*.service_names='stdby'
*.shared_pool_size=104857600
*.standby_file_management='AUTO'


STEP 2:

part A)

shutdown primary database cleanly and copy datafiles to standby location.

Part B) create standby controlfile by giving following command at production database at mount stage

alter databse create standby controlfiel as 'location\filename' ;

move this generated file to standby controlfile location as pointed by standby initSID.ora file

step 3)
create oracle service and password file with the same password at standby database location

step 4)
prepare the TNSNAMES.ORA and LISTENER.ora at both production and standy locations.

To check archive log gap

SELECT * FROM (
SELECT sequence#, archived, applied,
TO_CHAR(completion_time, 'RRRR/MM/DD HH24:MI') AS completed
FROM sys.v$archived_log
ORDER BY sequence# DESC)
WHERE ROWNUM <= 10