Oracle Data Guard DataGuard High Availability Failover TAF

Search the Reference Library pages:

Oracle Data Guard

Version 11.1

Docs

Doc ID & Link	Description
Click Here	11g Data Guard Docs
Click Here	11g Data Guard - What's New
Click Here	Data Guard Protection Modes Explained
Click Here	Switchovers
Click Here	Fast start Failovers
Click Here	Data Guard Broker

Acronyms

Acronym	Short For
ARC0	Archiver Process on Local Server
ARCH	Archiver
ASYNC	Asynchronous
DMON	Data Guard Monitor
DR	Disaster Recovery
FAL	Fetch Archive Log
LCR	Logical Change Record
LGWR	Logwriter
LNS	Network Server Process
LSP	Logical Standby Process (SQL Apply for Logical DG)
MRP	Managed Recovery Process (Redo Apply for Physical DG)
RFS	Remote File Server
SYNC	Synchronous
TAF	Transparent Application Failover

Dictionary Views

Active

Standby

DBA_LOG_GROUPS

DBA_LOG_GROUP_COLUMNS

DBA_REGISTERED_ARCHIVED_LOG

DBA_HIST_LOG

GV$DATAGUARD_CONFIG

GV$DATAGUARD_STATUS

DBA_LOGSTDBY_EVENTS	DBMS_DRS
DBA_LOGSTDBY_HISTORY	DBMS_LOGSTDBY
DBA_LOGSTDBY_LOG	GV$DATAGUARD_CONFIG
DBA_LOGSTDBY_NOT_UNIQUE	GV$DATAGUARD_STATUS
DBA_LOGSTDBY_PARAMETERS	GV$LOGSTDBY
DBA_LOGSTDBY_PROGRESS	GV$LOGSTDBY_STATS
DBA_LOGSTDBY_SKIP	GV$MANAGED_STANDBY
DBA_LOGSTDBY_SKIP_TRANSACTION	GV$STANDBY_LOG
DBA_LOGSTDBY_UNSUPPORTED	GV$ARCHIVE_GAP

Data Guard Manager

Start Data Guard Manager

$ DGMGRL

add

Add a standby database to the broker configuration

DGMGRL>

connect

Connect to the database via the broker

DGMGRL> connect sys/syspwd
Connected.
Error:
ORA-16525: the Data Guard broker is not yet available
ORA-06512: at SYS:DBMS_DRS", line 124
ORA-06512: at line 1

SQL> alter system set DG_BROKER_START = TRUE;
System altered.

DGMGRL> connect sys/syspwd
Connected.
DGMGRL> exit

SQL> alter system set DG_BROKER_START = FALSE;

create

Create a broker configuration

DGMGRL> CREATE CONFIGURATION proda AS

disable

Disable a configuration, a database, or Fast-Start Failover

DGMGRL>

edit

Edit a configuration, database, or instance

DGMGRL> editdatabase 'site1_edrsr8p1' set property 'LogXptMode'='SYNC';

DGMGRL> edit configuration set protection mode as MAXAVAILABILITY;

enable

Enable a configuration, a database, or Fast-Start Failover

DGMGRL>

exit

Exit the Data Guard Manager program

DGMGRL> exit

failover

Change a standby database to be the primary database

DGMGRL>

help

Display description and syntax for a command

DGMGRL> help

quit

Exit the program

DGMGRL> quit

reinstate

Change a disabled database into a viable standby database

DGMGRL>

rem

Comment (remark) to be ignored by DGMGRL

DGMGRL>

remove

Remove a configuration, database, or instance

DGMGRL>

show

Display information about a configuration, database, or instance

DGMGRL>

shutdown

Shutdown a currently running Oracle instance

DGMGRL>

start

Start Fast-Start Failover observer

DGMGRL>

startup

Start an Oracle database instance

DGMGRL>

stop

Stop Fast-Start Failover observer

DGMGRL>

switchover

Switch roles between the primary database and a standby database

DGMGRL>

Control Commands

TBD

Operating System Installation

As root: Create DBA Group and Oracle User (both servers)

