CODE
Oracle Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
PSOUG Home Code Snippets Oracle Reference Oracle Functions PSOUG Forum Oracle Blogs Bookmark and Share
 
 Search the Reference Library pages:  

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

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 TBD
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;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE;
Stop redo apply ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
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;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE NODELAY;
Start real-time apply as a foreground process ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE;
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;
Register standby logfile 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
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [330 users online]    © 2010 psoug.org