My Blog

December 11, 2008

Setup dataguard on Linux without shutting down primary database

Filed under: Oracle — by enotebook @ 9:26 am

This scenario assumes that you already have a database is running on archivelog mode and need to upgrade this single database to data guard solution, following are the steps to archive this.

1. Prepare for the standby database – install Linux and oracle database with the edition same as primary database.

2. Enable force logging on primary database

SQL> ALTER DATABASE FORCE LOGGING;

3. Configure the Standby Redo Log (only need to do this on primary database since following uses RMAN to duplicate the standby database)

SQL> ALTER DATABASE ADD STANDBY LOGFILE
2> (‘/u01/oradata/orcl/standbyredo01.log’,’/u01/oradata/orcl/standbyredo02.log
‘,’/u01/oradata/orcl/standbyredo03.log’,’/u01/oradata/orcl/standbyredo04.log’
) SIZE 50M;

Be aware of 3 points as below:
* Ensure log file sizes are identical on the primary and standby databases.
* Determine the appropriate number of standby redo log file groups. There is an equation: (maximum number of logfiles for each thread + 1) * maximum number of threads
* Verify related database parameters and settings – like the values of MAXLOGFILES and MAXLOGMEMBERS

4. Set Primary Database Initialization Parameters(refer to parameters showed in step 6).
Assume the primary database uses SPFILE and we cannot shutdown it, so we use ALTER SYSTEM SET parameter=value SCOPE=BOTH to change the initialization parameters. For examples:

SQL> ALTER SYSTEM SET LOG_ARCHIVE_CONFIG=’DG_CONFIG=(orcl,orclstb)’ SCOPE=BOTH;
SQL> ALTER SYSTEM SET log_archive_dest_1=’LOCATION=/u01/arch/dg1 VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=orcl’ SCOPE=BOTH;
SQL> ALTER SYSTEM SET log_archive_dest_2=’SERVICE=boston LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=orclstb’ SCOPE=BOTH;

5. Create pfile and standby control file on primary database and copy them to standby database, then re-name them accordingly.

SQL> CREATE pfile=’/u01/pfile.ora’ FROM SPFILE;
SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS ‘/u01/stb.ctl’;

6. Modify the pfile on standby database like following(copied from oracle database manual ):

DB_NAME=chicago
DB_UNIQUE_NAME=boston
LOG_ARCHIVE_CONFIG=’DG_CONFIG=(chicago,boston)’
CONTROL_FILES=’/arch1/boston/control1.ctl’, ‘/arch2/boston/control2.ctl’
DB_FILE_NAME_CONVERT=’chicago’,’boston’
LOG_FILE_NAME_CONVERT=
‘/arch1/chicago/’,’/arch1/boston/’,’/arch2/chicago/’,’/arch2/boston/’

LOG_ARCHIVE_FORMAT=log%t_%s_%r.arc
LOG_ARCHIVE_DEST_1=
‘LOCATION=/arch1/boston/
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=boston’
LOG_ARCHIVE_DEST_2=
‘SERVICE=chicago LGWR ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=chicago’

LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
STANDBY_FILE_MANAGEMENT=AUTO
FAL_SERVER=chicago
FAL_CLIENT=boston

7. Backup primary database and archived logs using RMAN, and copy them to standby database with same directory structure.

RMAN> BACKUP DATABASE format ‘/u01/backup/data.%U.rman’ plus archivelog format ‘/oracle/oradata/rmanbackup/ARCH.%U.rman’

8. Startup the instance of standby database without mounting it.
SQL> STARTUP NOMOUNT;

9. Using RMAN to duplicate the standby database (Do this operation on primary database).

oracle@dg1~$ rman target / auxiliary sys/change_on_install@STANDBY

RMAN> duplicate target database for standby dorecover nofilenamecheck;

Please be aware you will encounter an error while doing this, which is ORA-12528: TNS:listener: all appropriate instances are blocking new connections, it is because only the Auxiliary Instance is running .The instance registers with the listener by the PMON process. TO start the PMON process the database need to be in MOUNT status. Before there is nothing to register the database to the listerner so the instance is BLOCKED.
To avoid this issue, use the Oracle10G feature:
tnsnames.ora
————
SAIRAM =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = apadhi-idc)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = SAIRAM)(UR=A)
)
)

10. Bring up standby database to managed recovery

SQL> alter database recover managed standby database disconnect from session;

Done.

Connection to Auxilary using connect string failed with ORA-12528

Filed under: Oracle — by enotebook @ 9:25 am

Refer to metalink Note:419440.1
During duplicate database creation, connection to the auxiliary database from target database server fails with ORA-12528: TNS:listener: all appropriate instances are blocking new connections.

E:oracleproduct10.2.0db_1BIN>rman target / auxiliary sys/sys@duplicate

Recovery Manager: Release 10.2.0.1.0 – Production on Fri Mar 16 00:11:05 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.

connected to target database: ORIGINAL (DBID=1451288337)
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00554: initialization of internal recovery manager package failed
RMAN-04006: error from auxiliary database: ORA-12528: TNS:listener: all appropri
ate instances are blocking new connections

When it fails , add manual entry for sid in listener.ora as per Note 301099.1

However, still you receive the same error on connect in some cases.

Cause
Its because only the Auxiliary Instance is running .The instance registers with the listener by the PMON process. TO start the PMON process the database need to be in MOUNT status. Before there is nothing to register the database to the listerner so the instance is BLOCKED.
Solution

There are 2 options to solve this problem