$ /usr/sbin/groupadd oinstall
$ /usr/sbin/groupadd dba
$ /usr/sbin/useradd -m -g oinstall -G dba oracle
$id oracle

$passwd oracle
-- set the password to oracle1

$ id nobody
-- if nobody does not exist then
$ /usr/sbin/useradd nobody

As root: Create Installation Directories (both servers)

mkdir -p /app/oracle
chown -R oracle:dba /app/oracle
chmod -R 775 /app/oracle

mkdir /stage
chown -R oracle:dba /stage

As root: Alter Kernel Parameters in sysctl.conf by appending the highlighted text

(both servers)

cd /etc

vi sysctl.conf

fs.file-max = 65536
kernel.shmall = 2097152
kernel.shmmax = 2147483648
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
# send receive buffers
net.core.rmem_default = 4194304
net.core.rmem_max = 4194304
net.core.wmem_default = 8388608
net.core.wmem_max = 8388608
# tcp read and write buffers
net.ipv4.tcp_rmem=4096 262144 8388608
net.ipv4.tcp_wmem=4096 262144 8388608

net.ipv4.ip_forward = 0
net.ipv4.conf.default.rp_filter = 1
net.ipv4.ip_local_port_range = 1024 65000

-- Activate changes
/sbin/sysctl -p

As root: Set Shell Limits

(both servers)

cd /etc/security

-- Modify limits.conf

cat >> /etc/security/limits.conf <<EOF
oracle soft nproc 2047
oracle hard nproc 16384
oracle soft nofile 1024
oracle hard nofile 65536
EOF

-- Modify /etc/pam.d/ login
-- read the file and place above the last lines as indicated

session required /lib/security/pam_limits.so
session required pam_limits.so

As root: Change Default Profile

(both servers)

cd /etc

-- if Bourne, Korn or Bash shell

cat >> /etc/profile <<EOF
if [ \$USER = "oracle" ]; then
if [ \$SHELL = "/bin/ksh" ]; then
    ulimit -p 16384
    ulimit -n 65536
else
    ulimit -u 16384 -n 65536
fi
umask 022
fi
EOF

As root: Append servers to hosts file

(both servers)

cd /etc

Note: the line:
127.0.0.1 omega#.psoug.org omega# localhost.localadmin localhost
must remain. If you delete it ... nothing will work properly

vi hosts

192.168.1.101	omega1	omega1.psoug.org
192.168.1.102	omega2	omega2.psoug.org
10.0.1.1	dgomega1	dgomega1.psoug.org
10.0.1.2	dgomega2	dgomega2.psoug.org
192.168.10.01	omega1st	omega1st.psoug.org
192.168.10.02	omega2st	omega2st.psoug.org
192.168.10.100	netapp	netapp.psoug.org
192.168.1.119	bigdog	bigdog.psoug.org

-- ping all hosts (substitute actual node names for the "omega")
ping 192.168.1.1
ping www.oracle.com
ping omega1
ping omega2
ping omega1.psoug.org
ping omega2.psoug.org

Modify .bashrc (both servers)

cd /home/oracle

vi .bashrc

umask 022
ORACLE_BASE=/app/oracle/product
ORACLE_HOME=/app/oracle/product/10.1.0/db_1
# ORACLE_SID=proda
# ORACLE_SID=prodb
PATH=$ORACLE_HOME/bin:$PATH
export PATH ORACLE_BASE ORACLE_HOME ORACLE_SID

-- sid name on omega1 is proda
-- sid name on omega2 is prodb

initproda.ora

proda.__db_cache_size=687865856
proda.__java_pool_size=16777216
proda.__large_pool_size=16777216
proda.__oracle_base='/app/oracle/product'
proda.__pga_aggregate_target=671088640
proda.__sga_target=989855744
proda.__shared_io_pool_size=0
proda.__shared_pool_size=251658240
proda.__streams_pool_size=0
*.audit_file_dest='/app/oracle/product/admin/proda/adump'
*.audit_trail='db'
*.compatible='11.1.0.0.0'
*.control_files= '/app/oracle/product/oradata/proda/control01.ctl', '/app/oracle/product/oradata/proda/control02.ctl',
'/app/oracle/product/oradata/proda/control03.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='proda'
*.db_recovery_file_dest='/app/oracle/product/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.diagnostic_dest='/app/oracle/product'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=prodaXDB)'
*.memory_target=1658847232
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
ifile=?/dbs/proda_dg.ora

