How to Create an Oracle Standby Database in 5 Steps

• where is the standby database going to be created, which server? Theoretically the standby and the primary database could share the same server/host, but what would be the point of that? The reason to setup Data Guard is to provide high availability (HA) for your system. For learning and demonstration purposes, you can have the two, the primary and standby on the same server. However, please, do not have your production databases setup that way!
• create datafile locations and other directories, ensure you have enough storage space for your standby. Remember that the physical standby is an exact copy block for block, of the primary database. If the primary database is 100GB in size, you need at least the same amount of space for the standby, plus room for growth!
• decide on a database unique name for the standby database. This has to be different from the primary’s unique name!
• create a deployment plan, and the scripts with all your steps and commands! Remember, a good DBA always has a deployment plan!

1. Prepare the primary database
2. Configure Oracle Net
3. Instantiate or create the physical standby database
4. Start the redo propagation/transport from the primary to the standby
5. Start the redo apply at the standby

• enable archived log mode, if not already enabled. This is mandatory! If the primary database is in noarchive log mode, you cannot create a standby database.

  shutdown immediate;
  startup mount;
  alter database archivelog;

• enable flashback database. This is optional, but recommended!

  alter database flashback on;

• enable force logging. Nologging operations can cause real problems, imagine missing indexes on the standby database!

  alter database force logging;

   
• add standby redo logs to the primary. This is also optional, but recommended!

  alter database add standby logfile size 50m;
  alter database add standby logfile size 50m;
  alter database add standby logfile size 50m;
  alter database add standby logfile size 50m;
  

   
• make the appropriate parameter changes. There are about 9 parameters that need to be adjusted or setup on the primary.

  alter system set db_unique_name=london scope=spfile;    -- primary db name
  alter system set log_archive_config='dg_config=(london,berlin)';
  alter system set log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST valid_for=(all_logfiles,all_roles)';
  alter system set log_archive_dest_2='service=berlin lgwr async noaffirm valid_for=(online_logfiles, primary_role) db_unique_name=berlin';  -- tns alias pointing to the standby db service
  alter system set log_archive_dest_state_2=defer;
  alter system set fal_server=berlin;   -- where to go if you need to resolve gaps. this is optional.
  alter system set db_file_name_convert='berlin','london','BERLIN','LONDON' scope=spfile;
  alter system set log_file_name_convert='berlin','london','BERLIN','LONDON' scope=spfile;
  alter system set standby_file_management=auto;
  startup force;  --restart the database for changes to take effect

   

-- entry for the listener.ora file, for each database
 
sid_list_listener=(sid_list=(sid_desc=(sid_name=london))
                            (sid_desc=(sid_name=berlin))
                  )

lsnrctl stop
lsnrctl start

-- entry for the tnsnames.ora file 

london=(description=(address=(protocol=tcp)(host=127.0.0.1)(port=1521))(connect_data=(sid=london)))
berlin=(description=(address=(protocal=tcp)(host=127.0.0.1)(port=1521))(connect_data=(sid=berlin)))

startup nomount; -- startup the standby database in nomount;

rman target sys/***@london auxiliary sys/***@berlin
run 
(backup current controlfile for standby;
 backup as compressed backupset database;
 backup as compressed backupset archivelog all;
 duplicate target database for standby dorecover;}

alter system set log_archive_dest_state_2=enable;
alter system switch logfile;

alter database recover managed standby database using current logfile disconnect from session;

Free Video Tutorial