Oracle Database 11g Data Guard Configuration on Linux









Basic Information of Machines


1. Primary DB:

Database Name: ORCL
Unique Name: Primary
Machine IP: 192.168.0.1
TNS Service Name: toStandby

2. Standby DB:

 Database Name: ORCL
 Unique Name: Standby
 Machine IP: 192.168.0.2
 TNS Service Name: toPrimary


Configuring For Remote Installation


To configure Secure Shell:

 1. Create the public and private keys on all nodes:

 [PrimaryDB]$ /usr/bin/ssh-keygen -t dsa
 [StandbyDB]$ /usr/bin/ssh-keygen -t dsa

2. Concatenate id_dsa.pub for all nodes into the authorized_keys file on the first node:

[PrimaryDB]$ ssh 192.168.0.1 "cat ~/.ssh/id_dsa.pub" >> ~/.ssh/authorized_keys
[PrimaryDB]$ ssh 192.168.0.2 "cat ~/.ssh/id_dsa.pub" >> ~/.ssh/authorized_keys

3. Copy the authorized_keys file to the other nodes:

[PrimaryDB]$ scp ~/.ssh/authorized_keys 192.168.0.2:/home/oracle/.ssh/

4. Copy ~/.ssh folder on all nodes.
CONFIGURATION ON PRIMARY DATABASE





Step # 1.



SQL> Shutdown Immediate;
SQL> Startup Mount;
SQL> Alter Database ArchiveLog;



Step # 2.



Now Create a standby controlfile on the PRIMARY site.



SQL> Alter Database Create Standby Controlfile as ‘/opt/oracle/backup/standcontrol.ctl’;



Now copy this new created standby control file to the standby site where other database file like

datafiles, logfiles and control files are located. Rename this file to Control01.ctl, Control02.ctl, Control03.ctl.




Step # 3.



Create pfile from spfile on the primary database:



SQL> Create pfile from spfile=’/opt/oracle/OraDB11g/dbs/spfileORCL.ora’;



Step # 4.



Then make/add following settings in the initORCL.ora file on the PRIMARY Machine.



db_unique_name=’PRIMARY’
FAL_Client=’toPrimary’
FAL_Server=’toStandby’
Log_archive_config=’DG_CONFIG=(primary,standby)’
Log_archive_dest_1=’Location=/opt/oracle/backup VALID_FOR=(ALL_LOGFILES,ALL_ROLES) db_unique_name=primary’
Log_archive_dest_2=’Service=toStandby lgwr async VALID_FOR = (ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=standby’
Log_archive_dest_state_1=ENABLE
Log_archive_dest_state_2=DEFER
Service_names=’primary’
Standby_File_Management=’AUTO’



Step # 5



Create password file using ‘bash’.



$ orapwd file=/opt/oracle/OraDB11g/dbs/orapwORCL password=oracle entries=5 ignorecase=y force=y




Force option is used to replace an existing password file.



Step # 6.



Now startup the PRIMARY database to MOUNT stage.



SQL> Shutdown Immediate;
SQL> Startup Mount;
SQL> Alter Database Force Logging;



Step # 7



On the PRIMARY site, also create standby redo logfile for the Standby database. This Standby redo logfile will be used for Datagurad Observer later on. If you don’t want to use DG broker, then there is no need to created standby redo logfile.



SQL> Alter Database Add Standby Logfile (‘/opt/oracle/oradata/ORCL/StandbyRedo.log’) size 150m;



Step # 8



Now shutdown the primary database



SQL> Shutdown Immediate;



Copy all the datafiles and standby redo logfile from PRIMARY site to the same location on the STANDBY site. Then again startup the PRIMARY database to mount stage.



Step # 9



Now shutdown the primary database
SQL> Shutdown Immediate;



Create spFile from pFile.



SQL> Create spfile from pfile;



Restart the Primary database.



Step # 10



SQL> Startup;



Step # 11



Now on PRIMARY site create a service in TNSnames.ora file through which the PRIMARY site will be connected to Standby machine.



TOSTANDBY =

(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCAL = TCP) (HOST = 192.168.0.2) (PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = Standby)
)
)




Step # 12



Register the Primary Database in the Listener.ora file. Then stop and start the listener.



> lsnrctl stop
> lsnrctl start




Step # 13



Query the DATABASE_ROLE column from V$DATABASE to new the role of Primary database. It should return ‘PRIMARY’.



SQL> select database_role from v$database;


DATABASE_ROLE
-------------------------
PRIMARY





Step # 14



Also check the connectivity from the SQL prompt.



SQL> connect sys/oracle@toStandby as sysdba;
Connected.



Service can also be created through Net Manager utility available with Oracle Server. Connectivity can also be checked there.
CONFIGURATION ON STANDBY DATABASE







Step # 1



Check the mode of archiving by following command:



SQL> Archive Log List;



Step # 2



Then create pfile from spfile on the standby database:



SQL> Create pfile from spfile=’/opt/oracle/OraDB11g/dbs/spfileStandby.ora’;



Step # 3



Then make/add following settings in the initORCL.ora file on the STANDBY Machine.



