Solaris Zonas

root@CT1MGTSOLDC001 # zoneadm list -cv
ID NAME STATUS PATH BRAND IP
0 global running / native shared
1 ct1nszsodc001 running /storage0/zones/ct1nszsodc001 native shared
3 CT1DSSOLDC001 running /storage0/zones/CT1DSSOLDC001 native shared
4 ct1nszsodc003 running /storage0/zones/ct1nszsodc003 native shared
8 dboracle running /storage0/zones/dboracle native shared
root@CT1MGTSOLDC001 # zonecfg -z dboracle
zonecfg:dboracle> info
zonename: dboracle
zonepath: /storage0/zones/dboracle
brand: native
autoboot: true
bootargs:
pool:
limitpriv:
scheduling-class:
ip-type: shared
net:
address: 172.28.250.207
physical: e1000g853000
dedicated-cpu:
ncpus: 1
capped-memory:
physical: 2G
[swap: 2G]
rctl:
name: zone.max-swap
value: (priv=privileged,limit=2147483648,action=deny)
zonecfg:dboracle>

Oracle Data Guard

Startup of RAC database fails with ORA-01105 & ORA-01677

Problem Description

Oracle Data Guard was setup between two RAC databases and after switch-over operation is performed one node of primary database can be opened but another node can't be mounted. Following is the output while starting the instance using srvctl command.

$ srvctl start database -d orcl1
PRCR-1079 : Failed to start resource ora.orcl1.db
ORA-01105: mount is incompatible with mounts by other instances
ORA-01677: standby file name convert parameters differ from other instance
CRS-2674: Start of 'ora.orcl1.db' on 'rac1' failed
CRS-2632: There are no more servers to try to place resource 'ora.orcl1.db' on that would satisfy its placement policy

If we try to start the instance using SQL*Plus it also fails with error message ORA-01105 and ORA-01677 while mounting the database.

Cause of the Problem
The 'ORA-01105: mount is incompatible with mounts by other instances' error indicates that the parameter file does not match with the parameter file of another instance. So, you must verify the list of initialization parameters of one node with the list of initialization parameters on another node. The 'ORA-01677: standby file name convert parameters differ from other instance' error message gives you a hint which specific initialization parameter mismatch with another instance. It means the log_file_name_convert initialization parameter differs between two nodes.

Solution of the Problem
1) The best way to solve the problem is to use shared storage for your database initialization parameter (spfile) and keep same spfile for both instances so that if you perform any modification from database using ALTER SYSTEM command both instance are affected. So, check the spfile location from one instance and use same spfile for another instance.

2) If you use different spfile/pfile to start your database instance then set log_file_name_convert parameter to the same as it is used in another instance so that both instance has same value.

3) Once you ensure same parameter value in both database try to mount and start the instance.
Posted by Arju at 3:13 AM 0 comments Labels: Data Guard, RAC
Email This
BlogThis!
Share to Twitter
Share to Facebook
Share to Google Buzz
Reactions:

Saturday, March 26, 2011
Diagnosis Oracle Logical Standby Data Guard Configuration
-- NAME: DG_Logical_Standby_Diagnosis.sql
-- ------------------------------------------------------------------------
-- AUTHOR: Mohammad Abdul Momin Arju
-- January 2010.
-- ------------------------------------------------------------------------
-- PURPOSE:
-- This script is used to troubleshoot Oracle Data Guard issues for
-- Logical standby database.
-- ------------------------------------------------------------------------
-- DISCLAIMER:
-- This script is provided for educational purposes only.
-- Test it before you run in your production database.
-- ------------------------------------------------------------------------
-- Content of the Script


set echo off
set feedback off
column timecol new_value timestamp
column spool_extension new_value suffix
select to_char(sysdate,'Mondd_hhmi') timecol,
'.out' spool_extension from sys.dual;
column output new_value dbname
select value || '_' output
from v$parameter where name = 'db_name';
spool DG_Logical_Standby_&&dbname&×tamp&&suffix

set linesize 79
set pagesize 180
set long 1000
set trim on
set trims on
alter session set nls_date_format = 'MM-DD-YYYY HH24:MI:SS';
set feedback on
select to_char(sysdate) time from dual;

set echo on

-- The following select will give us the generic information about how
-- this standby is setup. The database_role should be logical standby as
-- that is what this script is intended to be ran on.

column ROLE format a7 tru
column NAME format a8 wrap
select name,database_role,log_mode,protection_mode
from v$database;

-- ARCHIVER can be (STOPPED | STARTED | FAILED). FAILED means that the
-- archiver failed to archive a log last time, but will try again within 5
-- minutes. LOG_SWITCH_WAIT The ARCHIVE LOG/CLEAR LOG/CHECKPOINT event log
-- switching is waiting for. Note that if ALTER SYSTEM SWITCH LOGFILE is
-- hung, but there is room in the current online redo log, then value is
-- NULL

column host_name format a20 tru
column version format a9 tru
select instance_name,host_name,version,archiver,log_switch_wait
from v$instance;

-- The following query give us information about catpatch.
-- This way we can tell if the procedure doesn't match the image.

select version, modified, status from dba_registry
where comp_id = 'CATPROC';

-- Force logging and supplemental logging are not mandatory but are
-- recommended if you plan to switchover. During normal operations it is
-- acceptable for SWITCHOVER_STATUS to be SESSIONS ACTIVE or NOT ALLOWED.

column force_logging format a13 tru
column remote_archive format a14 tru
column dataguard_broker format a16 tru

select force_logging,remote_archive,supplemental_log_data_pk,
supplemental_log_data_ui,switchover_status,dataguard_broker
from v$database;

-- This query produces a list of all archive destinations. It shows if
-- they are enabled, what process is servicing that destination, if the
-- destination is local or remote, and if remote what the current mount ID
-- is.

column destination format a35 wrap
column process format a7
column archiver format a8
column ID format 99
column mid format 99

select dest_id "ID",destination,status,target,
schedule,process,mountid mid
from v$archive_dest order by dest_id;

-- This select will give further detail on the destinations as to what
-- options have been set. Register indicates whether or not the archived
-- redo log is registered in the remote destination control file.

set numwidth 8
column ID format 99

select dest_id "ID",archiver,transmit_mode,affirm,async_blocks async,
net_timeout net_time,delay_mins delay,reopen_secs reopen,
register,binding
from v$archive_dest order by dest_id;

-- Determine if any error conditions have been reached by querying the
-- v$dataguard_status view (view only available in 9.2.0 and above):

column message format a80

select message, timestamp
from v$dataguard_status
where severity in ('Error','Fatal')
order by timestamp;

-- Query v$managed_standby to see the status of processes involved in
-- the shipping redo on this system. Does not include processes needed to
-- apply redo.

select process,status,client_process,sequence#
from v$managed_standby;

-- Verify that log apply services on the standby are currently
-- running. If the query against V$LOGSTDBY returns no rows then logical
-- apply is not running.

column status format a50 wrap
column type format a11
set numwidth 15

SELECT TYPE, STATUS, HIGH_SCN
FROM V$LOGSTDBY;

