Oracle Study Guide

alter system set log_archive_trace=7935;

This will cause lgwr and archN to write a bunch of stuff to the alert log.

his process is for setting up replication to another databse on the same system.

SYSTEM

add the service names in the /etc/hosts file:
127.0.0.1 localhost.localdomain localhost orcl klon

PRIMARY DATABASE

Enable force logging:
SYS@orcl 11:26:29> select force_logging from v$database;

FOR
—
NO

SYS@orcl 11:26:54> alter database force logging;

Database altered.

SYS@orcl 11:27:12> select force_logging from v$database;

FOR
—
YES

Create password file (usually not necessary). The SYS password must be the same for the primary and all standby databases.

Configure a standby redo log
Get the number of log groups and logfile sizes from EM and then create the standy redo log groups:
Remember to increment the group numbers, add the ’standby’ keyword, and change the logfile names

ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 ( ‘/u01/app/oracle/oradata/orcl/REDO/sb_redo01.log’) SIZE 51200K;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 ( ‘/u01/app/oracle/oradata/orcl/REDO/sb_redo02.log’) SIZE 51200K;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 ( ‘/u01/app/oracle/oradata/orcl/REDO/sb_redo03.log’) SIZE 51200K;
/

Verify that the standby logs have been added:
SYS@orcl 12:07:25> SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG;

GROUP# THREAD# SEQUENCE# ARC STATUS
———- ———- ———- — ———-
4 0 0 YES UNASSIGNED
5 0 0 YES UNASSIGNED
6 0 0 YES UNASSIGNED

Configure the Primary database (both as primary and secondary -- for switchover of roles):
# Primary Role Initialization Parameters:
#
#DB_NAME=orcl
DB_UNIQUE_NAME=orcl
LOG_ARCHIVE_CONFIG='DG_CONFIG=(orcl,klon)'
#
# get from show parameter control_files;
#CONTROL_FILES='/u01/app/oracle/oradata/orcl/control01.ctl','/u01/app/oracle/oradata/orcl/control02.ctl','/u01/app/oracle/oradata/orcl/control03.ctl'
#
# get from select a.dest_id,name,destination from v$archived_log a, v$archive_dest b where a.dest_id=b.dest_id;
#check the LOCATION directory to see if the db name is in a folder in lowercase or not!
LOG_ARCHIVE_DEST_1=
 'LOCATION=/u01/app/oracle/product/11.1.0/db_1/dbs/
  VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
  DB_UNIQUE_NAME=ORCL'
#
LOG_ARCHIVE_DEST_2=
 'SERVICE=klon LGWR ASYNC
  VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
  DB_UNIQUE_NAME=klon'
#
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
#REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
#
#get from show parameter log_archive_format
# this is setup automatically when archivelog is enabled
#LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
#
LOG_ARCHIVE_MAX_PROCESSES=4
#
#
#Standby Role Initialization Parameters:
#
FAL_SERVER=klon
FAL_CLIENT=orcl
DB_FILE_NAME_CONVERT='klon','orcl'
LOG_FILE_NAME_CONVERT='/klon/','/orcl/'
STANDBY_FILE_MANAGEMENT=AUTO


Apply these settings to the Primary database (also make sure that the database is in archivelog mode):
create pfile='/home/oracle/DATAGUARD/init_orcl.ora' from spfile;
Then add the above lines to what is there already. Be sure to comment out anything in your additons that are already in the pfile.
shutdown immediate;
create spfile from pfile='/home/oracle/DATAGUARD/init_orcl.ora';
startup

SECONDARY DATABASE

Create a backup copy of the primary database datafiles.

Configure a standby redo log

ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 ( ‘/u01/app/oracle/oradata/klon/REDO/sb_redo01.log’) SIZE 51200K;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 ( ‘/u01/app/oracle/oradata/klon/REDO/sb_redo02.log’) SIZE 51200K;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 ( ‘/u01/app/oracle/oradata/klon/REDO/sb_redo03.log’) SIZE 51200K;
/

Setup the parameters for the standby database:

create pfile=’/home/oracle/DATAGUARD/init_klon.ora’ from spfile;
shutdown immediate

Add the following lines to the parameter file, then apply the changes:

create spfile from pfile=’/home/oracle/DATAGUARD/init_klon.ora’;
startup

Make sure you give a different directory for the log_archive_dest_1 directory so that the logfiles do not clobber each other!

# db_name must be the same for the primary and all standby databases
DB_NAME=orcl
DB_UNIQUE_NAME=klon
LOG_ARCHIVE_CONFIG=’DG_CONFIG=(orcl,klon)’
DB_FILE_NAME_CONVERT=(’orcl’,'klon’)
LOG_FILE_NAME_CONVERT=’orcl’,'klon’
#LOG_ARCHIVE_FORMAT=log%t_%s_%r.arc
log_archive_trace=7935
LOG_ARCHIVE_DEST_1=
‘LOCATION=/u01/app/oracle/product/11.1.0/db_1/dbs/klon
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=klon’
LOG_ARCHIVE_DEST_2=
‘SERVICE=orcl LGWR ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=orcl’
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
STANDBY_FILE_MANAGEMENT=AUTO
FAL_SERVER=orcl
FAL_CLIENT=klon

HEADS-UP

I did this on an 11g installation and kept getting error ora-1017. Finally I redid the passwords to remove case-sensitivity and it fixed the problem:

orapwd file=/u01/app/oracle/product/11.1.0/db_1/dbs/orapw<DBID> password=<passwd> entries=10 force=y ignorecase=y

I had to enable lgwr logging in order to see the error as well. See post

set lgwr logging level

There was also mention that on some versions if the connect identifier had a multiple of 5 characters that the same problem would result.

see also http://www.colestock.com/blogs/2007/10/creating-physical-standby-using.html

http://oracle.3dub.com/10g/server.102/b14239/create_ps.htm