Physical Standby Dataguard Setup in Oracle 11g database

-
 Physical Standby Dataguard Setup in Oracle 11g database ---
Step1 --- create 2 virtual machine with minimum of 2 GB Ram .
Steps2 – install Vmware tool on machine to enable the copy of files in virtual machine.
Step3 – now on 1 virtual machine , create database installation of 11g and now on another machine create software only installation on another machine.
Step4—make sure both machine ip should ping successfully in order to transfer archive log from one machine to another.
Primary Server Setup
Logging
Check that the primary database is in archivelog mode.
SELECT log_mode FROM v$database;

LOG_MODE
------------
NOARCHIVELOG

SQL>
If it is noarchivelog mode, switch is to archivelog mode.
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;
Enabled forced logging by issuing the following command.
ALTER DATABASE FORCE LOGGING;
Note --
The FORCE LOGGING option is the safest method to ensure that all the changes made in the database will be captured and available for recovery in the redo logs.
Initialization Parameters
Check the setting for the DB_NAME and DB_UNIQUE_NAME parameters. In this case they are both set to "DB11G" on the primary database.
SQL> show parameter db_name

NAME     TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name     string DB11G

SQL> show parameter db_unique_name

NAME     TYPE VALUE
------------------------------------ ----------- ------------------------------
db_unique_name     string DB11G

----note
The reason why the db_name has to be the same in a primary-standby setup is because the name is "DB11G" on the datafiles. The db_unique_name is mandatory in order to make a difference between the databases and to setup a syncing. The recovery would be impossible if you use a different db_name
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 "DB11G_STBY".
ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(DB11G,DB11G_STBY)';
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.
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=db11g_stby NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DB11G_STBY';

The AFFIRM attribute specifies that all disk I/O to archived redo log files and standby redo log files is performed synchronously and must complete before the log writer process continues.
The SYNC attribute specifies that network I/O is to be performed synchronously for the destination, which means that once the I/O is initiated, the LGWR process waits for the I/O to complete before continuing. The SYNC attribute is one requirement for setting up a no-data-loss environment, because it ensures the redo records are successfully transmitted to the standby site before continuing.
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;
The LOG_ARCHIVE_FORMAT and LOG_ARCHIVE_MAX_PROCESSES parameters must be set to appropriate values and the REMOTE_LOGIN_PASSWORDFILE must be set to exclusive.
ALTER SYSTEM SET LOG_ARCHIVE_FORMAT='%t_%s_%r.arc' SCOPE=SPFILE;
ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=30;
ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE SCOPE=SPFILE;
In addition to the previous setting, it is recommended to make sure the primary is ready to switch roles to become a standby. For that to work properly we need to set the following parameters. Adjust the *_CONVERT parameters to account for your filename and path differences between the servers.
ALTER SYSTEM SET FAL_SERVER=DB11G_STBY;
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.
--ALTER SYSTEM SET DB_FILE_NAME_CONVERT='DB11G_STBY','DB11G' SCOPE=SPFILE;
--ALTER SYSTEM SET LOG_FILE_NAME_CONVERT='DB11G_STBY','DB11G'  SCOPE=SPFILE;
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;
standby_file_management=auto will ensure that all the modification of datafiles on the primary database will be reflected on the standby site.
Remember, some of the parameters are not modifiable, so the database will need to be restarted before they take effect.
Service Setup
Entries for the primary and standby databases are needed in the "$ORACLE_HOME/network/admin/tnsnames.ora" files on both servers. You can create these using the Network Configuration Utility (netca) or manually. The following entries were used during this setup.
DB11G =
 (DESCRIPTION =
   (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(HOST = ol5-112-dga1)(PORT = 1521))
   )
   (CONNECT_DATA =
     (SERVICE_NAME = DB11G.WORLD)
   )
 )

DB11G_STBY =
 (DESCRIPTION =
   (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(HOST = ol5-112-dga2)(PORT = 1521))
   )
   (CONNECT_DATA =
     (SERVICE_NAME = DB11G.WORLD)
   )
 )
Backup Primary Database
If you are planning to use an active duplicate to create the standby database, then this step is unnecessary. For a backup-based duplicate, or a manual restore, take a backup of the primary database.
$ rman target=/

