isitdevops.com/databases

ALTER DATABASE ARCHIVELOG;
ALTER DATABASE FORCE LOGGING;
ALTER DATABASE ADD STANDBY LOGFILE ‘..’ SIZE 50M;
SELECT GROUP#, TYPE, MEMBER FROM V$LOGFILE WHERE TYPE = ‘STANDBY’;
ALTER SYSTEM SET LOG_ARCHIVE_CONFIG = ‘DG_CONFIG=(db_unique_name_db, db_unique_name_sby)’;
SELECT * FROM V$DATAGUARD_CONFIG;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2=’SERVICE=stbydb, VALID_FOR=(ONLINE_LOGFILES, PRIMARY_ROLE) DB_UNIQUE_NAME=db_unique_name_sby’;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;

#VALID_FOR = pairs of (logfiletype, role)
#log file types = ONLINE_LOGFILES / STANDBY_LOGFILES / ALL_LOGFILES
#role = PRIMARY_ROLE / STANDBY_ROLE / ALL_ROLES
#
# COMBINATION   PRIMARY  PHYSICAL LOGICAL
#ONLINE_LOGFILE, PRIMARY_ROLE Valid  Ignored  Ignored
#ONLINE_LOGFILE, STANDBY_ROLE…I….I…………V
#ONLINE_LOGFILE, ALL_ROLES ……..V….I…………V
#STANDBY_LOGFILE,STANDBY_ROLE I….V………..V
#STANDBY_LOGFILE,ALL_ROLES …….I….V………..V
#ALL_LOGFILES,PRIMARY_ROLE …….V….I…………I
#ALL_LOGFILES,STANDBY_ROLE …….I….V………..V
#ALL_LOGFILES,ALL_ROLES  …………V….V………..V
#
# n.b. both single and plural keywords are valid
#
# SYNC or ASYN, AFFIRN or NOAFFIRM
# ASYNC is default
# NOAFFIRM is default with ASYNC
# AFFIRM   is default with SYNC
#
# DB_FILE_NAME_CONVERT – do not use if OMF is in use.
# LOG_FILE_NAME_CONVERT – used to rename redo log file data – do not use if OMF is in use.
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;
#
COPY PRIMARY DB PASSWORD FILE TO THE STANDBY LOCATION.

CREATE AN INIT.ORA FILE CONTAINING JUST ONE LINE, “DB_NAME=stbyname”
CREATE THE AUDIT TRAIL IN $oracle_base/admin, i.e. $ORACLE_BASE/admin/dbname/adump
CREATE A DIRECTORY FOR THE ORACLE DATA FILES in $ORACLE_BASE/oradata
STARTUP STBY DB IN NOMOUNT
sql> startup nomount pfile=$initsby.ora;

ALTER SYSTEM SET FAL_CLIENT=’primary’;
ALTER SYSTEM SET FAL_SERVER=’stby’;

CREATE THE STANDBY
==================
$rman
rman> connect target sys/oracle
rman> connect auxiliary sys/oracle@pc00sby1
run {
allocate channel prmy1 type disk;
allocate channel prmy2 type disk;
allocate channel prmy3 type disk;
allocate channel prmy4 type disk;
allocate auxiliary channel stby type disk;
duplicate target database for standby from active database
spfile
parameter_value_convert ‘pc00prmy’, ‘pc00sby1’
set db_unique_name=’pc00sby1′
set db_file_name_convert=’/pc00prmy/’,’/pc00sby1/’
set log_file_name_convert=’/pc00prmy/’,’/pc00sby1/’
set control_files=’/u01/app/oracle/oradata/pc00sby1.ctl’
set log_archive_max_processes=’5′
set fal_client=’pc00sby1′
set fal_server=’pc00prmy’
set standby_file_management=’AUTO’
set log_archive_config=’DG_CONFIG=(pc00prmy,pc00sby1)’
set log_archive_dest_1=’service=pc00prmy ASYNC valid_for=(onlibe_logfile, primary_role) db_unique_name=pc00prmy’;
}
rman> exit

The RECOVERY_MODE column of V$ARCHIVE_DEST_STATUS contains “MANAGED REAL TIME APPLY” when real-time apply is in use.
If you define a DELAY ON A DESTINATION AND USE REAL-TIME APPLY, the delay is ignored.

To start real-time apply use “ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

set DB_LOST_WRITE_PROTECT init parameter to detect lost writes.
TYPICAL (on primary) only read/write tablespaces
FULL    (on primary) r/w and readonly tablespaces
TYPICAL or FULL on standby, performs detection during mrp
NONE    disables LOST WRITE detection