Option 1

Connect to the auxiliary instance using OS authentication (as /) and use password file authentication for target database and connect via Listener.For an example:

E:>rman target sys/sys@original auxiliary /

Recovery Manager: Release 10.2.0.1.0 – Production on Thu Mar 15 23:57:08 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.

connected to target database: ORIGINAL (DBID=1451288337)
connected to auxiliary database: DUPLICATE (not mounted)

Option 2

Create a static entry for the auxiliary database in the listener.ora file and restart the listener. For example

Listener.ora
————-

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = SAIRAM)
(ORACLE_HOME = G:oracleproduct10.2.0db_1)
(SID_NAME = SAIRAM)
)
)

Or

Use the Oracle10G feature :

tnsnames.ora
————

SAIRAM =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = apadhi-idc)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = SAIRAM)(UR=A)
)
)

This fix is required for administrative connections where we specify (UR=A) in connect data.

December 9, 2008

Standby database cannot archive redo log with ORA-16014: no available destinations

Filed under: Oracle — by enotebook @ 4:20 pm

I have newly configured a data guard server, it initially was running fine after the configuration has been done, but the standby database freezed in 10 miunites with the following error in alert log:

ORA-16401: archivelog rejected by RFS
ARCk: Failed to archive thread 1 sequence 27 (4)
ARCH: Archival stopped, error occurred. Will continue retrying
Tue Dec 2 10:20:18 2008
ORACLE Instance orcl – Archival Error
Tue Dec 2 10:20:18 2008
ORA-16014: log 4 sequence# 27 not archived, no available destinations
ORA-00312: online log 4 thread 1: ‘/opt/oracle/product/10gr2/oradata/orcl/standbyredo01.log’
Tue Dec 2 10:20:18 2008
RFS[5]: Archivelog thread 1 sequence 28 cannot be reused
Tue Dec 2 10:20:18 2008
Errors in file /opt/oracle/product/10gr2/admin/orcl/bdump/orcl_arck_1746.trc:
ORA-16014: log 4 sequence# 27 not archived, no available destinations
ORA-00312: online log 4 thread 1: ‘/opt/oracle/product/10gr2/oradata/orcl/standbyredo01.log’
Tue Dec 2 10:20:18 2008
Errors in file /opt/oracle/product/10gr2/admin/orcl/udump/orcl_rfs_1786.trc:
ORA-16401: archivelog rejected by RFS

Apparently, the standby database freezed since the standby redo log is not able to be archived. i suspect if the parameter of log_archive_dest_1 is correctly specified, following lists some of the system parameters with non-default values in standby database:

db_unique_name=orcl144
log_archive_config=’DG_CONFIG=(orcl145,orcl144)’
log_archive_dest_1=’LOCATION=/opt/oracle/arch/archlog144/ VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl144′
log_archive_dest_2=’SERVICE=dg145 LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLES) DB_UNIQUE_NAME=orcl145′
log_archive_dest_state_1=enable
log_archive_dest_state_2=enable
log_archive_format=’%t_%s_%r.arc’
log_archive_max_processes=30
fal_server=dg145
fal_client=dg144
standby_file_management=auto
standby_archive_dest=’/opt/oracle/arch/archlog144/’

Yes, it causes the problem – the parameter of log_archive_dest_1 specified incorrectly. For VALID_FOR clause, the value associate to it should be ALL_LOGFILES instead of ONLINE_LOGFILES, which means all the log files intend to be archived in specified location including STANDBY REDOLOG.
Problem solved after changing the value of log_archive_dest_1 to ‘LOCATION=/opt/oracle/arch/archlog144/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl144’.

Regarding VALID_FOR clause, i extract some content from oracle database 10 manual.

The VALID_FOR attribute is optional. However, Oracle recommends that you define a VALID_FOR attribute for each destination so that your Data Guard configuration operates properly after a role transition.
Note:
Although the (ALL_LOGFILES,ALL_ROLES) keyword pair is the default, it is not appropriate for every destination. For example, if the destination is a logical standby database, which is an open database that is creating its own redo data, the redo data being transmitted by redo transport services could potentially overwrite the logical standby database’s local online redo log files.
To configure these factors for each LOG_ARCHIVE_DEST_n destination, you specify this attribute with a pair of keywords: VALID_FOR=(redo_log_type,database_role):
The redo_log_type keyword identifies the destination as valid for archiving one of the following:
ONLINE_LOGFILE—This destination is valid only when archiving online redo log files.
STANDBY_LOGFILE—This destination is valid only when archiving standby redo log files.
ALL_LOGFILES— This destination is valid when archiving either online redo log files or standby redo log files.
The database_role keyword identifies the role in which this destination is valid for archiving:
PRIMARY_ROLE—This destination is valid only when the database is running in the primary role.
STANDBY_ROLE—This destination is valid only when the database is running in the standby role.
ALL_ROLES—This destination is valid when the database is running in either the primary or the standby role.
If you do not specify the VALID_FOR attribute for a destination, by default, archiving online redo log files and standby redo log files is enabled at the destination, regardless of whether the database is running in the primary or the standby role. This default behavior is equivalent to setting the (ALL_LOGFILES,ALL_ROLES) keyword pair on the VALID_FOR attribute. For example:
LOG_ARCHIVE_DEST_1=’LOCATION=/disk1/oracle/oradata/payroll/arch/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
The VALID_FOR attribute enables you to use the same initialization parameter file for both the primary and standby roles.

Blog at WordPress.com.