|
Prerequisites for these steps are
* you have a working tape backup in place, and that you can perform redirected restores.
* you have the same software installed on both servers, in my case it was Oracle EE version 10.2.0.4
By "redirected restores" I mean that you are able to restore database backups through rman to a host different from the one where the backup was taken.
I will not explain the details around details such as standby redo log files, log transportation methods etc; there are plenty of good sources for this to be found on the internet.
1. prepare the primary database parameters:
LOG_ARCHIVE_CONFIG='DG_CONFIG=(proddb01,stby01)'
LOG_ARCHIVE_DEST_1='LOCATION=/u01/oracle/admin/proddb01/archive/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=proddb01'
LOG_ARCHIVE_DEST_2='SERVICE=stby01 LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=stby01'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=DEFER
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
LOG_ARCHIVE_MAX_PROCESSES=30
STANDBY_FILE_MANAGEMENT=AUTO
# The following parameters will only be applicable when the primary is turned into a standby
# We put them there to maker life easier in case of such event
FAL_SERVER=stby01
FAL_CLIENT=proddb01
DB_FILE_NAME_CONVERT='stby01','proddb01'
LOG_FILE_NAME_CONVERT='/u04/oradata/stby01/archive/','/u01/oracle/admin/proddb01/archive/'
2. create standby redo log files on primary database:
First, check the number of redo log groups currently in use:
SQL> select group# ,members, status from v$log;
GROUP# MEMBERS STATUS
---------- ---------- ----------------
4 1 ACTIVE
5 1 ACTIVE
6 1 CURRENT
Oracle recommends to add standby redo log file groups with the same size as the largest member in any group. They also recommend that the number of standby redo log groups is the same as the number of redo log groups + 1.In my case, this means that I will add 4 standby redo log groups of 512M size each:
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 7 ('/u02/oradata/proddb01/stb_redo7.log') size 512M;
Database altered.
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 8 ('/u02/oradata/proddb01/stb_redo8.log') size 512M;
SQL> select group# ,members, status from v$log;
Database altered.
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 9 ('/u02/oradata/proddb01/stb_redo9.log') size 512M;
Database altered.
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 10 ('/u02/oradata/proddb01/stb_redo10.log') size 512M;
Database altered.
Verify that they were created:
SQL> select group#,thread#,sequence#,archived,status from v$standby_log;
GROUP# THREAD# SEQUENCE# ARC STATUS
---------- ---------- ---------- --- ----------
7 0 0 YES UNASSIGNED
8 0 0 YES UNASSIGNED
9 0 0 YES UNASSIGNED
10 0 0 YES UNASSIGNED
3. enable force logging:
ALTER DATABASE FORCE LOGGING;
4. restart primary database
STARTUP MOUNT
Assuming that you have a valid tape backup solution in place, backup your current control file "for standby" using rman:
rman target / catalog uid/pwd@rmancat
run {
allocate channel c1 type 'sbt_tape' maxpiecesize 4000M;
BACKUP CURRENT CONTROLFILE FOR STANDBY;
release channel c1;
5. Open the database, and switch the current logfile
This must be done so that the last log file is indeed older than your backup controfile that you created in the previous step:
SQL> ALTER DATABASE OPEN;
SQL 'ALTER SYSTEM ARCHIVE LOG CURRENT'; # so backup is consistent and recoverable
6. Finally, backup the latest archived redo log to tape:
rman target / catalog uid/pwd@rmancat
run {
allocate channel c1 type 'sbt_tape' maxpiecesize 4000M;
BACKUP ARCHIVELOG ALL NOT BACKED UP 1 TIMES;
release channel c1;
7. Set up SQL*net connection
...by adding an entry in the primary database's tnsnames.ora:
stby01 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = psu0erdb01)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = stby01)
)
)
8. Prepare the standby database parameters.
# The db_name must from now on match your primary database db_name
db_name='proddb01'
# The db_unique_name is the name to which you will typically refer to the standby database
db_unique_name='stby01'
log_archive_config='DG_CONFIG=(proddb01,stby01)'
log_archive_format=log%t_%s_%r.arc
log_archive_dest_1='LOCATION=/u04/oradata/ stby01/archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME= stby01'
log_archive_dest_2='SERVICE=proddb01 LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=proddb01'
log_archive_dest_state_1=enable
log_archive_dest_state_2=defer
remote_login_passwordfile='EXCLUSIVE'
standby_file_management=auto
fal_server=proddb01
fal_client=stby01
db_file_name_convert='proddb01','stby01'
log_file_name_convert='proddb01','stby01'
9. shutdown the to-become standby database, open it in nomount-mode:
shutdown immediate
startup nomount pfile='your_pfile.ora'
Your standby database is now ready to be cloned from your primary.
10. Create an rman script:
in file duplicate_for_standby.sh
export ORACLE_SID=stby01
export ORACLE_HOME=/u01/oracle/product/10204
export NLS_DATE_FORMAT='DD.MM.YYYY HH24:MI:SS'
export NLS_LANG=AMERICAN_NORWAY.WE8ISO8859P15
rman target sys/password@proddb01 catalog uid/pwd@rmancat auxiliary / cmdfile='duplicate_for_standby.cmd' log='duplicate_for_standby.log' trace='duplicate_for_standby.trc'
exit
In file duplicate_for_standby.cmd:
run {
ALLOCATE AUXILIARY CHANNEL c1 TYPE 'SBT_TAPE';
ALLOCATE AUXILIARY CHANNEL c2 TYPE 'SBT_TAPE';
ALLOCATE AUXILIARY CHANNEL c3 TYPE 'SBT_TAPE';
ALLOCATE AUXILIARY CHANNEL c4 TYPE 'SBT_TAPE';
ALLOCATE AUXILIARY CHANNEL c5 TYPE 'SBT_TAPE';
ALLOCATE AUXILIARY CHANNEL c6 TYPE 'SBT_TAPE';
duplicate target database for standby
dorecover;
}
exit
chmod 755 duplicate_for_standby.sh
11. Start the cloning script in the background:
nohup ./duplicate_for_standby.sh &
Follow the progress with
tail -f duplicate_for_standby.log
As the directive "dorecover" indicates, rman will create a clone from the primary database, recover it, and leave the recovered database in mounted state.
12. Enable log transport services on the primary database:
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE SCOPE=BOTH;
As a recap, this will instruct Oracle to ship logs to LOG_ARCHIVE_DEST_2, which is defined as a service that you have made known to your primary server through an entry in tnsnames.ora:
LOG_ARCHIVE_DEST_2='SERVICE=stby01 LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=stby01'
13. Check that your standby is applying logs.
You can use the script found here
Pay particularly attention that the following section looks like this:
Is the MRP process running?
PROCESS STATUS
--------- ------------
MRP0 WAIT_FOR_LOG
Crosscheck that with a look at the current archived log status:
set lines 200
col name format a60
alter session set nls_language='american';
alter session set nls_date_format='dd.mm.yyyy hh24:mi:ss';
SELECT SEQUENCE#,NAME,STANDBY_DEST,ARCHIVED,APPLIED,DELETED,STATUS,COMPLETION_TIME
FROM V$ARCHIVED_LOG
ORDER BY SEQUENCE# ASC
/
The primary and standby should give similar views of the archived log situation:
Standby:
SEQUENCE# NAME STA ARC APP DEL S COMPLETION_TIME
---------- -------------------------------------------------------- --- --- --- --- - -------------------
75072 /u04/oradata/stby01/archive/log1_75072_681750063.arc NO YES YES NO A 22.10.2014 12:26:07
75073 /u04/oradata/stby01/archive/log1_75073_681750063.arc NO YES YES NO A 22.10.2014 12:26:37
Primary. Notice how the archived logs are sent to two destinations at every log switch:
SEQUENCE# NAME STA ARC APP DEL S COMPLETION_TIME
---------- -------------------------------------------------------- --- --- --- --- - -------------------
75072 stby01 YES YES YES NO A 22.10.2014 12:26:06
75072 /u01/oracle/admin/proddb01/archive/1_75072_681750063.arc NO YES NO NO A 22.10.2014 12:26:08
75073 stby01 YES YES YES NO A 22.10.2014 12:26:37
75073 /u01/oracle/admin/proddb01/archive/1_75073_681750063.arc NO YES NO NO A 22.10.2014 12:26:37
From the standby database's alert log, it's clear that logs are being applied:
Wed Oct 22 13:26:13 2014
Media Recovery Log /u04/oradata/stby01/archive/log1_75074_681750063.arc
Wed Oct 22 13:26:33 2014
Media Recovery Waiting for thread 1 sequence 75075 (in transit)
Wed Oct 22 13:26:39 2014
Primary database is in MAXIMUM PERFORMANCE mode
RFS[30]: Successfully opened standby log 7: '/u02/oradata/stby01/stb_redo7.log'
Wed Oct 22 13:26:43 2014
Media Recovery Log /u04/oradata/stby01/archive/log1_75075_681750063.arc
Media Recovery Waiting for thread 1 sequence 75076 (in transit)
Documentation used
"Creating a Physical Standby Database"
"Creating a Standby Database with Recovery Manager"
No comments:
Post a Comment