RMAN> BACKUP DATABASE PLUS ARCHIVELOG;
Create Standby Controlfile and PFILE
Create a controlfile for the standby database by issuing the following command on the primary database.
ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/tmp/db11g_stby.ctl';
Create a parameter file for the standby database.
CREATE PFILE='/tmp/initDB11G_stby.ora' FROM SPFILE;
Amend the PFILE making the entries relevant for the standby database. I'm making a replica of the original server, so in my case I only had to amend the following parameters.
*.db_unique_name='DB11G_STBY'
*.fal_server='DB11G'
*.log_archive_dest_2='SERVICE=db11g ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DB11G'
Standby Server Setup (Manual)
Copy Files
Create the necessary directories on the standby server.
$ mkdir -p /u01/app/oracle/oradata/DB11G
$ mkdir -p /u01/app/oracle/fast_recovery_area/DB11G
$ mkdir -p /u01/app/oracle/admin/DB11G/adump
Copy the files from the primary to the standby server.
$ # Standby controlfile to all locations.
$ scp oracle@ol5-112-dga1:/tmp/db11g_stby.ctl /u01/app/oracle/oradata/DB11G/control01.ctl
$ cp /u01/app/oracle/oradata/DB11G/control01.ctl /u01/app/oracle/fast_recovery_area/DB11G/control02.ctl

$ # Archivelogs and backups
$ scp -r oracle@ol5-112-dga1:/u01/app/oracle/fast_recovery_area/DB11G/archivelog /u01/app/oracle/fast_recovery_area/DB11G
$ scp -r oracle@ol5-112-dga1:/u01/app/oracle/fast_recovery_area/DB11G/backupset /u01/app/oracle/fast_recovery_area/DB11G

$ # Parameter file.
$ scp oracle@ol5-112-dga1:/tmp/initDB11G_stby.ora /tmp/initDB11G_stby.ora

$ # Remote login password file.
$ scp oracle@ol5-112-dga1:$ORACLE_HOME/dbs/orapwDB11G $ORACLE_HOME/dbs
Notice, the backups were copied across to the standby server as part of the FRA copy. If your backups are not held within the FRA, you must make sure you copy them to the standby server and make them available from the same path as used on the primary server.
Start Listener
Make sure the listener is started on the standby server.
$ lsnrctl start
Restore Backup
Create the SPFILE form the amended PFILE.
$ export ORACLE_SID=DB11G
$ sqlplus / as sysdba

SQL> CREATE SPFILE FROM PFILE='/tmp/initDB11G_stby.ora';
Restore the backup files.
$ export ORACLE_SID=DB11G
$ rman target=/

RMAN> STARTUP MOUNT;
RMAN> RESTORE DATABASE;
Create Redo Logs
Create online redo logs for the standby. It's a good idea to match the configuration of the primary server.
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=MANUAL;
ALTER DATABASE ADD LOGFILE ('/u01/app/oracle/oradata/DB11G/online_redo01.log') SIZE 50M;
ALTER DATABASE ADD LOGFILE ('/u01/app/oracle/oradata/DB11G/online_redo02.log') SIZE 50M;
ALTER DATABASE ADD LOGFILE ('/u01/app/oracle/oradata/DB11G/online_redo03.log') SIZE 50M;
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;
In addition to the online redo logs, you should create standby redo logs on both the standby and the primary database (in case of switchovers). The standby redo logs should be at least as big as the largest online redo log and there should be one extra group per thread compared the online redo logs. In my case, the following standby redo logs must be created on both servers.
ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/DB11G/standby_redo01.log') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/DB11G/standby_redo02.log') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/DB11G/standby_redo03.log') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/DB11G/standby_redo04.log') SIZE 50M;
Once this is complete, we can start the apply process.

Now once this setup complete, now it’s turn to restore datafile through rman command in it.
Connect to RMAN, specifying a full connect string for both the TARGET and AUXILLARY instances. DO not attempt to use OS authentication.
$ rman TARGET sys/password@DB11G AUXILIARY sys/password@DB11G_STBY
Now issue the following DUPLICATE command.
DUPLICATE TARGET DATABASE
 FOR STANDBY
 FROM ACTIVE DATABASE
 DORECOVER
 SPFILE
   SET db_unique_name='DB11G_STBY' COMMENT 'Is standby'
   SET LOG_ARCHIVE_DEST_2='SERVICE=db11g ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DB11G'
   SET FAL_SERVER='DB11G' COMMENT 'Is primary'
 NOFILENAMECHECK;
