|
Best Docs
|
| Doc ID / Link |
Doc Title |
Last Revision |
|
239100.1 |
Data Guard Protection Modes
Explained |
25-Feb-2007 |
|
Click
Here |
Data Guard and Real Application
Clusters |
10-Jul-2005 |
|
Click
Here |
Data Guard Concepts &
Administration: LOG_ARCHIVE_DEST_n Parameter Attributes |
10-Jul-2005 |
|
Click
Here |
Introduction to Data Guard |
10-Jul-2005 |
|
|
|
|
Acronyms
|
|
Acronym
|
Short For
|
Comment
|
| 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
|
-- if C shell
cat >> /etc/csh.login <<EOF
if ( \$USER == "oracle" ) then
limit maxproc 16384
limit descriptors 65536
umask 022
end if
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.111 |
omega1 |
omega1.psoug.org |
| 192.168.1.112 |
omega2 |
omega2.psoug.org |
| 10.0.1.1 |
dgomega1 |
dgomega1.psoug.org |
| 10.0.1.2 |
dgomega2 |
dgomega2.psoug.org |
| 192.168.10.11 |
omega1st |
omega1st.psoug.org |
| 192.168.10.12 |
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=306184192
proda.__java_pool_size=4194304
proda.__large_pool_size=4194304
proda.__shared_pool_size=125829120
proda.__streams_pool_size=0
*.audit_file_dest='/app/oracle/product/admin/proda/adump'
*.background_dump_dest='/app/oracle/product/admin/proda/bdump'
*.compatible='10.2.0.1.0'
*.control_files='/app/oracle/product/oradata/PRODA/controlfile/ -> o1_mf_353jw817_.ctl',
'/app/oracle/product/flash_recovery_area/PRODA/controlfile/ ->
o1_mf_353jw88l_.ctl'
*.core_dump_dest='/app/oracle/product/admin/proda/cdump'
*.db_block_size=8192
*.db_create_file_dest='/app/oracle/product/oradata'
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='proda'
*.db_recovery_file_dest='/app/oracle/product/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=prodaXDB)'
*.job_queue_processes=10
*.open_cursors=300
*.pga_aggregate_target=146800640
*.processes=150
*.sga_target=442499072
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/app/oracle/product/admin/proda/udump'
ifile=?/dbs/proda_dg.ora
|
initprodb.ora |
prodb.__db_cache_size=306184192
prodb.__java_pool_size=4194304
prodb.__large_pool_size=4194304
prodb.__shared_pool_size=125829120
prodb.__streams_pool_size=0
*.audit_file_dest='/app/oracle/product/admin/proda/adump'
*.background_dump_dest='/app/oracle/product/admin/proda/bdump'
*.compatible='10.2.0.1.0'
*.control_files='/app/oracle/product/oradata/PRODA/controlfile/ -?
o1_mf_353jw817_.ctl', '/app/oracle/product/flash_recovery_area/PRODA/controlfile/
- o1_mf_353jw88l_.ctl'
*.core_dump_dest='/app/oracle/product/admin/proda/cdump'
*.db_block_size=8192
*.db_create_file_dest='/app/oracle/product/oradata'
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='proda'
*.db_recovery_file_dest='/app/oracle/product/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=prodbXDB)'
*.job_queue_processes=10
*.open_cursors=300
*.pga_aggregate_target=146800640
*.processes=150
*.sga_target=442499072
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/app/oracle/product/admin/proda/udump'
ifile=?/dbs/prodb_dg.ora
|
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_archive_dest='LOCATION=USE_DB_RECOVERY_FILE_DEST'
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=prodb 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
# write local archive first
log_archive_local_first=TRUE |
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_archive_dest='LOCATION=USE_DB_RECOVERY_FILE_DEST'
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=proda 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
# write local archive first
log_archive_local_first=TRUE |
omega1: 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.1.1)(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/10.1.0/db_1)
(PROGRAM = extproc)
)
)
SID_LIST_DG_LISTENER =
(SID_LIST =
(SID_DESC =
(SDU=32767)
(GLOBAL_DBNAME = proda)
(ORACLE_HOME = /app/oracle/product/10.1.0/db_1)
(SID_NAME = proda)
)
) LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = omega1.psoug.org)(PORT = 1521))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
) |
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.1.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/10.1.0/db_1)
(PROGRAM = extproc)
)
)
SID_LIST_DG_LISTENER =
(SID_LIST =
(SID_DESC =
(SDU=32767)
(GLOBAL_DBNAME = prodb)
(ORACLE_HOME = /app/oracle/product/10.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
TCP_NODELAY=YES |
both servers: tnsnames.ora |
# tnsnames.ora Network Configuration File:
/app/oracle/product/10.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.1.2)(PORT = 1526))
)
(CONNECT_DATA =
(SERVICE_NAME = prodb)
)
)
DGLOGSHIPA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.1.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)
)
)
EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
) |
Prepare omega1 for backup |
SQL> startup mount;
SQL> show parameter %archive%
SQL> SELECT inst_id, log_mode FROM gv$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; |
| Duplicate database for Standby on omega2 |
SQL> startup nomount;
$ rman target sys/oracle1@proda auxiliary /
RMAN> duplicate target database for standby nofilenamecheck;
RMAN> exit; |
Create password file on omega2 |
$ 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 a Standby Redo Log |
/* 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
*/
|
| |
SQL Statements
|
SELECT inst_id, open_resetlogs, open_mode, protection_mode, protection_level
FROM gv$database;
SELECT inst_id, remote_archive, activation#, switchover#, database_role
FROM gv$database;
SELECT inst_id, archivelog_compression, switchover_status, dataguard_broker, guard_status
FROM gv$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 gv$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 gv$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 gv$database;
|