Dataguard configuration using
cold backup
Versions:
CentOs-5.5
Oracle
10.2.0
Databases information:
Primary
database name:ACDBP
Primary
db unique_name=ACDBP
Standby database name=ACDBP
Standby
db_unique_name=ACDBS
1. Create
the primary database
2. Set
the parameter file for the primary database:
initACDBP.ora
db_cache_size=125M
java_pool_size=8M
large_pool_size=8M
shared_pool_size=100M
streams_pool_size=20M
audit_file_dest='/opt/app/oracle/admin/ACDBP/adump'
background_dump_dest='/opt/app/oracle/admin/ACDBP/bdump'
compatible='10.2.0'
control_files='/opt/app/oracle/oradata/ACDBP/control01.ctl','/opt/app/oracle/admin/ACDBP/controlfile/control02.ctl'
core_dump_dest='/opt/app/oracle/admin/ACDBP/cdump'
db_block_size=8192
db_domain=''
db_file_multiblock_read_count=16
db_name='ACDBP'
db_recovery_file_dest='/opt/app/oracle/flash_recovery_area'
db_recovery_file_dest_size=2048M
job_queue_processes=10
open_cursors=100
pga_aggregate_target=70M
processes=50
remote_login_passwordfile='EXCLUSIVE'
sga_target=536M
undo_management='AUTO'
undo_tablespace='UNDOTBS1'
user_dump_dest='/opt/app/oracle/admin/ACDBP/udump'
service_names=ACDBP
#data
guard parameters
db_unique_name='ACDBP'
fal_client='ACDBP'
fal_server='ACDBS'
log_archive_config='DG_CONFIG=(ACDBP,ACDBS)'
log_archive_dest_1='LOCATION=/opt/app/oracle/admin/ACDBP/arch
VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ACDBP'
log_archive_dest_2='SERVICE=ACDBS LGWR ASYNC
VALID_FOR=(ALL_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ACDBS'
#log_archive_dest_2='SERVICE=ACDBS lgwr sync affirm reopen=10
VALID_FOR=(ALL_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ACDBS'
log_archive_dest_state_1='ENABLE'
log_archive_dest_state_2='ENABLE'
log_archive_format='%t_%s_%r.dbf'
ACDBP.log_archive_format='%t_%s_%r.dbf'
log_archive_max_processes=2
ACDBP.standby_archive_dest='/opt/app/oracle/admin/ACDBP/arch'
standby_file_management='AUTO'
log_archive_min_succeed_dest=1
3. Create
the appropriate directories for the stand by database and prepare the parameter
file for the standby database.
initACDBS.ora
db_cache_size=125M
java_pool_size=8M
large_pool_size=8M
shared_pool_size=100M
streams_pool_size=20M
audit_file_dest='/opt/app/oracle/admin/ACDBS/adump'
background_dump_dest='/opt/app/oracle/admin/ACDBS/bdump'
compatible='10.2.0'
control_files='/opt/app/oracle/oradata/ACDBS/standby1.ctl'
core_dump_dest='/opt/app/oracle/admin/ACDBS/cdump'
db_block_size=8192
db_domain=''
db_file_multiblock_read_count=16
db_name='ACDBP'
db_recovery_file_dest='/opt/app/oracle/flash_recovery_area'
db_recovery_file_dest_size=2048M
job_queue_processes=10
open_cursors=100
pga_aggregate_target=70M
processes=50
remote_login_passwordfile='EXCLUSIVE'
sga_target=536M
undo_management='AUTO'
undo_tablespace='UNDOTBS1'
user_dump_dest='/opt/app/oracle/admin/ACDBS/udump'
service_names=ACDBS
#data
guard parameters
db_unique_name='ACDBS'
fal_client='ACDBS'
fal_server='ACDBP'
log_archive_config='DG_CONFIG=(ACDBS,ACDBP)'
log_archive_dest_1='LOCATION=/opt/app/oracle/admin/ACDBS/arch
VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ACDBS'
#log_archive_dest_2='SERVICE=ACDBS LGWR ASYNC
VALID_FOR=(ALL_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ACDBS'
log_archive_dest_2='SERVICE=ACDBP LGWR
ASYNC VALID_FOR=(ALL_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ACDBP'
log_archive_dest_state_1='ENABLE'
log_archive_dest_state_2='ENABLE'
log_archive_format='%t_%s_%r.dbf'
db_file_name_convert='/opt/app/oracle/oradata/ACDBP/datafile','/opt/app/oracle/oradata/ACDBS/datafile'
log_file_name_convert='/opt/app/oracle/oradata/ACDBP/logfile','/opt/app/oracle/oradata/ACDBS/logfile','/opt/app/oracle/admin/ACDBP/logfile','/opt/app/oracle/admin/ACBS/logfile'
log_archive_max_processes=2
standby_archive_dest='/opt/app/oracle/admin/ACDBS/arch'
standby_file_management='AUTO'
log_archive_min_succeed_dest=1
4. Configure
the listener and tns entries on both primary and standby database servers:
Primary listener .ora and tnsnames.ora:
listerner.ora
LISTENER
=
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY =
ACDBP))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST =
192.168.1.210)(PORT = 1521))
)
)
)
SID_LIST_LISTENER
=
(SID_LIST =
(SID_DESC =
(ORACLE_HOME =
/opt/app/oracle/product/10.2.0)
(SID_NAME = ACDBP)
)
(SID_DESC =
(ORACLE_HOME =
/opt/app/oracle/product/10.2.0)
(SID_NAME = PLSExtProc)
(PROGRAM = extproc)
)
)
tnsnames.ora:
ACDBS
=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST =
192.168.1.211)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ACDBS)
)
)
Standby listener .ora and tnsnames.ora:
listerner.ora
LISTENER
=
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY =
ACDBS))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST =
192.168.1.211)(PORT = 1521))
)
)
)
SID_LIST_LISTENER
=
(SID_LIST =
(SID_DESC =
(ORACLE_HOME =
/opt/app/oracle/product/10.2.0)
(SID_NAME = ACDBS)
)
(SID_DESC =
(ORACLE_HOME =
/opt/app/oracle/product/10.2.0)
(SID_NAME = PLSExtProc)
(PROGRAM = extproc)
)
)
tnsnames.ora:
ACDBP
=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST =
192.168.1.210)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ACDBSP)
)
)
5.
Enable
the archive log mode for the primary database.
SQL>shut immediate;
SQL>startup mount;
SQL>alter
database archivelog;
SQL>alter database open;
SQL>alter database open;
6.
Create
the standby logfiles in the primary database.
ALTER DATABASE ADD STANDBY LOGFILE GROUP 3
('/opt/app/oracle/admin/ACDBP/logfile/redo_stand_1a.log',
'/opt/app/oracle/oradata/ACDBP/logfile/redo_stand_1b.log')
size 10M;
/
ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 ('/opt/app/oracle/admin/ACDBP/logfile/redo_stand_1a.log',
'/opt/app/oracle/oradata/ACDBP/logfile/redo_stand_1b.log')
size 10M;
/
7.
Backup
the database(cold) and scp the files to the standby server:
SQL>shut immediate;
$cd opt/app/oracle/oradata/ACDBP/datafile
$scp * oracle@192.168.1.211:/opt/app/oracle/oradata/ACDBS/datafile
$cd opt/app/oracle/oradata/ACDBP/logfile
$scp *
oracle@192.168.1.211:/opt/app/oracle/oradata/ACDBS/logfile
$cd opt/app/oracle/admin/ACDBP/logfile
$scp * oracle@192.168.1.211:/opt/app/oracle/
admin/ACDBS/logfile
8.
Create
the passwordfile for the primary database and scp the file to standby database
to $ORACLE_HOME/dbs location.
cd $ORACLE_HOME/dbs
$orapwd file=orapwd$ORACLE_SID password=abacus
enties=5
$cp orapwACDBP orapwACDBS
$scp orapwACDBP
oracle@192.168.1.211:$ORACLE_HOME/dbs
$rm orapwACDBS
Note: scp
the orapwdACDBP file
of the primary to the same location on the standby with the name of orapwACDBS.
9.
create
the standby controlfile and scp the file to standby server:
SQL> shut immediate;
SQL>startup mount;
SQL> alter database create standby controlfile as
‘/opt/app/oracle/admin/ACDBP/controlfile/stndby01.ctl’;
SQL>exit
$cd /opt/app/oracle/admin/ACDBP/controlfile/
$scp stndby01.ctl
oracle@192.168.1.211: opt/app/oracle/admin/ACDBS/controlfile/
10.
start
and verify the standby database (ON STANDBY DATABASE)
.
SQL>startup nomount;
SQL>alter database mount standby database;
SQL>archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /opt/app/oracle/admin/ACDBS/arch
Oldest online log sequence 201
Next log sequence to archive 0
Current log sequence 202
Check
the archive log sequence on both the databases(ACDBP AND ACDBS).
On
ACDBP (PRIMARY):
SQL>
archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /opt/app/oracle/admin/ACDBP/arch
Oldest online log sequence 201
Next log sequence to archive 202
Current log sequence 202
SQL>alter database recover managed standby
database using current logfile disconnect;
11. Identify
the existing files on the standby database
SQL> select sequence#, first_time, next_time from v$archived_log order by sequence#;
SQL> select sequence#, first_time, next_time from v$archived_log order by sequence#;
Switch
a log on the primary database:
SQL> alter system switch logfile;
SQL> alter system switch logfile;
- Re-Run the same SQL to make sure that the logs are received and applied to
the standby server.
Now verify that the logs created in the primary database were applied:
SQL> select sequence#,applied from v$archived_log
order by sequence#;
Note: Monitor the alert logfiles on both the primary and
standby databases to check the heartbeat of the databases(Redolog shipping and
sql apply).
Issue in the primary:
Fri Jul 22 06:23:45 2011
Errors in file
/opt/app/oracle/admin/ACDBP/bdump/acdbp_arc0_15992.trc:
ORA-16191: Primary log shipping client not logged on
standby
PING[ARC0]: Heartbeat failed to connect to standby
'ACDBS'. Error is 16191.
Issue
in standby database:
SQL>
alter database recover managed standby database using current logfile;
alter
database recover managed standby database using current logfile
*
ERROR
at line 1:
ORA-01093:
ALTER DATABASE CLOSE only permitted with no sessions connected
When
the standby database is opened in the read only mode, at that time if the
database needs to be recovered, there should be no active connections to the
standby database.
No comments:
Post a Comment