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#;

Migrating Oracle10g Database to Automatic Storage management (ASM)

Step By Step Instructions on Migrating Oracle10g Database to Automatic Storage

Disable Block change tracking:

SQL> select * from v$block_change_tracking;

STATUS

----------

FILENAME

--------------------------------------------------------------------------------

BYTES

----------

DISABLED



If not disabled then, disble using this command.


SQL> ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;
Database altered.


SQL>

Shutdown Database Cleanly:

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> exit

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options
Create pfile and add/modify the below parameters:


[oracle@node1-pub oracle]$ sqlplus "/ as sysdba"


SQL*Plus: Release 10.2.0.1.0 - Production on Fri Jul 21 12:17:50 2006



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



Connected to an idle instance.



SQL> create pfile from spfile;
File created.


Modify pfile with these parameters:

I have already created 2 ASM diskgroups DATA and FLASH.


*.control_files=(+DATA, +FLASH)

*.db_recovery_file_dest=+FLASH

*.db_recovery_file_dest_size=2147483648

*.db_create_file_dest=+DATA

*.db_create_online_log_dest_1=+FLASH

*.db_create_online_log_dest_2=+DATA -- optional if you want another online redo logs dest.


Create spfile back from modified pfile:

PS: take a copy of original spfile before you overwrite spfile using below command.

SQL> create spfile from pfile;



File created.

SQL> exit

Disconnected


Copy Database to ASM diskgroups using rman:

(1) start the instance on NOMOUNT state

(2) copy the controlfile from old location to ASM usin "resrore" rman command

(3) mount the database

(4) copy the datafiles to ASM disk group using rman "BACKUP AS COPY DATABASE" command

(5) Switch database to COPY and open the database.



[oracle@node1-pub oracle]$ $ORACLE_HOME/bin/rman



Recovery Manager: Release 10.2.0.1.0 - Production on Fri Jul 21 10:03:10 2006



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



RMAN> connect target



connected to target database (not started)



RMAN> startup nomount



Oracle instance started



Total System Global Area 167772160 bytes



Fixed Size 1218316 bytes

Variable Size 83888372 bytes

Database Buffers 79691776 bytes

Redo Buffers 2973696 bytes



RMAN> restore controlfile from '/home/oracle/oradata/db10g/control01.ctl';



Starting restore at 21-JUL-06

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=157 devtype=DISK



channel ORA_DISK_1: copied control file copy

output filename=+DATA/db10g/controlfile/backup.256.596369129

output filename=+FLASH/db10g/controlfile/backup.256.596369131

Finished restore at 21-JUL-06



RMAN> startup mount



database is already started

database mounted

released channel: ORA_DISK_1



RMAN> configure device type disk parallelism 4;



new RMAN configuration parameters:

CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE TO BACKUPSET;

new RMAN configuration parameters are successfully stored



RMAN> BACKUP AS COPY DATABASE FORMAT '+DATA';


Starting backup at 21-JUL-06

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=152 devtype=DISK

allocated channel: ORA_DISK_2

channel ORA_DISK_2: sid=151 devtype=DISK

allocated channel: ORA_DISK_3

channel ORA_DISK_3: sid=150 devtype=DISK

allocated channel: ORA_DISK_4

channel ORA_DISK_4: sid=149 devtype=DISK

channel ORA_DISK_1: starting datafile copy

input datafile fno=00001 name=/home/oracle/oradata/db10g/system01.dbf

channel ORA_DISK_2: starting datafile copy

input datafile fno=00003 name=/home/oracle/oradata/db10g/sysaux01.dbf

channel ORA_DISK_3: starting datafile copy

input datafile fno=00002 name=/home/oracle/oradata/db10g/undotbs01.dbf

channel ORA_DISK_4: starting datafile copy

input datafile fno=00004 name=/home/oracle/oradata/db10g/users01.dbf

output filename=+DATA/db10g/datafile/undotbs1.259.596369341 tag=TAG20060721T100858 recid=2 stamp=596369352

channel ORA_DISK_3: datafile copy complete, elapsed time: 00:00:16

channel ORA_DISK_3: starting datafile copy

copying current control file

output filename=+DATA/db10g/datafile/users.260.596369341 tag=TAG20060721T100858 recid=1 stamp=596369350

