This tutorial is for configuring
physical standby database.
The dataguard type which we are configuring
here is "Maximum Performance Mode".
Primary Machine IP: 192.168.1.160
Database name:orcl
Secondary Machine IP: 192.168.1.162
Database name:standby
On Primary Machine
1) Enable force logging on primary machine.
SQL> select force_logging from v$database;
FOR
---
NO
SQL> alter database force logging;
Database altered.
SQL> select force_logging from v$database;
FOR
---
YES
2) Create a password file if its not made.
[oracle@primary ~]$ orapwd file=/u01/app/oracle/product/10.2.0/db_1/dbs/
orapworcl password=sys
3) Create standby redo logs
The rule is to create one more Standby redo log group than the
total number of online redo log groups.
SQL> col members format a50
SQL> select group#, thread#, bytes, members from v$log;
GROUP# THREAD# BYTES MEMBERS
---------- ---------- ---------- ----------
1 1 52428800 ##########
2 1 52428800 ##########
3 1 52428800 ##########
# I already have three redo groups, So i will create a total of four
# standby groups.
SQL> select group#, type, member from v$logfile order by group#, member;
GROUP# TYPE MEMBER
---------- ------- --------------------------------------------------
1 ONLINE /u01/app/oracle/oradata/orcl/redo01.log
2 ONLINE /u01/app/oracle/oradata/orcl/redo02.log
3 ONLINE /u01/app/oracle/oradata/orcl/redo03.log
SQL> alter database add standby logfile group 4
2 '/u01/app/oracle/oradata/orcl/stby04.log' size 50m;
Database altered.
SQL> alter database add standby logfile group 5
2 '/u01/app/oracle/oradata/orcl/stby05.log' size 50m;
Database altered.
SQL> alter database add standby logfile group 6
2 '/u01/app/oracle/oradata/orcl/stby06.log' size 50m;
Database altered.
SQL> alter database add standby logfile group 7
2 '/u01/app/oracle/oradata/orcl/stby07.log' size 50m;
Database altered.
SQL> select group#, type, member from v$logfile order by group#, member;
GROUP# TYPE MEMBER
---------- ------- --------------------------------------------------
1 ONLINE /u01/app/oracle/oradata/orcl/redo01.log
2 ONLINE /u01/app/oracle/oradata/orcl/redo02.log
3 ONLINE /u01/app/oracle/oradata/orcl/redo03.log
4 STANDBY /u01/app/oracle/oradata/orcl/stby04.log
5 STANDBY /u01/app/oracle/oradata/orcl/stby05.log
6 STANDBY /u01/app/oracle/oradata/orcl/stby06.log
7 STANDBY /u01/app/oracle/oradata/orcl/stby07.log
7 rows selected.
4) Edit parameter file
SQL> create pfile='/u01/app/initorcl.ora' from spfile;
File created.
Parameters
orcl.__db_cache_size=188743680
orcl.__java_pool_size=4194304
orcl.__large_pool_size=4194304
orcl.__shared_pool_size=83886080
orcl.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/orcl/adump'
*.background_dump_dest='/u01/app/oracle/admin/orcl/bdump'
*.compatible='10.2.0.1.0'
*.control_files='/u01/app/oracle/oradata/orcl/control01.ctl',
'/u01/app/oracle/oradata/orcl/control02.ctl',
'/u01/app/oracle/oradata/orcl/control03.ctl'
*.core_dump_dest='/u01/app/oracle/admin/orcl/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='orcl'
*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.job_queue_processes=10
*.open_cursors=300
*.pga_aggregate_target=94371840
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=285212672
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/u01/app/oracle/admin/orcl/udump'
db_unique_name='orcl'
log_archive_config='DG_CONFIG=(orcl,standby)'
log_archive_dest_1='location=/u01/app/oracle/arch valid_for=(all_logfiles,
all_roles) db_unique_name=orcl'
log_archive_dest_2='service=standby lgwr async valid_for=(online_logfiles,
primary_role) db_unique_name=standby'
log_archive_dest_state_1=enable
log_archive_dest_state_2=enable
log_archive_format=arch%t_%s_%r.arc
log_archive_max_processes=4
#standby role parameters
fal_server=standby
fal_client=orcl
standby_file_management=auto
db_file_name_convert='/u01/app/oracle/oradata/standby/',
'/u01/app/oracle/oradata/orcl/'
log_file_name_convert='/u01/app/oracle/oradata/standby/',
'/u01/app/oracle/oradata/orcl/'
# Shutdown the database and create spfile
# from the edited pfile.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup nomount pfile='/u01/app/initorcl.ora';
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 1218992 bytes
Variable Size 92276304 bytes
Database Buffers 188743680 bytes
Redo Buffers 2973696 bytes
SQL> create spfile from pfile='/u01/app/initorcl.ora';
File created.
SQL> shutdown immediate;
ORA-01507: database not mounted
ORACLE instance shut down.
# Also put the database in archivelog mode.
# I have already mentioned the archive destination
# in the parameter file.
SQL> startup mount
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 1218992 bytes
Variable Size 92276304 bytes
Database Buffers 188743680 bytes
Redo Buffers 2973696 bytes
Database mounted.
SQL> alter database archivelog;
Database altered.
SQL> alter database open;
Database altered.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/arch
Oldest online log sequence 1
Next log sequence to archive 3
Current log sequence 3
Primary machine Backup.
Before going any further make identical directory structures
for backups on both the primary as well as secondary machines.
primary: mkdir -p /u01/app/oracle/backup
secondary: mkdir -p /u01/app/oracle/backup
Now we will take the backup of the orcl database on the primary machine.
[oracle@primary ~]$ export ORACLE_SID=orcl
[oracle@primary ~]$ rman target=/
Recovery Manager: Release 10.2.0.1.0 - Production on Fri Jan 6 20:22:38 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: ORCL (DBID=1299658972)
RMAN> backup device type disk format'/u01/app/oracle/backup/%U'
database plus archivelog;
RMAN> backup device type disk format '/u01/app/oracle/backup/%U'
current controlfile for standby;
# Backup the control file additionally for standby server.
Prepare the Standby Server or Secondary Machine.
Make appropriate directory structures.
[oracle@secondary ~]$ mkdir -p /u01/app/oracle/admin/standby/adump
[oracle@secondary ~]$ mkdir -p /u01/app/oracle/admin/standby/bdump
[oracle@secondary ~]$ mkdir -p /u01/app/oracle/oradata/standby/
[oracle@secondary ~]$ mkdir -p /u01/app/oracle/admin/standby/cdump
[oracle@secondary ~]$ mkdir -p /u01/app/oracle/flash_recovery_area
[oracle@secondary ~]$ mkdir -p /u01/app/oracle/admin/standby/udump
[oracle@secondary ~]$ mkdir -p /u01/app/oracle/arch
Copy the pfile as well as the backups from the primary machine to standby.
scp -r /u01/app/initorcl.ora 192.168.1.162:/u01/app/oracle/product/
10.2.0/db_1/dbs/initstandby.ora
scp -r /u01/app/oracle/backup/* 192.168.1.162:/u01/app/oracle/backup/
Edit the pfile for standby server.
Standby Parameters
standby.__db_cache_size=188743680
standby.__java_pool_size=4194304
standby.__large_pool_size=4194304
standby.__shared_pool_size=83886080
standby.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/standby/adump'
*.background_dump_dest='/u01/app/oracle/admin/standby/bdump'
*.compatible='10.2.0.1.0'
*.control_files='/u01/app/oracle/oradata/standby/control01.ctl',
'/u01/app/oracle/oradata/standby/control02.ctl',
'/u01/app/oracle/oradata/standby/control03.ctl'
*.core_dump_dest='/u01/app/oracle/admin/standby/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='orcl'
*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=standbyXDB)'
*.job_queue_processes=10
*.open_cursors=300
*.pga_aggregate_target=94371840
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=285212672
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/u01/app/oracle/admin/standby/udump'
db_unique_name='standby'
log_archive_config='DG_CONFIG=(orcl,standby)'
log_archive_dest_1='location=/u01/app/oracle/arch valid_for=(all_logfiles,
all_roles) db_unique_name=standby'
log_archive_dest_2='service=orcl lgwr async valid_for=(online_logfiles
,primary_role) db_unique_name=orcl'
log_archive_dest_state_1=enable
log_archive_dest_state_2=enable
log_archive_format=arch%t_%s_%r.arc
log_archive_max_processes=4
#standby role parameters
fal_server=orcl
fal_client=standby
standby_file_management=auto
db_file_name_convert='/u01/app/oracle/oradata/orcl/',
'/u01/app/oracle/oradata/standby/'
log_file_name_convert='/u01/app/oracle/oradata/orcl/',
'/u01/app/oracle/oradata/standby/'
Configure listeners on both the machines.
Primary listener
# listener.ora Network Configuration File: /u01/app/
oracle/product/10.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = orcl)
(ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
(SID_NAME = orcl)
)
)
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.160)(PORT = 1521))
)
Tnsnames.ora Common parameters on both the Machines.
# tnsnames.ora Network Configuration File: /u01/app/oracle/
product/10.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
STANDBY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.162)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = standby)
)
)
ORCL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.160)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl)
)
)
Secondary or Standby listener.
# listener.ora Network Configuration File: /u01/app/oracle
/product/10.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = standby)
(ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
(SID_NAME = standby)
)
)
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.162)(PORT = 1521))
)
Start the listeners on both servers.
Do tnsping on both to check their status.
tnsping orcl
tnsping standby
create password file on standby
[oracle@secondary ~]$ orapwd file=/u01/app/oracle/product/10.2.0/db_1/
dbs/orapwstandby password=sys
Start the standby database to nomount stage.
[oracle@secondary ~]$ export ORACLE_SID=standby
[oracle@secondary ~]$ sqlplus
SQL*Plus: Release 10.2.0.1.0 - Production on Fri Jan 6 21:24:06 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Enter user-name: / as sysdba
Connected to an idle instance.
SQL> create spfile from pfile='/u01/app/oracle/product/10.2.0/db_1/dbs/initstandby.ora';
File created.
SQL> startup nomount
exit
Connect to both the orcl and standby database using RMAN and start the
standby database creation process.
[oracle@secondary ~]$ export ORACLE_SID=standby
[oracle@secondary ~]$ rman target=sys/sys@orcl nocatalog auxiliary=/
Recovery Manager: Release 10.2.0.1.0 - Production on Fri Jan 6 21:28:12 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: ORCL (DBID=1299658972)
using target database control file instead of recovery catalog
connected to auxiliary database: ORCL (not mounted)
RMAN> duplicate target database for standby;
After the orcl database is duplicated to standby
exit from rman, and configure the standby database
to receive redo logs from orcl.
Copy the password file from production to standby.
[oracle@secondary ~]$ export ORACLE_SID=standby
[oracle@secondary ~]$ sqlplus
SQL*Plus: Release 10.2.0.1.0 - Production on Fri Jan 6 21:35:04 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Enter user-name: / as sysdba
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> select open_mode from v$database;
OPEN_MODE
----------
MOUNTED
SQL> alter database recover managed standby database disconnect
from session;
Database altered.
On Primary
Restart the instance.
SQL>shutdown immediate
SQL>startup
SQL> alter system switch logfile;
System altered.
SQL> alter system archive log current;
System altered.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/arch
Oldest online log sequence 10
Next log sequence to archive 12
Current log sequence 12
On Secondary
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/arch
Oldest online log sequence 10
Next log sequence to archive 0
Current log sequence 12
SQL> select sequence#, applied from v$archived_log order by sequence#;
Note:Restart database on primary and reload listeners for ora 12154
errors in alertlog file.
To shutdown the standby database execute the following procedure.
find out if standby database is performing managed recovery.
If the MRP0 or MRP process exists then it is performing
managed recovery.
SQL> select process, status from v$managed_standby;
cancel managed recovery operations.
SQL> alter database recover managed standby database cancel;
shutdown the standby database
SQL> shutdown immediate;
To startup the standby database execute the folowing commands.
SQL> startup nomount;
SQL> alter database mount standby database;
SQL> alter database recover managed standby database
disconnect from session;
Hi,The way describe the thing is so clear and close to live example.Thank you for writting such good practical.
ReplyDelete