initprodb.ora

prodb.__db_cache_size=687865856
prodb.__java_pool_size=16777216
prodb.__large_pool_size=16777216
prodb.__oracle_base='/app/oracle/product'
prodb.__pga_aggregate_target=671088640
prodb.__sga_target=989855744
prodb.__shared_io_pool_size=0
prodb.__shared_pool_size=251658240
prodb.__streams_pool_size=0
*.audit_file_dest='/app/oracle/product/admin/proda/adump'
*.audit_trail='db'
*.compatible='11.1.0.0.0'
*.control_files='/app/oracle/product/oradata/proda/control01.ctl', '/app/oracle/product/oradata/proda/control02.ctl', '/app/oracle/product/oradata/proda/control03.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='proda'
*.db_recovery_file_dest='/app/oracle/product/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.diagnostic_dest='/app/oracle/product'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=prodbXDB)'
*.memory_target=1658847232
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
ifile=?/dbs/prodb_dg.ora

On omega1 create a password file

$ cd $ORACLE_HOME/dbs

$ ls -la

orapwd file=orapwproda password=oracle1 ignorecase=y

set linesize 121
col name format a30
col value format a50

SELECT name, value
FROM gv$parameter
WHERE name LIKE '%remote_login%';

On omega2 create a password file

$ cd $ORACLE_HOME/dbs

$ ls -la

orapwd file=orapwprodb password=oracle1 ignorecase=y

set linesize 121
col name format a30
col value format a50

SELECT name, value
FROM gv$parameter
WHERE name LIKE '%remote_login%';

omega1: listener.ora

Note: SDU needs to be a multiple of MTU
# /sbin/ifconfig

# listener.ora Network Configuration File: /app/oracle/product/10.1.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

DG_LISTENER =
(DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.#.1)(PORT = 1526))
      (SEND_BUF_SIZE=9375000)
      (RECV_BUF_SIZE=9375000))
    )
)

SID_LIST_DG_LISTENER =
(SID_LIST =
    (SID_DESC =
      (SDU = 32767)
      (GLOBAL_DBNAME = proda)
      (ORACLE_HOME = /app/oracle/product/11.1.0/db_1)
      (SID_NAME = proda)
    )
)

LISTENER =
(DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = omega1.psoug.org)(PORT = 1521))
    )
)

SID_LIST_LISTENER =
(SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /app/oracle/product/11.1.0/db_1)
      (PROGRAM = extproc)
   )
)

omega2: listener.ora

# listener.ora Network Configuration File: /app/oracle/product/10.1.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

DG_LISTENER =
(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.#.2)(PORT = 1526)
    (SEND_BUF_SIZE=9375000)
    (RECV_BUF_SIZE=9375000))
)

SID_LIST_LISTENER =
(SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /app/oracle/product/11.1.0/db_1)
      (PROGRAM = extproc)
    )
)

SID_LIST_DG_LISTENER =
(SID_LIST =
    (SID_DESC =
      (SDU=32767)
      (GLOBAL_DBNAME = prodb)
      (ORACLE_HOME = /app/oracle/product/11.1.0/db_1)
      (SID_NAME = prodb)
    )
)

LISTENER =
(DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = omega2.psoug.org)(PORT = 1521))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
    )
)

both servers: sqlnet.ora

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
DEFAULT_SDU_SIZE=32767

both servers: tnsnames.ora

# tnsnames.ora Network Configuration File: /app/oracle/product/11.1.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

DGLOGSHIPB =
(DESCRIPTION =
    (SDU = 32767)
    (SEND_BUF_SIZE=9375000)
    (RECV_BUF_SIZE=9375000)
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.#.2)(PORT = 1526))
    )
   (CONNECT_DATA =
      (SERVICE_NAME = prodb)
    )
)