A brief explanation of the individual clauses is shown below.
  • FOR STANDBY: This tells the DUPLICATE command is to be used for a standby, so it will not force a DBID change.
  • FROM ACTIVE DATABASE: The DUPLICATE will be created directly from the source datafile, without an additional backup step.
  • DORECOVER: The DUPLICATE will include the recovery step, bringing the standby up to the current point in time.
  • SPFILE: Allows us to reset values in the spfile when it is copied from the source server.
  • NOFILENAMECHECK: Destination file locations are not checked.
Once the command is complete, we can start the apply process.
Start Apply Process
Start the apply process on standby server.
# Foreground redo apply. Session never returns until cancel.
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE;

# Background redo apply. Control is returned to the session once the apply process is started.
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
If you need to cancel the apply process, issue the following command.
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
If you prefer, you can set a delay between the arrival of the archived redo log and it being applied on the standby server using the following commands.
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DELAY 30 DISCONNECT FROM SESSION;

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE NODELAY DISCONNECT FROM SESSION;
Provided you have configured standby redo logs, you can start real-time apply using the following command.
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE;
Test Log Transport
On the primary server, check the latest archived redo log and force a log switch.
ALTER SESSION SET nls_date_format='DD-MON-YYYY HH24:MI:SS';

SELECT sequence#, first_time, next_time
FROM   v$archived_log
ORDER BY sequence#;

ALTER SYSTEM SWITCH LOGFILE;
Check the new archived redo log has arrived at the standby server and been applied.
ALTER SESSION SET nls_date_format='DD-MON-YYYY HH24:MI:SS';

SELECT sequence#, first_time, next_time, applied
FROM   v$archived_log
ORDER BY sequence#;



dont forget to comment for your issues :)

Comments

Post a Comment

Popular posts from this blog

Query for Tablespace usage with Autoextend

-
                                         Monitoring Tablespace Usage in Oracle I often see questions in Oracle newsgroups and forums about monitoring space in tablespaces, and many of those questions are regarding Enterprise Manager 12c and the alerts it generates reporting the percentage of used space. Of course how you monitor free space depends on whether or not the datafiles are autoextensible. Let's look at several ways to monitor free and used space, starting with methods used in older releases. In versions prior to Oracle 10 DBA_TABLESPACES, DBA_DATA_FILES and DBA_FREE_SPACE were used to generate such reports. One script, from MOS, uses these three views to compute the used space percentage: SELECT SUM(s.used_gb)/SUM(f.total_gb)*100 FROM dba_tablespaces t, ( SELECT tablespace_name, SUM(NVL(bytes,0))/(1024*1024*1024) total_gb FROM dba_data_files ...
Read more

How to configure multiple Oracle listeners

-
How to configure multiple Oracle listeners It happened to me few times during the stress tests that Oracle listener became a bottleneck and not be able to handle the required workload. It was resolved by creating  multiple  listeners which appeared to be a quick solution.  Here is a short step-by-step procedure to configure multiple Oracle listeners  on Solaris for standalone Oracle 9i and 10g environment. 1)  First of all, add additional NIC and cables. They can be on separate subnetwork or the same. In the later, make sure to set static route if needed.    2) Assume that we are going to configure two listeners,   LISTENER and LISTENER2 Modify listener.ora and tnsnames.ora as following: Here is a sample of   listener.ora  LISTENER =   (DESCRIPTION_LIST =     (DESCRIPTION =       (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.3)(PORT = 1521))     ) ...
Read more

sheel script - automatic tablespace addition in oracle 11g

-
      sheel script - automatic tablespace addition in oracle 11g #!/bin/bash a=`sqlplus -s / as sysdba <<END set pagesize 0 feedback off verify off heading off echo off; select tablespace_name from dba_segments group by tablespace_name having SUM(BYTES)/1024/1024>700; END` f=${#a} c=0 for file in $a ; do eval "var$c=$file"; c=$((c+1)); done echo $var0 array1=$(df -kh | grep '/u0*'|awk '{ print $6}') array2=$(df -kh | grep '/u0*'|awk '{ print $5}') size=${#array1} size1=${#array2} if ["$size" -eq 4] && ["$size1" -eq 3] && ["$array2" -gt 85%] && ["$f" -eq 1] then d=${array1[0]:0:4} sqlplus /nolog <<END connect /as sysdba alter tablespace $a add datafile '$d/$a01.dbf' size 10m autoextend on maxsize 28G; END` fi if ["$size" -eq 9] && ["$size1" -eq 3] && ["$array2 -gt...
Read more