One is All You Need

I got a little press in the July/August 2008 edition of Oracle magazine about my presentation at Collaborate and I thought it would be prudent to shamelessly refer you to my presentation. Additionally, I will be presenting this same topic at the Northern California Oracle Users Group Summer Conference on August 21 in San Ramon.

Aces in the Hole

Greg

Greg Pike

Step by Step Guide for Installing a Data Guard/ Data Guard Broker Environment

This focuses on setting up a test environment on a single node. The DBA can adapt this to set up a production environment.

By Roger Lenihan

Step 1:

Install an Oracle database and validate it is in Archive log Mode.

If Archive log is disabled enable it before continuing.

SQL> archive log list

Database log mode             Archive Mode

Automatic archival            Enabled

Archive destination           C:\oracle\product\10.2.0\archive

Oldest online log sequence    66

Next log sequence to archive  68

Current log sequence          68

Step 2:

Enable force logging mode.

SQL> alter database force logging;

Database altered.

Step 3:

Identify all the datafiles to copy for a cold back up or do an RMAN backup.

Since this is test environment I will shutdown the database and do a cold backup.

SQL> select name from v$datafile;

NAME

———————————————————————-

C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\SYSTEM01.DBF

C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\UNDOTBS01.DBF

C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\SYSAUX01.DBF

C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\USERS01.DBF

C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\EXAMPLE01.DBF

SQL>

Step 4:

In a windows environment create the service for the standby database. You will also have to create the folders and environment for the standby database.

C:\>oradim -new -sid stby -intpwd teststby -startmode manual

C:\oracle\product\10.2.0\oradata\teststby – Create this folder

C:\oracle\product\10.2.0\admin\teststby – Create this folder and all the folders underneath. Like bdump, udump, etc. Or just copy the production one.

Note: Since this is a dataguard environment on a single test node I do not have to install all the Oracle software again.

Step 5:

Shutdown Primary database and move the datafiles

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

Move the datafiles from step 3 into the oradata standby environment.

C:\oracle\product\10.2.0\oradata\test

C:\oracle\product\10.2.0\oradata\teststby

Step 6:

Create a standby control file and start the database

SQL> startup mount;

SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS

    ‘ C:\oracle\product\10.2.0\oradata\teststby\teststby.ctl’;

Database altered.

SQL> alter database open;

Step 7:

Create the Primary and Standby init.ora/spfile to facilitate Dataguard.

SQL> CREATE PFILE= ‘C:\oracle\product\10.2.0\testinit.ora’

from spfile;

File created.

Primary Database:

Make the corresponding changes: 

*.aq_tm_processes=1

*.background_dump_dest='C:\oracle\product\10.2.0/admin/test/bdump'

*.compatible='10.2.0.1.0'

*.control_files='C:\oracle\product\10.2.0/oradata/test/\control01.ctl'

*.core_dump_dest='C:\oracle\product\10.2.0/admin/test/cdump'

*.db_domain=''

*.db_file_multiblock_read_count=16

*.db_name='test'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=testXDB)'

*.fast_start_mttr_target=300

*.instance_name='test'

*.db_unique_name=’test’

*.job_queue_processes=10

*.LOG_ARCHIVE_CONFIG=’DG_CONFIG=(test,teststb)’

*.LOG_ARCHIVE_DEST_1=’location=C:\oracle\archive arch mandatory
valid_for=(ONLINE_LOGFILES,ALL_ROLES) db_unique_name=test’

*.LOG_ARCHIVE_DEST_2=’service=teststby lgwr sync affirm net_timeout=30
valid_for=(ONLINE_LOGFILES,ALL_ROLES) db_unique_name=teststby’

*.log_archive_dest_state_1=’ENABLE’

*.log_archive_dest_state_2=’ENABLE’

*.open_cursors=300

*.pga_aggregate_target=25165824

*.processes=150

*.query_rewrite_enabled='FALSE'

*.remote_login_passwordfile='EXCLUSIVE'

*.shared_pool_size=50331648

*.sort_area_size=524288