db_unique_name=’STANDBY’
FAL_Client=’toStandby’
FAL_Server=’toPrimary’
Log_archive_config=’DG_CONFIG=(primary,standby)’
Log_archive_dest_1=’Location=/opt/oracle/backup VALID_FOR=(ALL_LOGFILES,ALL_ROLES) db_unique_name=standby’
Log_archive_dest_2=’Service=toprimary VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=primary’
Log_archive_dest_state_1=ENABLE
Log_archive_dest_state_2=ENABLE
Service_names=’standby’
Standby_File_Management=’AUTO’




Step # 4



Create password file using ‘bash’.



$ orapwd file=/opt/oracle/OraDB11g/dbs/orapwORCL password=oracle entries=5 ignorecase=y force=y



Force option is used to replace an existing password file.



Step # 5



Now on STANDBY site create a service in TNSnames.ora file through which the STANDBY site will be connected to PRIMARY machine.




TOPRIMARY =

(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCAL = TCP) (HOST = 192.168.0.1) (PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = primary)
)
)




Step # 6



Check the connectivity from the SQL prompt.



SQL> Connect sys/oracle@toPrimary as sysdba;
Connected.



Service can also be created through Net Manager utility available with Oracle Server. Connectivity can also be checked there.



Step # 7



Register the Primary Database in the Listener.ora file. Then stop and start the listener.



> lsnrctl stop
> lsnrctl start



Step # 8



Now shutdown the primary database



SQL> Shutdown Immediate;



Create spFile from pFile.



SQL> Create spfile from pfile;



Restart the Primary database.




Step # 9



Now startup the STANDBY database to mount stage.



SQL> Startup NoMount;
SQL> Alter Database Mount Standby Database;



Enable Force logging.



SQL> Alter Database Force Logging;



Step # 10



Query the DATABASE_ROLE column from V$DATABASE to new the role of Standby database. It should return ‘PHYSICAL STANDBY’.



SQL> Select Database_role from v$Database;




DATABASE_ROLE
-------------------------
PHYSICAL STANDBY






LOG SHIPPING




On PRIMARY site enable log_archive_dest_state_2 to start shipping archive redo logs.



SQL> Alter system set Log_Archvie_Dest_State_2=ENABLE scope=both;



Check the sequence # and the archiving mode by executing following command.



SQL> Archive Log List;



Then switch the logfile.



SQL> Alter System Switch Logfile;



System Altered.



Now on the primary site check the status of Standby Archiving destination.



SQL> Select Status, Error from v$Archive_dest where dest_id=2;



The STATUS should return – VALID. If it return Error, then check the connectivity between the Primary and standby machines.






START PHYSICAL LOG APPLY SERVICE





On the STANDBY database execute the following command to start Managed Recover Process (MRP). This command is executed on mount stage.


SQL> Shutdown Immediate;
SQL> Startup Mount;


SQL> Alter Database Recover Managed Standby Database;

Database Altered.



By executing the above command the current session will become hanged because MRP is a foreground recovery process. It waits for the logs to come and apply them. To avoid this hanging, you can execute the following command with DISCONNECT option.



SQL> Alter Database Recover Managed Standby Database Disconnect;



Now the session will be available to you and MRP will work as a background process and apply the redo logs.



You can check whether the log is applied or not by querying v$archived_log



SQL> Select Name, Applied, Archived from v$Archived_Log;



This query will return the name of archived files and their status of being archived and applied.




Oracle 11g Data Guard Configuration Done.

Comments

Post a Comment

Popular posts from this blog

What is Oracle Integration Cloud Service - ICS?

Image
Oracle Integration Cloud Service (ICS) is a solution that enables you to simplify integrations between cloud applications, and between cloud and on premises applications. It helps you create connections to well-known and less-known SaaS and PaaS applications, using the available cloud adapters, publish or subscribe to the Messaging Cloud Service, or use industry standards such as SOAP, REST, FTP, File, and JMS. For more information watch this Oracle video.
Read more

How to Create Packages in Oracle Database using TOAD for Oracle

Image
What are Packages in Oracle Database A package is a group of procedures, functions, variables and SQL statements created as a single unit. It is used to store together related objects. A package has two parts, Package Specification and Package Body. Package Specification Package Specification acts as an interface to the package. Declaration of types, variables, constants, exceptions, cursors and subprograms is done in Package specifications. Package specification does not contain any code. Package Body Package body is used to provide implementation for the subprograms, queries for the cursors declared in the package specification.
Read more

How to create a Simple Scheduler Job in Oracle Database using Toad

Image
Scheduler is a process which runs programmed jobs at scheduled times. This process wakes up at the specified time and runs automatically. The basic elements of the scheduler are: Jobs Schedules Programs A job specifies what needs to be executed and when. A program is a collection of metadata that will be executed by the scheduler. It contains the name of the program (a procedure or an executable, the type of the program (pl/sql block, shell script etc). A schedule specifies when and how many times a job is executed. 1. Login to the database using Toad, and create a Scheduler Job as follows;
Read more