archive logs not get copied on standby database in a dataguard configuration.

[knp_11]

Hi All,

I am trying for first time, to create physical standby database using data guard feature of oracle on windows box.
As I am fresher in dba field, to keep it simple I am doing it on same box.

Issue:
1) Archive logs are getting created on primary database in archive folder specified “LOG_ARCHIVE_DEST_1" parameter in pfile of primary database.
But on standby database I am not able to see archive logs and they are not getting copied there.
2) What things need to be done so that archive logs will get copied on standby database?
3) Do I initializing the both database using wrong "redo transport, redo apply and role management parameters"?
How should I initialize following parameters in data guard configuration?

LOG_ARCHIVE_DEST_1
LOG_ARCHIVE_DEST_2
FAL_SERVER=
FAL_CLIENT=
db_file_name_convert=
log_file_name_convert
LOG_ARCHIVE_FORMAT

regards,
knp_11

[davey23uk]

otn.oracle.com -> datagurad manual

its all in there

[PaulH]

As a beginner, my suggestion to you would be to skip using dataguard
for now until you get comfortable with the concept of the primary and
standbny database. I say this because, I am first start to expirment with
a standby db and understand it.

Here are my instructions for creating a primary and standby DB on the same
machine if you want to follow them:

1) create a DB anyway you like (manually, clone, ...) and call it pri

2) Add the following to your tnsnames.ora and listener.ora file

note: ip_address must be replaceed with a valid ip address

tnsnames.ora

PRI =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ip_address)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = pri)
)
)

STDBY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ip_address)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = stdby)
)
)

listener.ora

LISTENER =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(Host = ip_address)(Port = 1521))
)

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(ORACLE_HOME= /rdbms/oracle/10g20)
(SID_NAME = pri)
)
(SID_DESC =
(ORACLE_HOME= /rdbms/oracle/10g20)
(SID_NAME = stdby)
)
)

TRACE_LEVEL_listener = OFF
STARTUP_WAIT_TIME_listener = 0
CONNECT_TIMEOUT_listener = 10

Add the following entries to your initpri.ora file

DB_NAME=PRI
DB_UNIQUE_NAME=PRI
CONTROL_FILES='/u1/pri/control1.ctl', '/u2/pri/control2.ctl'
LOG_ARCHIVE_DEST_1=
'LOCATION=/u1/oraarch/pri/
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=pri'
LOG_ARCHIVE_DEST_2=
'SERVICE=stdby ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=stdby'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
LOG_ARCHIVE_MAX_PROCESSES=30
FAL_SERVER=stdby
FAL_CLIENT=pri
DB_FILE_NAME_CONVERT='stdby','pri'
LOG_FILE_NAME_CONVERT='stdby','pri'
STANDBY_FILE_MANAGEMENT=AUTO

initpri.ora file (here is complete initpri.ora file if you want to cut and paste)

$ cat initpri.ora
*.audit_file_dest='/t4data/oracle_data/pri/audit'
*.audit_trail='false'
*.background_dump_dest='/t4data/oracle_data/pri/bdump'
*.compatible='10.2.0.1.0'
*.control_files='/t4data/oracle_data/pri/control01.ctl','/t4data/oracle_data/pri
/control02.ctl','/t4data/oracle_data/pri/control03.ctl'
*.core_dump_dest='/t4data/oracle_data/pri/cdump'
*.db_block_size=4096
*.db_cache_size=100m
*.db_domain='world.com'
*.db_name='pri'
*.DB_UNIQUE_NAME='pri'
*.instance_name='pri'
*.java_pool_size=31457280
*.job_queue_processes=20
*.large_pool_size=8388608
*.log_archive_format='arch%s%r%t.arc'
*.max_dump_file_size='10000'
*.nls_sort='binary'
*.open_cursors=600
*.pga_aggregate_target=100m
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.service_names='pri'
*.shared_pool_size=153431142
*.undo_management='AUTO'
*.undo_retention=10800
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/t4data/oracle_data/pri/udump'
*.workarea_size_policy='auto'
*.log_archive_dest_1='LOCATION=/t4data/oracle_data/pri/arch/ VALID_FOR=(ALL_LOGF
ILES,ALL_ROLES) DB_UNIQUE_NAME=pri'
*.log_archive_dest_2='SERVICE=stdby ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROL
E) DB_UNIQUE_NAME=stdby'
*.log_archive_dest_state_1=ENABLE
*.log_archive_dest_state_2=ENABLE
*.fal_server=stdby
*.fal_client=pri
*.db_file_name_convert='/t4data/oracle_data/stdby','/t4data/oracle_data/pri'
*.log_file_name_convert='/t4data/oracle_data/stdby','/t4data/oracle_data/pri'
*.standby_file_management=AUTO


cp initpri.ora to initstdby.ora and modify

