Creating a data guard configuration

Creating a Data Guard Physical Standby environment

The Enviroment

Two Solaris 5.10 servers
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
ssh is configured for user oracle on both nodes
Oracle Home is on identical path on both nodes


-Primary Database Steps


SQL> select name from v$database;


NAME
———
Cobra


SQL> show parameters unique


NAME TYPE VALUE
———————————— ———– ——————————
db_unique_name string cobra


–Put database in force logging: which takes precedence and all operations are log.ed into the redo logs.


SQL>alter database force logging;
Database altered.


–Create a password file on primary database and copy it standby because sys password should be same on both sites which tends to ship and apply archive logs from primary to standby.


#orapwd file=orapw password=oracle force=y


–Create standby redo log with the same size as online redo logs which enable Data Guard Maximum Availability and Maximum Protection modes.


SQL>select * from v$logfile;

GROUP# STATUS TYPE MEMBER IS_
———- ——- ——- —————————————- —
3 ONLINE /data02/oradata/cobra/redo03.log NO
2 ONLINE /data02/oradata/cobra/redo02.log NO
1 ONLINE /data02/oradata/cobra/redo01.log NO


SQL> select bytes from v$log;


BYTES
———-
52428800
52428800
52428800


SQL>ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 ‘/data02/oradata/cobra/redo04std.log’ SIZE 50M;


SQL>ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 ‘/data02/oradata/cobra/redo05std.log’ SIZE 50M;


SQL>ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 ‘/data02/oradata/cobra/redo06std.log’ SIZE 50M;


SQL>select * from v$logfile;


GROUP# STATUS TYPE MEMBER IS_
———- ——- ——- —————————————- —
3 ONLINE /data02/oradata/cobra/redo03.log NO
2 ONLINE /data02/oradata/cobra/redo02.log NO
1 ONLINE /data02/oradata/cobra/redo01.log NO
4 STANDBY /data02/oradata/cobra/redo04std.log NO
5 STANDBY /data02/oradata/cobra/redo05std.log NO
6 STANDBY /data02/oradata/cobra/redo06std.log NO

6 rows selected.


–Enable Archivelog mode:

SQL>shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.


SQL>startup mount
ORACLE instance started.

Total System Global Area 4194304000 bytes
Fixed Size 2045896 bytes
Variable Size 2600470584 bytes
Database Buffers 1577058304 bytes
Redo Buffers 14729216 bytes

Database mounted.


SQL>alter database archivelog;
Database altered.


SQL>alter system set log_archive_dest_1=’LOCATION=/data02/oradata/cobra/archives’ scope=both;


SQL>shu immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.


–Now copy all datafiles from primary database to standby database.However primary database must be shutdown before the entire files can be copied.

–Create pfile for Primary:


<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
cobra.__db_cache_size=1560281088cobra.__java_pool_size=33554432
cobra.__large_pool_size=50331648
cobra.__shared_pool_size=2499805184
cobra.__streams_pool_size=33554432

*.audit_file_dest=’/export/home/oracle/admin/cobra/adump’
*.background_dump_dest=’/export/home/oracle/admin/cobra/bdump’
*.compatible=’10.2.0.3.0′
*.control_files=’/data02/oradata/cobra/control01.ctl’,’/data02/oradata/cobra/control02.ctl’,’/data02/oradata/cobra/control03.c
tl’
*.core_dump_dest=’/export/home/oracle/admin/cobra/cdump’
*.db_block_size=8192
*.db_domain=”
*.db_file_multiblock_read_count=16
*.db_name=’cobra’
*.db_unique_name=’cobra’
*.fal_client=’cobra’
*.fal_server=’cobrastd’
*.log_archive_config=’DG_CONFIG=(cobra,cobrastd)’
*.log_archive_dest_1=’LOCATION=/data02/oradata/cobra/archives
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=cobra’
*.log_archive_dest_2=’SERVICE=cobrastd LGWR ASYNC
VALID_FOR=(ALL_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=cobrastd’
*.log_archive_dest_state_1=’ENABLE’
*.log_archive_dest_state_2=’ENABLE’
*.log_archive_format=’%t_%s_%r.dbf’
*.db_file_name_convert=’/xdata02/oradata/cobrastd’,’/data02/oradata/cobra/’
*.log_file_name_convert=’/xdata02/oradata/cobrastd’,’/data02/oradata/cobra/’
cobra.log_archive_format=’%t_%s_%r.dbf’
cobra.standby_archive_dest=’/data02/oradata/cobra/archives’
*.standby_file_management=’AUTO’
*.db_recovery_file_dest=’/export/home/oracle/flash_recovery_area’
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=cobraXDB)’
*.job_queue_processes=10
*.local_listener=’LISTENER_COBRA’
*.open_cursors=300
*.pga_aggregate_target=314572l8000
*.processes=1000
*.remote_login_passwordfile=’EXCLUSIVE’
*.sga_target=4194304000
*.undo_management=’AUTO’
*.undo_tablespace=’UNDOTBS1′
*.user_dump_dest=’/export/home/oracle/admin/cobra/udump’
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

SQL>startup mount pfile= ‘/export/home/oracle/product/10.2.0/db_1/dbs/initcobra.ora’;
ORACLE instance started.

Total System Global Area 4194304000 bytes
Fixed Size 2045896 bytes
Variable Size 2617247800 bytes
Database Buffers 1560281088 bytes
Redo Buffers 14729216 bytes
Database mounted.