*.star_transformation_enabled='FALSE'

*.timed_statistics=TRUE

*.undo_management='AUTO'

*.undo_retention=10800

*.undo_tablespace='UNDOTBS1'

*.user_dump_dest='C:\oracle\product\10.2.0/admin/test/udump'

*.sga_target=611319808

*.fal_server=teststby

*.fal_client=test

*.db_file_name_convert=’C:\oracle\product\10.2.0\oradata\teststby’,
‘C:\oracle\product\10.2.0\oradata\test’

*.log_file_name_convert=’C:\oracle\product\10.2.0\oradata\teststby’,
‘C:\oracle\product\10.2.0\oradata\test’

Standby Database:

Create a standby init.ora from a copy of the primary one and make the necessary changes below.

*.aq_tm_processes=1

*.background_dump_dest=’C:\oracle\product\10.2.0/admin/teststby/bdump’

*.compatible=’10.2.0.1.0′

*.control_files=’C:\oracle\product\10.2.0/oradata/teststby/\control01.ctl’

*.core_dump_dest=’C:\oracle\product\10.2.0/admin/teststby/cdump’

*.db_domain=”

*.db_file_multiblock_read_count=16

*.db_name=’test’

*.DB_UNIQUE_NAME=’teststby’

*.dispatchers=’(PROTOCOL=TCP) (SERVICE=testXDB)’

*.fast_start_mttr_target=300

*.instance_name=’teststby’

*.job_queue_processes=10

*.LOG_ARCHIVE_DEST_1=’location=C:\oracle\archive2 arch mandatory valid_for=(ONLINE_LOGFILES,ALL_ROLES) db_unique_name=teststby’

*.LOG_ARCHIVE_DEST_2=’service=test lgwr sync affirm net_timeout=30 valid_for=(ONLINE_LOGFILES,ALL_ROLES) db_unique_name=test’

*.log_archive_dest_state_1=’ENABLE’

*.log_archive_dest_state_2=’ENABLE’

*.open_cursors=300

*.pga_aggregate_target=25165824

*.remote_login_passwordfile=’EXCLUSIVE’

*.processes=150

*.query_rewrite_enabled=’FALSE’

*.shared_pool_size=50331648

*.sort_area_size=524288

*.star_transformation_enabled=’FALSE’

*.timed_statistics=TRUE

*.undo_management=’AUTO’

*.undo_retention=10800

*.undo_tablespace=’UNDOTBS1′

*.user_dump_dest=’C:\oracle\product\10.2.0/admin/teststby/udump’

*.sga_target=611319808

*.standby_archive_dest=’location=c:\oracle\product\10.2.0\archive’

*.db_file_name_convert=’C:\oracle\product\10.2.0\oradata\test’,

‘C:\oracle\product\10.2.0\oradata\teststby’

*.log_file_name_convert=’C:\oracle\product\10.2.0\oradata\test’,

‘C:\oracle\product\10.2.0\oradata\teststby’

*.standby_file_management=AUTO

*.log_archive_config=’DG_CONFIG=(test,teststby)’

*.service_names=teststby

*.fal_server=test

*.fal_client=teststby

Step 8:

Convert the control file created from step 6 to the name and location of control file parameter in the new standby init.ora.

C:\oracle\product\10.2.0\oradata\teststby\teststby.ctl

to

C:\oracle\product\10.2.0\oradata\teststby\control01.ctl

Step 9:

Configure the Listener.ora file. Since the example is on the same node I have both the standby and primary in the listener.ora. If this was a production environment you would create the listener for primary and standby separate.

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))

)

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = RLLAPTOP01)(PORT = 1521))

)

)

)

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(SID_NAME = PLSExtProc)

(ORACLE_HOME = C:\oracle\product\10.2.0)

(PROGRAM = extproc)

)

(SID_DESC =

(GLOBAL_DBNAME = test)

(ORACLE_HOME = C:\oracle\product\10.2.0)

(SID_NAME = test)

)