-- The DBA_LOGSTDBY_PROGRESS view describes the progress of SQL apply
-- operations on the logical standby databases. The APPLIED_SCN indicates
-- that committed transactions at or below that SCN have been applied. The
-- NEWEST_SCN is the maximum SCN to which data could be applied if no more
-- logs were received. This is usually the MAX(NEXT_CHANGE#)-1 from
-- DBA_LOGSTDBY_LOG. When the value of NEWEST_SCN and APPLIED_SCN are the
-- equal then all available changes have been applied. If your
-- APPLIED_SCN is below NEWEST_SCN and is increasing then SQL apply is
-- currently processing changes.

set numwidth 15

select
(case
when newest_scn = applied_scn then 'Done'
when newest_scn <= applied_scn + 9 then 'Done?'
when newest_scn > (select max(next_change#) from dba_logstdby_log)
then 'Near done'
when (select count(*) from dba_logstdby_log
where (next_change#, thread#) not in
(select first_change#, thread# from dba_logstdby_log)) > 1
then 'Gap'
when newest_scn > applied_scn then 'Not Done'
else '---' end) "Fin?",
newest_scn, applied_scn, read_scn from dba_logstdby_progress;

select newest_time, applied_time, read_time from dba_logstdby_progress;

-- Determine if apply is lagging behind and by how much. Missing
-- sequence#'s in a range indicate that a gap exists.

set numwidth 15
column trd format 99

select thread# trd, sequence#,
first_change#, next_change#,
dict_begin beg, dict_end end,
to_char(timestamp, 'hh:mi:ss') timestamp,
(case when l.next_change# < p.read_scn then 'YES'
when l.first_change# < p.applied_scn then 'CURRENT'
else 'NO' end) applied
from dba_logstdby_log l, dba_logstdby_progress p
order by thread#, first_change#;

-- Get a history on logical standby apply activity.

set numwidth 15

select to_char(event_time, 'MM/DD HH24:MI:SS') time,
commit_scn, current_scn, event, status
from dba_logstdby_events
order by event_time, commit_scn, current_scn;

-- Dump logical standby stats

column name format a40
column value format a20

select * from v$logstdby_stats;

-- Dump logical standby parameters

column name format a33 wrap
column value format a33 wrap
column type format 99

select name, value, type from system.logstdby$parameters
order by type, name;

-- Gather log miner session and dictionary information.

set numwidth 15

select * from system.logmnr_session$;
select * from system.logmnr_dictionary$;
select * from system.logmnr_dictstate$;
select * from v$logmnr_session;

-- Query the log miner dictionary for key tables necessary to process
-- changes for logical standby Label security will move AUD$ from SYS to
-- SYSTEM. A synonym will remain in SYS but Logical Standby does not
-- support this.

set numwidth 5
column name format a9 wrap
column owner format a6 wrap

select o.logmnr_uid, o.obj#, o.objv#, u.name owner, o.name
from system.logmnr_obj$ o, system.logmnr_user$ u
where
o.logmnr_uid = u.logmnr_uid and
o.owner# = u.user# and
o.name in ('JOB$','JOBSEQ','SEQ$','AUD$',
'FGA_LOG$','IND$','COL$','LOGSTDBY$PARAMETER')
order by u.name;

-- Non-default init parameters.

column name format a30 tru
column value format a48 wra
select name, value
from v$parameter
where isdefault = 'FALSE';

spool off

Posted by Arju at 10:53 PM 0 comments Labels: Data Guard, Scripts
Email This
BlogThis!
Share to Twitter
Share to Facebook
Share to Google Buzz
Reactions:

Tuesday, March 22, 2011
Scripts to Monitor a Data Guard Environment
From Oracle documentation followings are the list of views which can be used to monitor in Oracle Data Guard Environment. Based on these views you can get monitoring script that is mentioned in Diagnosis Oracle Physical Standby Data Guard Configuration and in Diagnosis Oracle Data Guard Primary Site Configuration. At the end of this topic I have also mentioned some script which you can run in your environment.

View Database Description
DBA_LOGSTDBY_EVENTS Logical only Contains information about the activity of a logical standby database. It can be used to determine the cause of failures that occur when SQL Apply is applying redo to a logical standby database.
DBA_LOGSTDBY_HISTORY Logical only Displays the history of switchovers and failovers for logical standby databases in a Data Guard configuration. It does this by showing the complete sequence of redo log streams processed or created on the local system, across all role transitions. (After a role transition, a new log stream is started and the log stream sequence number is incremented by the new primary database.)
DBA_LOGSTDBY_LOG Logical only Shows the log files registered for logical standby databases.
DBA_LOGSTDBY_NOT_UNIQUE Logical only Identifies tables that have no primary and no non-null unique indexes.
DBA_LOGSTDBY_PARAMETERS Logical only Contains the list of parameters used by SQL Apply.
DBA_LOGSTDBY_SKIP Logical only Lists the tables that will be skipped by SQL Apply.
DBA_LOGSTDBY_SKIP_TRANSACTION Logical only Lists the skip settings chosen.
DBA_LOGSTDBY_UNSUPPORTED Logical only Identifies the schemas and tables (and columns in those tables) that contain unsupported data types. Use this view when you are preparing to create a logical standby database.
V$ARCHIVE_DEST Primary, physical, snapshot, and logical Describes all of the destinations in the Data Guard configuration, including each destination's current value, mode, and status.
Note: The information in this view does not persist across an instance shutdown.
V$ARCHIVE_DEST_STATUS Primary, physical, snapshot, and logical Displays runtime and configuration information for the archived redo log destinations.
Note: The information in this view does not persist across an instance shutdown.
V$ARCHIVE_GAP Physical, snapshot, and logical Displays information to help you identify a gap in the archived redo log files.
V$ARCHIVED_LOG Primary, physical, snapshot, and logical Displays archive redo log information from the control file, including names of the archived redo log files.
V$DATABASE Primary, physical, snapshot, and logical Provides database information from the control file. Includes information about fast-start failover (available only with the Data Guard broker).
V$DATABASE_INCARNATION Primary, physical, snapshot, and logical Displays information about all database incarnations. Oracle Database creates a new incarnation whenever a database is opened with the RESETLOGS option. Records about the current and the previous incarnation are also contained in the V$DATABASE view.
V$DATAFILE Primary, physical, snapshot, and logical Provides datafile information from the control file.
V$DATAGUARD_CONFIG Primary, physical, snapshot, and logical Lists the unique database names defined with the DB_UNIQUE_NAME and LOG_ARCHIVE_CONFIGinitialization parameters.
V$DATAGUARD_STATS Primary, physical, snapshot, and logical Displays various Data Guard statistics, including apply lag and transport lag. This view can be queried on any instance of a standby database. No rows are returned if queried on a primary database. n.
V$DATAGUARD_STATUS Primary, physical, snapshot, and logical Displays and records events that would typically be triggered by any message to the alert log or server process trace files.
V$FS_FAILOVER_STATS Primary Displays statistics about fast-start failover occurring on the system.
V$LOG Primary, physical, snapshot, and logical Contains log file information from the online redo log files.
V$LOGFILE Primary, physical, snapshot, and logical Contains information about the online redo log files and standby redo log files.
V$LOG_HISTORY Primary, physical, snapshot, and logical Contains log history information from the control file.
V$LOGSTDBY_PROCESS Logical only Provides dynamic information about what is happening with SQL Apply. This view is very helpful when you are diagnosing performance problems during SQL Apply on the logical standby database, and it can be helpful for other problems.
V$LOGSTDBY_PROGRESS Logical only Displays the progress of SQL Apply on the logical standby database.
V$LOGSTDBY_STATE Logical only Consolidates information from the V$LOGSTDBY_PROCESS and V$LOGSTDBY_STATS views about the running state of SQL Apply and the logical standby database.
V$LOGSTDBY_STATS Logical only Displays LogMiner statistics, current state, and status information for a logical standby database during SQL Apply. If SQL Apply is not running, the values for the statistics are cleared.
V$LOGSTDBY_TRANSACTION Logical only Displays information about all active transactions being processed by SQL Apply on the logical standby database.
V$MANAGED_STANDBY Physical and snapshot Displays current status information for Oracle database processes related to physical standby databases.
Note: The information in this view does not persist across an instance shutdown.
V$REDO_DEST_RESP_HISTOGRAM Primary Contains the response time information for destinations that are configured for SYNC transport.
Note: The information in this view does not persist across an instance shutdown.
V$STANDBY_EVENT_HISTOGRAM Physical Contains a histogram of apply lag values for the physical standby. An entry is made in the corresponding apply lag bucket by the Redo Apply process every second. (This view returns rows only on a physical standby database that has been open in real-time query mode.)
Note: The information in this view does not persist across an instance shutdown.
V$STANDBY_LOG Physical, snapshot, and logical Contains log file information from the standby redo log files.

Run the following queries in your standby database in order to know details about your Data Guard environment.

1) In order to know when your log last applied as well as last received log time issue following query:

select 'Last Applied : ' Logs, to_char(next_time,'DD-MON-YY:HH24:MI:SS') Time
from v$archived_log
where sequence# = (select max(sequence#) from v$archived_log where applied='YES')
union
select 'Last Received : ' Logs, to_char(next_time,'DD-MON-YY:HH24:MI:SS') Time
from v$archived_log
where sequence# = (select max(sequence#) from v$archived_log);

Example output:


SQL> select 'Last Applied : ' Logs, to_char(next_time,'DD-MON-YY:HH24:MI:SS') Time
2 from v$archived_log
3 where sequence# = (select max(sequence#) from v$archived_log where applied='YES')
4 union
5 select 'Last Received : ' Logs, to_char(next_time,'DD-MON-YY:HH24:MI:SS') Time
6 from v$archived_log
7 where sequence# = (select max(sequence#) from v$archived_log);

LOGS TIME
---------------- ------------------
Last Applied : 08-MAR-11:19:18:30
Last Received : 22-MAR-11:14:20:28

2) Verify the last sequence# received and the last sequence# applied to standby database by following query:

select al.thrd "Thread", almax "Last Seq Received", lhmax "Last Seq Applied"
from (select thread# thrd, max(sequence#) almax
from v$archived_log
where resetlogs_change#=(select resetlogs_change# from v$database)
group by thread#) al,
(select thread# thrd, max(sequence#) lhmax
from v$log_history
where first_time=(select max(first_time) from v$log_history)
group by thread#) lh
where al.thrd = lh.thrd;
Example output:


SQL> select al.thrd "Thread", almax "Last Seq Received", lhmax "Last Seq Applied"
2 from (select thread# thrd, max(sequence#) almax
3 from v$archived_log
4 where resetlogs_change#=(select resetlogs_change# from v$database)
5 group by thread#) al,
6 (select thread# thrd, max(sequence#) lhmax
7 from v$log_history
8 where first_time=(select max(first_time) from v$log_history)
9 group by thread#) lh
10 where al.thrd = lh.thrd;

Thread Last Seq Received Last Seq Applied
---------- ----------------- ----------------
1 33978 2074


3)In order to know about transport lag time, apply lag and apply finish time issue,

set lines 180
col name for a40
col value for a40
col unit for a40
select
NAME,
VALUE,
UNIT
from v$dataguard_stats
union
select null,null,' ' from dual
union
select null,null,'Time Computed: '||MIN(TIME_COMPUTED)
from v$dataguard_stats;

Example output:


SQL> set lines 180
SQL> col name for a40
SQL> col value for a40
SQL> col unit for a40
SQL> select
2 NAME,
3 VALUE,
4 UNIT
5 from v$dataguard_stats
6 union
7 select null,null,' ' from dual
8 union
9 select null,null,'Time Computed: '||MIN(TIME_COMPUTED)
10 from v$dataguard_stats;

NAME VALUE UNIT
---------------------------------------- ---------------------------------------- ----------------------------------------
apply finish time day(2) to second(3) interval
apply lag +13 16:29:57 day(2) to second(0) interval
estimated startup time 45 second
transport lag day(2) to second(0) interval

Time Computed: 03/22/2011 14:24:26

6 rows selected.

4)In order to know the status of the processes involved in Data Guard configuration issue following query,

select process,status,client_process,sequence#,block#,active_agents,known_agents
from v$managed_standby;
Example output:


SQL> select process,status,client_process,sequence#,block#,active_agents,known_agents
from v$managed_standby;

PROCESS STATUS CLIENT_P SEQUENCE# BLOCK# ACTIVE_AGENTS KNOWN_AGENTS
--------- ------------ -------- ---------- ---------- ------------- ------------
ARCH CLOSING ARCH 33979 61441 0 0
ARCH CLOSING ARCH 33976 61441 0 0
ARCH CONNECTED ARCH 0 0 0 0
ARCH CLOSING ARCH 33977 61441 0 0
ARCH CLOSING ARCH 33978 61441 0 0
RFS IDLE LGWR 33980 42566 0 0
MRP0 WAIT_FOR_LOG N/A 24128 0 25 25
RFS IDLE UNKNOWN 0 0 0 0

8 rows selected.


Where the types of PROCESS may be,
- RFS - Remote file server
- MRP0 - Detached recovery server process
- MR(fg) - Foreground recovery session
- ARCH - Archiver process
- FGRD
- LGWR
- RFS(FAL)
- RFS(NEXP)
- LNS - Network server process

The process status may be,
UNUSED - No active process
ALLOCATED - Process is active but not currently connected to a primary database
CONNECTED - Network connection established to a primary database
ATTACHED - Process is actively attached and communicating to a primary database
IDLE - Process is not performing any activities
ERROR - Process has failed
OPENING - Process is opening the archived redo log
CLOSING - Process has completed archival and is closing the archived redo log
WRITING - Process is actively writing redo data to the archived redo log
RECEIVING - Process is receiving network communication
ANNOUNCING - Process is announcing the existence of a potential dependent archived redo log
REGISTERING - Process is registering the existence of a completed dependent archived redo log
WAIT_FOR_LOG - Process is waiting for the archived redo log to be completed
WAIT_FOR_GAP - Process is waiting for the archive gap to be resolved
APPLYING_LOG - Process is actively applying the archived redo log to the standby database

The client process may be,
Archival - Foreground (manual) archival process (SQL)
ARCH - Background ARCn process
LGWR - Background LGWR process

5) In the primary database ensure that everything is fine by issuing following commands. In case of RAC database ensure that these output is ok across all the instances.


select status,error from v$archive_dest where status <>'INACTIVE';
select * from v$archive_processes where status <> 'STOPPED';
show parameter dest_2
show parameter state_2
alter system set log_archive_dest_state_2=enable scope=both sid='*';
alter system switch logfile;
alter system switch logfile;
select status,error from v$archive_dest where status <>'INACTIVE';
Posted by Arju at 1:32 AM 0 comments Labels: Data Guard, Scripts
Email This
BlogThis!
Share to Twitter
Share to Facebook
Share to Google Buzz
Reactions:

Wednesday, March 16, 2011
Error ORA-01555 on Active Data Guard Standby Database
Problem Description
We have active data guard setup in our 11.2.0.1 version of Oracle database. It is upgraded to version 11.2.0.2 and now we see this ORA-1555 on the Standby Database. We can't logon to database with system, dbsnmp or other database users. Only sys as sysdba is working. Also we have investigated that we are running on system's undo Tablespace, - and not the one which has been created for undo.

From the alert log following entries are generated:

Wed Mar 16 11:48:22 2011
ORA-01555 caused by SQL statement below (SQL ID: dskjv8dgqdax0, Query Duration=8519 sec, SCN: 0x03cd.7fb9ca61):
SELECT TRANSMAIN.RECSEQNO,TRANSMAIN.TRANSIK,TRANSMAIN.TRAUNIEX,TRANSMAIN.TRANSEX,TRANSMAIN.DEPKEY,TRANSMAIN.SECIK,TRANSMAIN.PORIK,TRANSMAIN.CTP,TRANSMAIN.DEALER,TRANSMAIN.TRASPECIK,TRANSMAIN.CDYIK,TRANSMAIN.BLKIK,TRANSMAIN.TRCELMNO,TRANSMAIN.TRCBUSNO,TRANSMAIN.TRASTATREQ,TRANSMAIN.TRASTATACT,TRANSMAIN.TRASTATREV,TRANSMAIN.TRACAN,TRANSMAIN.BLKDATE,TRANSMAIN.TRANSORG,TRANSMAIN.TRANOLINK,TRANSMAIN.XI_TRANSCOSTTAX,TRANSMAIN.XI_TRANSSETTLE,TRANSMAIN.XI_TRANSINSTRUC,TRANSMAIN.XI_BALBOOKPFC,TRANSMAIN.NOMVAL,TRANSMAIN.NOMVALEX,TRANSMAIN.AGRDATE,TRANSMAIN.INTBEGDATE,TRANSMAIN.STLMDATE,TRANSMAIN.PMTDATE,TRANSMAIN.TRAPRICE,TRANSMAIN.TRAYIELD,TRANSMAIN.CURVALQC,TRANSMAIN.CURVALPC,TRANSMAIN.ACRINTQC,TRANSMAIN.ACRINTPC,TRANSMAIN.STLMAMTSC,TRANSMAIN.FXRATEQP,TRANSMAIN.FXRATEQS,TRANSMAIN.ACRINTDAYS,TRANSMAIN.STLMCUR,TRANSMAIN.EXTNOTEDATE,TRANSMAIN.EXTNOTEID,TRANSMAIN.BOOKMONTH,TRANSMAIN.FXRATEIP,TRANSMAIN.ACRINTSC,TRANSMAIN.AGIODISASC,TRANSMAIN.CAPCHGID,TRANSMAIN.REFIRATE,TRANSMAIN.FROMDATE,TRANSMAIN.TODATE,TRANSMAIN.EXCTYPE
Wed Mar 16 11:59:29 2011
Thread 1 cannot allocate new log, sequence 13253
Private strand flush not complete
Current log# 4 seq# 17780 mem# 0: /u02/redo/redo401.log
Thread 1 advanced to log sequence 13253 (LGWR switch)
Current log# 1 seq# 13253 mem# 0: /u02/redo/redo101.log
Wed Mar 16 11:59:32 2011
LNS: Standby redo logfile selected for thread 1 sequence 13253 for destination LOG_ARCHIVE_DEST_2
Wed Mar 16 11:59:34 2011
Archived Log entry 35541 added for thread 1 sequence 17780 ID 0x21653caa dest 1:
Wed Mar 16 12:02:20 2011
ALTER SYSTEM ARCHIVE LOG
Wed Mar 16 12:02:20 2011
Thread 1 cannot allocate new log, sequence 13254
Private strand flush not complete
Current log# 1 seq# 13253 mem# 0: /u02/redo/redo101.log
Thread 1 advanced to log sequence 13254 (LGWR switch)
Current log# 2 seq# 13254 mem# 0: /u02/redo/redo201.log
Archived Log entry 35543 added for thread 1 sequence 13253 ID 0x21653caa dest 1:

Cause of the Problem
According to Oracle support document ID 1273808.1 "ORA-01555 on Active Data Guard Standby Database" raised due to Oracle bug -
Bug 10320455: QUERY IN ACTIVE DATA GUARD RETURNS ORA-1555
which is duplicate from
Bug 10092353: ACTIVE DATAGUAD INSTANCE HANGS DUE TO A LIBRARY CACHE LOCK HOLDER

Solution of the Problem
To solve this bug you have to apply the Patch 10018789 from Oracle support for your Platform on Top of 11.2.0.2.
Posted by Arju at 1:22 AM 0 comments Labels: Data Guard, Troubleshooting
Email This
BlogThis!
Share to Twitter
Share to Facebook
Share to Google Buzz
Reactions:

Monday, March 7, 2011
Convert a Physical Standby Database into a Snapshot Standby Database using dgmgrl
This document will discuss the detail steps of converting a physical standby database to a snapshot standby database using the Data Guard Command Line Interface (dgmgrl). If you would like to know how to perform the conversion manually then please have a look at the post Convert a Physical Standby Database into a Snapshot Standby Database. Note that, in order to use the dgmgrl you must first configure data guard broker.

Step 01: Invoke dgmgrl utility from OS and connect to eithe primary or physical standby database.

[oracle@DRS-DB-01 ~]$ dgmgrl
DGMGRL for Linux: Version 11.2.0.1.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys
Password:
Connected.
DGMGRL> show configuration;

Configuration - bddipdc

Protection Mode: MaxPerformance
Databases:
bddipdc- Primary database
bddipdrs- Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL>
Step 02: Issue CONVERT DATABASE TO SNAPSHOT to convert a physical standby to a snapshot standby database.

DGMGRL> convert database standby to snapshot standby;
Converting database "standby" to a Snapshot Standby database, please wait...
Database "standby" converted successfully
DGMGRL>
Step 03: Issue SHOW CONFIGURATION command again to see the status.

Configuration - bddipdc

Protection Mode: MaxPerformance
Databases:
bddipdc - Primary database
bddipdrs - Snapshot standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL>
Note that, to convert the snapshot standby database back to a physical standby database use the command CONVERT DATABASE TO PHYSICAL STANDBY. The conversion process does perform shutdown of the standby database which is required for the FLASHBACK DATABASE operation.

DGMGRL> convert database standby to physical standby;
Converting database "standby" to a Physical Standby database, please wait...
Operation requires shutdown of instance "standby" on database "standby"
Shutting down instance "standby"...
Database closed.
Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "standby" on database "standby"
Starting instance "standby"...
ORACLE instance started.
Database mounted.
Continuing to convert database "standby" ...
Operation requires shutdown of instance "standby" on database "standby"
Shutting down instance "standby"...
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "standby" on database "standby"
Starting instance "standby"...
ORACLE instance started.
Database mounted.
Database "standby" converted successfully
DGMGRL>
Posted by Arju at 11:29 PM 0 comments Labels: Data Guard
Email This
BlogThis!
Share to Twitter
Share to Facebook
Share to Google Buzz
Reactions:

Converting a Physical Standby Database into a Snapshot Standby Database
Before starting into the steps it is worth to know what is snapshot standby database.

A snapshot standby database is created from physical standby database. Once we create a snapshot standby database it continues to receive redo log from primary database and archive the redo logs but it does not apply redo log or archived redo log to the snapshot standby database.

Redo data received from the primary database is applied when a snapshot standby database is converted back into a physical standby database, after discarding all local updates to the snapshot standby database.

A snapshot standby database typically diverges from its primary database over time because redo data from the primary database is not applied as it is received. As once we create snapshot standby database in READ WRITE mode then we can update the database locally, so local updates to the snapshot standby database will cause additional divergence. The data in the primary database is fully protected however, because a snapshot standby can be converted back into a physical standby database at any time, and the redo data received from the primary will then be applied.

The following are the steps to convert a physical standby database into a snapshot standby database in RAC environment.

Step 01: Stop Redo Apply in Physical Standby Database, if it is active.
Login to any database instance of the Physical standby database if it is RAC. In case of Non-RAC you have only once instance/one database.

- Check status by,

SQL> select open_mode , database_role, name, DB_UNIQUE_NAME from v$database;

OPEN_MODE DATABASE_ROLE NAME DB_UNIQUE_NAME
-------------------- ---------------- --------- ------------------------------
MOUNTED PHYSICAL STANDBY BDDIPDC BDDIPDRS
- I have also checked the instance to know in which instance I am performing stop redo log.

SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
bddipdc1
- Issue stop redo log apply command.

SQL> alter database recover managed standby database cancel;

Database altered.
Step 02: In case of RAC database, shut down all but one instance.
If you are non RAC environment then simply you can ignore this step. In case of RAC environment just you need to shutdown other instances except one. In this case I have 2 node RAC and I am just shutting down bddipdc2 instance.

$ sqlplus sys/oracle@bddipdrs2 as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Tue Mar 8 12:48:48 2011

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,
OLAP, Data Mining, Oracle Database Vault and Real Application Testing options

SQL> select open_mode , database_role, name, DB_UNIQUE_NAME from v$database;

OPEN_MODE DATABASE_ROLE NAME DB_UNIQUE_NAME
-------------------- ---------------- --------- ------------------------------
MOUNTED PHYSICAL STANDBY BDDIPDC BDDIPDRS

SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
bddipdc2

SQL> shutdown immediate;
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
Step 03: Ensure that database is mounted and not open. Also ensure that a fast recovery area has been configured.
It is not necessary for flashback database to be enabled but you should enable FLASHBACK database if you want to convert a snapshot standby database back into a physical standby database.

Checking fast recovery area by,

SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 1
Next log sequence to archive 1
Current log sequence 1

SQL> show parameter db_reco

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string +RECOVERY
db_recovery_file_dest_size big integer 800G
which means fast recovery area is configured as we are using DB_RECOVERY_FILE_DEST as archival destination.

You can check it is mounted but not opened by,

SQL> select open_mode , database_role, name, DB_UNIQUE_NAME from v$database;

OPEN_MODE DATABASE_ROLE NAME DB_UNIQUE_NAME
-------------------- ---------------- --------- ------------------------------
MOUNTED PHYSICAL STANDBY BDDIPDC BDDIPDRS
You can check flashback database option by,

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
YES

Step 04: Issue the "CONVERT TO SNAPSHOT STANDBY" command.

SQL> alter database convert to snapshot standby;

Database altered.

Step 05: Open the database and check the database_role and open_mode.

SQL> alter database open;

Database altered.

SQL> select open_mode , database_role, name, DB_UNIQUE_NAME from v$database;

OPEN_MODE DATABASE_ROLE NAME DB_UNIQUE_NAME
-------------------- ---------------- --------- ------------------------------
READ WRITE SNAPSHOT STANDBY BDDIPDC BDDIPDRS
Now let's see in the alert log to know what happens in the backend.

alter database recover managed standby database cancel
Tue Mar 08 12:17:24 2011
MRP0: Background Media Recovery cancelled with status 16037
Errors in file /u01/app/oracle/diag/rdbms/bddipdrs/bddipdc1/trace/bddipdc1_pr00_17371.trc:
ORA-16037: user requested cancel of managed recovery operation
Recovery interrupted!
Waiting for MRP0 pid 17366 to terminate
Errors in file /u01/app/oracle/diag/rdbms/bddipdrs/bddipdc1/trace/bddipdc1_pr00_17371.trc:
ORA-16037: user requested cancel of managed recovery operation
Tue Mar 08 12:17:24 2011
MRP0: Background Media Recovery process shutdown (bddipdc1)
Managed Standby Recovery Canceled (bddipdc1)
Completed: alter database recover managed standby database cancel
Tue Mar 08 12:17:42 2011
Reconfiguration started (old inc 8, new inc 10)
List of instances:
1 (myinst: 1)
Global Resource Directory frozen
* dead instance detected - domain 0 invalid = TRUE
Communication channels reestablished
Master broadcasted resource hash value bitmaps
Non-local Process blocks cleaned out
Tue Mar 08 12:17:42 2011
LMS 0: 0 GCS shadows cancelled, 0 closed, 0 Xw survived
Tue Mar 08 12:17:42 2011
LMS 1: 0 GCS shadows cancelled, 0 closed, 0 Xw survived
Set master node info
Submitted all remote-enqueue requests
Dwn-cvts replayed, VALBLKs dubious
All grantable enqueues granted
Post SMON to start 1st pass IR
Submitted all GCS remote-cache requests
Post SMON to start 1st pass IR
Fix write in gcs resources
Reconfiguration complete
Tue Mar 08 12:17:53 2011
alter database convert to snapshot standby
Created guaranteed restore point SNAPSHOT_STANDBY_REQUIRED_03/08/2011 12:17:53
Tue Mar 08 12:17:53 2011
krsv_proc_kill: Killing 219043332100 processes (all RFS)
Begin: Standby Redo Logfile archival
End: Standby Redo Logfile archival
RESETLOGS after incomplete recovery UNTIL CHANGE 449582938
Resetting resetlogs activation ID 1523683415 (0x5ad19057)
Online log +DATA/bddipdrs/onlinelog/group_1.351.728933147: Thread 1 Group 1 was previously cleared
Online log +DATA/bddipdrs/onlinelog/group_1.256.728933147: Thread 1 Group 1 was previously cleared
Online log +DATA/bddipdrs/onlinelog/group_2.356.728933149: Thread 1 Group 2 was previously cleared
Online log +DATA/bddipdrs/onlinelog/group_2.326.728933149: Thread 1 Group 2 was previously cleared
Online log +DATA/bddipdrs/onlinelog/group_4.379.728933149: Thread 2 Group 4 was previously cleared
Online log +DATA/bddipdrs/onlinelog/group_4.366.728933149: Thread 2 Group 4 was previously cleared
Online log +DATA/bddipdrs/onlinelog/group_14.374.728933149: Thread 2 Group 14 was previously cleared
Online log +DATA/bddipdrs/onlinelog/group_14.354.728933149: Thread 2 Group 14 was previously cleared
Standby became primary SCN: 449582936
Tue Mar 08 12:17:55 2011
Setting recovery target incarnation to 6
CONVERT TO SNAPSHOT STANDBY: Complete - Database mounted as snapshot standby
Completed: alter database convert to snapshot standby
alter database open
This instance was first to open
Picked broadcast on commit scheme to generate SCNs
Tue Mar 08 12:18:00 2011
Assigning activation ID 1523975741 (0x5ad6063d)
Thread 1 opened at log sequence 1
Current log# 1 seq# 1 mem# 0: +DATA/bddipdrs/onlinelog/group_1.351.728933147
Current log# 1 seq# 1 mem# 1: +DATA/bddipdrs/onlinelog/group_1.256.728933147
Successful open of redo thread 1
Tue Mar 08 12:18:01 2011
ARC4: Becoming the 'no SRL' ARCH
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Tue Mar 08 12:18:01 2011
ARC0: Becoming the 'no SRL' ARCH
Tue Mar 08 12:18:01 2011
SMON: enabling cache recovery
Instance recovery: looking for dead threads
Instance recovery: lock domain invalid but no dead threads
Successfully onlined Undo Tablespace 2.
Dictionary check beginning
Dictionary check complete
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
SMON: enabling tx recovery
Database Characterset is AL32UTF8
No Resource Manager plan active
Starting background process GTX0
Tue Mar 08 12:18:02 2011
GTX0 started with pid=43, OS id=24612
Starting background process RCBG
Tue Mar 08 12:18:02 2011
RCBG started with pid=44, OS id=24614
replication_dependency_tracking turned off (no async multimaster replication found)
Redo thread 2 internally disabled at seq 1 (CKPT)
Tue Mar 08 12:18:02 2011
ARC3: Becoming the 'no SRL' ARCH
ARC3: Archiving disabled thread 2 sequence 1
Archived Log entry 48206 added for thread 2 sequence 1 ID 0x0 dest 1:
Tue Mar 08 12:18:03 2011
Starting background process QMNC
Tue Mar 08 12:18:03 2011
QMNC started with pid=46, OS id=24618
LOGSTDBY: Validating controlfile with logical metadata
LOGSTDBY: Validation complete
Completed: alter database open

Note that, when the physical standby is converted to a snapshot standby database a guaranteed restore point is created. In this case we see the name is SNAPSHOT_STANDBY_REQUIRED_03/08/2011. When the snapshot standby is converted back into a physical standby this restore point will be used to flashback the standby to its original state prior to the conversion.

If you want to convert the snapshot standby database into physical standby database then you simply need to do,
SQL> shutdown immediate
SQL> startup mount
SQL> alter database convert to physical standby;

In case of RAC you just need to shut down all but one instance.
Posted by Arju at 11:08 PM 0 comments Labels: Data Guard, RAC
Email This
BlogThis!
Share to Twitter
Share to Facebook
Share to Google Buzz
Reactions:

Monday, October 18, 2010
Creation of second standby database using RMAN DUPLICATE fails with RMAN-20242
Problem Description
You can run the following command to create a physical standby database from the primary database.

run{
allocate channel prmy1 type disk;
allocate channel prmy2 type disk;
allocate auxiliary channel stby1 type disk;
duplicate target database for standby from active database
spfile
parameter_value_convert 'BDDIPDC','BDDIPDRS'
set db_file_name_convert='/BDDIPDC/','/BDDIPDRS/'
set log_file_name_convert='/BDDIPDC/','/BDDIPDRS/'
set 'db_unique_name'='BDDIPDRS'
set control_files='+DATA/BDDIPDRS/control01.ctl','+DATA/BDDIPDRS/control02.ctl'
set db_recovery_file_dest='+RECOVERY/BDDIPDRS'
set DB_RECOVERY_FILE_DEST_SIZE='500G'
nofilenamecheck;
}
So you have one primary and one physical standby database. Now you want to add another standby database in your Data Guard environment. And you issued following command but it failed with
RMAN-20242

rman target sys/sys@bddipdc auxiliary sys/sys@bddipdrs
DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE DORECOVER SPFILE
SET db_unique_name = 'BDDIPDRS'
SET FAL_CLIENT = 'BDDIPDRS'
SET FAL_SERVER = 'BDDIPDC'
set log_archive_dest_1 = 'LOCATION=USE_DB_RECOVERY_FILE_DEST valid_for=(all_logfiles,all_roles) db_unique_name=x11106_stby2'
set log_archive_dest_2 = 'db_unique_name=BDDIPDC SERVICE=BDDIPDC valid_for=(online_logfile, primary_role) REOPEN=60 OPTIONAL LGWR SYNC AFFIRM'
CONTROL_FILES='+DATA/BDDIPDRS/control01.dbf'
set DB_FILE_NAME_CONVERT='/BDDIPDC/','/BDDIPDRS/'
set LOG_FILE_NAME_CONVERT='/BDDIPDC/','/BDDIPDRS/'
NOFILENAMECHECK;
Following is the error output,

RMAN-8162: executing Memory Script

RMAN-3090: Starting backup at 01-NOV-10
RMAN-12016: using channel ORA_DISK_1
RMAN-571: ===========================================================
RMAN-569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-571: ===========================================================
RMAN-3002: failure of Duplicate Db command at 11/01/2010 04:26:39
RMAN-3015: error occurred in stored script Memory Script
RMAN-20242: specification does not match any archived log in the recovery
catalog
Cause of the Problem
The problem happened due to oracle bug. If the source database has standby destination, then while copying archived logs from the source database the bug will fire. Oracle named this bug as Bug 6603587.

Solution of the Problem
Solution 01: This bug is fixed in Oracle 11.2 and in patchset 11.1.0.7. So upgrade your current oracle software is a solution.

Solution 02: For DUPLICATE DATABASE FOR STANDBY FROM ACTIVE, simply avoid using the DORECOVER clause. Logs will ship naturally as part of the standby log shipping.

Solution 03: For DUPLICATE TARGET DATABASE ... FROM ACTIVE, defer all standby destinations prior to executing the command. For example, issue
alter system set LOG_ARCHIVE_DEST_STATE_2=DEFER scope=both sid='*';
where LOG_ARCHIVE_DEST_STATE_2 is standby destination.
Posted by Arju at 12:50 AM 0 comments Labels: Data Guard, RMAN
Email This
BlogThis!
Share to Twitter
Share to Facebook
Share to Google Buzz
Reactions:

Thursday, October 14, 2010
Step by step Oracle Data Guard Configuration
In the post Step by step Oracle Data Guard Configuration I have already discussed about about the detail procedure of creating physical standby database. After you create standby database if you want to configure Oracle Data Guard Broker in order to centrally manage primary and all the standby databases then in the post Prerequisites for Oracle Data Guard Broker it is discussed the prerequisites for data guard broker.

After you follow above two posts now it is the time to configure data guard broker.

Step 01: Check for DG_BROKER_START initialization parameter. Login to database through sql*plus and issue show parameter dg_broker_start,

SQL> show parameter dg_broker_start

NAME TYPE VALUE
------------------------------------ ----------- ----------------------------
dg_broker_start boolean FALSE
If it is set to FALSE then proceed further. If it is TRUE then your Data Guard broker DMON process is running. So you will not be able to change configuration file parameters. In order to set the configuration file of your own ensure that dg_broker_start is set to FALSE. You can do it by,

SQL> ALTER SYSTEM SET DG_BROKER_START=FALSE;
Step 02: Set the configuration filenames for the database.
Two copies of the configuration file are maintained for each database so as to always have a record of the last known valid state of the configuration. When the broker is started for the first time, the configuration files are automatically created and named using a default path name and filename that is operating-system specific. You can override this default path name and filename by setting the following initialization parameters for that database:

DG_BROKER_CONFIG_FILE1
DG_BROKER_CONFIG_FILE2

You can check the current configuration file by,

SQL> show parameter dg_broker_config

NAME TYPE VALUE
------------------------------------ ----------- ---------------------------
dg_broker_config_file1 string /u01/app/oracle/DR1A.DAT
dg_broker_config_file2 string /u01/app/oracle/DR2A.DAT
You can change/override these settings. Note that if you are in Oracle RAC instances then these two parameter must be to a raw device or Oracle ASM file or cluster file system file that is shared by every instance of the RAC. For example issue,

SQL> ALTER SYSTEM SET DG_BROKER_CONFIG_FILE1=+DATA/bddipdc/config1.dat;
SQL> ALTER SYSTEM SET DG_BROKER_CONFIG_FILE2=+DATA/bddipdc/config2.dat;
Step 03: Restart the DMON process by setting DG_BROKER_START to TRUE.

SQL> ALTER SYSTEM SET DG_BROKER_START=TRUE;
Step 04: Invoke DGMGRL and connect to the primary database.
$ dgmgrl

The DGMGRL prompt is displayed:
DGMGRL>

Note that, the command you write inside dgmgrl prompt is treated as lowercase. So if you want to write something in uppercase within dgmgrl you have to specify within quote.

To connect to primary database on the Local System issue,

DGMGRL> CONNECT sys;
Password: password
Connected.
If you connect to the Primary Database on a Remote System issue,

DGMGRL> CONNECT sys@Nbddipdc
Step 05: Create the broker configuration
If your primary database name is BDDIPDC, connect identifier/tns names of primary database is BDDIPDC and you want to name the configuration as DGCONF then your command will be following.

DGMGRL> CREATE CONFIGURATION 'DGCONF' AS PRIMARY DATABASE IS 'BDDIPDC' CONNECT IDENTIFIER IS 'BDDIPDC';
After you create configuration, you can issue 'show configuration' in order to show the configuration information.

DGMGRL> SHOW CONFIGURATION;
Step 06: Add a standby database to the configuration.
To add a standby database named BDDIPDRS (note that this need to be DB_UNIQUE_NAME) and connect identifier BDDIPDRS issue,

DGMGRL> ADD DATABASE 'BDDIPDRS' AS CONNECT IDENTIFIER IS 'BDDIPDRS';
Note that, you can omit single quote if your DB_UNIQUE_NAME is in lowercase.

Use the SHOW CONFIGURATION command to verify that the 'BDDIPDRS' database was added to the 'DGCONF' configuration:

DGMGRL> SHOW CONFIGURATION;
Posted by Arju at 1:38 AM 0 comments Labels: Data Guard
Email This
BlogThis!
Share to Twitter
Share to Facebook
Share to Google Buzz
Reactions:

Tuesday, October 12, 2010
Prerequisites for Oracle Data Guard Broker
In the post Step by step creating a physical standby database I have already discussed about the detail procedure of creating physical standby database. After you create a standby database now it is the time to configure data guard broker. This post discuss about the prerequisites for Oracle Data Guard Broker configuration.

Following are the prerequisites:

1. Primary and standby databases must use same version of Oracle database and must have Oracle Enterprise Edition or Personal Edition. Either primary or standby or both can be RAC or non-RAC database.

2. You must use SPFILE so that the broker can persistently reconcile values between broker properties and any related initialization parameter values. If you have RAC database then keep the spfile in shared storage.

3. Setup DG_BROKER_CONFIG_FILEn initialization parameters to a location where you want to keep broker configuration file. If you are in RAC environment, then you must set up the DG_BROKER_CONFIG_FILEn initialization parameters for that database such that they point to the same shared files for all instances of that database.

4. The value of the DG_BROKER_START initialization parameter must be set to TRUE.

5. To enable DGMGRL to restart instances during the course of broker operations, a service with a specific name must be statically registered with the local listener of each instance. A static service registration is also required to enable the observer to restart instances as part of automatic reinstatement of the old primary database after a fast-start failover has occurred. The broker uses a default name for the GLOBAL_DBNAME attribute of db_unique_name_DGMGRL.db_domain. For example, in the LISTENER.ORA file:

SID_LIST_LISTENER2 =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = bdafisdc_DGMGRL.world)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = bdafisdc1)
)
)

LISTENER2 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = LISTENER2))
(ADDRESS = (PROTOCOL = TCP)(HOST =DC-DB-01)(PORT = 1522))
)
)
Alternatively, you can use a different static service name. If you do, be sure to modify the StaticConnectIdentifier instance-specific property to reflect the different service name.