DGLOGSHIPA =
(DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.#.1)(PORT = 1526))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = proda)
    )
)

PRODB =
(DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = omega2.psoug.org)(PORT =1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = prodb)
    )
)

PRODA =
(DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = omega1.psoug.org)(PORT =1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = proda)
    )
)

REPOS =
(DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.119)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = repos)
    )
)

Physical Data Guard

dg_proda.ora

remote_login_passwordfile='EXCLUSIVE'

# set max_processes equal to max_connections + 1 for local
log_archive_max_processes=4

log_archive_config='DG_CONFIG=(proda,prodb)'
db_unique_name='proda'
# db_name='proda'
# service_names='proda'

fal_client=proda
fal_server=prodb

standby_file_management='auto'

log_file_name_convert=' ',' '

log_archive_dest_1= 'LOCATION=/app/oracle/product/flash_recovery_area/arch
valid_for=(all_logfiles,all_roles) db_unique_name=proda'

log_archive_dest_2='service=dglogshipb valid_for=(online_logfile,primary_role) db_unique_name=prodb
NET_TIMEOUT=30 REOPEN=60 OPTIONAL MAX_CONNECTIONS=3'

log_archive_dest_state_1='ENABLE'
log_archive_dest_state_2='ENABLE'

# define archive log naming format
log_archive_format=%t+%s+%r.arc

dg_prodb.ora

remote_login_passwordfile='EXCLUSIVE'
# set max_processes equal to max_connections + 1 for local
log_archive_max_processes=4

log_archive_config='DG_CONFIG=(prodb,proda)'
db_unique_name='prodb'
# db_name='proda'
# service_names='proda'

fal_client=prodb
fal_server=proda

standby_file_management='auto'

log_file_name_convert=' ',' '

log_archive_dest_1= 'LOCATION=/app/oracle/product/flash_recovery_area/arch
valid_for=(all_logfiles,all_roles) db_unique_name=prodb'

log_archive_dest_2='service=dglogshipa valid_for=(online_logfile,primary_role) db_unique_name=proda
NET_TIMEOUT=30 REOPEN=60 OPTIONAL MAX_CONNECTIONS=3'

log_archive_dest_state_1='ENABLE'
log_archive_dest_state_2='ENABLE'

# define archive log naming format
log_archive_format=%t+%s+%r.arc

Prepare omega1 for backup

SQL> startup mount;
SQL> show parameter %archive%

SQL> SELECT inst_id, log_mode FROM v$database;

SQL> alter database archivelog;
SQL> alter database force logging;
SQL> alter database add supplemental log data;
SQL> alter database open;
SQL> archive log list;
SQL> alter system switch logfile;
SQL> archive log list;
SQL> SELECT dest_id, valid_type, valid_role, valid_now
FROM gv$archive_dest;

Create RMAN catalog and backup omega1

-- as oracle on bigdog
$ sqlplus / as sysdba

-- using OMF
CREATE TABLESPACE cat_tbs;

CREATE USER repoomega#
IDENTIFIED BY repoomega#
DEFAULT TABLESPACE cat_tbs
TEMPORARY TABLESPACE temp
QUOTA UNLIMITED ON cat_tbs;

GRANT create session, recovery_catalog_owner TO repoomega#;
GRANT execute ON dbms_stats TO repoomega#; -- for class only

Duplicate database for Standby on omega2

SQL> conn / as sysdba

SQL> startup nomount;

$ rman target sys/oracle1@proda auxiliary /

RMAN> duplicate target database for standby nofilenamecheck;

RMAN> exit;

On both servers create a password file

$ cd $ORACLE_HOME/dbs

$ ls -la

orapwd file=orapwprodb password=oracle1

set linesize 121
col name format a30
col value format a50

SELECT name, value
FROM gv$parameter
WHERE name LIKE '%remote_login%';

Step 3: Configure Standby Redo Logs

