Hostname: primaryhost (Primary), stbyhost (Standby)
db_name: primdb
db_unique_name: primdb (Primary), stbydb (Standby)
Platform: Linux primaryhost.localdomain 2.6.18-238.el5 #1 SMP Sun Dec 19 14:22:44 EST 2010 x86_64 x86_64 x86_64 GNU/Linux
Linux stbyhost.localdomain 2.6.18-238.el5 #1 SMP Sun Dec 19 14:22:44 EST 2010 x86_64 x86_64 x86_64 GNU/Linux
PART 1: PRE-CHECK
1. A.) On Primary, ensure that archive dest for standby (log_archive_dest_state_2) on Primary is active.
SQL> show parameter log_archive_dest_state_2
If not, enable it:
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;
B.) From Primary, verify that log shipping to Standby is set up:
SYS@primdb SQL>show parameter log_archive_dest_2
On Primary, the value can be set using below. (Since this is a rebuild, NO NEED to set this as this is already configured.)NAME TYPE VALUE
------------------------------------ ----------- ----------------------------------------------------------------------------
log_archive_dest_2 string service=stbydb valid_for=(online_logfile,primary_role) db_unique_name=stbydb
SQL> alter system set log_archive_dest_2='SERVICE=stbydb VALID_FOR=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=stbydb' scope = both;
2. Determine if FORCE LOGGING is enabled on Primary. (Since this is a rebuild, FORCE LOGGING is assumed to be already enabled.)
SQL> SELECT force_logging FROM v$database;
If it is not enabled, enable FORCE LOGGING mode.
SQL> ALTER DATABASE FORCE LOGGING;
3. Ensure that log_archive_dest_state_1 and log_archive_dest_state_2 are enabled.
SYS@primdb SQL>show parameter log_archive_dest_state_
4. From Standby, verify that essential Standby Role Initialization Parameters are properly set:NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_1 string enable
log_archive_dest_state_2 string enable
SQL>show parameter [parameter_name]
5. You may also verify the primary database initialization parameters that control redo transport services while the database is in the primary role. These include:*.db_unique_name='stbydb'
*.db_name='primdb'
*.db_create_file_dest='+DATA'
*.db_recovery_file_dest='+FLASH'
*.db_recovery_file_dest_size=400G
*.db_create_online_log_dest_1='+DATA'
*.fal_client=stbydb
*.fal_server=primdb
*.log_archive_config='dg_config=(stbydb,primdb)' #Uses the DG_CONFIG attribute to list the DB_UNIQUE_NAME of the primary and standby databases.
*.standby_file_management='AUTO'
6. Test that you can connect remotely as "sys" to both databases, from both servers.DB_NAME #Specifies the database name. Must be primdb.
DB_UNIQUE_NAME #Specify a unique name for each database. Does not change even if DG roles change. Must be primdb.
CONTROL_FILES #Specifies the local path name for the control files on the primary database.
LOG_ARCHIVE_CONFIG #Uses the DG_CONFIG attribute to list the DB_UNIQUE_NAME of the primary and standby databases.
LOG_ARCHIVE_DEST_1 #Defaults to archive destination for the local archived redo log files.
LOG_ARCHIVE_DEST_2 #Valid only for the primary role, this destination transmits redo data to the remote physical standby destination stbydb.
REMOTE_LOGIN_PASSWORDFILE #Must be EXCLUSIVE or SHARED if a remote login password file is used (default = EXCLUSIVE)
LOG_ARCHIVE_DEST_STATE_n #Must be ENABLE (default)
sqlplus "sys@stbydb as sysdba"
sqlplus "sys@primdb as sysdba"
7. Shutdown Standby database (stbydb) (Hostname: stbyhost).
SQL> shutdown immediate;
8. On Standby, clean up old files. DO NOT touch spfile (+DATA/stbydb/spfiledfprimdb.ora) and password (/appl/oracle/product/11.2.0/dbs/orapwstbydb) files.
A.) Logon to Standby Database as grid user and set ASM environment variables:
sudo su - grid
. oraenv
ORACLE_SID = [+ASM] ? +ASM
B.) Verify the correct environment:
echo $ORACLE_SID
+ASM
echo $ORACLE_HOME
/appl/grid/11.2.0
D.) Delete the database files from ASM +DATA and +FLASH diskgroups using ASMCMD utility:
asmcmd rm -rf '+DATA/stbydb/DATAFILE/*'
asmcmd rm -rf '+DATA/stbydb/ONLINELOG/*'
asmcmd rm -rf '+DATA/stbydb/CONTROLFILE/*'
asmcmd rm -rf '+FLASH/stbydb/ONLINELOG/*'
asmcmd rm -rf '+FLASH/stbydb/ARCHIVELOG/*'
Tips:
RMAN will not overwrite database files on the Standby, they must be removed first.
ASM will not allow the deletion of files if the database is open (files are accessed).
e.g. ORA-15028: ASM file '+flash/stbydb/ARCHIVELOG/2012_07_23/thread_1_seq_105632.9337.789396403' not dropped; currently being accessed (DBD ERROR: OCIStmtExecute)
ASM will implicitly remove empty sub-directories, so you only need to delete the files.
9. On Standby, verify that storage has enough space to match the Primary. In this case, verify ASM:
ASMCMD> lsdg
10. Disable currently running RMAN backups on standby.State Type Rebal Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Voting_files Name
MOUNTED EXTERN N 512 4096 1048576 3660090 3659898 0 3659898 0 N DATA/
MOUNTED EXTERN N 512 4096 1048576 4182960 4155907 0 4155907 0 N FLASH/
Note: Ensure that PRE-CHECK is successful before proceeding to actual Rebuild.
**** END of PRE-CHECK ****
PART 2: REBUILD
1. Startup Standby database (stbydb) in NOMOUNT mode (Hostname: stbyhost).
SQL> startup nomount;
2. Login to Primary host (primaryhost). (In this particular CRQ, this is fired via shell script. See Option 2.)
OPTION 1: BEGIN MANUAL REPLICATION.
A.) Set the correct target (Primary) SID and ORACLE_HOME
[primaryhost.localdomain]:primdb >. oraenv
ORACLE_SID = [primdb] ? primdb
- OR -
export ORACLE_SID=primdb
export ORACLE_HOME=/appl/oracle/product/11.2.0
B.) Verify:
[primaryhost.localdomain]:primdb >echo $ORACLE_SID
primdb
[primaryhost.localdomain]:primdb >echo $ORACLE_HOME
/appl/oracle/product/11.2.0
C.) Login to target (Primary) and connect to auxiliary (Standby) databases via RMAN:
[primaryhost.localdomain]:primdb >rman target /
RMAN> connect auxiliary sys/password@stbydb
D.) Run below:
RUN {
allocate channel tgt1 type disk;
allocate channel tgt2 type disk;
allocate channel tgt3 type disk;
allocate channel tgt4 type disk;
allocate channel tgt5 type disk;
allocate auxiliary channel dup1 type disk;
allocate auxiliary channel dup2 type disk;
allocate auxiliary channel dup3 type disk;
DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE NOFILENAMECHECK;
}
Note: Replication will take a while, depending on the database size. If successful, proceed to step 3.
OPTION 2: KICK OFF REPLICATION VIA SHELL SCRIPT.
Note: Replication will take a while, depending on the database size. If successful, proceed to step 3.##############################################################################################################################################################################################################
### Begin Shell Script ###
##############################################################################################################################################################################################################
#!/bin/sh
#primdb_DG_copy.ksh
#primdb to stbydb rebuild script#
#exit 0
# ---------------------------------------------------------------------------
# Determine the user which is executing this script.
# ---------------------------------------------------------------------------
CUSER=`id |cut -d"(" -f2 | cut -d ")" -f1`
# ---------------------------------------------------------------------------
# Replace /db/oracle/product/ora81, below, with the Oracle home path.
# ---------------------------------------------------------------------------
ORACLE_HOME=/appl/oracle/product/11.2.0
export ORACLE_HOME
RMAN_LOG_FILE=/appl/oracle/product/11.2.0/logs/primdb_DG_copy_`date +%Y%m%d`.out
# -----------------------------------------------------------------
# Initialize the log file.
# -----------------------------------------------------------------
echo >> $RMAN_LOG_FILE
#chmod 666 $RMAN_LOG_FILE
# ---------------------------------------------------------------------------
# Log the start of this script.
# ---------------------------------------------------------------------------
echo Script $0 >> $RMAN_LOG_FILE
echo ==== started on `date` ==== >> $RMAN_LOG_FILE
echo >> $RMAN_LOG_FILE
# ---------------------------------------------------------------------------
# Replace ora81, below, with the Oracle SID of the target database.
# ---------------------------------------------------------------------------
ORACLE_SID=primdb
export ORACLE_SID
# ---------------------------------------------------------------------------
# Replace ora81, below, with the Oracle DBA user id (account).
# ---------------------------------------------------------------------------
ORACLE_USER=oracle
# ---------------------------------------------------------------------------
# Set the Oracle Recovery Manager name.
# ---------------------------------------------------------------------------
RMAN=$ORACLE_HOME/bin/rman
# ---------------------------------------------------------------------------
# Print out the value of the variables set by this script.
# ---------------------------------------------------------------------------
echo >> $RMAN_LOG_FILE
echo "RMAN: $RMAN" >> $RMAN_LOG_FILE
echo "ORACLE_SID: $ORACLE_SID" >> $RMAN_LOG_FILE
echo "ORACLE_USER: $ORACLE_USER" >> $RMAN_LOG_FILE
echo "ORACLE_HOME: $ORACLE_HOME" >> $RMAN_LOG_FILE
echo >> $RMAN_LOG_FILE
# ---------------------------------------------------------------------------
# Start REPLICATION
# ---------------------------------------------------------------------------
CMD_STR="
ORACLE_HOME=$ORACLE_HOME
export ORACLE_HOME
ORACLE_SID=$ORACLE_SID
export ORACLE_SID
$RMAN target / auxiliary sys/password@stbydb msglog $RMAN_LOG_FILE append << EOF
RUN {
allocate channel tgt1 type disk;
allocate channel tgt2 type disk;
allocate channel tgt3 type disk;
allocate channel tgt4 type disk;
allocate channel tgt5 type disk;
allocate auxiliary channel dup1 type disk;
allocate auxiliary channel dup2 type disk;
allocate auxiliary channel dup3 type disk;
DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE NOFILENAMECHECK;
}
EOF
"
# Initiate the command string
if [ "$CUSER" = "root" ]
then
su - $ORACLE_USER -c "$CMD_STR" >> $RMAN_LOG_FILE
RSTAT=$?
else
/bin/sh -c "$CMD_STR" >> $RMAN_LOG_FILE
RSTAT=$?
fi
# ---------------------------------------------------------------------------
# Log the completion of this script.
# ---------------------------------------------------------------------------
#----------------------------------------------------------------------------------------------------------------
HOSTNAMEX=`hostname`
if [ "$RSTAT" = "0" ]
then
LOGMSG="ended successfully"
cat $RMAN_LOG_FILE | mailx -s "PROD: primdb Rebuild successful on $HOSTNAMEX : $ORACLE_SID" dba1@localdomain,dba2@localdomain, dba3@localdomain, dba4@localdomain
else
LOGMSG="ended in error"
cat $RMAN_LOG_FILE | mailx -s "PROD: primdb Rebuild failed on $HOSTNAMEX : $ORACLE_SID" dba1@localdomain,dba2@localdomain, dba3@localdomain, dba4@localdomain
fi
#-------------------------------------------------------------------------------------------------------------------
echo >> $RMAN_LOG_FILE
echo Script $0 >> $RMAN_LOG_FILE
echo ==== $LOGMSG on `date` ==== >> $RMAN_LOG_FILE
echo >> $RMAN_LOG_FILE
exit $RSTAT
##############################################################################################################################################################################################################
### END Shell Script ###
##############################################################################################################################################################################################################
3. After the clone/standby creation process, start the apply process on standby server.
On the standby database, issue the following command to start Redo Apply:
SQL> alter database recover managed standby database disconnect from session;
- OR -
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
SQL>exit;
5. Find out if the switch was successfully sent and applied:
On Primary
-----------------------------------------
alter system switch logfile;
select dest_id, status, error
from v$archive_dest
where dest_id in (1,2);
select max(sequence#) from v$archived_logDEST_ID STATUS ERROR
---------- --------- -----------------------------------------------------------------
1 VALID
2 VALID
where archived = 'YES'
and RESETLOGS_TIME = (select max(RESETLOGS_TIME) from v$archived_log);
On StandbyMAX(SEQUENCE#)
--------------
7
------------------------------------------
select a.thread#, a.sequence#, a.applied
from v$archived_log a, v$database d
where a.activation# = d.activation#
and a.applied='YES'
/
select max(sequence#) from v$archived_log
where applied = 'YES'
and RESETLOGS_TIME = (select max(RESETLOGS_TIME) from v$archived_log);
6. ENABLE previously disabled running RMAN backups on standby.MAX(SEQUENCE#)
--------------
7
#### END of REBUILD ####