Along with previous configuration it is assumed that you have environment of standby database.
Posted by Arju at 6:49 AM 0 comments Labels: Data Guard
Email This
BlogThis!
Share to Twitter
Share to Facebook
Share to Google Buzz
Reactions:

Sunday, October 3, 2010
Step by step Create an Oracle 11gR2 Physical Standby Database
In this article I will show step by step procedure to create an Oracle 11gR2 physical standby database. This example environment is as follows:

- Both primary and standby database are on RAC environment. Both are 2 nodes RAC.
- The distance between primary and standby database is 300 kilometers and they are within Bangladesh.
- There is 10 Mbps dedicated bandwidth between primary and standby database.
- All 4 nodes are using Red Hat Linux Enterprise version 5 operating system and same hardware structure.
- Oracle 11gR2 is installed both in primary and physical standby database.
- Oracle datafiles and clusterware are on ASM file system.

Though this example is based on RAC and ASM, you can use this document to setup any type of physical standby database. I will tell explicitly what to do based on whether your environment is non-RAC or not, whether you use ASM or not.

In this example the following values are used:
- Database name is bddipdc
- Primary database unique name as well as oracle net service name is bddipdc
- Standby database unique name as well as oracle net service name is bddipdrs
- Primary database hostname is DC-DB-01 and standby database hostname is DRS-DB-01
- Primary database IP Address is 192.168.100.101 and standby database IP Address is 192.168.105.101