channel ORA_DISK_4: datafile copy complete, elapsed time: 00:00:20

channel ORA_DISK_4: starting full datafile backupset

channel ORA_DISK_4: specifying datafile(s) in backupset

output filename=+DATA/db10g/controlfile/backup.261.596369361 tag=TAG20060721T100858 recid=3 stamp=596369364

channel ORA_DISK_3: datafile copy complete, elapsed time: 00:00:06

including current SPFILE in backupset

channel ORA_DISK_4: starting piece 1 at 21-JUL-06

channel ORA_DISK_4: finished piece 1 at 21-JUL-06

piece handle=+DATA/db10g/backupset/2006_07_21/nnsnf0_tag20060721t100858_0.262.596369369 tag=TAG20060721T100858 comment=NONE

channel ORA_DISK_4: backup set complete, elapsed time: 00:00:10

output filename=+DATA/db10g/datafile/sysaux.258.596369341 tag=TAG20060721T100858 recid=4 stamp=596369390

channel ORA_DISK_2: datafile copy complete, elapsed time: 00:01:05

output filename=+DATA/db10g/datafile/system.257.596369339 tag=TAG20060721T100858 recid=5 stamp=596369414

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:21

Finished backup at 21-JUL-06



RMAN> SWITCH DATABASE TO COPY;



datafile 1 switched to datafile copy "+DATA/db10g/datafile/system.257.596369339"

datafile 2 switched to datafile copy "+DATA/db10g/datafile/undotbs1.259.596369341"

datafile 3 switched to datafile copy "+DATA/db10g/datafile/sysaux.258.596369341"

datafile 4 switched to datafile copy "+DATA/db10g/datafile/users.260.596369341"



RMAN> alter database open;



database opened



RMAN> exit


Recovery Manager complete.





Migrate tempfile to ASM:



RMAN does not migrate the tempfile as part of the BACKUP AS COPY and SWITCH command becuase the tempfile is not listed in controlfile.
The tempfile has to be manually migrated to ASM.



[oracle@node1-pub oracle]$ sqlplus "/ as sysdba"



SQL*Plus: Release 10.2.0.1.0 - Production on Fri Jul 21 10:12:42 2006



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





Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options



SQL> select name, bytes from v$tempfile;



NAME

--------------------------------------------------------------------------------

BYTES

----------

/home/oracle/oradata/db10g/temp01.dbf

20971520



SQL> create temporary tablespace temp1 tempfile SIZE 100M extent management local uniform size 1M;



Tablespace created.



SQL> alter database default temporary tablespace temp1;



Database altered.



SQL> drop tablespace temp including contents;



Tablespace dropped.



SQL> create temporary tablespace temp tempfile SIZE 100M extent management local uniform size 1M;



Tablespace created.



SQL> alter database default temporary tablespace temp;



Database altered.



SQL> drop tablespace temp1 including contents;



Tablespace dropped.



SQL> select name from v$tempfile;



NAME

--------------------------------------------------------------------------------

+DATA/db10g/tempfile/temp.264.596370217



Migrate and drop the old Online Redo Logs to ASM:



Run the below procedure to migrate the redo logs to ASM. This program is taken from OTN/metalink.



SQL> declare

cursor orlc is

select lf.member, l.bytes

from v$log l, v$logfile lf

where l.group# = lf.group# and

lf.type = 'ONLINE'

order by l.thread#, l.sequence#;



type numTab_t is table of number index by binary_integer;

type charTab_t is table of varchar2(1024) index by binary_integer;

byteslist numTab_t; namelist charTab_t;



procedure migrateorlfile(name IN varchar2, bytes IN number) is

retry number;

stmt varchar2(1024);

als varchar2(1024) := 'alter system switch logfile';

begin

select count(*) into retry from v$logfile;

stmt := 'alter database add logfile size ' || bytes;

execute immediate stmt;