/* Note:
Minimally, the configuration should have one more standby redo log file group than the number of online redo log file groups on the primary database. However, the recommended number of standby redo log file groups is dependent on the number of threads on the primary database. Use the following equation to determine an appropriate number of standby redo log file groups:

(max. number of logfiles for each thread + 1) * max. number of threads

Using this equation reduces the likelihood that the primary instance's log writer (LGWR) process will be blocked because a standby redo log file cannot be allocated on the standby database. For example, if the primary database has 2 log files for each thread and 2 threads, then 6 standby redo log file groups are needed on the standby database.

By default Oracle has 1 thread with three log files so: (3+1) * 1 = 4
*/

Commands: Physical Standby

Complete applying all available redo in preparation for a failover. Use the FINISH clause only in the event of primary database failure.

ALTER DATABASE ACTIVATE PHYSICAL STANDBY DATABASE [FINISH];

ALTER DATABASE ACTIVATE PHYSICAL STANDBY DATABASE FINISH;

Create standby logfile

ALTER DATABASE ADD STANDBY LOGFILE '/app/oracle/product/dbs/stdbylog01a.log' SIZE 50M;

Switchover of a primary database to standby

ALTER DATABASE <PREPARE | COMMIT> TO SWITCHOVER TO PHYSICAL STANDBY [WITH SESSION SHUTDOWN];

SELECT SWITCHOVER_STATUS FROM V$DATABASE;

ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;

Convert a primary to a standby

ALTER DATABASE CONVERT TO <PHYSICAL | SNAPSHOT> STANDBY

ALTER DATABASE CONVERT TO PHYSICAL STANDBY;

Start redo apply as a foreground process

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE;

Stop redo apply

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Start redo apply as a background process

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT [FROM SESSION]

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

Cancel an apply time delay

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE NODELAY;

Start real-time apply as a foreground process

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE;

Start real-time apply as a background process

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE [DISCONNECT FROM SESSION];

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

Use from the standby database to manually register log files from a failed primary.

ALTER DATABASE REGISTER [OR REPLACE] [PHYSICAL] LOGFILE <file_path_and_name> [FOR <logminer_session_name>]

ALTER DATABASE REGISTER LOGFILE '/u01/dest1/stdbylog01a.log';

Change the protection mode

ALTER DATABASE SET STANDBY DATABASE TO <AVAILABILITY | PERFORMANCE | PROCTECTION>

ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE;

Switchover of a primary database to standby

ALTER DATABASE <PREPARE | COMMIT> TO SWITCHOVER TO ....

SELECT SWITCHOVER_STATUS FROM V$DATABASE;

ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;

Logical Data Guard

dg_proda.ora

remote_login_passwordfile='EXCLUSIVE'

# set max_processes equal to max_connections + 1 for local
log_archive_max_processes=4
log_archive_config='DG_CONFIG=(proda,prodb)'
db_unique_name='proda'
# db_name='proda'
# service_names='proda'

fal_client=proda
fal_server=prodb

standby_file_management='auto'
log_file_name_convert=' ',' '

log_archive_dest_1= 'LOCATION=/app/oracle/product/flash_recovery_area/arch
VALID_FOR=(online_logfiles,all_roles) DB_UNIQUE_NAME=proda'

log_archive_dest_2='service=dglogshipb VALID_FOR=(online_logfile,primary_role) DB_UNIQUE_NAME=prodb
NET_TIMEOUT=30 REOPEN=60 OPTIONAL MAX_CONNECTIONS=3'

log_archive_dest_3=
'LOCATION=/app/oracle/product/arch/proda
VALID_FOR=(standby_logfiles,standby_role) DB_UNIQUE_NAME=proda'

log_archive_dest_state_1='ENABLE'
log_archive_dest_state_2='ENABLE'
log_archive_dest_state_3='ENABLE'

# define archive log naming format
log_archive_format=%t+%s+%r.arc

dg_prodb.ora

remote_login_passwordfile='EXCLUSIVE'

# set max_processes equal to max_connections + 1 for local
log_archive_max_processes=4

log_archive_config='DG_CONFIG=(prodb,proda)'
db_unique_name='prodb'
# db_name='prodb'
# service_names='prodb'

