1) On Node check if the DB is in archivelog mode or not
SQL> select log_mode from v$database;
LOG_MODE
------------
ARCHIVELOG
2) Enable Force Logging
ALTER DATABASE FORCE LOGGING;
3) Initialization Parameters
Check the setting for the DB_NAME and DB_UNIQUE_NAME parameters. In this case they are both set to "CRAMPBB" on the primary database.
SQL> show parameter db_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string CRAMPBB
SQL> show parameter db_unique_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_unique_name string CRAMPBB
The DB_NAME of the standby database will be the same as that of the primary, but it must have a different DB_UNIQUE_NAME value. The DB_UNIQUE_NAME values of the primary and standby database should be used in the DG_CONFIG setting of the LOG_ARCHIVE_CONFIG parameter. For this example, the standby database will have the value "CRAMPRD_STBY".
**SQL> ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(CRAMPBB,PBBADG)';
Set suitable remote archive log destinations. In this case I'm using the fast recovery area for the local location, but you could specify an location explicitly if you prefer. Notice the SERVICE and the DB_UNIQUE_NAME for the remote location reference the standby location.
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=PBBADG NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=PBBADG';
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;
ALTER SYSTEM SET LOG_ARCHIVE_FORMAT='archCRAMPBB_%r_%t_%s.arc' SCOPE=SPFILE;
ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=30;
ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE SCOPE=SPFILE;
**ALTER SYSTEM SET FAL_SERVER=PBBADG;
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO; (on both nodes)
4) Entries for the primary and standby databases are needed in the "$ORACLE_HOME/network/admin/tnsnames.ora" files on both servers
on ADG and PBB
---------------
PBBADG=
(DESCRIPTION =
(ADDRESS_LIST =
(LOAD_BALANCE = yes)
(ADDRESS = (PROTOCOL = TCP)(HOST = cramuatrpt-scan)(PORT = 1527))
(ADDRESS = (PROTOCOL = TCP)(HOST = inpudicrmrdbdevrpt1v)(PORT = 1527))
(ADDRESS = (PROTOCOL = TCP)(HOST = inpudicrmrdbdevrpt2v)(PORT = 1527))
)
(CONNECT_DATA =
(SERVICE_NAME = PBBADG)
(failover_mode=(type=select)(method=basic))
)
)
CRAMPBB=
(DESCRIPTION =
(ADDRESS_LIST =
(LOAD_BALANCE = yes)
(failover=on)
(ADDRESS = (PROTOCOL = TCP)(HOST = crampbb-scan.vsnl.co.in)(PORT = 1527))
(ADDRESS = (PROTOCOL = TCP)(HOST = inpudicrmrdbuatpbb1v.vsnl.co.in)(PORT = 1527))
(ADDRESS = (PROTOCOL = TCP)(HOST = inpudicrmrdbuatpbb2v.vsnl.co.in)(PORT = 1527))
)
(CONNECT_DATA =
(SERVICE_NAME = CRAMPBB)
(failover_mode=(type=select)(method=basic))
)
)
5) Backup Primary DB
run
{
allocate channel c1 type disk format '/export/rman_backup_for_standby/rman_db_%T_%U.rman';
backup as compressed backupset full database PLUS ARCHIVELOG;
release channel c1;
}
6) Create standby controlfile
ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/tmp/PBBADG.ctl'
7) Create a parameter file for the standby database.
CREATE PFILE='/tmp/PBBADG1.ora' FROM SPFILE;
8) Ammend below parameters in pfile
*.db_unique_name='PBBADG'
*.fal_server='PBBADG'
*.log_archive_dest_2='SERVICE=CRAMPBB NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=CRAMPBB'
STANDby server commands
9) check if all listener are up and running
10) Create the SPFILE form the amended PFILE.
SQL> create spfile from pfile=
run {
set until sequence=35 thread=1;
recover database;
}
11) Create redo logs
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=MANUAL;
ALTER DATABASE ADD LOGFILE '+FRA/oradata/crampbb/redo_g1_m01.dbf';
ALTER DATABASE ADD LOGFILE '+FRA/oradata/crampbb/redo_g1_m02.dbf';
ALTER DATABASE ADD LOGFILE '+FRA/oradata/crampbb/redo_g2_m01.dbf';
ALTER DATABASE ADD LOGFILE '+FRA/oradata/crampbb/redo_g2_m02.dbf';
ALTER DATABASE ADD LOGFILE '+FRA/oradata/crampbb/redo_g3_m01.dbf';
ALTER DATABASE ADD LOGFILE '+FRA/oradata/crampbb/redo_g3_m02.dbf';
ALTER DATABASE ADD LOGFILE '+FRA/oradata/crampbb/redo_g4_m01.dbf';
ALTER DATABASE ADD LOGFILE '+FRA/oradata/crampbb/redo_g4_m02.dbf';
ALTER DATABASE ADD LOGFILE '+FRA/oradata/crampbb/redo_g5_m01.dbf';
ALTER DATABASE ADD LOGFILE '+FRA/oradata/crampbb/redo_g5_m02.dbf';
ALTER DATABASE ADD LOGFILE '+FRA/oradata/crampbb/redo_g6_m01.dbf';
ALTER DATABASE ADD LOGFILE '+FRA/oradata/crampbb/redo_g6_m02.dbf';
ALTER DATABASE ADD LOGFILE '+FRA/oradata/crampbb/redo_g7_m01.dbf';
ALTER DATABASE ADD LOGFILE '+FRA/oradata/crampbb/redo_g7_m02.dbf';
ALTER DATABASE ADD LOGFILE '+FRA/oradata/crampbb/redo_g8_m01.dbf';
ALTER DATABASE ADD LOGFILE '+FRA/oradata/crampbb/redo_g8_m02.dbf';
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;
12) Create Standby Log file
ALTER DATABASE ADD STANDBY LOGFILE GROUP 9 '+DATA' SIZE 1G;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 10 '+DATA' SIZE 1G;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 11 '+DATA' SIZE 1G;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 12 '+DATA' SIZE 1G;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 13 '+DATA' SIZE 1G;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 14 '+DATA' SIZE 1G;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 15 '+DATA' SIZE 1G;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 16 '+DATA' SIZE 1G;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 16 '+DATA' SIZE 1G;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 18 '+DATA' SIZE 1G;
13) Create password file with orapwd utility.
