Steps to Create Physical Standby Database

Creating a Physical Standby Database:

On Primary Database:

a. Please make sure primary database is ARCHIVELOG enabled.

SQL> SELECT LOG_MODE FROM SYS.V$DATABASE;
LOG_MODE
------------
NOARCHIVELOG

SQL> select name from v$database;
NAME
---------
PROD

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount;
ORACLE instance started.
Total System Global Area  726540288 bytes
Fixed Size                  2230040 bytes
Variable Size             469764328 bytes
Database Buffers          251658240 bytes
Redo Buffers                2887680 bytes
Database mounted.

SQL> alter database archivelog;
Database altered.

SQL> alter database open;
Database altered.

SQL> select log_mode from v$database;
LOG_MODE
------------
ARCHIVELOG

b. Please make sure primary database is FORCE LOGGING is enabled

SQL> SELECT force_logging FROM v$database;
FOR
---
NO

SQL> ALTER DATABASE force logging;
Database altered.

SQL>  SELECT force_logging FROM v$database;
FOR
---
YES

c. Please Set the following Initialization Parameters on the Primary Database:

ALTER SYSTEM SET log_archive_dest_1='location=/archive/prod/' SCOPE=both;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='service=stby_db async valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=stby_db' SCOPE=both ;
ALTER SYSTEM SET  log_archive_dest_state_2 ='defer';
ALTER SYSTEM SET log_archive_config= 'dg_config=(prim_db,stby_db)';
ALTER SYSTEM SET log_archive_max_processes=8;
ALTER SYSTEM SET db_unique_name = prim_db scope=spfile;

Recycle the database.


Those Parameters prepare the Primary Database to support a Standby Database 'stby_db'. log_archive_dest_2is responsible for transferring Redo to the Standby Site later – currently it is deferred, we will enable later once theStandby Database exists. log_archive_config records all db_unique_name's participating in this Configuration.Log transport will not be allowed to any database not on the list

d.Create a PFILE from the Primary SPFILE in the Format 'init<Standby SID>.ora'
create pfile='/tmp/initstby_db.ora' from spfile;


e.Add the tns entry for both primary and standby databases in primary tnsnames.ora

f.Copy prepared 'initstby_db.ora' together with the Passwordfile of the Primary Database (orapwprim_db in$ORACLE_HOME/dbs) to the Standby Site and place both into '$ORACLE_HOME/dbs'-Folder. Then you have to rename the Passwordfile to match with the Standby SID:

mv orapwprim_db orapwstby_db

On Standby database:

a.Adjust the values in initstby_db.ora

*.log_archive_dest_1='location=/archive/standby/''
*.log_archive_config= 'dg_config=(prim_db,stby_db)'
*.log_archive_max_processes = 8
*.fal_server='prim_db'
*.log_file_name_convert = '/u01/app/oracle/oradata/PROD','/u01/app/oracle/oradata/STANDBY'
*.db_file_name_convert = '/u01/app/oracle/oradata/PROD','/u01/app/oracle/oradata/STANDBY'
*.db_unique_name='stby_db'


log_archive_dest_1 --> here defines the local Archive Destination where we will put ArchiveLogs arriving from the Primary Database later.
log_archive_config and db_unique_name are required for the Data Guard Configuration (like on the Primary)
fal_server is required for Gap Resolution to be able to automatically fetch Archive Logs from the Primary again in Case there is a Gap.
To automate the Substitution of Database File Locations we set db_file_name_convert and log_file_name_convert.
You may also adjust audit_file_dest and control_files to match with your Environment and Locations.

Create the Physical Standby Database

In the previous Section we prepared the Environments and the Primary Database to support a Standby Database.Now we can proceed and create the Physical Standby Database itself. There are 3 common Ways to perform thiswhich will be shown in the next Section. You can choose any Way most suitable for you:

1. Creating manually via User-Managed Backups
2. Using RMAN Backup-based Duplication
3. Creating a Standby Database from the active Primary Database without a Backup using RMAN Duplicate.

On Primary Database:

If you want to use a Hot Backup, we have to put the Primary Database into Backup Mode and copy the Datafiles:

SQL> alter database begin backup;
$ cp /oracle/oradata/prim_db/*.dbf /backup/
SQL> alter database end backup;
We can create the Standby Controlfile since the Backup is complete (either Hot or Cold Backup)
SQL> alter database create standby controlfile as '/tmp/control01.ctl';
Copy the data and control files to standby datafile location.

On Standby Database:

$ export ORACLE_SID = stby_db
SQL> connect / as sysdba
SQL> startup mount

Post steps:

we prepared and created the Physical Standby Database. Now we can start the Log Transport and Log Apply Services to have the Data Guard Environment completely operational.
First of all we should now add Standby RedoLogs to the new created Standby Database to collect the current Redo arriving from the Primary Database. We can add those using

On Standby:

SQL> alter database add standby logfile <group #> ('<Member>') size <size>;
To verify if Standby RedoLogs get allocated on the Standby Database
select * from v$standby_log;

On Primary:

Next we can enable Log Transport Services on the Primary Database which have been prepared initially – performing a Logfile Switch afterwards will finally enable it (all changes to log_archive_dest_n and log_archive_dest_state_n become active after the next Log Switch once set)


SQL> alter system set log_archive_dest_state_2 = 'enable' scope=both;
SQL> alter system switch logfile;

On Standby:

alter database recover managed standby database using current logfile disconnect;
Query v$managed_standby to monitor the Progress of the Managed Recovery:


SQL> select * from v$managed_standby where process = 'MRP0';




FAL_SERVER specifies the FAL (fetch archive log) server for a standby database. The value is an Oracle Net service name, which is assumed to be configured properly on the standby database system to point to the desired FAL server.
FAL_CLIENT specifies the FAL (fetch archive log) client name that is used by the FAL service, configured through theFAL_SERVERp arameter, to refer to the FAL client. The value is an Oracle Net service name, which is assumed to be configured properly on the FAL server system to point to the FAL client (standby database). Given the dependency of FAL_CLIENT on FAL_SERVER, the two parameters should be configured or changed at the same time.

At primary:
fal_server = ' '
fal_client = ' '
Primary will never will have gap, so no need for any fal* parameter here.

At remote standby database:
fal_server = 'prim_db'
fal_client = 'stby_db'
Remote standby when has gap, can get the archive logs from the primary database . Hence the fal_server parameter. It wants the primary to send the FAL request response to 'stby_db', hence fal_client setting.
In case if there is a gap in stby_db,it will request fal_server prim_db. Primary will send the fal_client stby_db.