Installation Guide of Oracle 11gR2 Data Guard

Installation Guide of Oracle 11gR2
Data Guard (11.2.0.1) for Oracle
Enterprise Linux 5R4 over VMware
Server 2.0.2


This guide requires the existence of pre-installed Oracle Enterprise Linux EL 5 U4.

This guide requires the existence of pre-installed Oracle Database 11gR2.

Creation of profile for user Oracle on both nodes
cd
vi .bash_profile
add the following lines:
export ORACLE_SID=prod
export ORACLE_BASE=/home/oracle/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1
export TNS_ADMIN=$ORACLE_HOME/network/admin
export PATH= $ORACLE_HOME/bin :$PATH
Execute profile
. .bash_profile
In standby system ORACLE_SID will be dg

Creation of database with dbca (database creator assistant)
Before starting dbca we create a listener with network configuration assistant:
[oracle@prod ~]$ netca
You should follow the default steps

Now run dbca (database configuration assistant)
[oracle@prod ~]$ dbca

1. Choose Create a Database and click Next.
2. We will choose a pre-seeded database and then click Next.
3. Choose the Global Database Name (give dns suffix) and the SID Prefix is automatically created and then click Next.
4. Leave the default choice and click Next.
5. Choose for convenience for this exercise «Use the same password (manager1)for all the accounts» and click Next.
6. 11gR2 has strict password policies enabled by default. We will ignore this message for our test purpose and choose Yes to continue.
7. Choose File System and click Next.
8. Click Next
9. Check the sample schemas as they will be needed . Click Next
10. Click tab «Character Sets
11. Use Choose from the list character sets to pick EL8ISO8859P7 – ISO 8859-7 Latin/Greek and click Next.
12. Click Next
13. Click OK
14. A progress bar appears of the installation of the database. It will take 5-10 minutes.
15. Click Exit

Creation of Physical standby database

You only need to perform these preparatory tasks once. After you complete these

steps, the database is prepared to serve as the primary database for one or more

standby databases. You should perform the following steps:

1. Issue the following statements to determine your database's archival state, and then

put the primary database in ARCHIVELOG mode to enable automatic archiving.

archive log list

shutdown immediate;

startup mount

alter database archivelog;

alter database open;

Hellas

File Ref: Workshop_Notes_DG_11gR2_v2.doc (v. DRAFT 2A )

Creation of Physical standby database 24 of 24

Creation of Physical standby database

You only need to perform these preparatory tasks once. After you complete these

steps, the database is prepared to serve as the primary database for one or more

standby databases. You should perform the following steps:

1. Issue the following statements to determine your database's archival state, and then

put the primary database in ARCHIVELOG mode to enable automatic archiving.

archive log list

shutdown immediate;

startup mount

alter database archivelog;

alter database open;

2. Determine if FORCE LOGGING is enabled. If it is not enabled, enable FORCE

LOGGING mode. This statement may take some time to complete, because it waits

for all unlogged direct write I/O to finish. You use the following commands:

Select FORCE_LOGGING from V$DATABASE;

ALTER DATABASE FORCE LOGGING;

3. Configure redo transport authentication.

Data Guard uses Oracle Net sessions to transport redo data and control messages

between the members of a Data Guard configuration. These redo transport sessions

are authenticated using either the Secure Sockets Layer (SSL) protocol or a remote

login password file.

In this workshop, we will be using a remote login password file which will be created

in a subsequent step.

4. Configure the primary database to receive redo data, by adding the standby

logfiles to the primary.

It is highly recommended that you have one more standby redo log group than you

have online redo log groups as the primary database. The files must be the same size

or larger than the primary database’s online redo logs.

SQL> alter database add standby logfile size 50M;

Database altered.

SQL> alter database add standby logfile size 50M;

Database altered.

SQL> alter database add standby logfile size 50M;

Database altered.

SQL>.

5. Set primary database initialization parameters

On the primary database, you define initialization parameters that control the redo

transport services while the database is in the primary role. You use SQL commands

similar to the following to verify these settings:

Use the following commands to set the LOG_ARCHIVE_CONFIG and

LOG_ARCHIVE_DEST_2 indicating the primary and standby databases.

alter system set log_archive_config='dg_config=(prod,dg)';

alter system set log_archive_dest_2='service=dg async

valid_for=(online_logfile,primary_role) db_unique_name=dg';

show parameter log_archive_dest_2

show parameter log_archive_config

Next we will configure the network environment and create the physical standby

database.

Use Oracle Net Manager to create an Oracle Net service name for your physical

standby database on the prod node.

netmgr

1. Expand Local. Select Service Naming and click the green plus sign
2. Enter dg in the Net Service Name field and click Next
3. Select TCP/IP (Internet Protocol) and click Next
4. Enter dg.gr.oracle.com and click Next.
5. Enter dg.gr.oracle.com in the Service Name field and click Next
6. Click Finish

Click File -> Save Network Configuration to save the information to the

tnsnames.ora file.