select protection_mode,protection_level, database_role as role,
switchover_status
from v$database

select inst_id,thread#,sequence#,status
from gv$log
where status=’CURRENT’
order by inst_id,thread#,sequence#

Perform the following tasks to prepare for standby database creation:

1. Enable forced logging on the primary database.
2. Enable archiving on the primary database.
3. Set all necessary initialization parameters on the primary database.
4. Create an initialization parameter file for the standby database.
5. If the primary database is configured to use OMF, then Oracle recommends that the standby database be configured to use OMF, too. To do this, set the DB_CREATE_FILE_DEST and DB_CREATE_ONLINE_LOG_DEST_n initialization parameters to appropriate values. Maintenance and future role transitions are simplified if the same disk group names are used for both the primary and standby databases.
6. Set the STANDBY_FILE_MANAGEMENT initialization parameter to AUTO.
7. Configure Oracle Net, as required, to allow connections to the standby database.
8. Create a remote login password file for the standby database. Use the same password for the SYS account as on the primary database.
9. Start the standby database instance without mounting the control file.

Perform the following tasks to create the standby database:

1. If the standby database is going to use ASM, create an ASM instance if one does not already exist on the standby database system.
2. Use the RMAN BACKUP command to create a backup set that contains a copy of the primary database’s datafiles, archived log files, and a standby control file.
3. Use the RMAN DUPLICATE … FOR STANDBY command to copy the datafiles, archived redo log files and standby control file in the backup set to the standby database’s storage area.

The DUPLICATE … FOR STANDBY command performs the actual data movement at the standby instance. If the backup set is on tape, the media manager must be configured so that the standby instance can read the backup set. If the backup set is on disk, the backup pieces must be readable by the standby instance, either by making their primary path names available through NFS, or by copying them to the standby system and using RMAN CATALOG BACKUPPIECE command to catalog the backup pieces before restoring them.

After you successfully complete these steps, continue with the steps in Section 3.2.7, to verify the configuration of the physical standby database.

To create a logical standby database, continue with the standby database creation process described in Chapter 4, but with the following modifications:

1. For a logical standby database, setting the DB_CREATE_FILE_DEST parameter does not force the creation of OMF filenames. However, if this parameter was set on the primary database, it must also be set on the standby database.
2. After creating a logical standby control file on the primary system, do not use an operating system command to copy this file to the standby system. Instead, use the RMAN RESTORE CONTROLFILE command to restore a copy of the logical standby control file to the standby system.
3. If the primary database uses OMF files, use RMAN to update the standby database control file to use the new OMF files created on the standby database. To perform this operation, connect only to the standby database, as shown in the following example:

> RMAN TARGET sys/oracle@lstdby RMAN> CATALOG START WITH '+stby_diskgroup'; RMAN> SWITCH DATABASE TO COPY; 

After you successfully complete these steps, continue with the steps in Section 4.2.5 to start, recover, and verify the logical standby database.

Using a combination of Data Guard, restore points, and Flashback Database, a physical standby database can be opened temporarily in read/write mode for development, reporting, or testing purposes, and then flashed back to a point in the past to be reverted back to a physical standby database. When the database is flashed back, Data Guard automatically synchronizes the standby database with the primary database, without the need to re-create the physical standby database from a backup copy of the primary database.

Figure 12-7 shows a physical standby database being activated as a read/write clone database, resynchronized with the primary database, and eventually flashed back and reverted to its physical standby database role. You can repeat this cycle of activate, flashback and revert as many times as is necessary.

Figure 12-7 Using a Physical Standby Database As a Testing and Reporting Database

Description of “Figure 12-7 Using a Physical Standby Database As a Testing and Reporting Database”
Caution:

While the database is activated, it is not receiving redo data from the primary database and cannot provide disaster protection. It is recommended that there be at least two physical standby databases participating in the configuration so that the primary database remains protected against data loss.

Perform the following steps to activate the physical standby database as a production database and later resynchronize it with the primary database.

Step 1 Prepare the physical standby database to be activated.

1. Set up a flash recovery area.

On the physical standby database that will be activated for read/write access, you should set the following initialization parameters to ensure a guaranteed restore point can be created. This scenario sets up the flash recovery area in the /arch/oradata location:

SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE=20G; SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='/arch/oradata'; 

2. Cancel Redo Apply and create a guaranteed restore point.

