Considerations
Primary
Database: orcl
Standby
Database: standby
Once
you have your primary database up and running these are the steps to follow-
#
Enable Archiving
# Enable Forced Logging
# Create a Password File
# Configure a Standby Redo Log
# Set Primary Database Initialization Parameters
# Enable Forced Logging
# Create a Password File
# Configure a Standby Redo Log
# Set Primary Database Initialization Parameters
Having followed these steps to implement the
Physical Standby you need to follow these steps
# Create a Control File for the Standby Database
# Backup the Primary Database and transfer a copy to the Standby node.
# Prepare an Initialization Parameter File for the Standby Database
# Configure the listener and tnsname to support the database on both nodes
# Set Up the Environment to Support the Standby Database on the standby node.
# Start the Physical Standby Database
# Verify the Physical Standby Database Is Performing Properly
# Create a Control File for the Standby Database
# Backup the Primary Database and transfer a copy to the Standby node.
# Prepare an Initialization Parameter File for the Standby Database
# Configure the listener and tnsname to support the database on both nodes
# Set Up the Environment to Support the Standby Database on the standby node.
# Start the Physical Standby Database
# Verify the Physical Standby Database Is Performing Properly
Step by Step
Implementation of a Physical Standby Environment.
Primary Database Steps
Primary Database Steps
login
as: oracle
oracle@192.168.0.138's
password:
Last
login: Fri Jul 5 01:40:42 2013
[oracle@localhost
~]$ export orcl
[oracle@localhost
~]$ export ORACLE_SID=orcl
[oracle@localhost
~]$ sqlplus / as sysdba
SQL*Plus:
Release 10.2.0.1.0 - Production on Fri Jul 5 01:55:32 2013
Copyright
(c) 1982, 2005, Oracle. All rights
reserved.
Connected
to:
Oracle
Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With
the Partitioning, OLAP and Data Mining options
Enabling archive log
mode
SQL>
select log_mode from v$database;
LOG_MODE
------------
NOARCHIVELOG
SQL>
alter database archivelog;
alter
database archivelog
*
ERROR
at line 1:
ORA-01126:
database must be mounted in this instance and not open in any
instance
SQL>
shut immediate
Database
closed.
Database
dismounted.
ORACLE
instance shut down.
SQL>
startup mount
ORACLE
instance started.
Total
System Global Area 608174080 bytes
Fixed
Size 1220844 bytes
Variable
Size 201330452 bytes
Database
Buffers 402653184 bytes
Redo
Buffers 2969600 bytes
Database
mounted.
SQL>
alter database archivelog;
Database
altered.
SQL>
select log_mode from v$database;
LOG_MODE
------------
ARCHIVELOG
SQL>
archive log list
Database
log mode Archive Mode
Automatic
archival Enabled
Archive
destination USE_DB_RECOVERY_FILE_DEST
Oldest
online log sequence 2
Next
log sequence to archive 4
Current
log sequence 4
Enabling Force
Logging
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
SQL>
select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/home/app/oracle/oradata/orcl/redo03.log
/home/app/oracle/oradata/orcl/redo02.log
/home/app/oracle/oradata/orcl/redo01.log
Adding Standby
logfiles
SQL>
alter database add standby logfile
'/home/app/oracle/oradata/orcl/standby01.log' size 50m;
Database
altered.
SQL> alter database add standby logfile
'/home/app/oracle/oradata/orcl/standby02.log' size 50m;
Database
altered.
SQL>
alter database add standby logfile
'/home/app/oracle/oradata/orcl/standby03.log' size 50m;
Database
altered.
SQL>
select * from v$standby_log;
GROUP#
DBID
THREAD# SEQUENCE# BYTES USED ARC STATUS FIRST_CHANGE# FIRST_TIM LAST_CHANGE#
LAST_TIME
----------
---------------------------------------- ---------- ---------- ----------
---------- --- ---------- ------------- --------- ------------ ---------
4 UNASSIGNED 0 0
52428800 512 YES
UNASSIGNED 0 0
5 UNASSIGNED 0 0
52428800 512 YES
UNASSIGNED 0 0
6 UNASSIGNED 0 0
52428800 512 YES
UNASSIGNED 0 0
SQL>
select member from v$logfile;
MEMBER
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/home/app/oracle/oradata/orcl/redo03.log
/home/app/oracle/oradata/orcl/redo02.log
/home/app/oracle/oradata/orcl/redo01.log
/home/app/oracle/oradata/orcl/standby01.log
/home/app/oracle/oradata/orcl/standby02.log
/home/app/oracle/oradata/orcl/standby03.log
6
rows selected.
SQL>
create spfile from pfile;
File
created.
SQL>
shutdown immediate
Database
closed
Database
dismounted.
ORACLE
instance shut down.
Add the following
lines to initorcl.ora
db_unique_name=orcl
LOG_ARCHIVE_CONFIG='DG_CONFIG=(orcl,standby)'
LOG_ARCHIVE_DEST_1=
'LOCATION=/home/app/oracle/flash_recovery_area/orcl/ARCHIVELOG
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=orcl'
LOG_ARCHIVE_DEST_2=
'SERVICE=STAN
LGWR ASYNC
VALID_FOR=(ONLINE_LOGFILES,orclARY_ROLE)
DB_UNIQUE_NAME=standby'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
LOG_ARCHIVE_MAX_PROCESSES=30
remote_login_passwordfile='EXCLUSIVE'
FAL_SERVER=standby
FAL_CLIENT=orcl
standbyBY_FILE_MANAGEMENT=AUTO
DB_FILE_NAME_CONVERT=’/home/app/oracle/oradata/standby’,’/home/app/oracle/oradata/orcl’
LOG_FILE_NAME_CONVERT=’/home/app/oracle/oradata/standby’,’/home/app/oracle/oradata/orcl’
Resultant pfile
[oracle@localhost
~]$ vi $ORACLE_HOME/dbs/initorcl.ora;
[oracle@localhost
~]$ cat $ORACLE_HOME/dbs/initorcl.ora;
orcl.__db_cache_size=402653185
orcl.__java_pool_size=4194304
orcl.__large_pool_size=4194304
orcl.__shared_pool_size=192937984
orcl.__streams_pool_size=0
*.audit_file_dest='/home/app/oracle/admin/orcl/adump'
*.background_dump_dest='/home/app/oracle/admin/orcl/bdump'
*.compatible='10.2.0.1.0'
*.control_files='/home/app/oracle/oradata/orcl/control01.ctl','/home/app/oracle/oradata/orcl/control02.ctl','/home/app/oracle/oradata/orcl/control03.ctl'
*.core_dump_dest='/home/app/oracle/admin/orcl/cdump'
*.db_block_size=8192
*.db_domain='oracle.com'
*.db_file_multiblock_read_count=16
*.db_name='orcl'
db_unique_name=orcl
LOG_ARCHIVE_CONFIG='DG_CONFIG=(orcl,standby)'
LOG_ARCHIVE_DEST_1=
'LOCATION=/home/app/oracle/flash_recovery_area/ORCL/ARCHIVELOG
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=%t_%s_%r.arc
LOG_ARCHIVE_MAX_PROCESSES=30
remote_login_passwordfile='EXCLUSIVE'
FAL_SERVER=standby
FAL_CLIENT=orcl
STANDBY_FILE_MANAGEMENT=AUTO
DB_FILE_NAME_CONVERT=./home/app/oracle/oradata/standby.,./home/app/oracle/oradata/orcl.
LOG_FILE_NAME_CONVERT=./home/app/oracle/oradata/standby.,./home/app/oracle/oradata/orcl.
*.db_recovery_file_dest='/home/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=201326592
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=605028352
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/home/app/oracle/admin/orcl/udump'
Create spfile from
pfile
[oracle@localhost
~]$ sqlplus / as sysdba
SQL*Plus:
Release 10.2.0.1.0 - Production on Fri Jul 5 03:16:10 2013
Copyright
(c) 1982, 2005, Oracle. All rights
reserved.
Connected
to an idle instance.
SQL>
startup nomount pfile=$ORACLE_HOME/dbs/initorcl.ora;
ORACLE
instance started.
Total
System Global Area 608174080 bytes
Fixed
Size 1220844 bytes
Variable
Size 201330452 bytes
Database
Buffers 402653184 bytes
Redo
Buffers 2969600 bytes
SQL>
create spfile from pfile;
File
created.
SQL>
alter database mount;
Database
altered.
SQL>
ALTER DATABASE CREATE STANDBY CONTROLFILE AS '$ORACLE_HOME/dbs/standby.ctl';
Database
altered.
SQL>
alter database open;
Database
altered.
SQL>
select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/home/app/oracle/oradata/orcl/system01.dbf
/home/app/oracle/oradata/orcl/undotbs01.dbf
/home/app/oracle/oradata/orcl/sysaux01.dbf
/home/app/oracle/oradata/orcl/users01.dbf
/home/app/oracle/oradata/orcl/example01.dbf
SQL>
shutdown immediate
Database
closed
Database
dismounted.
ORACLE
instance shut down.
No comments:
Post a Comment