Setup dataguard on Linux without shutting down primary database

This scenario assumes that you already have a database is running on archivelog mode and need to upgrade this single database to data guard solution, following are the steps to archive this.

1. Prepare for the standby database – install Linux and oracle database with the edition same as primary database.

2. Enable force logging on primary database

SQL> ALTER DATABASE FORCE LOGGING;

3. Configure the Standby Redo Log (only need to do this on primary database since following uses RMAN to duplicate the standby database)

SQL> ALTER DATABASE ADD STANDBY LOGFILE
2> (‘/u01/oradata/orcl/standbyredo01.log’,’/u01/oradata/orcl/standbyredo02.log
‘,’/u01/oradata/orcl/standbyredo03.log’,’/u01/oradata/orcl/standbyredo04.log’
) SIZE 50M;

Be aware of 3 points as below:
* Ensure log file sizes are identical on the primary and standby databases.
* Determine the appropriate number of standby redo log file groups. There is an equation: (maximum number of logfiles for each thread + 1) * maximum number of threads
* Verify related database parameters and settings – like the values of MAXLOGFILES and MAXLOGMEMBERS

4. Set Primary Database Initialization Parameters(refer to parameters showed in step 6).
Assume the primary database uses SPFILE and we cannot shutdown it, so we use ALTER SYSTEM SET parameter=value SCOPE=BOTH to change the initialization parameters. For examples:

SQL> ALTER SYSTEM SET LOG_ARCHIVE_CONFIG=’DG_CONFIG=(orcl,orclstb)’ SCOPE=BOTH;
SQL> ALTER SYSTEM SET log_archive_dest_1=’LOCATION=/u01/arch/dg1 VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=orcl’ SCOPE=BOTH;
SQL> ALTER SYSTEM SET log_archive_dest_2=’SERVICE=boston LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=orclstb’ SCOPE=BOTH;

5. Create pfile and standby control file on primary database and copy them to standby database, then re-name them accordingly.

SQL> CREATE pfile=’/u01/pfile.ora’ FROM SPFILE;
SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS ‘/u01/stb.ctl’;

6. Modify the pfile on standby database like following(copied from oracle database manual ):

DB_NAME=chicago
DB_UNIQUE_NAME=boston
LOG_ARCHIVE_CONFIG=’DG_CONFIG=(chicago,boston)’
CONTROL_FILES=’/arch1/boston/control1.ctl’, ‘/arch2/boston/control2.ctl’
DB_FILE_NAME_CONVERT=’chicago’,’boston’
LOG_FILE_NAME_CONVERT=
‘/arch1/chicago/’,’/arch1/boston/’,’/arch2/chicago/’,’/arch2/boston/’
LOG_ARCHIVE_FORMAT=log%t_%s_%r.arc
LOG_ARCHIVE_DEST_1=
‘LOCATION=/arch1/boston/
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=boston’
LOG_ARCHIVE_DEST_2=
‘SERVICE=chicago LGWR ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=chicago’
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
STANDBY_FILE_MANAGEMENT=AUTO
FAL_SERVER=chicago
FAL_CLIENT=boston

7. Backup primary database and archived logs using RMAN, and copy them to standby database with same directory structure.

RMAN> BACKUP DATABASE format ‘/u01/backup/data.%U.rman’ plus archivelog format ‘/oracle/oradata/rmanbackup/ARCH.%U.rman’

8. Startup the instance of standby database without mounting it.
SQL> STARTUP NOMOUNT;

9. Using RMAN to duplicate the standby database (Do this operation on primary database).

oracle@dg1~$ rman target / auxiliary sys/change_on_install@STANDBY

RMAN> duplicate target database for standby dorecover nofilenamecheck;

Please be aware you will encounter an error while doing this, which is ORA-12528: TNS:listener: all appropriate instances are blocking new connections, it is because only the Auxiliary Instance is running .The instance registers with the listener by the PMON process. TO start the PMON process the database need to be in MOUNT status. Before there is nothing to register the database to the listerner so the instance is BLOCKED.
To avoid this issue, use the Oracle10G feature:
tnsnames.ora
————
SAIRAM =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = apadhi-idc)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = SAIRAM)(UR=A)
)
)

10. Bring up standby database to managed recovery

SQL> alter database recover managed standby database disconnect from session;

Done.