(SID_DESC =

(GLOBAL_DBNAME = teststby)

(ORACLE_HOME = C:\oracle\product\10.2.0)

(SID_NAME = teststby)

)

(SID_DESC =

(GLOBAL_DBNAME = teststby_DGMGRL)

(ORACLE_HOME = C:\oracle\product\10.2.0)

(SID_NAME = teststby)

)

(SID_DESC =

(GLOBAL_DBNAME = test_DGMGRL)

(ORACLE_HOME = C:\oracle\product\10.2.0)

(SID_NAME = test)

)

)

Note: The DGMGRL entries are for the Data Guard Broker to be able to start and stop the databases on switchovers.

Step 10:

Stop and restart the listener

C:\>lsnrctl stop

LSNRCTL for 32-bit Windows: Version 10.2.0.1.0 - Production on 30-MAR-2008 10:40

:55

Copyright (c) 1991, 2005, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC0)))

The command completed successfully

C:\>lsnrctl start

LSNRCTL for 32-bit Windows: Version 10.2.0.1.0 - Production on 30-MAR-2008 10:41

:32

Copyright (c) 1991, 2005, Oracle. All rights reserved.

Starting tnslsnr: please wait…

TNSLSNR for 32-bit Windows: Version 10.2.0.1.0 - Production

System parameter file is C:\oracle\product\10.2.0\db_1\network\admin\listener.or

a

Log messages written to C:\oracle\product\10.2.0\db_1\network\log\listener.log

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC0ipc

)))

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rllaptop01)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC0)))

STATUS of the LISTENER

————————

Alias LISTENER

Version TNSLSNR for 32-bit Windows: Version 10.2.0.1.0 - Produ

ction

Start Date 30-MAR-2008 10:41:34

Uptime 0 days 0 hr. 0 min. 1 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File C:\oracle\product\10.2.0\db_1\network\admin\listener.o

ra

Listener Log File C:\oracle\product\10.2.0\db_1\network\log\listener.log

Listening Endpoints Summary…

(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC0ipc)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rllaptop01)(PORT=1521)))

Services Summary…

Service “PLSExtProc” has 1 instance(s).

Instance “PLSExtProc”, status UNKNOWN, has 1 handler(s) for this service…

Service “test” has 1 instance(s).

Instance “test”, status UNKNOWN, has 1 handler(s) for this service…

Service “test_DGMGRL” has 1 instance(s).

Instance “test”, status UNKNOWN, has 1 handler(s) for this service…

Service “teststby” has 1 instance(s).

Instance “teststby”, status UNKNOWN, has 1 handler(s) for this service…

Service “teststby_DGMGRL” has 1 instance(s).

Instance “teststby”, status UNKNOWN, has 1 handler(s) for this service…

The command completed successfully

Step 11:

Add the standby database to the tnsnames.ora file.

teststby =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = RLLAPTOP01)(PORT = 1521))

)

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = teststby)

)

)

Step 12:

Shutdown the primary database and start again with the new init.ora.

Convert it back to a spfile.

C:\> set oracle_sid=test

C:\> sqlplus

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup pfile=C:\oracle\product\10.2.0\testinit.ora;

ORACLE instance started.

Total System Global Area 612368384 bytes

Fixed Size 1250428 bytes

Variable Size 83889028 bytes

Database Buffers 520093696 bytes

Redo Buffers 7135232 bytes

Database mounted.

Database opened.

SQL> create spfile from pfile=c:\oracle\product\10.2.0\testinit.ora;

Database altered.

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> Startup;

ORACLE instance started.

Total System Global Area 612368384 bytes

Fixed Size 1250428 bytes

Variable Size 83889028 bytes

Database Buffers 520093696 bytes

Redo Buffers 7135232 bytes

Database mounted.

Database opened.

Note: You have to use a spfile to use Data Guard Broker.

Step 13:

Create standby redo logs if you want to run the configuration in MaxAvailibility or MaxProtection. Basically you have to create them if you want to run in SYNC mode.

SQL> Select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME

———- ———- ———- ———- ———- ——– —————- ————- ———–