Step 01: Prepare the Primary and physical standby database environment: (Both bddipdc and bddipdrs)
i) After you have physically placed hardware in both site install operating system in both end. The site in which primary database reside called as Data Center (DC) and the site in which standby database reside called as Disaster Recovery Site (DRS) and that is I choose the unique name as bddipdc and bddipdrs correspondingly.

ii) Install Oracle software as well as oracle database both in primary and standby database site. Though database creation in standby site is not mandatory but you can create database specially if you are in RAC environment. Because in that case you don't need to do many manual tasks like register database to cluster registry etc. Note that both in primary and standby site, while creating database keep the database name same that is bddipdc.

Step 02: Prepare the Primary Database for Standby Database Creation: (Only bddipdc)
i) Enable force logging on primary database.
SQL> ALTER DATABASE FORCE LOGGING;

ii) Set the database initialization parameters.

#Primary database role initialization parameters.
DB_NAME=bddipdc
DB_UNIQUE_NAME=bddipdc
LOG_ARCHIVE_CONFIG='DG_CONFIG=(bddipdc,bddipdrs)'
LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=bddipdc'
LOG_ARCHIVE_DEST_2='SERVICE=bddipdrs SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=bddipdrs'
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

# Standby role initialization parameters.
FAL_SERVER=bddipdrs
DB_FILE_NAME_CONVERT='bddipdrs','bddipdc'
LOG_FILE_NAME_CONVERT='bddipdrs','bddipdc'
STANDBY_FILE_MANAGEMENT=AUTO
As primary database is using spfile so run the following commands on primary database.