DB_NAME=pri
DB_UNIQUE_NAME=stdby
CONTROL_FILES='/u1/stdby/control1.ctl', '/u2/stdby/control2.ctl'
DB_FILE_NAME_CONVERT='pri','stdby'
LOG_FILE_NAME_CONVERT='pri','stdby'
LOG_ARCHIVE_FORMAT=log%t_%s_%r.arc
LOG_ARCHIVE_DEST_1=
'LOCATION=/u1/oraarch/stdby/
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=stdby'
LOG_ARCHIVE_DEST_2=
'SERVICE=pri ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=pri'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
STANDBY_FILE_MANAGEMENT=AUTO
FAL_SERVER=pri
FAL_CLIENT=stdby

Here is a complete initstdby.ora file if you want to cut and paste.

$ cat initstdby.ora
*.background_dump_dest='/t4data/oracle_data/stdby/bdump'
*.compatible='10.2.0.1.0'
*.control_files='/t4data/oracle_data/stdby/control01.ctl','/t4data/oracle_data/s
tdby/control02.ctl','/t4data/oracle_data/stdby/control03.ctl'
*.core_dump_dest='/t4data/oracle_data/stdby/cdump'
*.db_block_size=4096
*.db_cache_size=100m
*.db_domain='world.com'
*.db_name='pri'
*.DB_UNIQUE_NAME='stdby'
*.instance_name='pri'
*.java_pool_size=31457280
*.job_queue_processes=20
*.large_pool_size=8388608
*.log_archive_format='arch%s%r%t.arc'
*.max_dump_file_size='10000'
*.nls_sort='binary'
*.open_cursors=600
*.pga_aggregate_target=100m
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.service_names='pri'
*.shared_pool_size=153431142
*.undo_management='AUTO'
*.undo_retention=10800
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/t4data/oracle_data/stdby/udump'
*.workarea_size_policy='auto'
*.LOG_ARCHIVE_DEST_1='LOCATION=/t4data/oracle_data/stdby/arch/ VALID_FOR=(ALL_LO
GFILES,ALL_ROLES) DB_UNIQUE_NAME=stdby'
*.LOG_ARCHIVE_DEST_2='SERVICE=pri ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=pri'
*.log_archive_dest_state_1=ENABLE
*.log_archive_dest_state_2=ENABLE
*.fal_server=pri
*.fal_client=stdby
*.db_file_name_convert='pri','stdby'
*.log_file_name_convert='pri','stdby'
*.standby_file_management=AUTO


Take a cold backup of Primary database
Create control file from primary for standby as
sql> startup mount
sql> ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/tmp/pri.ctl';
sql> ALTER DATABASE OPEN;

In my sample I have 3 control files so you need to copy
cp /tmp/pri.ctl ...control01.ctl
cp /tmp/pri.ctl ...control02.ctl
cp /tmp/pri.ctl ...control03.ctl

Set environment variable for standby database in terms of ORACLE_HOME. ORACLE_SID, PATH etc and run sqlplus as

$ sqlplus “/ as sysdba”
sql> startup mount;

Standby at this stage will be in mount mode and now take it to managed recovery mode as

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

use orapwd to create password files for both primary and standby db


Here is a good document to follow. Let me know if you need anymore help

[knp_11]

Hi Paul
Thanks for such detailed information.Actually I was trying on one system since last 15 days but coulden't get through so moved to two box configuration on linux
1)I have installed "PRIM" database on primary and on standby installed without starter database.
2)I configured pfile for primary ie. added parameters needed for dataguard configuration also created net service for it and created listner for it
3)I started the database using modified pfile(which contain parameters for dataguard)
4)created spfile and mounted database using spfile
5)I enabled log archive mode and force logging on primary and logs are getting generated at the location specifed in LOG_ARCHIVE_DEST_1 in primary pfile.
6)On standby :I created pfile for standby and then transfered it to standby then modified it for datagurd configuration and standby database was mounted using that pfile then created spfile and mounted database using that spfile.
7)I also cretated listner and net service on standby and my standby server is running fine
8)But when I "alter database recover managed standby database disconnect from session;" on standby i found no logs getting generated on standby
9)I think somehow log transport service is not able to transport the archived logs to standby server

These are some link I refered
http://www.orafaq.com/node/2030
http://www.singlequery.com/2008/06/s...rd-enviroment/

I have few questions for you.
1)Do we need to create listners for primary and standby on primary server and on standby server also
or
On primary server, listner for primary only
On standby server, listner for standby only

2)I have taken cold backup and also tried with RAMAN backup from primary to secondary does it make difference?

3)Can you send me any link which can help me

4)How can I troubleshoot this issue as when I was doing it on single box same thing was happening and now on two box configurastion also it is there
I think I am not initilizing datagurad parameters in the pfiles properly

regards,
NandyK

[PaulH]

I have not got up to the point of making this work on 2 servers but I
would strongly suggest trying to keep things simple and use one machine
first.

As for the listeners and tnsnames.ora file since both DB's need to communicate with each other I would suggest making them work
on both machines.

Also, remember you need to put your prim db in a mode where it forces
logging.

Hope this helps

[PaulH]

This is a very good link note the queries that can help and also
you may want to look at v$log

http://download.oracle.com/docs/cd/B...htm#SBYDB00426