On the physical standby database, stop Redo Apply and create a restore point:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; SQL> CREATE RESTORE POINT before_application_patch GUARANTEE FLASHBACK DATABASE; 

When you create a guaranteed restore point, you associate an easy-to-remember name with a timestamp or SCN so that you can later flash back the database to a name instead of specifying an exact SCN or time.

Step 2 Prepare the primary database to have the physical standby be diverged.

1. Archive the current log file.

On the primary database, switch logs so the SCN of the restore point (created in step 1) will be archived on the physical standby database:

SQL> ALTER SYSTEM ARCHIVE LOG CURRENT; 

When using standby redo log files, this step is essential to ensure the database can be properly flashed back to the restore point.

2. Defer log archive destinations pointing to the standby that will be activated.

On the primary database (on all instances if this is a Real Applications Cluster), defer the archival of redo data to the destination associated with the physical standby database that will be opened. For example:

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=DEFER;

Step 3 Activate the physical standby database.

On the physical standby database, perform the following steps:

1. Activate the physical standby database:

SQL> ALTER DATABASE ACTIVATE STANDBY DATABASE; 

2. If the physical standby database has been opened read-only since the instance was started, perform this step. Otherwise, skip to step 3.

Enter the following statement to shut down and restart the physical standby database:

SQL> STARTUP MOUNT FORCE; 

3. Set the protection mode to maximum performance and open the database for read/write access:

SQL> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE; SQL> ALTER DATABASE OPEN; 

After the standby database is activated, its protection mode is downgraded to the maximum performance mode, because there is no standby database configured to protect the database against data loss while it is temporarily activated as a production database. Note that this protection mode setting does not affect the protection mode of the original primary database, it affects only the activated standby database.

When the activated standby database is converted back to a physical standby database, its protection mode is automatically changed to match that of the original primary database.

If the standby database that was opened read/write temporarily has remote archive log destinations, you might need to disable them. In this way, the read/write testing or reporting database will not propagate its temporary changes to other standby databases in the original Data Guard environment.

Step 4 Use the activated database for reporting or testing.

Once the standby database has been activated, you can run reporting tools or perform other testing and activities for days or even weeks, independent of the primary database.

Caution:

While the database is activated, it is not receiving redo data from the primary database and cannot provide disaster protection. It is recommended that there be at least two physical standby databases participating in the configuration so that the primary database remains protected against data loss.

Also, any results stored in the activated database will be lost when you later flash back the database. Results that should be saved must be copied out of the activated database before flashing it back.

Step 5 Revert the activated database back to a physical standby database.

After you finish testing, you need to resynchronize the activated database with the primary database. Issue the following statements on the activated database to quickly flash it back to the guaranteed restore point and resynchronize it with the primary database:

SQL> STARTUP MOUNT FORCE; SQL> FLASHBACK DATABASE TO RESTORE POINT before_application_patch; SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY; SQL> STARTUP MOUNT FORCE;

Step 6 Catch up the standby database to the primary database.

The method you use will depend on how far the activated standby database lags behind the primary database in its application of redo data:

Let archive gap resolution fetch all missing archived redo log files and allow Redo Apply to apply the gap.

If the activated database has not fallen too far behind the original primary database, issue the following statement on the standby database to resynchronize it with the primary database and restart Redo Apply. For example:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT; 

Then, go to Step 7.

Create an incremental backup on the primary and apply it to the standby.

If the activated database has fallen too far behind the original primary database (for example, if there are not sufficient log files available), you can take an incremental backup from the primary database and apply it to the standby database. See Section 12.7.1 for information about using RMAN incremental backups to resynchronize the standby database with the primary database.

Note:

If the standby database lags very far behind the primary database, it may be quicker to apply an incremental backup created from the primary database using the steps in Section 12.7.1.

After you apply an incremental backup to the standby database, you typically need to apply more redo to the standby database to activate the physical standby database again for read/write testing or reporting purposes. More specifically, you might need to apply the redo generated by the primary database while the incremental backup was taken. Otherwise, issuing an ALTER DATABSE ACTIVATE STANDBY DATABASE will return an error.

Step 7 Reenable archiving to the physical standby database destination.

On the primary database, issue the following statement to reenable archiving to the physical standby database:

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;

Suppose an error has occurred on the primary database in a Data Guard configuration in which the standby database is using real-time apply. In this situation, the same error will be applied on the standby database.