alter system set DB_UNIQUE_NAME=bddipdc scope=spfile sid='*';
alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(bddipdc,bddipdrs)' scope=both sid='*';
alter system set LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=bddipdc' scope=both sid='*';
alter system set LOG_ARCHIVE_DEST_2='SERVICE=bddipdrs SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=bddipdrs' scope=both sid='*';
alter system set LOG_ARCHIVE_DEST_STATE_1=ENABLE scope=both sid='*';
alter system set LOG_ARCHIVE_DEST_STATE_2=ENABLE scope=both sid='*';
alter system set LOG_ARCHIVE_MAX_PROCESSES=30 scope=both sid='*';
alter system set STANDBY_FILE_MANAGEMENT=AUTO scope=both sid='*';
alter system set FAL_SERVER=bddipdrs scope=both sid='*';
alter system set FAL_CLIENT=bddipdc scope=both sid='*';
We have not set any DB_FILE_NAME_CONVERT and LOG_FILE_NAME_CONVERT parameters because in standby database we want to keep same file structure as it is in primary database.

iii) Enable archivelog in primary database.
If the primary database is not in archivelog mode then enable archivelog in primary database. If your database is in archivelog mode then you can simply forward to next phase.

SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE ARCHIVELOG;
SQL> ALTER DATABASE OPEN;
If your database is in RAC environment then you can follow Enable archivelog for RAC. If you are in non RAC environment then you can follow Change archivelog mode .

Step 03: Determine datafiles, controlfiles, password file, initialization parameter files to be copied from primary to standby database (bddipdc):
You can create standby database using RMAN. But in this post I want to show all steps manually because RMAN has several bug while creating another copy of database over the network.

i)Connect to one Oracle instance and determine the location of datafiles and tempfiles. Save the file location in a notepad.
Determine the location of datafile,

SQL> select name from v$datafile;
NAME
----------------------------------------------------
+DATA/bddipdc/datafile/system.267.728507607
+DATA/bddipdc/datafile/sysaux.259.728507607
+DATA/bddipdc/datafile/undotbs1.258.728507609
+DATA/bddipdc/datafile/users.257.728507609
+DATA/bddipdc/datafile/undotbs2.270.728507783
Determine the location of oracle tempfile.
SQL> select name from v$tempfile;
NAME
----------------------------------
+DATA/bddipdc/tempfile/temp.271.728507701

ii)Create the pfile from spfile of primary database.
SQL> conn / as sysdba
SQL> create pfile='/tmp/bddipdc/initbddipdc.ora' from spfile;

iii) Shutdown the primary database. If you are in RAC environment, then shut down all instances.
SQL> shut immediate;

Using srvctl,
$ srvctl stop database -d bddipdc

