See how to quickly implement a Data Guard standby database in a day.
Learn how to switch over or fail over to your standby database in minutes.
Possibly offload your batch reporting workload to your standby database.
Replace your forebodings about crashes with "Don't worry ... be happy!" (by Bobby McFerrin; Simple Pleasures; EMI-Manhattan Records; 1988)
The presentation given at SETA Central 2005 and the Arkansas Banner Users Group 2006 entitled "Crashes Happen - Downtime Won't - With Data Guard" is in DataGuard.ppt (about 170 K) If you don't have Microsoft PowerPoint on your PC, you can download the presentation along with the PowerPoint viewer (for Windows XP) at DataGuard_with_Viewer.zip (about 3 Meg).
Note: This is a work in progress, which I will be adding to as we test our implementation (we went live with Data Guard on our production database on 6/15/07, with our standby on a remote site). However, there is enough information here for you to create and begin testing your own Data Guard implementation.Note 2: I am only covering implementing physical standby databases here. Logical standby databases can only be implemented in Banner for reporting purposes, not for failover or switchover purposes, since Banner uses datatypes that aren't supported by logical standby databases (such as LONG's). To see those tables with unsupported datatypes, run the following (thanks to Lee Johnston at the University of West Florida for this heads-up): select distinct owner,table_name from dba_logstdby_unsupported order by owner,table_name;If you do implement logical standby's for Banner, you should exclude those unsupported tables from the standby processing, such as using the following for each of those tables listed above (read the chapters on logical standby's for further information; thanks to April Sims at Southern Utah University for this information): exec dbms_logstdby.skip('DML','
Change PPRD2 to be the SID for your standby database,
Change PPRD to be the SID for your primary database,
Change myserver_pprd2 to be the connect string for your standby database,
Change myserver_pprd to be the connect string for your primary database,
Change the directories to match your own directory structures, such as for data, index, and redo log directories (but this also shows the SQL to list them), along with the Oracle home directory,
Change 123.45.67.89 to be your own host IP number for the primary site in the tnsnames.ora files,
Change 234.56.78.90 to be your own host IP number for the standby site in the tnsnames.ora files,
Possibly change the "file name convert" parameters and other SQL commands if you're doing doing anything more complicated than just putting the local standby database in directories similar to the primary database except for the SID level (such as "/data/oradata/
. oraenv
PPRD
or to the standby database:
. oraenv
PPRD2
before running any of the commands below (but after adding the standby database to /etc/oratab). The best way may be to have two unix sessions running, one with PPRD and one with PPRD2. To see which instance you are in (such as PPRD or PPRD2), and it's role (such as PRIMARY or PHYSICAL STANDBY) and status (such as OPEN or MOUNTED):
select name,instance_name,database_role,status,open_mode from v$instance,v$database;
Also note that you need to be connected as sysdba (sqlplus "/ as sysdba") to run most of these commands.Create an spfile (in the $ORACLE_HOME/dbs directory) for your primary database, if needed (see Notes at end), and bounce the database to have it take effect.
. oraenv
PPRD
sqlplus "/ as sysdba"
create spfile from pfile;
shutdown immediate
startup
exit
Note: BEFORE making any of the Data Guard changes to the primary's parameter file (spfile or init.ora pfile), prepare the primary database for standby database creation, including the FORCE LOGGING, ARCHIVELOG mode, MAXLOGFILES check, and creating the directory and datafile copy commands below, just in case you need to shut down and start up the primary database during this preparation. Turn on FORCE LOGGING on the primary database, if not already on (keep it on as long as the standby database is required) [3.1.1].
. oraenv
PPRD
sqlplus "/ as sysdba"
select force_logging from v$database;
alter database force logging;
Ensure the primary database is in ARCHIVELOG mode and automatic archiving is enabled [3.1.2] (the standby database will also need to be in ARCHIVELOG mode if it is switched to become a primary database [7.1.2.1]). For log_archive_format, you may want to include %D the for database ID, such as in arch_PPRD_%D_%S.arc, and %T for the thread (required for RAC - Real Application Clusters), such as in arch_PPRD_%D_%T_%S.arc.
archive log list
should show:
Database log mode Archive Mode
Automatic archival Enabled
If the database is not in ARCHIVELOG mode:
If you are using an spfile:
alter system set log_archive_dest = '/orcl/oradata/PPRD/archivelogs' scope = spfile;
alter system set log_archive_format = 'arch_PPRD_%S.arc' scope = spfile;
alter system set log_archive_start = true scope = spfile;
Else, if you are using a pfile:
edit the $ORACLE_HOME/dbs/initPPRD.ora file to contain:
log_archive_dest = /orcl/oradata/PPRD/archivelogs
log_archive_format = arch_PPRD_%S.arc
log_archive_start = true
shutdown immediate
startup mount
alter database archivelog;
alter database open;
archive log listEnsure that the MAXLOGFILES value for the primary database is at least one more than twice the actual number of redo log groups in the primary database, since we will be adding standby redo log groups to both the primary and standby databases [5.3.3.1.1-2].
select records_used "Current Groups",records_total "Max Groups",
decode(sign(records_total-(records_used*2+1)),-1,LPAD('YES',21),LPAD('NO',21))
"Recreate MAXLOGFILES?"
from v$controlfile_record_section where type = 'REDO LOG';
If Max Groups is less than (Current Groups * 2) + 1, then recreate the control file with a larger value for MAXLOGFILES.
alter database backup controlfile to trace;
select value from v$parameter where name = 'user_dump_dest';
!ls -ltr /pgms/oradata/PPRD/udump tail
!vi /pgms/oradata/PPRD/udump/pprd_ora_475358.trc
Edit the latest .trc (textual control) file and remove all lines before the
STARTUP NOMOUNT line, change the maxlogfiles value from, say, 6 to 10, comment
out (put # in front of) the RECOVER command, and, for Oracle 9i and above,
remove the lines after ALTER DATABASE OPEN, or, if present, ALTER TABLESPACE
TEMP ADD TEMPFILE, and change all comment lines to start with dashes. The vi
commands to do these (for 9i+) are usually:
:1,/STARTUP NOMOUNT/-1d
:/MAXLOGFILES/s/6/10/
:/RECOVER DATABASE/s/^/# /
:/ALTER TABLESPACE TEMP/+2,$d
:1,$s/^#/--/
:wq
shutdown immediate
@/pgms/oradata/PPRD/udump/pprd_ora_475358.trc
Set up the listener.ora file (in $ORACLE_HOME/network/admin) to specify the new PPRD2 standby at the standby site, which is this local site for a local standby [3.2.8]. When they are not on the same system, this means to put PPRD2 into the remote site's listener.ora file. (The SID_NAME is actually the INSTANCE_NAME parameter value specified in the standby's pfile or spfile, not the DB_NAME parameter value.)
(SID_DESC=
(SID_NAME=PPRD2)
(ORACLE_HOME=/pgms/oracle/product/v9203)
)
Set up the tnsnames.ora file (in $ORACLE_HOME/network/admin) to specify the connect string (myserver_pprd2) for the new standby at the primary site, which is this local site [3.2.10]. When they are not on the same system, this means a minimum is to put myserver_pprd2 into the primary site's tnsnames.ora file (but, go ahead and put both myserver_pprd on the standby site's tnsnames.ora file and myserver_pprd2 on the primary site's tnsnames.ora file for switchovers).
myserver_pprd2 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS =
(PROTOCOL = TCP)
(Host = 234.56.78.90) -- whatever host IP has PPRD2
(Port = 1521)
)
)
(CONNECT_DATA = (SID = PPRD2)
)
)
In the sqlnet.ora parameter file (in $ORACLE_HOME/network/admin) on the standby site, enable dead connection detection [3.2.9], in minutes (but, put this on both primary and standby sqlnet.ora files for switchovers [A.7]).
sqlnet.expire_time=2
Add the standby database to /etc/oratab on the standby site. Note: It can't be set to start or stop when dbstart or dbshut is run (requires special commands), so, set the flag to N. TBD: Will also have to set PPRD's startup to N and change the backup scripts and any shutdown scripts to cancel managed recovery on the standby, and change any startup scripts (and the backup scripts) to start the standby in managed recovery mode.
PPRD2:/pgms/oracle/product/v9203:N
Create the directories for the standby database (from unix user oracle; including directories that may be used after switching standby to primary role). Note that the "replace's" here change the directory name strings containing "/PPRD" for the primary database to "/PPRD2" for the standby database. Equivalent conversions are used throughout this presentation. If your directory name mapping from primary to standby is different, you will need to modify the code and parameters for all of these conversions to match your site's needs.
On the primary database:
select distinct 'mkdir -p '
replace(substr(file_name,1,instr(file_name,'/',-1)-1),'/PPRD','/PPRD2')
from dba_data_files
union
select distinct 'mkdir -p ' replace(value,'/PPRD/','/PPRD2/') from v$parameter
where (name in ('background_dump_dest','user_dump_dest','core_dump_dest',
'audit_file_dest') or name like 'log_archive_dest%')
and name not like 'log_archive_dest_state%' and value is not null;
Run the resulting unix commands on the standby's site, for example for this
local standby:
host
mkdir -p /data/oradata/PPRD2
mkdir -p /ndxs/oradata/PPRD2
mkdir -p /orcl/oradata/PPRD2/archivelogs
mkdir -p /pgms/oradata/PPRD2/audit
mkdir -p /pgms/oradata/PPRD2/bdump
mkdir -p /pgms/oradata/PPRD2/cdump
mkdir -p /pgms/oradata/PPRD2/udump
exit
Create commands to copy the primary database datafiles to the standby directories (use these commands in 3.2.2.2) (or just get a list of the datafiles if you are going to copy or transfer them manually) [3.2.1].
set pagesize 0 recsep off linesize 160 trimspool on feedback off
spool cp_standby.shl
select 'cp -p ' name ' ' replace(name,'/PPRD/','/PPRD2/') from v$datafile
order by substr(name,instr(name,'/',-1));
spool off
!cat cp_standby.shl sed '/^[^c][^p]/d' >cp_standbyx.shl; mv cp_standbyx.shl cp_standby.shl
Create the standby init.ora parameter file from the primary parameter file [3.2.4].
If you are using an spfile:
create pfile='$ORACLE_HOME/dbs/initPPRD2.ora' from spfile;
Else, if you are using a pfile:
!cp -p $ORACLE_HOME/dbs/initPPRD.ora $ORACLE_HOME/dbs/initPPRD2.ora
Modify the init.ora file for the standby database ($ORACLE_HOME/dbs/initPPRD2.ora), leaving all of the parameters the same as the primary database, including db_name (PPRD), compatible, log_archive_format, and log_archive_start (true), except for making the following changes and additions [3.2.6, 11.3] (note: if the pfile was created from an spfile, it will contain "*." at the beginning of the parameter names, which means the default SID). (Note: In order to get to the Maximum Availability protection mode (instead of staying at the default Maximum Performance protection mode) requires specifying "LGWR SYNC" in the log_archive_dest_2 parameter [5.7].)
control_files = (/orcl/oradata/PPRD2/ctrl_PPRD_01.ctl,
/pgms/oradata/PPRD2/ctrl_PPRD_02.ctl) # [3.2.6]
background_dump_dest = /pgms/oradata/PPRD2/bdump # [3.2.6]
core_dump_dest = /pgms/oradata/PPRD2/cdump # [3.2.6]
user_dump_dest = /pgms/oradata/PPRD2/udump # [3.2.6]
audit_file_dest = /pgms/oradata/PPRD2/audit # [3.2.6]
#log_archive_dest = /orcl/oradata/PPRD2/archivelogs
log_archive_dest_1 = 'LOCATION=/orcl/oradata/PPRD2/archivelogs MANDATORY' # [3.1.2,3.2.6,5.4.2,5.8.2.2,12]; for switchover
log_archive_dest_state_1 = ENABLE # [5.8.2.2]; for switchover
log_archive_dest_2 = 'SERVICE=myserver_pprd LGWR SYNC' # [5.7,5.8.2.2,12]; for switchover
log_archive_dest_state_2 = ENABLE # [5.8.2.2]; for switchover
standby_archive_dest = /orcl/oradata/PPRD2/archivelogs # [3.2.6,5.4.2,5.8.2.2]
standby_file_management = AUTO # [3.2.6,5.8.2.2]; or MANUAL for raw devices [8.4.1.2]
remote_archive_enable = TRUE # [3.2.6,5.3.2.1,5.8.2.2]; TRUE or RECEIVE, but must change RECEIVE to SEND on switchover
instance_name = PPRD2 # [3.2.6]
lock_name_space = PPRD2 # [3.2.6]; use when primary and standby on same system; same as instance_name
fal_server = myserver_pprd # [5.8.2.2,6.4.4]
fal_client = myserver_pprd2 # [5.8.2.2,6.4.4]
db_file_name_convert = ('/PPRD/','/PPRD2/') # [3.2.6,5.8.2.2]
log_file_name_convert = ('/PPRD/','/PPRD2/') # [3.2.6,5.8.2.2]
# log_archive_trace = 15 # [5.8.2.4.5,6.7.3]; to see progression of archiving of redo logs to the standby site
audit_trail = false # do not have auditing turned on in a standby database - can't audit to read-only database!
Shut down the primary database [3.2.2.1] (using shutdown normal, or, if that hangs, do shutdown immediate, startup, shutdown normal; see note at end if you want your standby to have its database name to be the same as its instance name or some other value, instead of being the same as the primary database name).
shutdown normal
Copy the primary database datafiles to the standby directory if local (see 3.2.1) or to a temporary staging directory if remote or on a separate local server (which will be transferred to the standby site below; log files and control files for the primary will not be copied to the standby site) [3.2.2.2].
!sh cp_standby.shl
Create the standby control file from the primary database (copying it to the standby directory if local, or to the temporary staging directory if remote or on a separate local server) [3.2.3].
startup
select replace(value,'/PPRD/','/PPRD2/') from v$parameter where name = 'control_files';
alter database create standby controlfile as '/orcl/oradata/PPRD2/ctrl_PPRD_01.ctl';
exit
Then, copy the resulting control file to the other standby control file(s) listed in the standby's parameter file (initPPRD2.ora), if any:
cp -p /orcl/oradata/PPRD2/ctrl_PPRD_01.ctl /pgms/oradata/PPRD2/ctrl_PPRD_02.ctl
Transfer the primary database datafiles and the standby control file from the temporary staging directory to the standby site if on a remote system or on a separate local server (else already done by 3.2.2.2) [3.2.5]. Transfer the standby init.ora file from the $ORACLE_HOME/dbs directory to the standby site if on a remote system or on a separate local server (else already done by 3.2.4) [3.2.5]. Reload the primary database listener with the modified listener.ora file on the primary site [3.2.8].
lsnrctl reload
Reload the standby database listener with the modified listener.ora file on the standby site if on a remote system or on a separate local server (else already done above) [3.2.8].
lsnrctl reload
Create the standby's password file, if needed.
rm $ORACLE_HOME/dbs/orapwPPRD2
orapwd file=$ORACLE_HOME/dbs/orapwPPRD2 password=
Connect as sysdba on the standby database, bring up in nomount mode, create the spfile if needed, mount the standby database, and change to managed recovery mode (note: "alter database force logging" already set from primary copy) [3.2.11-3.2.13, 6.2.1, 6.2.2.1, 8.1.1, 8.2.3.1].
. oraenv
PPRD2
sqlplus "/ as sysdba"
create spfile from pfile;
startup nomount
alter database mount standby database;
alter database recover managed standby database disconnect from session;
exit
If you are using a pfile instead of an spfile for the primary database, modify the init.ora file (initPPRD.ora) for the primary database (or, for spfile, the mods will be done in the next step).
#log_archive_dest = /orcl/oradata/PPRD/archivelogs
log_archive_dest_1 = 'LOCATION=/orcl/oradata/PPRD/archivelogs MANDATORY' # [3.1.2,5.8.2.1,12]
log_archive_dest_state_1 = ENABLE # [5.8.2.1]
log_archive_dest_2 = 'SERVICE=myserver_pprd2 LGWR SYNC' # [3.2.14,5.4.1,5.7,5.8.2.1,12]
log_archive_dest_state_2 = ENABLE # [3.2.14,5.4.1,5.8.2.1]
standby_archive_dest = /orcl/oradata/PPRD/archivelogs # [5.8.2.1]; for switchover
standby_file_management = AUTO # [5.8.2.1]; for switchover; or MANUAL for raw devices [8.4.1.2]
remote_archive_enable = TRUE # [5.3.2.1,5.8.2.1]; TRUE or SEND, but must change SEND to RECEIVE on switchover
instance_name = PPRD # [3.2.6]
lock_name_space = PPRD # [3.2.6]; use when primary and standby on same system; same as instance_name
fal_server = myserver_pprd2 # [5.8.2.1,6.4.4]; for switchover
fal_client = myserver_pprd # [5.8.2.1,6.4.4]; for switchover
db_file_name_convert = ('/PPRD2/','/PPRD/') # [5.8.2.1]; for switchover
log_file_name_convert = ('/PPRD2/','/PPRD/') # [5.8.2.1]; for switchover
# log_archive_trace = 15 # [5.8.2.4.5,6.7.3]; to see progression of archiving of redo logs to the standby site
Start up the primary database with the modified parameters [3.2.2.3].
. oraenv
PPRD
If you are using an spfile (the first alter system command removes log_archive_dest
from the spfile; for a description of the others, see the pfile initPPRD.ora above):
sqlplus "/ as sysdba"
shutdown normal
startup nomount
alter system reset log_archive_dest scope=spfile sid='*';
alter system set log_archive_dest_1 = 'LOCATION=/orcl/oradata/PPRD/archivelogs MANDATORY' scope=spfile;
alter system set log_archive_dest_state_1 = ENABLE scope=spfile;
alter system set log_archive_dest_2 = 'SERVICE=myserver_pprd2 LGWR SYNC' scope=spfile;
alter system set log_archive_dest_state_2 = ENABLE scope=spfile;
alter system set standby_archive_dest = '/orcl/oradata/PPRD/archivelogs' scope=spfile;
alter system set standby_file_management = AUTO scope=spfile;
alter system set remote_archive_enable = TRUE scope=spfile;
alter system set instance_name = PPRD scope=spfile;
alter system set lock_name_space = PPRD scope=spfile;
alter system set fal_server = myserver_pprd2 scope=spfile;
alter system set fal_client = myserver_pprd scope=spfile;
alter system set db_file_name_convert = '/PPRD2/','/PPRD/' scope=spfile;
alter system set log_file_name_convert = '/PPRD2/','/PPRD/' scope=spfile;
# alter system set log_archive_trace = 15 scope=spfile;
shutdown
startup
Else, if you are using a pfile:
sqlplus "/ as sysdba"
shutdown normal
startup
Start archiving to the standby database by issuing a log switch on the primary database [3.2.14].
alter system switch logfile;
Create standby logfile groups on the standby database, starting with the next group number and adding one more group than the current number of log groups on the primary database [5.3.3.3]. Standby redo logs are an exact replica of the primary database’s online redo logs (instead of waiting for a complete archive log) and are required for maximum availability protection mode and when using LGWR process with maximum performance mode [5.7].
On the primary database:
column o1 noprint
column o2 noprint
column maxgroup new_value maxgroup
select max(group#) maxgroup from v$logfile;
select group# o1,1 o2,'alter database add standby logfile group '
to_char(group#+&maxgroup) ' ('
from v$log
union all
select group#,2,' ''' replace(replace(member,group#,group#+&maxgroup),
'PPRD/','PPRD2/stby_') ''','
from v$logfile lf1 where member not in (select max(member) from v$logfile lf2
where lf1.group# = lf2.group#)
union all
select l1.group#,3,' ''' replace(replace(member,l1.group#,l1.group#+&maxgroup),
'PPRD/','PPRD2/stby_') ''') size ' bytes / 1024 'K;'
from v$logfile lf1,v$log l1 where lf1.group# = l1.group#
and member = (select max(member) from v$logfile lf2 where lf1.group# = lf2.group#)
union all
select group#+1,1,'alter database add standby logfile group '
to_char(group#+1+&maxgroup) ' (' from v$log
where group# = &maxgroup
union all
select group#+1,2,' ''' replace(replace(member,group#,group#+1+&maxgroup),
'PPRD/','PPRD2/stby_') ''',' from v$logfile lf1 where group# = &maxgroup
and member not in (select max(member) from v$logfile lf2 where lf1.group# = lf2.group#)
union all
select l1.group#+1,3,' ''' replace(replace(member,l1.group#,l1.group#+1+&maxgroup),
'PPRD/','PPRD2/stby_') ''') size ' bytes / 1024 'K;'
from v$logfile lf1,v$log l1 where lf1.group# = l1.group#
and l1.group# = &maxgroup
and member = (select max(member) from v$logfile lf2 where lf1.group# = lf2.group#)
order by 1,2,3;
exit
On the standby database, run the resulting sql from the above. (Note: This can't be done
until after archiving has been started by issuing "alter system switch logfile;" on the
primary.):
. oraenv
PPRD2
sqlplus "/ as sysdba"
alter database recover managed standby database cancel;
alter database open read only;
alter database add standby logfile group 4 (
'/orcl/oradata/PPRD2/stby_log_PPRD_4A.rdo',
'/orcl/oradata/PPRD2/stby_log_PPRD_4B.rdo') size 4096K;
alter database add standby logfile group 5 (
'/orcl/oradata/PPRD2/stby_log_PPRD_5A.rdo',
'/orcl/oradata/PPRD2/stby_log_PPRD_5B.rdo') size 4096K;
alter database add standby logfile group 6 (
'/orcl/oradata/PPRD2/stby_log_PPRD_6A.rdo',
'/orcl/oradata/PPRD2/stby_log_PPRD_6B.rdo') size 4096K;
alter database add standby logfile group 7 (
'/orcl/oradata/PPRD2/stby_log_PPRD_7A.rdo',
'/orcl/oradata/PPRD2/stby_log_PPRD_7B.rdo') size 4096K;
column member format a55
select vs.group#,vs.bytes,vl.member from v$standby_log vs,v$logfile vl
where vs.group# = vl.group# order by vs.group#,vl.member;
Then, add a tempfile to the standby database for switchover or read-only access. (Note: This can't be done until after archiving has been started by issuing "alter system switch logfile;" on the primary.)
alter tablespace temp add tempfile '/data/oradata/PPRD2/temp_PPRD_01.dbf'
size 400064K reuse;
alter database recover managed standby database disconnect from session;
select * from v$tempfile;
exit
Create standby logfile groups on the primary database for switchovers, adding one more group than the current number of log groups on the primary database [5.3.3.3].
. oraenv
PPRD
sqlplus "/ as sysdba"
column o1 noprint
column o2 noprint
column maxgroup new_value maxgroup
select max(group#) maxgroup from v$logfile;
select group# o1,1 o2,'alter database add standby logfile group '
to_char(group#+&maxgroup) ' ('
from v$log
union all
select group#,2,' ''' replace(replace(member,group#,group#+&maxgroup),
'PPRD/','PPRD/stby_') ''','
from v$logfile lf1 where member not in (select max(member) from v$logfile lf2
where lf1.group# = lf2.group#)
union all
select l1.group#,3,' ''' replace(replace(member,l1.group#,l1.group#+&maxgroup),
'PPRD/','PPRD/stby_') ''') size ' bytes / 1024 'K;'
from v$logfile lf1,v$log l1 where lf1.group# = l1.group#
and member = (select max(member) from v$logfile lf2 where lf1.group# = lf2.group#)
union all
select group#+1,1,'alter database add standby logfile group '
to_char(group#+1+&maxgroup) ' (' from v$log
where group# = &maxgroup
union all
select group#+1,2,' ''' replace(replace(member,group#,group#+1+&maxgroup),
'PPRD/','PPRD/stby_') ''',' from v$logfile lf1 where group# = &maxgroup
and member not in (select max(member) from v$logfile lf2 where lf1.group# = lf2.group#)
union all
select l1.group#+1,3,' ''' replace(replace(member,l1.group#,l1.group#+1+&maxgroup),
'PPRD/','PPRD/stby_') ''') size ' bytes / 1024 'K;'
from v$logfile lf1,v$log l1 where lf1.group# = l1.group#
and l1.group# = &maxgroup
and member = (select max(member) from v$logfile lf2 where lf1.group# = lf2.group#)
order by 1,2,3;
Run the resulting sql from the above, for example:
alter database add standby logfile group 4 (
'/orcl/oradata/PPRD/stby_log_PPRD_4A.rdo',
'/orcl/oradata/PPRD/stby_log_PPRD_4B.rdo') size 4096K;
alter database add standby logfile group 5 (
'/orcl/oradata/PPRD/stby_log_PPRD_5A.rdo',
'/orcl/oradata/PPRD/stby_log_PPRD_5B.rdo') size 4096K;
alter database add standby logfile group 6 (
'/orcl/oradata/PPRD/stby_log_PPRD_6A.rdo',
'/orcl/oradata/PPRD/stby_log_PPRD_6B.rdo') size 4096K;
alter database add standby logfile group 7 (
'/orcl/oradata/PPRD/stby_log_PPRD_7A.rdo',
'/orcl/oradata/PPRD/stby_log_PPRD_7B.rdo') size 4096K;
column member format a55
select vs.group#,vs.bytes,vl.member from v$standby_log vs,v$logfile vl
where vs.group# = vl.group# order by vs.group#,vl.member;
Issue some log switches on the primary database, and confirm that the log files were received on the standby archive destination and processed by the standby database. (Note: If the archiver process is being used to write to the standby database when in Maximum Performance mode (the initial default), you may need to issue as many "alter system switch logfile;" commands as there are redo log groups before you see them processed in the alert log.)
alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';
select sysdate from dual;
alter system switch logfile;
!ls -ltr /orcl/oradata/PPRD2/archivelogs
You should see the archive logs that were received by the standby.
!tail /pgms/oradata/PPRD2/bdump/alert_PPRD2.log
You should see a message in the standby alert log such as:
Media Recovery Log /orcl/oradata/PPRD2/archivelogs/arch_PPRD_0000002866.arc
On the standby (8.5.3.1):
select process,status,thread#,sequence#,block#,blocks from v$managed_standby;
Another way to check to see that the archived redo logs are going to the standby:
On the standby, see what logs are already there:
alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';
select sequence#, first_time, next_time, archived, applied
from v$archived_log order by sequence#;
On the primary, force a log switch:
alter system archive log current;
On the standby, see the new logs and if they have been applied (may need to wait
a few seconds before they are applied):
select sequence#, first_time, next_time, archived, applied
from v$archived_log order by sequence#;
On the primary, you can also see the new logs and if the standby has acknowledged
that they've been applied (again, may need to wait a few seconds before they
are acknowledged):
column name format a15
select name,sequence#, first_time, next_time, archived, applied
from v$archived_log where name not like '%/%' order by sequence#;
Switch to the desired "maximum availability" protection mode on the primary database. (The default is "maximum performance"; the redo log transmission (in log_archive_dest_2) must have been set to use the log writer process in synchronous mode (LGWR SYNC) before changing the protection mode to maximum availability; if standby database becomes unavailable, maximum availability mode is temporarily switched to maximum performance mode.) [1.4, 5.7, 13.14].
. oraenv
PPRD
sqlplus "/ as sysdba"
select value from v$parameter where name = 'log_archive_dest_2';
shutdown normal
(If that hangs, then do: shutdown immediate, startup, shutdown normal)
startup mount
alter database set standby database to maximize availability;
alter database open;
select protection_mode from v$database;
Note: If you don't have logging to the standby site set to "LGWR SYNC" (in the log_archive_dest_2 parameter in the pfile or spfile) when you try to change the protection mode to maximum availability, you will get:
ORA-03113: end-of-file on communication channel
In that case, you will have to switch the protection mode back to get the primary to open in order to remedy the problem:
exit
sqlplus "/ as sysdba"
startup mount
alter database set standby database to maximize performance;
alter database open;
Try some edits on the primary, archive the current log with the edits, open the standby as read-only, and check to see that the changes made it to the standby.
On the primary:
update spriden set spriden_first_name = 'James'
where spriden_pidm = 1234 and spriden_change_ind is null;
commit;
alter system switch logfile;
On the standby (may take a few seconds for the change to be applied):
select name,instance_name,database_role,status,open_mode from v$instance,v$database;
alter database recover managed standby database cancel;
alter database open read only;
select name,instance_name,database_role,status,open_mode from v$instance,v$database;
set pagesize 60
select * from spriden where spriden_pidm = 1234 and spriden_change_ind is null;
alter database recover managed standby database disconnect from session;
select name,instance_name,database_role,status,open_mode from v$instance,v$database;
On the primary:
update spriden set spriden_first_name = 'Stephen' where spriden_pidm = 1234
and spriden_change_ind is null;
commit;
alter system switch logfile;
On the standby:
alter database recover managed standby database cancel;
alter database open read only;
set pagesize 60
select * from spriden where spriden_pidm = 1234 and spriden_change_ind is null;
alter database recover managed standby database disconnect from session;
A standby database can be used to run reports on and do other intensive queries on if you need to offload that workload from your primary database. Like the above edit tests, you will switch the standby database to read-only mode, then run your reports or queries on the standby, then switch the standby back to managed recovery mode. Changes in the primary database during that time are sent to the standby's archive log files, but aren't applied to the standby database until the mode is switched back to managed recovery mode. To run an SQL report (such as myreport.sql) on the standby database (the connect's are for when you need to run the report as a specific user, such as myuserid):
sqlplus "/ as sysdba"
alter database recover managed standby database cancel;
alter database open read only;
connect myuserid/mypassword
@myreport.sql
connect / as sysdba
alter database recover managed standby database disconnect from session;
If the primary database crashes while the standby is in read-only mode, you can still switch the standby back to managed recovery mode to let the queued-up changes be applied to the standby. After switching back to managed recovery mode, you can keep checking to see what queued-up changes have yet to be applied (which will return no rows once they have all been applied, which may take several minutes if there are lots of changes queued up):
alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';
select sequence#, first_change#, next_change#, first_time, next_time
from v$archived_log where applied = 'NO' order by sequence#;
Note: If you try to update data in a standby database open for read-only, you will get:
ORA-01552: cannot use system rollback segment for non-system tablespace
The primary database can be shut down and started up as usual without any detrimental effects on the standby. However, you might want to archive the current redo log file so that the latest changes are sent to the standby database before shutting the primary database down (such as for backup).
alter system switch logfile;
NOTE: To avoid creating archive gaps [B.3.1.2] (however, archive gaps should be resolved by the RFS process anyway, so they shouldn't be a problem; be aware that trying to start up a primary database while archiving to the standby is set to "defer" causes an "ORA-03113: end-of-file on communication channel" error):
Start the standby databases and listeners before starting the primary database.
Shut down the primary database (or defer sending transactions to the standby)
before shutting down the standby database.
To shut down a standby database [8.1.2]:
If standby is currently in read-only access, terminate any active user sessions (run
the resulting SQL and commands generated below) and switch back to managed recovery:
select open_mode from v$database;
select 'alter system kill session ''' sid ',' serial# ''';'
from v$session where username is not null
and sid not in (select distinct sid from v$mystat);
select '!kill -9 ' vp.spid
from v$session vs,v$process vp where vs.paddr = vp.addr
and vs.username is not null
and vs.sid not in (select distinct sid from v$mystat);
alter database recover managed standby database disconnect from session;
Then, cancel managed recovery (but see Note below):
select * from v$standby_log;
Should show all standby logs with a status of UNASSIGNED if archiving is
deferred (otherwise, the primary database is still sending transactions
to the standby - but this isn't always the case).
alter database recover managed standby database cancel;
shutdown immediate
Note: Before canceling managed recovery on the standby, it is suggested that you defer
archiving to the standby by doing the following on the primary. However, trying to
start up a primary database while archiving to the standby is set to "defer" causes an
"ORA-03113: end-of-file on communication channel" error (see further down below for
how to start up the primary if you encounter this problem). So, until this startup
problem is addressed and resolved by Oracle, don't defer archiving.
alter system set log_archive_dest_state_2 = DEFER;
alter system switch logfile;
To start up a standby database in the usual managed recovery mode [8.1.1]:
On the standby (note: on startup, you will get the message: "ORA-01666: controlfile
is for a standby database"):
startup nomount
alter database mount standby database;
alter database recover managed standby database disconnect from session;
On the primary, re-enable archiving to the standby database, if needed:
alter system set log_archive_dest_state_2 = ENABLE;
alter system switch logfile;
To start up a standby database for read-only access when it is currently down [8.2.2]:
startup nomount
alter database mount standby database;
alter database open read only;
To switch a standby database from managed recovery mode to read-only access (note: be sure to switch back to managed recovery mode after you have finished running whatever queries you were doing in read-only mode) [8.2.2]:
alter database recover managed standby database cancel;
alter database open read only;
To switch a standby database from read-only access to managed recovery mode [8.2.2]:
Terminate any active user sessions on the standby database (except your own):
select 'alter system kill session ''' sid ',' serial# ''';'
from v$session where username is not null
and sid not in (select distinct sid from v$mystat);
select '!kill -9 ' vp.spid
from v$session vs,v$process vp where vs.paddr = vp.addr
and vs.username is not null
and vs.sid not in (select distinct sid from v$mystat);
alter database recover managed standby database disconnect from session;
To see if a standby database is in managed recovery or read-only mode, or if managed recovery has been cancelled, or if this database is a primary database instead:
select decode(database_role,'PRIMARY','PRIMARY',decode(open_mode,
'MOUNTED',decode((select count(*) from v$managed_standby
where process like 'MRP%'),0,'CANCELLED','MANAGED RECOVERY'),
'READ ONLY','READ ONLY','UNKNOWN')) from v$database;
To initiate a switchover in which the primary database becomes the standby database and the standby database becomes the primary database (this is not for when the primary database has crashed - see failover below for that; you probably will want have two telnet sessions going to do this switchover - one pointing to the primary and one pointing to the standby):
End all activities on the primary and standby database [7.1.2.1], probably just
doing on the primary database (PPRD):
select 'alter system kill session ''' sid ',' serial# ''';'
from v$session where username is not null
and sid not in (select distinct sid from v$mystat);
select '!kill -9 ' vp.spid
from v$session vs,v$process vp where vs.paddr = vp.addr
and vs.username is not null
and vs.sid not in (select distinct sid from v$mystat);
Check primary database (PPRD) switchover status (on primary; looking for
"TO STANDBY"; but mine said "SESSIONS ACTIVE" (my sysdba session)) [7.2.1.1]:
select database_role,switchover_status from v$database;
Initiate switchover operation on the primary database (PPRD) [7.2.1.2]:
alter database commit to switchover to physical standby;
Shut down and restart the former primary instance (PPRD) as the new standby [7.2.1.3]:
shutdown immediate
if remote_archive_enable is set to SEND in the primary's init.ora file
(initPPRD.ora), change it to RECEIVE:
If you are using an spfile (do this after the startup below):
alter system set remote_archive_enable = RECEIVE scope = both;
Else, if you are using a pfile:
remote_archive_enable = RECEIVE
if audit_trail is set to anything but FALSE in the primary's init.ora file
(initPPRD.ora), change it to FALSE:
If you are using an spfile (do this after the startup below):
alter system set audit_trail = FALSE scope = both;
Else, if you are using a pfile:
audit_trail = FALSE
if log_archive_dest_state_2 is set to ENABLE in the primary's init.ora file
(initPPRD.ora), change it to DEFER (on pre-10g databases; added 8/23/07):
If you are using an spfile (do this after the startup below):
alter system set log_archive_dest_state_2 = DEFER scope = both;
Else, if you are using a pfile:
log_archive_dest_state_2 = DEFER
startup nomount
alter database mount standby database;
Check standby database (PPRD2) switchover status (on standby; looking for
"SWITCHOVER PENDING"; but mine said "TO PRIMARY") [7.2.1.4]:
select database_role,switchover_status from v$database;
Change the former standby instance (PPRD2) to the primary role, shut down, and
restart [7.2.1.5-6]:
alter database commit to switchover to primary;
shutdown normal
if remote_archive_enable is set to RECEIVE in the standby's init.ora file
(initPPRD2.ora), change it to SEND:
If you are using an spfile (do this after the startup below):
alter system set remote_archive_enable = SEND scope = both;
Else, if you are using a pfile:
remote_archive_enable = SEND
if audit_trail needs to be turned on for this "new" primary, change it
to TRUE (or whatever setting is needed) in the standby's init.ora file
(initPPRD2.ora):
If you are using an spfile (do this after the startup below):
alter system set audit_trail = TRUE scope = both;
Else, if you are using a pfile:
audit_trail = TRUE
if log_archive_dest_state_2 is set to DEFER in the standby's init.ora file
(initPPRD2.ora), change it to ENABLE (on pre-10g databases; added 8/23/07):
If you are using an spfile (do this after the startup below):
alter system set log_archive_dest_state_2 = ENABLE scope = both;
Else, if you are using a pfile:
log_archive_dest_state_2 = ENABLE
startup
select name,instance_name,database_role,status,open_mode from v$instance,v$database;
Start managed recovery on the new standby (on old primary) (PPRD) [7.2.1.7]:
alter database recover managed standby database disconnect from session;
select name,instance_name,database_role,status,open_mode from v$instance,v$database;
Write the current archive log on the new primary (on old standby) (PPRD2) [7.2.1.8]:
alter system archive log current;
Change tnsnames.ora entry on all application hosts (T:\APPS\ban6\orawin\NET80\ADMIN)
to point to the new primary (PPRD2) (or, just tell the users to use myserver_pprd2 as
the database to log into - make sure myserver_pprd2 is defined in the tnsnames.ora
file on the network; but, see "Multiple tnsnames addresses" for another option) [10.1.1]:
myserver_pprd =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS =
(PROTOCOL = TCP)
(Host = 234.56.78.90) -- whatever host IP has PPRD2
(Port = 1521)
)
)
(CONNECT_DATA = (SID = PPRD2) -- the new primary (old standby)
)
)
myserver_pprd2 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS =
(PROTOCOL = TCP)
(Host = 123.45.67.89) -- whatever host IP has PPRD
(Port = 1521)
)
)
(CONNECT_DATA = (SID = PPRD) -- the new standby (old primary)
)
)
To initiate a failover in which the standby database becomes the primary database because the primary database has crashed (primary becomes useless and must be rebuilt as a new standby, then do a switchover back to it when things are back to normal; there is a remote possibility for data loss on a failover), with all of these commands being run on the standby (i.e., PPRD2):
If running in maximum protection mode [7.1.3.1]:
select protection_mode from v$database;
alter database set standby database to maximize performance;
See if any archived redo log gaps exist [7.2.2.1.1]:
select thread#,low_sequence#,high_sequence# from v$archive_gap;
If any gaps exist:
Copy or transfer all missing archive logs from the primary system to the
standby system; then, for each (substituting filename):
alter database register physical logfile 'filename';
See if other missing archived redo logs exist (this actually refers to looking
at other standby sites, but, this can also be done on a single standby if you
could get the archive logs off the failed primary site) [7.2.2.1.2]:
select unique thread# as thread, max(sequence#) over (partition by thread#)
as last from v$archived_log;
!ls -ltr /orcl/oradata/PPRD/archivelogs tail
If missing sequences are found:
Copy or transfer all missing archive logs from the other standby systems
to the first standby system; then, for each (substituting filename):
alter database register physical logfile 'filename';
Repeat steps 7.2.2.1.1 and 7.2.2.1.2 until no gaps remain [7.2.2.1.3].
Initiate failover operation on the standby database [7.2.2.1.4]. If the standby
database has standby redo logs and you have not manually registered any partial
archived redo logs, issue the following statement:
alter database recover managed standby database finish;
Otherwise, issue the following statement:
alter database recover managed standby database finish skip standby logfiles;
Then, convert the standby database to the primary role [7.2.2.1.5]:
alter database commit to switchover to primary;
shutdown immediate
(You might want to make a backup of this new primary database now, just in
case you have to recover it before the primary site is fixed and back up and
running.)
if audit_trail needs to be turned on for this "new" primary, change it
to TRUE (or whatever setting is needed) in the standby's init.ora file
(initPPRD2.ora):
If you are using an spfile (do this after the startup below):
alter system set audit_trail = TRUE scope = both;
Else, if you are using a pfile:
audit_trail = TRUE
startup
select name,instance_name,database_role,status,open_mode from v$instance,v$database;
Change tnsnames.ora entry on all application hosts (T:\APPS\ban6\orawin\NET80\ADMIN)
to point to the new primary (PPRD2) (or, just tell the users to use myserver_pprd2 as
the database to log into - make sure myserver_pprd2 is defined in the tnsnames.ora
file on the network; but, see "Multiple tnsnames addresses" for another option) [10.1.1]:
myserver_pprd =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS =
(PROTOCOL = TCP)
(Host = 234.56.78.90) -- whatever host IP has PPRD2
(Port = 1521)
)
)
(CONNECT_DATA = (SID = PPRD2) -- the new primary (old standby)
)
)
When the hardware or other problem is corrected, create a new "standby" database
on the primary site (as shown above), then do a switchover (also as shown above),
resulting in the primary database being back on the primary site, and the standby
database being on the standby site.
To see Data Guard messages on the primary or standby [6.5.5]:
alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';
set recsep off
column message format a59
select timestamp,message from v$dataguard_status;
If you start up your primary database and get: ORA-03113: end-of-file on communication channelafter the "Database mounted." message, check at the end of the alert.log file (such as alert_PPRD.log) in the primary database's bdump directory. If you see: ORA-16072: a minimum of one standby database destination is requiredthat probably means that archiving to the standby database was set to DEFER when you tried to start up the primary database. To start up your primary, re-enable archiving to the standby, mount and open the primary, and switch back to defer archiving:
connect / as sysdba
startup nomount
column name format a25
column value format a52
select name,value from v$parameter where name like 'log_archive_dest_%';
alter system set log_archive_dest_state_2=ENABLE scope=both;
alter database mount;
alter database open;
alter system set log_archive_dest_state_2=DEFER scope=both;
If the entire standby site is down, and the above fails, you may have to switch the protection mode back to "maximum performance" to get the primary to open until the standby site is available again:
connect / as sysdba
startup mount
alter database set standby database to maximize performance;
alter database open;
Then, when the standby site is up again, shut down the primary database and switch the protection mode back to "maximum availability":
sqlplus "/ as sysdba"
shutdown immediate
startup mount
alter database set standby database to maximize availability;
alter database open;
If you add a datafile to the primary, you may also have to add it to the standby. If standby_file_management is set to AUTO in the init.ora files on the standby site (and on the primary site for switchover), an "add datafile" is automatically propagated to the standby database on the next logfile switch. Otherwise, to add the matching datafile on the standby (note that no size information is needed):
alter database recover managed standby database cancel;
alter database create datafile '/data/oradata/PPRD2/devl_PPRD_02.dbf';
alter database recover managed standby database disconnect from session;
Scripts:Below are scripts that will start up and shut down a Data Guard primary or physical standby database. They test to see which type of database they are running on, and what state it is in, and take the appropriate action to start it up or to shut it down. They must be run when logged in as sysdba (sqlplus "/ as sysdba"). They can also be run against non Data Guard databases.Also included is a dataguard_state function, which, optionally, enables or defers archiving to the standby database from the primary database, and returns the resulting archiving state. Both the start up and shut down scripts run it on the primary database when this option is turned on; however, if a primary database running with maximum availability or maximum performance mode is shut down while archiving has been deferred, starting it up will fail with an "ORA-16072: a minimum of one standby database destination is required" error, so, for now, deferring and enabling archiving to the standby is turned off in the two scripts. A limited user, named dgstate, is created for security reasons to run it so that you don't have to connect to system to run it (thus the system password isn't required in the shut down and start up scripts), and so that you don't need to grant alter system privileges to dgstate. To load dataguard_state.sql and to create the dgstate user (be sure to change dgstatepw to some other password here and in the Data Guard shutdown and startup scripts first): sqlplus "/ as sysdba" grant select on v_$parameter to system; connect system @dataguard_state.sql create user dgstate identified by dgstatepw default tablespace users temporary tablespace temp quota 0 on users quota 0 on temp; grant create session to dgstate; grant alter system to system; grant execute on system.dataguard_state to dgstate;The dgstartup.sql script starts up a Data Guard primary or physical standby database. If this is a primary database, it just has to be mounted and opened. If this is a physical standby database, the startup command will fail with "ORA-01666: controlfile is for a standby database" when the mount is attempted. So, try mounting the database again, this time as a standby database, and enable managed recovery, then, enable the archiving by the primary database to this standby database (optional).The dgshutdown.sql script shuts down a Data Guard primary or physical standby database. If this is a primary database, just do a log file switch before shutting it down. Otherwise, if this is a standby database, terminate any active sessions on the standby and switch to managed recovery mode if currently in read-only access, then, defer the archiving by the primary database to this standby database (optional), and cancel managed recovery.You'll probably want to edit the dbstart and dbshut scripts (in $ORACLE_HOME/bin) to run these Data Guard startup and shutdown scripts, replacing the "startup" commands with "@/home/oracle/dgstartup.sql", and the "shutdown" commands with "@/home/oracle/dgshutdown.sql" (assuming they are put in the /home/oracle directory). Just don't forget to do @dgstartup.sql and @dgshutdown.sql instead of the startup and shutdown commands when you want to start up or shut down a Data Guard primary or physical standby database.Be aware that the functionality in these two scripts can't be incorporated into PL/SQL database procedures (such as database startup and shutdown triggers) because of all of the restrictions on what can be run and accessed in PL/SQL from a database that is not in the open state.References and Notes: Oracle Data Guard Concepts and Administration Release 2 (9.2) http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96653.pdf Implementing Oracle9i Data Guard for Higher Availability; by Daniel T. Liu (note the tips, especially RMAN backup, disabling log transport services when the standby is down) http://www.dbazine.com/oracle/or-articles/liu4 Oracle9i Data Guard Configuration Example - (Physical, Maximum Performance Mode); by Jeff Hunter http://www.idevelopment.info/data/Oracle/DBA_tips/Data_Guard_9i/DG_1.shtml If your backup scripts look for SID-based file names and directories to back up, you may need to change them to exclude the standby database directories, depending on how you named your standby, i.e., find PPRD, but exclude PPRD2 directories, such as by using:
grep -v "/[^/]*${BACKUP_SID}[^/].*/"
so that the standby files don't overwrite the primary files in the backup staging directory, such as:
replace:
find / -name "*${BACKUP_SID}*" ! -type d 2>/dummy
egrep '(\.ora$\.dbf$\.ctl$\.rdo$)' sort -t/ -k3 >backup_list.lst
with:
find / -name "*${BACKUP_SID}*" ! -type d 2>/dummy
grep -v "/[^/]*${BACKUP_SID}[^/].*/"
egrep '(\.ora$\.dbf$\.ctl$\.rdo$)' sort -t/ -k3 >backup_list.lst
You may need to make similar changes to other scripts that do SID-based searches. Multiple tnsnames addresses: (added 8/23/07)For standby databases off-site (at a different IP address; and where the SID is the same for the primary and standby), if you don't want your users to have to enter a different database name connect string after a switchover or a failover, you can use this trick from Helena Whitaker. Just define two addresses for the connect string in the tnsnames.ora file on the primary site, with the main address listed first and the secondary address listed second. If Oracle can't reach the database at the first address, it will try to reach it at the second address. So, when the user specifies the myserver_pprd connect string for the Banner database, if the primary is down, and you have done a failover or switchover, Oracle will not find that main address (for the original primary), but will find the secondary address (for the original standby, which is now the primary). To do this, your connect string in tnsnames.ora on the primary site will be something like:
myserver_pprd =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS =
(PROTOCOL = TCP)
(Host = 123.45.67.89) -- whatever host IP has PPRD
(Port = 1521)
)
(ADDRESS =
(PROTOCOL = TCP)
(Host = 234.56.78.90) -- whatever host IP has PPRD2
(Port = 1521)
)
)
(CONNECT_DATA = (SID = PPRD)
)
)
RFS errors: (added 8/23/07)If you are getting an RFS message "Destination database mount ID mismatch" in the alert log and an RFS trace file generated in the udump directory on each log switch, you can stop this from happening by disabling the LOG_ARCHIVE_DEST_2 on the standby database by putting "log_archive_dest_state_2 = DEFER" in the standby's init.ora file on pre-10g databases, or, preferrably, by putting the "VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)" parameter in the LOG_ARCHIVE_DEST_2 line in the standby and primary (for switchover) init.ora files on 10g databases.The problem is caused because the standby LOG_ARCHIVE_DEST_2 defaults to valid for all roles and is enabled, so RFS is trying to send log information from the standby to the primary. This doesn't cause a problem applying the changes to the standby, except for generating those messages in the alert log and creating those RFS trace files. The VALID_FOR parameter indicates that this destination only should be use when this database become primary.Note that if you disable LOG_ARCHIVE_DEST_2 on the standby, you will have to set it back to ENABLE if you do a switchover to have the standby become the primary (and you will have to DISABLE it on the old primary at that time as well to prevent those messages and trace files). Also, if you use the "VALID_FOR" option, you may want to put "VALID_FOR=(ALL_LOGFILES,ALL_ROLES)" in the LOG_ARCHIVE_DEST_1 line for consistency.Notes on using server parameter files: The use of a server parameter file (spfile), which is a binary version of the init.ora file (pfile - parameter initialization file), is required with Oracle9i Release 2 when using a Data Guard Broker Configuration (the GUI interface to Data Guard). If you don't need a Data Guard Broker configuration, which I currently don't, you could continue to use a pfile if you wanted to. Oracle will look for an spfile first and use it when starting up the database instead of the init.ora file. When using an spfile, a parameter can be changed using the "alter system set parameter_name=parameter_value scope=both;" command (such as "alter system set log_archive_dest_state_2 = DEFER;") without editing the init.ora file and possibly without having to shut down and start up the database to have the change take effect (however, some parameters require using scope=spfile in the nomount state and bouncing the database to take effect). It is for that reason that I'm switching over to using spfile's. (Note: Put the scope setting at the end of the alter system command, such as if you are including a comment for the parameter.) WARNING: IF YOU MAKE A CHANGE TO A PARAMETER IN THE PRIMARY DATABASE THAT YOU WANT REFLECTED IN THE STANDBY DATABASE, YOU MUST ALSO MAKE THE CHANGE IN THE STANDBY DATABASE, since primary database parameter changes are not automatically propagated to the standby. For more information on spfile's, see: So You Want to Use Oracle's SPFILE; by James Koopmann http://www.dbasupport.com/oracle/ora9i/spfile.shtml Oracle's SPFILE; by Amar Kumar Padhi http://www.dbasupport.com/oracle/ora10g/spfile01.shtml You may need to change your $ORACLE_HOME/bin/dbstart script to check for server parameter files in addition to parameter files:
1) after:
PFILE=${ORACLE_HOME}/dbs/init${ORACLE_SID}.ora
add:
SPFILE=${ORACLE_HOME}/dbs/spfile${ORACLE_SID}.ora
2) replace:
if [ -f $PFILE ] ; then
with:
if [ -f $PFILE -o -f $SPFILE ] ; then
If you use the RESETLOGS option on the primary database, you have to re-create the standby database [8.4]. Also see chapter 8 for adding and dropping tablespaces and datafiles and online redo logs (note: standby logs aren't the same as online redo logs), or renaming datafiles, whether standby_file_management is set to MANUAL or AUTO. The database name in your standby is the same as the database name in your primary, but they differ in their instance name. If you want your database name in your standby to be the same as it's instance name, you can make the following modifications to steps 3.2.2.1, 3.2.2.2, and 3.2.3 above. Thanks to Lee Johnston of the University of West Florida for this tip.
Change the primary database name the secondary instance name (PPRD2) before shutting it
down to do the copy (see 3.2.2.1 above):
shutdown normal
startup mount
!nid target=sys/
answer Y
shutdown normal
Copy the primary database to the standby directory (see 3.2.2.2 above):
!sh cp_standby.shl
Create the standby control file (see 3.2.3 above):
startup
select replace(value,'/PPRD/','/PPRD2/') from v$parameter where name = 'control_files';
alter database create standby controlfile as '/orcl/oradata/PPRD2/ctrl_PPRD_01.ctl';
Change the primary database name back to the primary instance name (PPRD):
shutdown normal
!nid target=sys/
answer Y
alter database open;
exit
No comments:
Post a Comment