1 1 108 52428800 1 NO CURRENT 658611 4/9/2008 10

2 1 106 52428800 1 YES ACTIVE 658530 4/9/2008 10

3 1 107 52428800 1 YES ACTIVE 658552 4/9/2008 10

SQL> Select * from v$logfile;

GROUP# STATUS TYPE MEMBER IS_RECOVERY_DEST_FILE

———- ——- ——- ——————————————————————————– ———————

3 ONLINE C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\REDO03.LOG NO

2 ONLINE C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\REDO02.LOG NO

1 ONLINE C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\REDO01.LOG NO

Now we create the standby redologs

SQL> alter database add standby logfile ‘C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\REDOSTB01.LOG’
size 52428800;

SQL> alter database add standby logfile ‘C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\REDOSTB02.LOG’
size 52428800;

SQL> alter database add standby logfile ‘C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\REDOSTB03.LOG’
size 52428800;

Once you mount standby you will have to open it in read only and issue the above commands
in the standby database location. 

i.e –

SQL> alter database add standby logfile ‘C:\ORACLE\PRODUCT\10.2.0\ORADATA\TESTSTBY\REDOSTB01.LOG’ size 52428800;

--- Do this for all 3

Step 14:

Start up the standby database using its init.ora file that you created in step 7.

Convert the init.ora file to a spfile.

Shut down the database and restart it using the spfile.

C:\> set oracle_sid=teststby

C:\> sqlplus

SQL> startup nomount pfile=C:\oracle\product\10.2.0\teststbyinit.ora;

ORACLE instance started.

Total System Global Area 612368384 bytes

Fixed Size 1250428 bytes

Variable Size 67111812 bytes

Database Buffers 536870912 bytes

Redo Buffers 7135232 bytes

SQL> alter database mount standby database;

Database altered.

SQL> create spfile from pfile=c:\oracle\product\10.2.0\teststbyinit.ora;

Database altered.

SQL> Shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> Startup nomount;

ORACLE instance started.

Total System Global Area 612368384 bytes

Fixed Size 1250428 bytes

Variable Size 67111812 bytes

Database Buffers 536870912 bytes

Redo Buffers 7135232 bytes

Now the standby database is started under the spfile.

Step 15:

Mount the standby database and recover it.

This will bring the standby database into standby mode.

SQL> alter database mount standby database;

Database altered.

SQL> recover managed standby database disconnect from session;

Media recovery complete.

SQL>

—- Go back to step 13 and add standby redo logs on standby database.

Step 16:

Now that Data Guard is all configured, we set up the Broker component.

First, we create the configuration.

C:\>dgmgrl

DGMGRL for 32-bit Windows: Version 10.2.0.1.0 - Production

Copyright (c) 2000, 2005, Oracle. All rights reserved.

Welcome to DGMGRL, type “help” for information.

DGMGRL> connect sys@test

Password:

Connected.

DGMGRL> CREATE CONFIGURATION ‘DGBTEST’ AS

PRIMARY DATABASE IS ‘TEST’

CONNECT IDENTIFIER IS TEST;

Configuration “DGBTEST’ created with primary database “TEST”

Step 17:

We add the standby database to the broker configuration.

DGMGRL> ADD DATABASE ‘TESTSTBY’ AS

CONNECT IDENTIFIER IS TESTSTBY

MAINTAINED AS PHYSICAL;

Database “TESTSTBY” added.

Step 18:

Enable the configuration.

DGMGRL> enable configuration;

Enabled.

DGMGRL>

Step 19:

Verify the broker configuration is set up correctly and running.

DGMGRL> show configuration

Configuration

Name: dgbtest

Enabled: YES

Protection Mode: MaxAvailability

Fast-Start Failover: DISABLED

Databases:

test - Primary database

teststby - Physical standby database

Current status for “dgbtest”:

SUCCESS

DGMGRL>

Now we have a fully functional Data Guard and Broker configuration. If you have any questions or any ideas to make this document better please contact me at rlenihan@piocon.com