iv) This step is only applicable if your database files are in ASM. As our datafiles and tempfiles are in ASM file system so we need to copy them under /tmp/bddipdc in order to move the datafiles and tempfiles into another computer.

Ensure that your ORACLE_SID and ORACLE_HOME points to ASM home and ASM SID.
Invoke asmcmd.

$asmcmd
ASMCMD> cp +DATA/bddipdc/datafile/system.267.728507607 /tmp/bddipdc/system
ASMCMD> cp +DATA/bddipdc/datafile/sysaux.259.728507607 /tmp/bddipdc/sysaux
ASMCMD> cp +DATA/bddipdc/datafile/undotbs1.258.728507609 /tmp/bddipdc/undotbs1
ASMCMD> cp +DATA/bddipdc/datafile/users.257.728507609 /tmp/bddipdc/users
ASMCMD> cp +DATA/bddipdc/datafile/undotbs2.270.728507783 /tmp/bddipdc/undotbs2
ASMCMD> cp +DATA/bddipdc/tempfile/temp.271.728507701 /tmp/bddipdc/temp
v) Start one instance of primary database in mount state. If you are in non RAC environment then just start database in mount state.
SQL> startup mount;

Create the standby control file.
SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/tmp/bddipdc/control01.ora';

Step 04: Copy password file, datafile, standby controlfile, pfile from primary database to standby database.
Up to now we have datafile, tempfile, standby controlfile, pfile under one directory /tmp/bddipdc in the primary database. Now also we need to move password file into /tmp/bddipdc directory so that we can simply scp to remote location and with one command all files will be copied to standby database.
$ scp -r /tmp/bddipdc oracle@drs-db-01:/tmp/bddipdc

Copy node 1 of primary database password file across all nodes in standby database (This one is only applicable for RAC)
The password file of node 1 of primary database need to be copied across all instances in standby database as well as all other nodes in primary database as well.

Step 05: Start the Standby database and set initialization parameters. (bddipdrs)
At this stage we have all required files under location /tmp/bddipdc to setup physical standby database. We have already one database created in standby site. So we can use that spfile for our standby database creation. Though we have pfile in the location /tmp/bddipdc (which we copied from primary database) we will use current spfile of existing standby database in order minimize the steps.

i) Shut down standby database (in RAC shut down all instances) and start in nomount state (in RAC start one instance in nomount stage).
SQL> shut immediate;
SQL> startup nomount;

Set the initialization parameters.

SQL> alter system set DB_UNIQUE_NAME=bddipdrs scope=spfile sid='*';
alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(bddipdc,bddipdrs)' scope=both sid='*';
alter system set LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=bddipdrs' scope=both sid='*';
alter system set LOG_ARCHIVE_DEST_2='SERVICE=bddipdc SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=bddipdc' scope=both sid='*';
alter system set LOG_ARCHIVE_DEST_STATE_1=ENABLE scope=both sid='*';
alter system set LOG_ARCHIVE_DEST_STATE_2=ENABLE scope=both sid='*';
alter system set LOG_ARCHIVE_MAX_PROCESSES=30 scope=both sid='*';
alter system set STANDBY_FILE_MANAGEMENT=AUTO scope=both sid='*';
alter system set FAL_SERVER=bddipdc scope=both sid='*';
alter system set FAL_CLIENT=bddipdrs scope=both sid='*';
We also set the location of control_files. We will copy standby control file into this location.
alter system set control_files='+DATA/bddipdc/controlfile/control01.ctl' scope=spfile sid='*';

iii) shutdown the standby database.
SQL> shut immediate;

iv) Delete all control files, datafiles, tempfiles of current standby database and copy these standby control file, datafile, tempfile into ASM file system. I am keeping the path of these files same as primary database. Note that if you are not in ASM file system then instead of +DATA use your desired location.

$ asmcmd
ASMCMD> cp /tmp/bddipdc/control01.ora +DATA/bddipdc/controlfile/control01.ctl
ASMCMD> cp /tmp/bddipdc/system +DATA/bddipdc/datafile/system
ASMCMD> cp /tmp/bddipdc/sysaux +DATA/bddipdc/datafile/sysaux
ASMCMD> cp /tmp/bddipdc/undotbs1 +DATA/bddipdc/datafile/undotbs1
ASMCMD> cp /tmp/bddipdc/undotbs2 +DATA/bddipdc/datafile/undotbs2
ASMCMD> cp /tmp/bddipdc/users +DATA/bddipdc/datafile/users
ASMCMD> cp /tmp/bddipdc/temp /tmp/bddipdc/temp
v) Start the standby database in mount state.
SQL> startup mount

Set the undo_management parameter manual.
SQL> alter system set undo_management=MANUAL scope=both sid='*';

Now rename datafiles and tempfiles in order to use the correct location as we copied to ASM file system.

SQL> alter database rename file '+DATA/bddipdc/datafile/system.267.728507607' to '+DATA/bddipdc/datafile/system';
SQL> alter database rename file '+DATA/bddipdc/datafile/sysaux.259.728507607' to '+DATA/bddipdc/datafile/sysaux';
SQL> alter database rename file '+DATA/bddipdc/datafile/undotbs1.258.728507609' to '+DATA/bddipdc/datafile/undotbs1';
SQL> alter database rename file '+DATA/bddipdc/datafile/users.257.728507609' to '+DATA/bddipdc/datafile/users';
SQL> alter database rename file '+DATA/bddipdc/datafile/undotbs2.270.728507783' to '+DATA/bddipdc/datafile/undotbs2';
SQL> alter database rename file '+DATA/bddipdc/tempfile/temp.271.728507701' to '+DATA/bddipdc/datafile/temp';
Step 06: Configure TNS entry and listener across all nodes. (All nodes)
In all nodes configure TNS entry so that you can reach from primary to standby database and vice versa.
Within the file $ORACLE_HOME/network/admin/tnsnames.ora add the following two entries.

BDDIPDRS =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = DRS-DB-01)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = bddipdc.world)
)
)

BDDIPDC =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = DC-DB-01)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = bddipdc.world)
)
)
Before configuring TNS entry be sure about your host name, port number and service name. You can easily check that by issuing,
$ lsnrctl status

You should put an static listener entry within $GRID_HOME/network/admin/listener.ora if you are in RAC
and you should put an static listener entry within $ORACLE_HOME/network/admin/listener.ora if you are in non RAC system.

Following is an static entry for node 1 from primary database.

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = bddipdc.world)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = bddipdc1)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = LISTENER))
(ADDRESS = (PROTOCOL = TCP)(HOST =DC-DB-01)(PORT = 1521))
)
)
Note: If you are in non RAC system then instead of SID bddipdc1 you must use Database name that is bddipdc.

Following is an static listener entry for node 2 in primary database.

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = bddipdc.world)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = bddipdc2)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = LISTENER))
(ADDRESS = (PROTOCOL = TCP)(HOST =DC-DB-02)(PORT = 1521))
)
)
Note: Just host name and Oracle SID name is change for node 2.

Following is the listener entry for the node 1 in standby database.

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = bddipdc.world)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = bddipdc1)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = LISTENER))
(ADDRESS = (PROTOCOL = TCP)(HOST =DRS-DB-01)(PORT = 1521))
)
)
Note: Just I have changed the hostname. If you are non RAC system then instead of instance SID bddipdc1 use database name bddipddc instead.

Step 7: Verify that TNS names and password file across all the instances are working properly. (All nodes):
Both in primary and standby database across all nodes issue,

$ tnsping bddipdc
$ tnsping bddipdrs
$ sqlplus sys/password@bddipdc as sysdba
$ sqlplus sys/password@bddipdrs as sysdba
If you can connect to both primary and standby instances using TNS entry from all nodes through sqlplus then your password file as well as TNS entry is perfect.


Step 8: Create standby redo log and start redo apply on standby database (bddipdrs):
Create standby redo logfile on standby database bddipdrs by following command,

alter database add standby logfile thread 1 size 50M;
alter database add standby logfile thread 2 size 50M;
alter database add standby logfile thread 1 size 50M;
alter database add standby logfile thread 2 size 50M;
alter database add standby logfile thread 1 size 50M;
alter database add standby logfile thread 2 size 50M;
Or, simply you can run following command if you are in non RAC environment.

alter database add standby logfile size 50M;
Set the undo_management parameter to AUTO.

SQL> alter system set undo_management=AUTO scope=both sid='*';
Start Redo Apply,

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

Step 09: Verify that standby database is performing properly.
i)Identify the existing archived redo log files. (bddipdrs -standby)
On the standby database, query the V$ARCHIVED_LOG view to identify existing files in the archived redo log. For example:

SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

ii)Force a log switch to archive the current online redo log file.
On the primary database, issue the ALTER SYSTEM SWITCH LOGFILE statement to force a log switch and archive the current online redo log file group:

SQL> ALTER SYSTEM SWITCH LOGFILE;

iii) Verify the new redo data was archived on the standby database.
On the standby database, query the V$ARCHIVED_LOG view to verify the redo data was received and archived on the standby database:

SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
In the output you should see one extra row than previous query.

iv)Verify that received redo has been applied.
On the standby database, query the V$ARCHIVED_LOG view to verify that received redo has been applied:

SQL> SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
Posted by Arju at 7:42 AM 3 comments Labels: ASM, Data Guard, RAC
Email This
BlogThis!
Share to Twitter
Share to Facebook
Share to Google Buzz
Reactions:

Wednesday, September 29, 2010
Primary DB freezed with waited too long for a row cache enqueue lock
Problem Description
On Oracle database 10.2.0.3.0 Data Guard with Broker configuration whenever there is an attempting to restart the standby in read only, the following error occurs:

ERROR: WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
ORA-12170: TNS:Connect timeout occurred
PING[ARC6]: Error 3113 when pinging standby
ARC6: Attempting destination LOG_ARCHIVE_DEST_2 network reconnect (1089)

The errors occurred in standby database and primary database hangs!

Cause of the Problem
The above error is caused by LGWR process in Oracle Data Guard having RAC environment and it is in fact Oracle bug. The bug number is 7487408.
The bug fires whenever Data Guard is managed by Broker and standby database is shutdown or opened in read only mode. The Primary still tries to ship/send redo streams to standby in SYNC mode and eventually hangs.

Solution of the Problem
- Download the optach 7487408 from oracle support/metalink in order to solve the bug.
- The above bug is fixed in Oracle patchset 10.2.0.5. So you need to upgrade your oracle database to 10.2.0.5.
Posted by Arju at 1:00 AM 0 comments Labels: Bug, Data Guard, RAC
Email This
BlogThis!
Share to Twitter
Share to Facebook
Share to Google Buzz
Reactions:

Wednesday, September 22, 2010
New Features in Oracle Data Guard Broker 11g
As 11g has two releases 11.2g and 11.1g so I will include new features into two sections.
New features in Oracle Data Guard Broker 11.1g
1. Fast start failover can be performed from a Data Guard broker if the database configuration is operating in maximum performance mode.