We will use Oracle Net Manager to configure an entry for our standby database in

the listener.ora file.

1. Expand Listeners
2. Select LISTENER.
3. Select Database Services in the drop-down list.
4. Click Add Database.

Enter dg.gr.oracle.com in the Global Database Name field. Enter your

/home/oracle/app/oracle/product/11.2.0/dbhome_1 and your dg in the SID field.

Add also the prod database on the listener and after clicking Add Database enter

prod.gr.oracle.com in the Global Database Name field. Enter

/home/oracle/app/oracle/product/11.2.0/dbhome_1 and prod in the SID field

File -> Save Network Configuration.

Select File -> Exit.

Reload the listener.

lsnrctl reload

On the dg node run again netmgr and add dg

Copy the tnsnames.ora of the prod vm to dg with the following commands:

cd $ORACLE_HOME/network/admin

scp tnsnames.ora oracle@dg:$ORACLE_HOME/network/admin

Navigate to ORACLE_HOME/dbs folder to perform these steps.

Create temporary password file for dg database which must have same password as

primary.

cp orapwprod orapwdg

Copy the orapwdg file to dg server with the scp command

scp orapwdg oracle@dg:$ORACLE_HOME/dbs

The password file must be re-copied each time the SYSDBA or SYSOPER privilege is

granted or revoked and whenever the login password of a user with these privileges

is changed. You may need FTP, or some other remote file transfer mechanism, if you

are using different servers.

In the ORACLE_HOME/dbs folder, for the standby system, create an initialization

parameter file named initorclsby1.ora containing a single parameter: DB_NAME=dg

For the standby system, change to the /home/oracle/app/oracle/ directory. Create

the admin directory and your dg directory. Change to dg directory and create the

adump directory.

You need to also add the following directory paths:

$ORACLE_BASE/flash_recovery_area and $ORACLE_BASE/oradata on the dg

system

For the standby system, create your dg directory in $ORACLE_BASE/oradata for the

data files.

On the standby system, set the ORACLE_SID environment variable to your dg and

start the instance in NOMOUNT mode with the text initialization parameter file.

startup nomount pfile=$ORACLE_HOME/dbs/initdg.ora

On the primary system, ensure the ORACLE_SID environment variable is set to your

primary DB <SID> which should be prod

On the primary system, invoke RMAN and connect as SYSDBA to the target

database. Connect to the auxiliary database.

and run the following script:

run {

allocate channel prmy1 type disk;

allocate channel prmy2 type disk;

allocate channel prmy3 type disk;

allocate channel prmy4 type disk;

allocate channel prmy5 type disk;

allocate auxiliary channel stby1 type disk;

duplicate target database for standby from active database

spfile

parameter_value_convert 'prod','dg'

set 'db_unique_name'='dg'

set log_archive_max_processes='5'

set fal_client='dg'

set fal_server='prod'

set standby_file_management='AUTO'

set log_archive_config='dg_config=(prod,dg)'

set log_archive_dest_2='service=prod ASYNC

valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=prod'

nofilenamecheck;

sql channel prmy1 "alter system set standby_file_management=''AUTO''";

sql channel prmy1 "alter system set log_archive_max_processes=5";

sql channel prmy1 "alter system set fal_client=prod";

sql channel prmy1 "alter system set fal_server=dg";

sql channel prmy1 "alter system set standby_file_management=AUTO";

sql channel prmy1 "alter system set log_archive_config=''dg_config=(prod,dg)''";

sql channel prmy1 "alter system archive log current";

sql channel prmy1 "alter system set log_archive_dest_2=''service=dg ASYNC

valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=dg''";

allocate auxiliary channel stby2 type disk;

}

Perform a log switch on the primary database and redo will start being sent to the

standby.

On the standby system, ensure the ORACLE_SID environment variable is set to your

dg and start the MRP process with the following command:

alter database recover managed standby database using current logfile disconnect;

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

sequence#

On the primary database, issue the ALTER SYSTEM SWITCH LOGFILE statement to

force a log switch and archive the current online redo log file group.

On the standby database, re-query the V$ARCHIVED_LOG view to verify the redo

data was received and archived on the standby database:

On the standby database, query the V$ARCHIVED_LOG view to verify the archived

redo log files were actually applied. Please requery until you see a YES in the

APPLIED column.

At the completion of this step, the physical standby database is running and provides

the maximum performance level of data protection.

Enable Flashback Database on both nodes

We will enable flashback database on both prod and dg.

To enable flashback database, perform the following steps:

On the physical standby database, determine if Flashback Database is enabled by

querying V$DATABASE.

Invoke sqlplus and connect as sys/oracle. Disable the MRP process. Enable flashback

database and then restart MRP process.

If Flashback Database has not been enabled for the physical standby database, enable

it by using the ALTER DATABASE command. You will need to shutdown dg and

issue the command startup mount to issue the ALTER DATABASE command.

Do the same for the primary database by first shutting it down, mounting it, enable

flashback database and open it.