fal_client=prodb
fal_server=proda

standby_file_management='auto'

log_file_name_convert=' ',' '

log_archive_dest_1=
'LOCATION=/app/oracle/product/flash_recovery_area/arch
VALID_FOR=(online_logfiles,all_roles) DB_UNIQUE_NAME=prodb'

log_archive_dest_2='service=dglogshipa
VALID_FOR=(online_logfile,primary_role) DB_UNIQUE_NAME=proda
NET_TIMEOUT=30 REOPEN=60 OPTIONAL MAX_CONNECTIONS=3'

log_archive_dest_3=
'LOCATION=/app/oracle/product/flash_recovery_area/arch
VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=prodb'

log_archive_dest_state_1='ENABLE'
log_archive_dest_state_2='ENABLE'
log_archive_dest_state_3='ENABLE'

# define archive log naming format
log_archive_format=%t+%s+%r.arc

Commands: Logical Standby

Build the Log Miner dictionary

exec DBMS_LOGSTDBY.BUILD;

Continue redo apply to a physical standby being converted to logical

ALTER DATABASE RECOVER TO LOGICAL STANDBY <database_name>;

ALTER DATABASE RECOVER TO LOGICAL STANDBY prodc;

Creating a logical standby database as part of a rolling upgrade of a physical standby

ALTER DATABASE RECOVER TO LOGICAL STANDBY KEEP IDENTITY;

Open the logical standby

ALTER DATABASE OPEN RESETLOGS;

Begin redo apply to the logical standby

ALTER DATABASE START LOGICAL STANDBY APPLY [IMMEDIATE] [NODELAY] [NEW PRIMARY <db_link_name>

ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;

Abort redo apply

ALTER DATABASE <ABORT | STOP> LOGICAL STANDBY APPLY;

ALTER DATABASE ABORT LOGICAL STANDBY APPLY;

Stop redo apply

ALTER DATABASE STOP LOGICAL STANDBY APPLY;

ALTER DATABASE REGISTER LOGICAL LOGFILE '/u01/dest1/stdbylog01a.log';

Cancel an apply time delay

ALTER DATABASE START LOGICAL STANDBY APPLY NODELAY;

Initiate terminal apply (apply remaining redo to bring the logical standby database to the same state as the primary database). When terminal apply is complete, the database completes the switchover from logical standby to primary database.

ALTER DATABASE ACTIVATE PHYSICAL STANDBY DATABASE FINISH APPLY;

Related SQL Statements

SQL Statements

SELECT inst_id, open_resetlogs, open_mode, protection_mode, protection_level
FROM v$database;

SELECT inst_id, remote_archive, activation#, switchover#, database_role
FROM v$database;

SELECT inst_id, archivelog_compression, switchover_status, dataguard_broker, guard_status
FROM v$database;

SELECT inst_id, supplemental_log_data_min, supplemental_log_data_pk, supplemental_log_data_ui, supplemental_log_data_fk, supplemental_log_data_all, force_logging
FROM v$database;

col name format a20
col db_unique_name format a20

SELECT inst_id, current_scn, db_unique_name, name, standby_became_primary_scn
FROM v$database;

col fs_failover_observer_host format a20

SELECT fs_failover_status, fs_failover_current_target, fs_failover_observer_present, fs_failover_observer_host
FROM v$database;

Start Active Data Guard

-- on the physical standby stop the recovery process
alter database recover managed standby database cancel;

-- open the database in read-only mode
alter database open read only;

-- restart managed recovery
alter database recover managed standby database disconnect;

SELECT inst_id, open_resetlogs, open_mode, protection_mode, protection_level
FROM v$database;

SELECT inst_id, remote_archive, activation#, switchover#, database_role
FROM v$database;

SELECT inst_id, archivelog_compression, switchover_status, dataguard_broker, guard_status
FROM v$database;

SELECT fs_failover_status, fs_failover_current_target, fs_failover_observer_present, fs_failover_observer_host
FROM v$database;

Other Related Topics

DBMS_DG

DBMS_DRS

DBMS_LOGSTDBY

-----