2. The new DBMS_DG PL/SQL package allows applications to notify the observer process to initiate a fast-start failover to the target standby database.

3. We can configure data guard broker to perform fast start failover based errors raised by the Oracle server.

4. After a failover, the observer automatically attempts to reinstate bystander standby databases.

5. Through DGMGRL CONVERT DATABASE command, you can convert physical standby database to a snapshot standby database, or reverts the snapshot standby database back to a physical standby database.

6. You can specify a connect identifier for Redo Transport Services as you want.

7. No need to shutdown database when changing the protection mode to and from maximum availability and maximum performance.

8. Support for Redo Apply on physical standby databases opened for read.

9. Allow a primary or logical standby database in a Data Guard Broker configuration to be mounted without having to be opened automatically.

10. The DGMGRL client ADD DATABASE command can import a pre-configured service attribute from a LOG_ARCHIVE_DEST_n parameter when adding a standby database to the configuration. So we can only issue "ADD DATABASE {DATABASE_NAME}" command instead of specifying any connect identifier.

11. A primary database can be in either the TRANSPORT-OFF or TRANSPORT-ON state. A standby database can be in either the APPLY-OFF or APPLY-ON state. In this way, database states become simpler.

12. In 11.1g the network bandwidth utilization in a Data Guard configuration is done efficiently by compressing archived redo logs as they are transmitted over the network to standby databases, when the log is sent to satisfy a gap at a standby database.

13. You can now specify USE_DB_RECOVERY_FILE_DEST as the value for the AlternateLocation and StandbyArchiveLocation instance-specific properties.

14. The following new Data Guard command-line interface (DGMGRL) commands are available:

CONVERT DATABASE TO SNAPSHOT STANDBY

CONVERT DATABASE TO PHYSICAL STANDBY

DISABLE FAST_START FAILOVER CONDITION

ENABLE FAST_START FAILOVER CONDITION

SHOW FAST_START FAILOVER

15. The following new database properties are available:

DGConnectIdentifier (replaces InitialConnectIdentifier)

ObserverConnectIdentifier

RedoCompression

16. The following new configuration properties are available:

FastStartFailoverAutoReinstate

FastStartFailoverLagLimit

FastStartFailoverPmyShutdown

17. The LsbyTxnConsistency database property has been renamed to LsbyPreserveCommitOrder.

18. New default of ASYNC for the LogXptMode database property.


New features in Oracle Data Guard Broker 11.2g
1. Now the broker configuration consist of 1 primary database and up to 30 standby databases.

2. The value of an instance-specific property can be changed for all instances in one command.

3. In 11.1g redo transport compression was limited to compressing redo data only when a redo gap is being resolved. In 11.2g when compression is enabled for a destination, all redo data sent to that destination is compressed.

4. Improved status and error reporting for the SHOW CONFIGURATION and SHOW DATABASE commands.

5. The SHOW DATABASE command displays the apply lag and transport lag of a standby database.

6. Shutting down all instances but one on a target physical standby for role changes is no longer required.

7. FAN/ONS (Fast Application Notification/Oracle Notification Services) notification is sent after a failover for databases configured with Oracle Clusterware and for single-instance databases registered with Oracle Restart.

8. FAN/AQ (Fast Application Notification/Advanced Queueing) notification is sent after a failover to a logical standby database.

9. Integration with Active Data Guard (automatic stop and restart of apply) when an apply instance is opened.

10. Reinstatement of the old (original) primary database after an immediate failover.

11. Improved hang detection and resolution for broker network communications. In addition, a new configuration property, CommunicationTimeout, enables the user to select an appropriate timeout value for their environment.

12. New database property, StaticConnectIdentifier, which allows the user to specify a static connect identifier that the DGMGRL client will use to start database instances.

13. Protection mode upgrades from maximum availability to maximum protection no longer require a restart of the primary database. This means that when upgrading from maximum performance mode to maximum protection mode, you can avoid a restart of the primary database by first upgrading to maximum availability. Once in maximum availability mode, you can then upgrade to maximum protection mode.

14. Data Recovery Adviser now uses Data Guard to suggest viable repair options for a primary database that cannot be repaired in a timely manner.
Posted by Arju at 12:26 AM 0 comments Labels: Data Guard
Email This
BlogThis!
Share to Twitter
Share to Facebook
Share to Google Buzz
Reactions:

Saturday, July 17, 2010
While startup standby database it fails with ORA-01154
Problem Description
Whenever you try to open or shutdown a standby database it fails with error ORA-01154
SQL> connect / as sysdba
Connected.

SQL> alter database open
ORA-01154: database busy. Open, close, mount, and dismount not allowed now

SQL> shutdown
ORA-01154: database busy. Open, close, mount, and dismount not allowed now

Cause of the Problem
The database is a standby database and it is in managed recovery mode. To verify the database is in managed recovery mode, enter this command

SQL> SELECT RECOVERY_MODE FROM V$ARCHIVE_DEST_STATUS;

If it returns MANAGED then the standby database is in managed recovery mode.

If database is in managed recover mode then it is not allowed to do open or shutdown the database.

Solution of the Problem
To take the database out of managed recovery mode, enter this command

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

You can now open the database as,
SQL> ALTER DATABASE OPEN READ ONLY;

Or, you may now shutdown the database.
SQL> shutdown
Posted by Arju at 12:31 PM 0 comments Labels: Data Guard
Email This
BlogThis!
Share to Twitter
Share to Facebook
Share to Google Buzz
Reactions:

Wednesday, December 16, 2009
ORA-01665: control file is not a standby control file
Problem Description
While recovering standby database, using command alter database recover managed standby database disconnect from session it fails with message ORA-01665: control file is not a standby control file like below.
SQL> alter database recover managed standby database disconnect from session;
alter database recover managed standby database disconnect from session
*
ERROR at line 1:
ORA-01665: control file is not a standby control file

Cause of the Problem
The error occurred because there was an attempt to mount, recover, or activate a standby database without a standby controlfile. In order to recover standby database you need standby controlfile. If you don't have standby controlfile and use normal controlfile then above error will occurred.

Solution of the Problem
The solution is to create a standby controlfile before attempting to use the database as a standby database.

1)So if you don't have a standby controlfile for your standby first create a standby controlfile from source database using command,

SQL>alter database create standby controlfile as 'standbyctlfile.ctl';

2)Transfer this standby controlfile into standby database.

3)Edit the standby database pfile control_files parameter in order to effect the changes.

4)Startup the database in nomount stage.
SQL> startup nomount pfile='your_pfile_location';

5)Create spfile from the pfile.
SQL> create spfile from pfile='your_pfile_location';

6)Shutdown the database.
SQL>shutdown immediate;

7)Start the database in mount state.
SQL>startup mount;

8)On the standby database, start redo apply by following command.
SQL>alter database recover managed standby database disconnect from session;

Note that only step 1) need to be performed in primary database only. All other steps will be done in standby database.

Related Documents
http://arjudba.blogspot.com/2009/04/different-types-of-standby-database-in.html
http://arjudba.blogspot.com/2009/04/what-is-oracle-data-guard.htm

Oracle 11gR2 Physical Standby Database

Step by step Create an Oracle 11gR2 Physical Standby Database In this article I will show step by step procedure to create an Oracle 11gR2 physical standby database. This example environment is as follows:

- Both primary and standby database are on RAC environment. Both are 2 nodes RAC.
- The distance between primary and standby database is 300 kilometers and they are within Bangladesh.
- There is 10 Mbps dedicated bandwidth between primary and standby database.
- All 4 nodes are using Red Hat Linux Enterprise version 5 operating system and same hardware structure.
- Oracle 11gR2 is installed both in primary and physical standby database.
- Oracle datafiles and clusterware are on ASM file system.

Though this example is based on RAC and ASM, you can use this document to setup any type of physical standby database. I will tell explicitly what to do based on whether your environment is non-RAC or not, whether you use ASM or not.

In this example the following values are used:
- Database name is bddipdc
- Primary database unique name as well as oracle net service name is bddipdc
- Standby database unique name as well as oracle net service name is bddipdrs
- Primary database hostname is DC-DB-01 and standby database hostname is DRS-DB-01
- Primary database IP Address is 192.168.100.101 and standby database IP Address is 192.168.105.101

Step 01: Prepare the Primary and physical standby database environment: (Both bddipdc and bddipdrs)
i) After you have physically placed hardware in both site install operating system in both end. The site in which primary database reside called as Data Center (DC) and the site in which standby database reside called as Disaster Recovery Site (DRS) and that is I choose the unique name as bddipdc and bddipdrs correspondingly.

ii) Install Oracle software as well as oracle database both in primary and standby database site. Though database creation in standby site is not mandatory but you can create database specially if you are in RAC environment. Because in that case you don't need to do many manual tasks like register database to cluster registry etc. Note that both in primary and standby site, while creating database keep the database name same that is bddipdc.

Step 02: Prepare the Primary Database for Standby Database Creation: (Only bddipdc)
i) Enable force logging on primary database.
SQL> ALTER DATABASE FORCE LOGGING;

ii) Set the database initialization parameters.

#Primary database role initialization parameters.
DB_NAME=bddipdc
DB_UNIQUE_NAME=bddipdc
LOG_ARCHIVE_CONFIG='DG_CONFIG=(bddipdc,bddipdrs)'
LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=bddipdc'
LOG_ARCHIVE_DEST_2='SERVICE=bddipdrs SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=bddipdrs'
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

# Standby role initialization parameters.
FAL_SERVER=bddipdrs
DB_FILE_NAME_CONVERT='bddipdrs','bddipdc'
LOG_FILE_NAME_CONVERT='bddipdrs','bddipdc'
STANDBY_FILE_MANAGEMENT=AUTO
As primary database is using spfile so run the following commands on primary database.

alter system set DB_UNIQUE_NAME=bddipdc scope=spfile sid='*';
alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(bddipdc,bddipdrs)' scope=both sid='*';
alter system set LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=bddipdc' scope=both sid='*';
alter system set LOG_ARCHIVE_DEST_2='SERVICE=bddipdrs SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=bddipdrs' scope=both sid='*';
alter system set LOG_ARCHIVE_DEST_STATE_1=ENABLE scope=both sid='*';
alter system set LOG_ARCHIVE_DEST_STATE_2=ENABLE scope=both sid='*';
alter system set LOG_ARCHIVE_MAX_PROCESSES=30 scope=both sid='*';
alter system set STANDBY_FILE_MANAGEMENT=AUTO scope=both sid='*';
alter system set FAL_SERVER=bddipdrs scope=both sid='*';
alter system set FAL_CLIENT=bddipdc scope=both sid='*';
We have not set any DB_FILE_NAME_CONVERT and LOG_FILE_NAME_CONVERT parameters because in standby database we want to keep same file structure as it is in primary database.