However, if Flashback Database is enabled, you can revert the primary and standby databases back to their pre-error condition by issuing the FLASHBACK DATABASE and OPEN RESETLOGS statements on the primary database, and then issuing a similar FLASHBACK STANDBY DATABASE statement on the standby database before restarting log apply services. (If Flashback Database is not enabled, you need to re-create the standby database, as described in Chapter 3 and Chapter 4, after the point-in-time recovery was performed on the primary database.)

12.5.1 Flashing Back a Physical Standby Database to a Specific Point-in-Time

The following steps describe how to avoid re-creating a physical standby database after you issued the OPEN RESETLOGS statement on the primary database.

Step 1 Determine the SCN before the RESETLOGS operation occurred.

On the primary database, use the following query to obtain the value of the system change number (SCN) that is 2 SCNs before the RESETLOGS operation occurred on the primary database:

SQL> SELECT TO_CHAR(RESETLOGS_CHANGE# - 2) FROM V$DATABASE;

Step 2 Obtain the current SCN on the standby database.

On the standby database, obtain the current SCN with the following query:

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

Step 3 Determine if it is necessary to flash back the database.

If the value of CURRENT_SCN is larger than the value of resetlogs_change# – 2, issue the following statement to flash back the standby database.

SQL> FLASHBACK STANDBY DATABASE TO SCN resetlogs_change# -2; 

If the value of CURRENT_SCN is less than the value of the resetlogs_change# – 2, skip to Step 4.

If the standby database’s SCN is far enough behind the primary database’s SCN, log apply services will be able to continue through the OPEN RESETLOGS statement without stopping. In this case, flashing back the database is unnecessary because log apply services do not stop upon reaching the OPEN RESETLOGS statement in the redo data.

Step 4 Restart Redo Apply.

To start Redo Apply on the physical standby database, issue the following statement:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT; 

The standby database is now ready to receive and apply redo from the primary database.

Step 1 Determine the SCN to which to flash back the failed primary database.

On the new primary database, issue the following query to determine the SCN to which you want to flash back the failed primary database:

SQL> SELECT APPLIED_SCN AS FLASHBACK_SCN FROM V$LOGSTDBY_PROGRESS;

Step 2 Determine the log files that must be copied to the failed primary database for Flashback Database.

On the new primary database, issue the following query to determine the log files that must be copied to the failed primary database for Flashback Database to reach a consistent state

SQL> SELECT NAME FROM DBA_LOGSDTBY_LOG  2>   WHERE NEXT_CHANGE# >   3>           (SELECT VALUE FROM DBA_LOGSTDBY_PARAMETERS  4>   WHERE NAME = 'STANDBY_BECAME_PRIMARY_SCN') 5>           AND FIRST_CHANGE <= (FLASHBACK_SCN from step 1);

Step 3 Flash back the failed primary database.

To create a new logical standby database, shut down the database (if necessary), mount the failed primary database, flash it back to the FLASHBACK_SCN determined in step 1, and enable the database guard.

SQL> SHUTDOWN IMMEDIATE; SQL> STARTUP MOUNT; SQL> FLASHBACK DATABASE TO SCN became_primary_scn; SQL> ALTER DATABASE GUARD ALL;

Step 4 Open the database with the RESETLOGS option.

SQL> ALTER DATABASE OPEN RESETLOGS;

Step 5 Create a database link to the new primary database and start SQL Apply.

SQL> CREATE PUBLIC DATABASE LINK mylink 2> CONNECT TO system IDENTIFIED BY password    3> USING 'service_name_of_new_primary_database';  SQL> ALTER DATABASE START LOGICAL STANDBY APPLY NEW PRIMARY mylink; 

The role reversal is now complete.

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.

To create a new physical standby 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 Restart transporting redo to the new physical standby database.

Step 5 Start Redo Apply.

Start Redo Apply or real-time apply on the new physical standby database:

To start Redo Apply:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT; 

To start real-time apply:

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

Ensure all archive is present.

Ensure recovery in progress.

Check switchover status again – should be ok

SELECT SWITCHOVER_STATUS FROM V$DATABASE;

to primary

IF SESSIONS ACTIVE then error is:

ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY *

ORA-01093: ALTER DATABASE CLOSE only permitted with no sessions connected

—

SELECT SID, PROCESS, PROGRAM FROM V$SESSION WHERE TYPE = ‘USER’ AND SID <> (SELECT DISTINCT SID FROM V$MYSTAT);

if example shows jobs (i.e. CJQ0 is present) then

SHOW PARAMETER JOB_QUEUE_PROCESSES;

ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;

If still errors then:

SELECT SID, PROCESS, PROGRAM FROM V$SESSION;

Kill the sessions.