SQL>create spfile from pfile;
File created.

SQL>shu immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.


SQL>startup
ORACLE instance started.


Total System Global Area 4194304000 bytes
Fixed Size 2045896 bytes
Variable Size 2617247800 bytes
Database Buffers 1560281088 bytes
Redo Buffers 14729216 bytes
Database mounted.
Database opened.

SQL>startup mount
ORACLE instance started.
Total System Global Area 4194304000 bytes
Fixed Size 2045896 bytes
Variable Size 2617247800 bytes
Database Buffers 1560281088 bytes
Redo Buffers 14729216 bytes
Database mounted.

–Creating standby database controlfile:

The standby database will use a control file that is generated on the primary database.

SQL>ALTER DATABASE CREATE STANDBY CONTROLFILE AS
‘/xdata02/oradata/cobrastd/controlstd01.ctl’;


Database altered.


NOTE: The created file must meet two conditions:
Its filename must be different from any other control file.
Must be created after the backup of the datafiles.


SQL>alter database open;
Database altered.


>>>>>>>>>>>>>>>>>>>>>..


——-STANDBY SITE———–

–Creating pfile for standby:


<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
cobrastd.__db_cache_size=2449473536
cobrastd.__java_pool_size=33554432
cobrastd.__large_pool_size=167772160
cobrastd.__shared_pool_size=4009754624
cobrastd.__streams_pool_size=33554432
*.audit_file_dest=’/export/home/oracle/admin/cobrastd/adump’
*.background_dump_dest=’/export/home/oracle/admin/cobrastd/bdump’
*.compatible=’10.2.0.4.0′
*.control_files=’/xdata02/oradata/cobrastd/controlstd01.ctl’,’/xdata02/oradata/cobrastd/controlstd02.ctl’,’/xdata02/oradata/cobrastd/contr
olstd03.ctl’
*.core_dump_dest=’/export/home/oracle/admin/cobrastd/cdump’
*.db_block_size=8192
*.db_domain=”
*.db_file_multiblock_read_count=16
*.db_name=’cobra’
*.db_unique_name=’cobrastd’
*.fal_client=’cobrastd’
*.fal_server=’cobra’
*.log_archive_config=’DG_CONFIG=(cobrastd,cobra)’
*.log_archive_dest_1=’LOCATION=/xdata02/oradata/cobrastd/archives
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=cobrastd’
*.log_archive_dest_2=’SERVICE=cobra LGWR ASYNC
VALID_FOR=(ALL_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=’cobra’
*.log_archive_dest_state_1=’ENABLE’
*.log_archive_dest_state_2=’ENABLE’
*.log_archive_format=’%t_%s_%r.dbf’
*.db_file_name_convert= ‘/data02/oradata/cobra/’, ‘/xdata02/oradata/cobrastd/’

*.log_file_name_convert=’/data02/oradata/cobra/’, ‘/xdata02/oradata/cobrastd/’
nsnstd.log_archive_format=’%t_%s_%r.dbf’
nsnstd.standby_archive_dest=’/xdata02/oradata/cobrastd/archives’
*.standby_file_management=’AUTO’
*.dispatchers='(PROTOCOL=TCP)(DISPATCHERS=5)’
*.fast_start_mttr_target=150

*.job_queue_processes=10
*.max_dispatchers=5
*.open_cursors=600
*.optimizer_index_cost_adj=40
*.pga_aggregate_target=3145728000
*.processes=1500
*.remote_login_passwordfile=’EXCLUSIVE’
*.resource_manager_plan=’INTERNAL_PLAN’
*.session_cached_cursors=200
*.session_max_open_files=20
*.sga_max_size=2147483648
*.sga_target=4194304000
*.shared_servers=6
*.statistics_level=’ALL’
*.undo_management=’AUTO’
*.undo_tablespace=’UNDOTBS1′
*.user_dump_dest=’/export/home/oracle/admin/cobrastd/udump’
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

NOTE:

1. Create all required directories for dump directories and archived log destination.
2. Copy from the primary the standby control file to its destination.
3. Configure listener.ora on both servers to hold entries for both databases.
4. Configure tnsnames.ora on both servers to hold entries for both databases.
5. Start listener on both nodes and check tnsping for both from both nodes.


SQL>startup nomount pfile=’/export/home/oracle/product/10.2.0/db_1/dbs/initcobrastd.ora’;
ORACLE instance started.
Total System Global Area 4194304000 bytes
Fixed Size 2045896 bytes
Variable Size 788531256 bytes
Database Buffers 3388997632 bytes
Redo Buffers 14729216 bytes

SQL>create spfile from pfile;
File created.

SQL>shu immediate
ORA-01507: database not mounted
ORACLE instance shut down.


SQL>startup mount
ORACLE instance started.
Total System Global Area 4194304000 bytes
Fixed Size 2045896 bytes
Variable Size 788531256 bytes
Database Buffers 3388997632 bytes
Redo Buffers 14729216 bytes
Database mounted.


–Perform recovery on standby:


SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;


NOTE: Check alert log on both sites (primary and standby) whether it’s running smoothly or not.


–Switch logfiles on Primary


SQL> alter system switch logfile;
System altered.


Now check the whether archive logs are getting applied on standby or not using following commands on both sites:


SQL>SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

You can skip to the end and leave a response. Pinging is currently not allowed.

Leave a Reply