iii) Enable archivelog in primary database.
If the primary database is not in archivelog mode then enable archivelog in primary database. If your database is in archivelog mode then you can simply forward to next phase.

SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE ARCHIVELOG;
SQL> ALTER DATABASE OPEN;
If your database is in RAC environment then you can follow Enable archivelog for RAC. If you are in non RAC environment then you can follow Change archivelog mode .

Step 03: Determine datafiles, controlfiles, password file, initialization parameter files to be copied from primary to standby database (bddipdc):
You can create standby database using RMAN. But in this post I want to show all steps manually because RMAN has several bug while creating another copy of database over the network.

i)Connect to one Oracle instance and determine the location of datafiles and tempfiles. Save the file location in a notepad.
Determine the location of datafile,

SQL> select name from v$datafile;
NAME
----------------------------------------------------
+DATA/bddipdc/datafile/system.267.728507607
+DATA/bddipdc/datafile/sysaux.259.728507607
+DATA/bddipdc/datafile/undotbs1.258.728507609
+DATA/bddipdc/datafile/users.257.728507609
+DATA/bddipdc/datafile/undotbs2.270.728507783
Determine the location of oracle tempfile.
SQL> select name from v$tempfile;
NAME
----------------------------------
+DATA/bddipdc/tempfile/temp.271.728507701

ii)Create the pfile from spfile of primary database.
SQL> conn / as sysdba
SQL> create pfile='/tmp/bddipdc/initbddipdc.ora' from spfile;

iii) Shutdown the primary database. If you are in RAC environment, then shut down all instances.
SQL> shut immediate;

Using srvctl,
$ srvctl stop database -d bddipdc

iv) This step is only applicable if your database files are in ASM. As our datafiles and tempfiles are in ASM file system so we need to copy them under /tmp/bddipdc in order to move the datafiles and tempfiles into another computer.

Ensure that your ORACLE_SID and ORACLE_HOME points to ASM home and ASM SID.
Invoke asmcmd.

$asmcmd
ASMCMD> cp +DATA/bddipdc/datafile/system.267.728507607 /tmp/bddipdc/system
ASMCMD> cp +DATA/bddipdc/datafile/sysaux.259.728507607 /tmp/bddipdc/sysaux
ASMCMD> cp +DATA/bddipdc/datafile/undotbs1.258.728507609 /tmp/bddipdc/undotbs1
ASMCMD> cp +DATA/bddipdc/datafile/users.257.728507609 /tmp/bddipdc/users
ASMCMD> cp +DATA/bddipdc/datafile/undotbs2.270.728507783 /tmp/bddipdc/undotbs2
ASMCMD> cp +DATA/bddipdc/tempfile/temp.271.728507701 /tmp/bddipdc/temp
v) Start one instance of primary database in mount state. If you are in non RAC environment then just start database in mount state.
SQL> startup mount;

Create the standby control file.
SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/tmp/bddipdc/control01.ora';

Step 04: Copy password file, datafile, standby controlfile, pfile from primary database to standby database.
Up to now we have datafile, tempfile, standby controlfile, pfile under one directory /tmp/bddipdc in the primary database. Now also we need to move password file into /tmp/bddipdc directory so that we can simply scp to remote location and with one command all files will be copied to standby database.
$ scp -r /tmp/bddipdc oracle@drs-db-01:/tmp/bddipdc

Copy node 1 of primary database password file across all nodes in standby database (This one is only applicable for RAC)
The password file of node 1 of primary database need to be copied across all instances in standby database as well as all other nodes in primary database as well.

Step 05: Start the Standby database and set initialization parameters. (bddipdrs)
At this stage we have all required files under location /tmp/bddipdc to setup physical standby database. We have already one database created in standby site. So we can use that spfile for our standby database creation. Though we have pfile in the location /tmp/bddipdc (which we copied from primary database) we will use current spfile of existing standby database in order minimize the steps.

i) Shut down standby database (in RAC shut down all instances) and start in nomount state (in RAC start one instance in nomount stage).
SQL> shut immediate;
SQL> startup nomount;

Set the initialization parameters.

SQL> alter system set DB_UNIQUE_NAME=bddipdrs scope=spfile sid='*';
alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(bddipdc,bddipdrs)' scope=both sid='*';
alter system set LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=bddipdrs' scope=both sid='*';
alter system set LOG_ARCHIVE_DEST_2='SERVICE=bddipdc SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=bddipdc' scope=both sid='*';
alter system set LOG_ARCHIVE_DEST_STATE_1=ENABLE scope=both sid='*';
alter system set LOG_ARCHIVE_DEST_STATE_2=ENABLE scope=both sid='*';
alter system set LOG_ARCHIVE_MAX_PROCESSES=30 scope=both sid='*';
alter system set STANDBY_FILE_MANAGEMENT=AUTO scope=both sid='*';
alter system set FAL_SERVER=bddipdc scope=both sid='*';
alter system set FAL_CLIENT=bddipdrs scope=both sid='*';
We also set the location of control_files. We will copy standby control file into this location.
alter system set control_files='+DATA/bddipdc/controlfile/control01.ctl' scope=spfile sid='*';

iii) shutdown the standby database.
SQL> shut immediate;

iv) Delete all control files, datafiles, tempfiles of current standby database and copy these standby control file, datafile, tempfile into ASM file system. I am keeping the path of these files same as primary database. Note that if you are not in ASM file system then instead of +DATA use your desired location.

$ asmcmd
ASMCMD> cp /tmp/bddipdc/control01.ora +DATA/bddipdc/controlfile/control01.ctl
ASMCMD> cp /tmp/bddipdc/system +DATA/bddipdc/datafile/system
ASMCMD> cp /tmp/bddipdc/sysaux +DATA/bddipdc/datafile/sysaux
ASMCMD> cp /tmp/bddipdc/undotbs1 +DATA/bddipdc/datafile/undotbs1
ASMCMD> cp /tmp/bddipdc/undotbs2 +DATA/bddipdc/datafile/undotbs2
ASMCMD> cp /tmp/bddipdc/users +DATA/bddipdc/datafile/users
ASMCMD> cp /tmp/bddipdc/temp /tmp/bddipdc/temp
v) Start the standby database in mount state.
SQL> startup mount

Set the undo_management parameter manual.
SQL> alter system set undo_management=MANUAL scope=both sid='*';

Now rename datafiles and tempfiles in order to use the correct location as we copied to ASM file system.

SQL> alter database rename file '+DATA/bddipdc/datafile/system.267.728507607' to '+DATA/bddipdc/datafile/system';
SQL> alter database rename file '+DATA/bddipdc/datafile/sysaux.259.728507607' to '+DATA/bddipdc/datafile/sysaux';
SQL> alter database rename file '+DATA/bddipdc/datafile/undotbs1.258.728507609' to '+DATA/bddipdc/datafile/undotbs1';
SQL> alter database rename file '+DATA/bddipdc/datafile/users.257.728507609' to '+DATA/bddipdc/datafile/users';
SQL> alter database rename file '+DATA/bddipdc/datafile/undotbs2.270.728507783' to '+DATA/bddipdc/datafile/undotbs2';
SQL> alter database rename file '+DATA/bddipdc/tempfile/temp.271.728507701' to '+DATA/bddipdc/datafile/temp';
Step 06: Configure TNS entry and listener across all nodes. (All nodes)
In all nodes configure TNS entry so that you can reach from primary to standby database and vice versa.
Within the file $ORACLE_HOME/network/admin/tnsnames.ora add the following two entries.

BDDIPDRS =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = DRS-DB-01)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = bddipdc.world)
)
)

BDDIPDC =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = DC-DB-01)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = bddipdc.world)
)
)
Before configuring TNS entry be sure about your host name, port number and service name. You can easily check that by issuing,
$ lsnrctl status

You should put an static listener entry within $GRID_HOME/network/admin/listener.ora if you are in RAC
and you should put an static listener entry within $ORACLE_HOME/network/admin/listener.ora if you are in non RAC system.

Following is an static entry for node 1 from primary database.

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = bddipdc.world)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = bddipdc1)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = LISTENER))
(ADDRESS = (PROTOCOL = TCP)(HOST =DC-DB-01)(PORT = 1521))
)
)
Note: If you are in non RAC system then instead of SID bddipdc1 you must use Database name that is bddipdc.

Following is an static listener entry for node 2 in primary database.

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = bddipdc.world)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = bddipdc2)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = LISTENER))
(ADDRESS = (PROTOCOL = TCP)(HOST =DC-DB-02)(PORT = 1521))
)
)
Note: Just host name and Oracle SID name is change for node 2.

Following is the listener entry for the node 1 in standby database.

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = bddipdc.world)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = bddipdc1)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = LISTENER))
(ADDRESS = (PROTOCOL = TCP)(HOST =DRS-DB-01)(PORT = 1521))
)
)
Note: Just I have changed the hostname. If you are non RAC system then instead of instance SID bddipdc1 use database name bddipddc instead.

Step 7: Verify that TNS names and password file across all the instances are working properly. (All nodes):
Both in primary and standby database across all nodes issue,

$ tnsping bddipdc
$ tnsping bddipdrs
$ sqlplus sys/password@bddipdc as sysdba
$ sqlplus sys/password@bddipdrs as sysdba
If you can connect to both primary and standby instances using TNS entry from all nodes through sqlplus then your password file as well as TNS entry is perfect.


Step 8: Create standby redo log and start redo apply on standby database (bddipdrs):
Create standby redo logfile on standby database bddipdrs by following command,

alter database add standby logfile thread 1 size 50M;
alter database add standby logfile thread 2 size 50M;
alter database add standby logfile thread 1 size 50M;
alter database add standby logfile thread 2 size 50M;
alter database add standby logfile thread 1 size 50M;
alter database add standby logfile thread 2 size 50M;
Or, simply you can run following command if you are in non RAC environment.

alter database add standby logfile size 50M;
Set the undo_management parameter to AUTO.

SQL> alter system set undo_management=AUTO scope=both sid='*';
Start Redo Apply,

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

Step 09: Verify that standby database is performing properly.
i)Identify the existing archived redo log files. (bddipdrs -standby)
On the standby database, query the V$ARCHIVED_LOG view to identify existing files in the archived redo log. For example:

SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

ii)Force a log switch to archive the current online redo log file.
On the primary database, issue the ALTER SYSTEM SWITCH LOGFILE statement to force a log switch and archive the current online redo log file group:

SQL> ALTER SYSTEM SWITCH LOGFILE;

iii) Verify the new redo data was archived on the standby database.
On the standby database, query the V$ARCHIVED_LOG view to verify the redo data was received and archived on the standby database:

SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
In the output you should see one extra row than previous query.

iv)Verify that received redo has been applied.
On the standby database, query the V$ARCHIVED_LOG view to verify that received redo has been applied:

SQL> SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;