stmt := 'alter database drop logfile ''' || name || '''';

for i in 1..retry loop

begin execute immediate stmt;

exit;

exception

when others then

if i > retry then raise;

end if;

execute immediate als;

end;

end loop;

end;

begin

open orlc;

fetch orlc bulk collect into namelist, byteslist;

close orlc;

for i in 1..namelist.count loop migrateorlfile(namelist(i), byteslist(i));

end loop;

end;

/

2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40



PL/SQL procedure successfully completed.



SQL> SQL>

SQL>





SQL> select member from v$logfile;



MEMBER

--------------------------------------------------------------------------------

+FLASH/db10g/onlinelog/group_3.259.596373299

+FLASH/db10g/onlinelog/group_2.258.596373295

/home/oracle/oradata/db10g/redo01.log

+FLASH/db10g/onlinelog/group_4.257.596373293



SQL> alter system switch logfile;



System altered.



SQL> /



System altered.



SQL> /



System altered.



SQL> /



System altered.



Re-Execute the same script again in order to migrate the remaining ones.



SQL> declare

cursor orlc is

select lf.member, l.bytes

from v$log l, v$logfile lf

where l.group# = lf.group# and

lf.type = 'ONLINE'

order by l.thread#, l.sequence#;



type numTab_t is table of number index by binary_integer;

type charTab_t is table of varchar2(1024) index by binary_integer;

byteslist numTab_t; namelist charTab_t;



procedure migrateorlfile(name IN varchar2, bytes IN number) is

retry number;

stmt varchar2(1024);

als varchar2(1024) := 'alter system switch logfile';

begin

select count(*) into retry from v$logfile;

stmt := 'alter database add logfile size ' || bytes;

execute immediate stmt;

stmt := 'alter database drop logfile ''' || name || '''';

for i in 1..retry loop

begin execute immediate stmt;

exit;

exception

when others then

if i > retry then raise;

end if;

execute immediate als;

end;

end loop;

end;

begin

open orlc;

fetch orlc bulk collect into namelist, byteslist;

close orlc;

for i in 1..namelist.count loop migrateorlfile(namelist(i), byteslist(i));

end loop;

end;

/ 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40



PL/SQL procedure successfully completed.



SQL> select member from v$logfile;



MEMBER

--------------------------------------------------------------------------------

+FLASH/db10g/onlinelog/group_3.259.596373619

+FLASH/db10g/onlinelog/group_2.258.596373615

+FLASH/db10g/onlinelog/group_1.261.596373613

+FLASH/db10g/onlinelog/group_4.257.596373293

+FLASH/db10g/onlinelog/group_5.260.596373609



SQL> exit

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options



DELETE THE OLD DATAFILES USING RMAN.



This way, it will also clear out the datafiles entry from controlfile.



[oracle@node1-pub oracle]$ $ORACLE_HOME/bin/rman



Recovery Manager: Release 10.2.0.1.0 - Production on Fri Jul 21 11:22:33 2006



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



RMAN> connect target



connected to target database: DB10G (DBID=4283639931)



RMAN> run {

2> DELETE COPY OF DATABASE;

3> }



using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=134 devtype=DISK

allocated channel: ORA_DISK_2

channel ORA_DISK_2: sid=151 devtype=DISK

allocated channel: ORA_DISK_3

channel ORA_DISK_3: sid=153 devtype=DISK

allocated channel: ORA_DISK_4

channel ORA_DISK_4: sid=138 devtype=DISK



List of Datafile Copies

Key File S Completion Time Ckp SCN Ckp Time Name

------- ---- - --------------- ---------- --------------- ----

6 1 A 21-JUL-06 461254 21-JUL-06 /home/oracle/oradata/db10g/system01.dbf

7 2 A 21-JUL-06 461254 21-JUL-06 /home/oracle/oradata/db10g/undotbs01.dbf

8 3 A 21-JUL-06 461254 21-JUL-06 /home/oracle/oradata/db10g/sysaux01.dbf

9 4 A 21-JUL-06 461254 21-JUL-06 /home/oracle/oradata/db10g/users01.dbf



Do you really want to delete the above objects (enter YES or NO)? YES

deleted datafile copy

datafile copy filename=/home/oracle/oradata/db10g/system01.dbf recid=6 stamp=596369439

deleted datafile copy

datafile copy filename=/home/oracle/oradata/db10g/undotbs01.dbf recid=7 stamp=596369439

deleted datafile copy

datafile copy filename=/home/oracle/oradata/db10g/sysaux01.dbf recid=8 stamp=596369440

deleted datafile copy

datafile copy filename=/home/oracle/oradata/db10g/users01.dbf recid=9 stamp=596369440

Deleted 4 objects





RMAN> exit





Recovery Manager complete.


REMOVE THE OLD ONLINE REDO LOGS FILES PHYSICALLY:




[oracle@node1-pub oracle]$ rm /home/oracle/oradata/db10g/redo*.log

[oracle@node1-pub oracle]$ sqlplus "/ as sysdba"



SQL*Plus: Release 10.2.0.1.0 - Production on Fri Jul 21 11:29:56 2006



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





Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options


Enable the block change tracking:

SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING;
Database altered.


SQL>

How To Move The Database To Different Diskgroup Migrating from External Redundancy to Normal Redundancy

Solution

Create a new diskgroup with desired redundancy and move the existing data to newly created diskgroup.

1) If we have extra disk space available,then we can create a new diskgroup and move the files from old diskgroup to it.

-- Initially I have 3 diskgroup with external redundancy as:

This blog will move the database in all the diskgroups to one diskgroup


export ORACLE_HOME=/opt/oracle/product/10.2.0.2/asm1

export PATH=$ORACLE_HOME/bin:$PATH

export ORACLE_SID=+ASM1

SQL> select state,name from v$asm_diskgroup;

STATE NAME

----------- ------------------------------

MOUNTED DATA1

MOUNTED RMAN1

MOUNTED SYS1

SQL> !ls -ltr /dev/rhdisk*

crw------- 2 root system 14, 1 Feb 5 15:58 /dev/rhdisk1

crw------- 1 root system 14, 0 May 14 13:41 /dev/rhdisk0

crw-r----- 1 root oinstall 14, 2 May 14 17:13 /dev/rhdisk2

crw-r----- 1 root oinstall 14, 7 Jul 11 09:26 /dev/rhdisk7

crw------- 1 oracle dba 14, 9 Jul 11 09:26 /dev/rhdisk9

crw-r--r-- 1 oracle oinstall 14, 8 Jul 11 09:26 /dev/rhdisk8

crw------- 1 oracle dba 14, 14 Jul 11 09:26 /dev/rhdisk14

crw-r--r-- 1 oracle oinstall 14, 13 Jul 11 09:26 /dev/rhdisk13

crw-r----- 1 root oinstall 14, 12 Jul 11 09:26 /dev/rhdisk12

crw------- 1 oracle dba 14, 11 Jul 11 09:26 /dev/rhdisk11

crw------- 1 oracle dba 14, 10 Jul 11 09:26 /dev/rhdisk10

crw------- 1 oracle dba 14, 16 Jul 11 09:26 /dev/rhdisk16

crw------- 1 oracle dba 14, 15 Jul 11 09:26 /dev/rhdisk15

crw------- 1 oracle dba 14, 6 Jul 11 12:05 /dev/rhdisk6

crw------- 1 oracle dba 14, 5 Jul 11 12:06 /dev/rhdisk5

crw------- 1 oracle dba 14, 4 Jul 11 12:06 /dev/rhdisk4

crw-r--r-- 1 oracle oinstall 14, 3 Jul 11 12:06 /dev/rhdisk3

SQL> show PARAMETER ASM_DISKSTRING

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

asm_diskstring string

SQL> !ssh node2 ls -l /dev/rhdisk*

crw------- 2 root system 15, 0 Feb 5 10:23 /dev/rhdisk0

crw------- 1 root system 15, 1 Feb 5 10:47 /dev/rhdisk1

crw------- 1 oracle dba 15, 11 Jul 11 09:33 /dev/rhdisk10

crw------- 1 oracle dba 15, 10 Jul 11 09:35 /dev/rhdisk11

crw-r----- 1 root oinstall 15, 12 Jul 11 09:36 /dev/rhdisk12

crw-r--r-- 1 oracle oinstall 15, 13 Jul 11 09:37 /dev/rhdisk13

crw------- 1 oracle dba 15, 14 Jul 11 09:38 /dev/rhdisk14

crw------- 1 oracle dba 15, 15 Jul 11 09:40 /dev/rhdisk15

crw------- 1 oracle dba 15, 16 Jul 11 09:42 /dev/rhdisk16

crw-r----- 1 root oinstall 15, 2 Jul 11 12:07 /dev/rhdisk2

crw-r--r-- 1 oracle oinstall 15, 3 Jul 11 12:07 /dev/rhdisk3

crw------- 1 oracle dba 15, 4 Jul 11 12:07 /dev/rhdisk4

crw------- 1 oracle dba 15, 5 Jul 11 12:07 /dev/rhdisk5

crw------- 1 oracle dba 15, 6 Jul 11 12:07 /dev/rhdisk6

crw-r----- 1 root oinstall 15, 7 Jul 11 09:30 /dev/rhdisk7

crw-r--r-- 1 oracle oinstall 15, 8 Jul 11 09:31 /dev/rhdisk8

crw------- 1 oracle dba 15, 9 Jul 11 09:32 /dev/rhdisk9
2) Create a new diskgroup with normal redundancy as :


SQL> create diskgroup DATA2 normal redundancy failgroup datafg01 disk '/dev/rhdisk9' failgroup datafg02 disk '/dev/rhdisk14';

Diskgroup created.

SQL> select state,name,type from v$asm_diskgroup;

STATE NAME TYPE

----------- ------------------------------ ------

MOUNTED DATA1 EXTERN

MOUNTED RMAN1 EXTERN

MOUNTED SYS1 EXTERN

MOUNTED DATA2 NORMAL

export ORACLE_HOME=/opt/oracle/product/10.2.0.2/db1

export PATH=$ORACLE_HOME/bin:$PATH

export ORACLE_SID=db1
3)Backup the current database as follows:


SQL> show parameter db_name

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

db_name string db

SQL> show parameter control

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

control_file_record_keep_time integer 7

control_files string +SYS1/db/controlfile/current.257.623341273

SQL> alter database backup controlfile to '+DATA2';

Database altered.

SQL> alter system set control_files='+DATA2\db\CONTROLFILE\Backup.256.627662879' SCOPE=SPFILE;

System altered.

-- Connect to rman

home/oracle > rman target /

Recovery Manager: Release 10.2.0.2.0 - Production on Wed Jul 11 14:53:59 2007

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

connected to target database: DB (DBID=732666905)

RMAN> shutdown immediate;=== if its RAC shutdown from node 2 also

using target database control file instead of recovery catalog

database closed

database dismounted

Oracle instance shut down

RMAN> startup nomount;

connected to target database (not started)

Oracle instance started

Total System Global Area 2063597568 bytes

Fixed Size 2072480 bytes

Variable Size 469762144 bytes

Database Buffers 1577058304 bytes

Redo Buffers 14704640 bytes

RMAN> restore controlfile from '+SYS1\db\CONTROLFILE\Current.257.623341273';

Starting restore at 11-JUL-07

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=145 instance=db1 devtype=DISK

channel ORA_DISK_1: copied control file copy

output filename=+DATA2/db/controlfile/backup.256.627662879

Finished restore at 11-JUL-07

RMAN> alter database mount;

database mounted

released channel: ORA_DISK_1

RMAN> backup as copy database format '+DATA2';
4)Switch the database to copy. At this moment we are switching to the new Diskgroup

== A SWITCH is equivalent to using the PL/SQL "alter database rename file" statement.


RMAN> switch database to copy;

RMAN > alter database open;==wil not work
this is a bug in 10.2.0.2

exit out and


sqlplus “/ as sysdba”

alter database open

shutdown immediate;

startup

alter system set db_create_file_dest ='+DATA2';
5)Add new tempfile to newly created database.


SQL> create bigfile temporary tablespace temp03 tempfile size 100M;

Tablespace created.

SQL> alter database default temporary tablespace temp03;

Database altered.

Drop any existing tempfile on the old diskgroup

SQL> drop tablespace temp;

Tablespace dropped.
6)Find out how many members we have have in redolog groups, make sure that we have only one member in each log group.(drop other members).

SQL> select * from v$log;

Suppose we have 4 log groups, then add one member to each log group as following:


SQL> alter database add logfile member '+data2' to group 1;

Database altered.

SQL> alter database add logfile member '+data2' to group 2;

Database altered.

SQL> alter database add logfile member '+data2' to group 3;

Database altered.

SQL> alter database add logfile member '+data2' to group 4;

Database altered.

SQL>
Then we can drop the old logfile member from earlier diskgroups as:

Issue the below command till all the logfiles are in valid state


alter system switch logfile;

SQL> alter database drop logfile member '+SYS1/db/onlinelog/group_2.260.623341307';

Database altered.

SQL> select * from v$logfile;
Logfiles from group 3 and 4 cannot be dropped at this point from node 1 as node 2 is still down

start the database on node 2 and delete the old logfiles in the data1/sys1 diskgroup

7)Use the following query to verify that all the files are moved to new diskgroup with desired redundancy:


SQL> select name from v$controlfile

union

select name from v$datafile

union

select name from v$tempfile

union

select member from v$logfile

union

select filename from v$block_change_tracking
union
select name from v$flashback_database_logfile;

Switching Datafile Filenames After a Restore:

Example

Switching Datafile Filenames After a Restore: Example
This example allocates one disk device and one tape device to allow RMAN to restore from disk and tape.

RUN
{
ALLOCATE CHANNEL dev1 DEVICE TYPE DISK;
ALLOCATE CHANNEL dev2 DEVICE TYPE sbt;
SQL "ALTER TABLESPACE tbs_1 OFFLINE IMMEDIATE";
SET NEWNAME FOR DATAFILE '/disk7/oracle/tbs11.f'
TO '/disk9/oracle/tbs11.f';
RESTORE TABLESPACE tbs_1;
SWITCH DATAFILE ALL;
RECOVER TABLESPACE tbs_1;
SQL "ALTER TABLESPACE tbs_1 ONLINE";
}

Migracion de File System +ASM

Cambiar la single instance 11.1.0.6 a ASM con RMAN

Una vez hecha la migración de nuestra base de datos es el momento de realizar el
cambio de tipo de almacenamiento. Actualmente tenemos nuestra base de datos
trabajando en File System y vamos a realizar el paso a Automatic Storage
Management (ASM).

Antes de empezar cabe recordar que la instalación de ASM que hemos realizado en
un paso anterior la hemos hecho en cluster, es decir, cuando cambiemos el
almacenamiento de file system a ASM ya tendremos el almacenamiento preparado
para RAC. No habrá que hacer nuevas configuraciones a este respecto.
Para realizar esta tarea lo primero que debemos hacer es editar el init.ora para
que apunte al nuevo control file que va a tener la base de datos:

control_files =
'+DATA/control01.ctl','+FRA/control02.ctl','+FRA/con
trol03.ctl'

Ahora, debemos rearrancarar la base de datos en modo nomount. Esto lo hacemos
mediante:

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup nomount


Tras esto, debemos conectarnos mediante una sesión de RMAN para realizar una
copia de los control files (son 3 archivos) y llevarlos a su nueva localización. Así
pues, nos conectamos a RMAN:
[oracle@nodo1 ~]$ rman
Recovery Manager: Release 11.1.0.6.0 - Production on
Tue Nov 3 17:35:37 2009
Copyright (c) 1982, 2007, Oracle. All rights
reserved.
RMAN> connect target
connected to target database: DBASE10 (not mounted)
Realizamos la copia de los control files ejecutando:
RMAN> RESTORE CONTROLFILE FROM
'/u01/app/oracle/oradata/DBase10/control02.ctl';
Starting restore at 03-NOV-09
using target database control file instead of
recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=147 device type=DISK
channel ORA_DISK_1: copied control file copy
output file
name=/u01/app/oracle/oradata/DBase10/control01.ctl
output file
name=/u01/app/oracle/oradata/DBase10/control02.ctl
output file
name=/u01/app/oracle/oradata/DBase10/control03.ctl
Finished restore

RMAN> RESTORE CONTROLFILE FROM
'/u01/app/oracle/oradata/DBase10/control01.ctl';
Starting restore at 03-NOV-09
using channel ORA_DISK_1
channel ORA_DISK_1: copied control file copy
output file
name=/u01/app/oracle/oradata/DBase10/control01.ctl
output file
name=/u01/app/oracle/oradata/DBase10/control02.ctl
output file
name=/u01/app/oracle/oradata/DBase10/control03.ctl
Finished restore

RMAN> RESTORE CONTROLFILE FROM
'/u01/app/oracle/oradata/DBase10/control03.ctl';
Starting restore at 03-NOV-09
using channel ORA_DISK_1
channel ORA_DISK_1: copied control file copy
output file
name=/u01/app/oracle/oradata/DBase10/control01.ctl
output file
name=/u01/app/oracle/oradata/DBase10/control02.ctl
output file
name=/u01/app/oracle/oradata/DBase10/control03.ctl
Finished restore


Antes de continuar debemos poner la base de datos de nuevo en modo mount:

SQL> alter database mount;


El siguiente paso es realizar una copia de los datafiles y situarlos en ASM. Esto lo
realizamos mediante la siguiente ejecución en RMAN:
RMAN> BACKUP AS COPY DATABASE FORMAT '+DATA';
Starting backup at 03-NOV-09
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=147 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001
name=/u01/app/oracle/oradata/DBase10/system01.dbf
output file
name=+DATA/dbase10/datafile/system.256.701977649
tag=TAG20091103T174721 RECID=1 STAMP=701977758
channel ORA_DISK_1: datafile copy complete, elapsed
time: 00:02:10
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002
name=/u01/app/oracle/oradata/DBase10/sysaux01.dbf
output file
name=+DATA/dbase10/datafile/sysaux.257.701977775
tag=TAG20091103T174721 RECID=2 STAMP=701977847
channel ORA_DISK_1: datafile copy complete, elapsed
time: 00:01:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003
name=/u01/app/oracle/oradata/DBase10/undotbs01.dbf
output file
name=+DATA/dbase10/datafile/undotbs1.258.701977851
tag=TAG20091103T174721 RECID=3 STAMP=701977853
channel ORA_DISK_1: datafile copy complete, elapsed
time: 00:00:07
channel ORA_DISK_1: starting datafile copy
copying current control file
output file
name=+DATA/dbase10/controlfile/backup.259.701977857
tag=TAG20091103T174721 RECID=4 STAMP=701977859
channel ORA_DISK_1: datafile copy complete, elapsed
time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004
name=/u01/app/oracle/oradata/DBase10/users01.dbf
output file
name=+DATA/dbase10/datafile/users.260.701977861
tag=TAG20091103T174721 RECID=5 STAMP=701977861
channel ORA_DISK_1: datafile copy complete, elapsed
time: 00:00:01
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup
set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 03-NOV-09
channel ORA_DISK_1: finished piece 1 at 03-NOV-09
piece
handle=+DATA/dbase10/backupset/2009_11_03/nnsnf0_tag
20091103t174721_0.261.701977863
tag=TAG20091103T174721 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed
time: 00:00:01
Finished backup

Ahora renombramos los datafiles mediante RMAN ejecutando:
RMAN> SWITCH DATABASE TO COPY;

datafile 1 switched to datafile copy
"+DATA/dbase10/datafile/system.256.701977649"
datafile 2 switched to datafile copy
"+DATA/dbase10/datafile/sysaux.257.701977775"
datafile 3 switched to datafile copy
"+DATA/dbase10/datafile/undotbs1.258.701977851"
datafile 4 switched to datafile copy
"+DATA/dbase10/datafile/users.260.701977861"

Una vez hecho esto debemos realizar un switch del tempfile mediante:
RMAN> run{
2> set newname for tempfile 1 to '+DATA';
3> switch tempfile all;
4> }
executing command: SET NEWNAME
renamed tempfile 1 to +DATA in control file

Tras esta última ejecución abrimos la base de datos y salimos de RMAN:
RMAN> ALTER DATABASE OPEN;
database opened

Tras estas operaciones comprobamos cuáles son ahora los datafiles de la base de
datos:
SQL> select FILE#, STATUS, NAME from v$datafile;
FILE# STATUS
---------- -------
NAME
-----------------------------------------------------
1 SYSTEM
+DATA/dbase11/datafile/system.256.701977649
2 ONLINE
+DATA/dbase11/datafile/sysaux.257.701977775
3 ONLINE
+DATA/dbase11/datafile/undotbs1.258.701977851
4 ONLINE
+DATA/dbase11/datafile/users.260.701977861

El último paso que nos queda por dar para finalizar la migración de File System a
ASM es el cambio de los archivos de redo. Para ello deberemos añadir nuevos redo
logs a la base de datos en ASM para que reemplacen a los actuales, ejecutar un
cambio de redo logs y eliminar los antiguos.

Consultamos los archivos de redo log actuales:
SQL> SELECT a.group#, b.member, a.status FROM v$log
a, v$logfile b WHERE a.group#=b.group#;
GROUP# MEMBER
-----------------------------------------------
STATUS
----------------
3 /u01/app/oracle/oradata/DBase10/redo03.log
INACTIVE
2 /u01/app/oracle/oradata/DBase10/redo02.log
INACTIVE
1 /u01/app/oracle/oradata/DBase10/redo01.log
CURRENT
Como se puede observar, el redo log que está actualmente en uso es el redo01.log,
como indica la consulta mediante su STATUS CURRENT.
Como se puede observar en la consulta mostrada, nuestra base de datos actual tiene
3 grupos de redo con un redo log cada uno.

Ahora añadimos un redo logfile a cada grupo de redo mediante:

SQL> ALTER DATABASE ADD LOGFILE MEMBER '+DATA'TO
GROUP 1;
Database altered.

SQL> ALTER DATABASE ADD LOGFILE MEMBER '+DATA'TO
GROUP 2;
Database altered.

SQL> ALTER DATABASE ADD LOGFILE MEMBER '+DATA'TO
GROUP 3;
Database altered.

SQL> ALTER DATABASE DROP LOGFILE MEMBER
'/u01/app/oracle/oradata/DBase10/redo02.log';


Database altered.
Después de añadir los archivos debemos realizar varios operaciones. Primero,
teniendo en cuenta que el redo log que está actualmente en uso es el redo01.log,
realizamos un switch consiguiendo cambiar al nuevo redo log que hemos creado en
+DATA, en el grupo 2:
SQL> ALTER SYSTEM SWITCH LOGFILE;
System altered.
Esto lo hacemos así ya que redo02.log ya ha sido eliminado y con el switch
cambiamos al archivo que hemos creado antes. De esta forma ya estamos trabajando
con los nuevos logs en ASM.
Tras este cambio podemos eliminar el redo03.log y el redo01.log ya que no están en
uso dejando así en los grupos 1 y 3 un solo archivo de log.
SQL> ALTER DATABASE DROP LOGFILE MEMBER
'/u01/app/oracle/oradata/DBase10/redo03.log';
Database altered.
SQL> ALTER DATABASE DROP LOGFILE MEMBER
'/u01/app/oracle/oradata/DBase10/redo01.log';
Database altered
Ahora, una vez hechas estas operaciones cambiamos de logfile por última vez para
comprobar que funcionan todos correctamente.
SQL> ALTER SYSTEM SWITCH LOGFILE;
System altered.
Hacemos una consulta para ver el estado final de los grupos de log:
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES
---------- ---------- ---------- ----------
MEMBERS ARC STATUS
----------- --- ----------------
FIRST_CHANGE# FIRST_TIM
------------- ---------
1 1 7 52428800
1 NO ACTIVE
614424 06-NOV-09
2 1 8 52428800
2 NO ACTIVE
614594 06-NOV-09
3 1 9 52428800
2 NO CURRENT
614698 06-NOV-09
Consultamos el nombre y la localización de los nuevos archivos de log y
comprobamos que se han eliminado los antiguos:
SQL> select * from v$logfile;
GROUP# STATUS TYPE
---------- ------- -------
MEMBER
-----------------------------------------------------

IS_
---
1 ONLINE
+DATA/dbase10/onlinelog/group_1.263.701979411
NO
2 ONLINE
+DATA/dbase10/onlinelog/group_2.265.701981023
NO
3 ONLINE
+DATA/dbase10/onlinelog/group_3.267.702213275
NO

Con esta última consulta comprobamos que está todo correcto y damos por
finalizada está configuración.

En este momento tenemos un sistema con una base de datos 11.1.0.6 con ASM
funcionando sin problemas.