ORA-00600: internal error code, arguments: [kcidr_io_check_common_6], [4], [H:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\ORA11G\ORA11G\CONTROL02.CTL], [16384], [1], [0]

##########################
#  Error
##########################

ORA-00600: internal error code, arguments: [kcidr_io_check_common_6], [4], [H:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\ORA11G\ORA11G\CONTROL02.CTL], [16384], [1], [0],

##########################
#  Full Error 
##########################


ORA-00600: internal error code, arguments: [kcidr_io_check_common_6], [4], [H:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\ORA11G\ORA11G\CONTROL02.CTL], [16384], [1], [0], [], [], [], [], [], []
ORA-27046: file size is not a multiple of logical block size
OSD-04000: logical block size mismatch (OS 16384)
ORA-00210: cannot open the specified control file
ORA-00202: control file: 'H:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\ORA11G\ORA11G\CONTROL02.CTL'
ORA-27048: skgfifi: file header information is invalid
OSD-04001: invalid logical block size (OS 538984480)


##########################
#  Cause
##########################

Error occurred while trying to startup the database of 11.2.0.1.0 version. Database is not mounted and its saying that Controlfile cant be opened.

##########################
#  Command Executed
##########################

Startup

##########################
## Solution
##########################

### 11.2.0.1.0

Initially i tried to investigate with the error, ORA-600 which led me saying that this is Bug 13256753  in this version of the database which is because of the INVALID file name. This answer doesnt satisfy me, so i tried to look into the other errors associated with the ORA-600.

So when i looked into the other errors, it shows that Controlfile header is corrupted. I tried to confirm it by running DBV which also notified that controlfile header is corrupted.



C:\Users\Administrator>dbv file=H:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\ORA11G\ORA11G\CONTROL02.CTL

DBVERIFY: Release 11.2.0.1.0 - Production on Thu Dec 27 20:04:30 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

DBV-00107: Unknown header format (194) (2054913149)

I'm lucky enough to have mirrored controlfiles, so shutdown the database, copied the mirrored controlfiles to the corrupted file location and renamed it to the corrupted one.

Database is now Up and Running :D

ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS1'

##########################
#  Error
##########################

ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS1'

##########################
#  Full Error 
##########################

ORA-39171: Job is experiencing a resumable wait.
ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS1'
ORA-39171: Job is experiencing a resumable wait.
ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS1'

##########################
#  Cause
##########################

Error occurred while trying to import a schema using datapump of 11.2.0.1.0 version.

##########################
#  Command Executed
##########################

impdp -- Schema Import

##########################
## Solution
##########################

### 11.2.0.1.0

1) Index maintenance activity will be performed when impdp import is in progress, so disable the primary key constraints in the tables, then undo generation will be less and import will be successful.

2) In case if import is still failling after disabling primary constraints then try to add some more space to UNDO tablespace.

For me 1st solution worked.

RMAN-06617: UNTIL TIME (****) is ahead of last NEXT TIME in archived logs (****)

##########################
#  Error
##########################

RMAN-06617: UNTIL TIME (12-DEC-12) is ahead of last NEXT TIME in archived logs (12-DEC-12)

##########################
#  Full Error 
##########################


RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 12/12/2012 08:41:01
RMAN-06617: UNTIL TIME (12-DEC-12) is ahead of last NEXT TIME in archived logs (12-DEC-12)


##########################
#  Cause
##########################

Error occured while trying to duplicate a database of 11.2.0.1.0 version.

##########################
#  Command Executed
##########################

duplicate database to dup11r2

until time "To_Date('12-DEC-2012 07:40:38','DD-MON-YYYY HH24:MI:SS')"
SPFILE
SET CONTROL_FILES='H:\app\Administrator\oradata\DUP11R2\control01.ctl'
set db_file_name_convert='H:\app\Administrator\oradata\ora11g','H:\app\Administrator\oradata\DUP11R2'
set log_file_name_convert='H:\app\Administrator\oradata\ora11g','H:\app\Administrator\oradata\DUP11R2'
backup location 'H:\app\Administrator\backup\ORA11G';


##########################
## Solution
##########################

### 11.2.0.1.0


Set Until time used is ahead of the next_time of the last backed up archivelog.

select sequence#,completion_time,next_time from v$archived_log order by 2

 SEQUENCE# COMPLETION_TIME      NEXT_TIME
---------- -------------------- --------------------
       176 12-DEC-2012 07:33:31 12-DEC-2012 07:33:27
       177 12-DEC-2012 07:40:29 12-DEC-2012 07:40:28

So the next_time of the last backed up sequence 177 is "12-DEC-2012 07:40:28". Duplication is successful with this timestamp.

duplicate database to dup11r2
until time "To_Date('12-DEC-2012 07:40:28','DD-MON-YYYY HH24:MI:SS')"
SPFILE
SET CONTROL_FILES='H:\app\Administrator\oradata\DUP11R2\control01.ctl'
set db_file_name_convert='H:\app\Administrator\oradata\ora11g','H:\app\Administrator\oradata\DUP11R2'
set log_file_name_convert='H:\app\Administrator\oradata\ora11g','H:\app\Administrator\oradata\DUP11R2'
backup location 'H:\app\Administrator\backup\ORA11G';

RMAN-06457: UNTIL SCN (****) is ahead of last SCN in archived logs (****)

##########################
#  Error
##########################

RMAN-06457: UNTIL SCN (3680420) is ahead of last SCN in archived logs (3680404)

##########################
#  Full Error 
##########################


RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 12/12/2012 08:09:45
RMAN-06457: UNTIL SCN (3680420) is ahead of last SCN in archived logs (3680404)


##########################
#  Cause
##########################

Error occured while trying to duplicate a database of 11.2.0.1.0 version.

##########################
#  Command Executed
##########################


duplicate database to dup11r2
until time "To_Date('12-DEC-2012 07:40:38','DD-MON-YYYY HH24:MI:SS')"
SPFILE
SET CONTROL_FILES='H:\app\Administrator\oradata\DUP11R2\control01.ctl'
set db_file_name_convert='H:\app\Administrator\oradata\ora11g','H:\app\Administrator\oradata\DUP11R2'
set log_file_name_convert='H:\app\Administrator\oradata\ora11g','H:\app\Administrator\oradata\DUP11R2'
backup location 'H:\app\Administrator\backup\ORA11G';


##########################
## Solution
##########################

### 11.2.0.1.0


Set Until time used is ahead of the next_time of the last backed up archivelog.

select sequence#,completion_time,next_time from v$archived_log order by 2

 SEQUENCE# COMPLETION_TIME      NEXT_TIME
---------- -------------------- --------------------
       176 12-DEC-2012 07:33:31 12-DEC-2012 07:33:27
       177 12-DEC-2012 07:40:29 12-DEC-2012 07:40:28

So the next_time of the last backed up sequence 177 is "12-DEC-2012 07:40:28". Duplication is successful with this timestamp.

duplicate database to dup11r2
until time "To_Date('12-DEC-2012 07:40:28','DD-MON-YYYY HH24:MI:SS')"
SPFILE
SET CONTROL_FILES='H:\app\Administrator\oradata\DUP11R2\control01.ctl'
set db_file_name_convert='H:\app\Administrator\oradata\ora11g','H:\app\Administrator\oradata\DUP11R2'
set log_file_name_convert='H:\app\Administrator\oradata\ora11g','H:\app\Administrator\oradata\DUP11R2'
backup location 'H:\app\Administrator\backup\ORA11G';

RMAN-05537: DUPLICATE without TARGET connection when auxiliary instance is started with spfile cannot use SPFILE clause

##########################
#  Error
##########################


RMAN-05537: DUPLICATE without TARGET connection when auxiliary instance is started with spfile cannot use SPFILE clause

##########################
#  Full Error 
##########################


Starting Duplicate Db at 12-DEC-12
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 12/12/2012 08:27:52
RMAN-05537: DUPLICATE without TARGET connection when auxiliary instance is started with spfile cannot use SPFILE clause

##########################
#  Cause
##########################


Error occured while trying to duplicate a database of 11.2.0.1.0 version.

##########################
#  Command Executed
##########################


duplicate database to dup11r2
until time "To_Date('12-DEC-2012 07:40:29','DD-MON-YYYY HH24:MI:SS')"
SPFILE
SET CONTROL_FILES='H:\app\Administrator\oradata\DUP11R2\control01.ctl'
set db_file_name_convert='H:\app\Administrator\oradata\ora11g','H:\app\Administrator\oradata\DUP11R2'
set log_file_name_convert='H:\app\Administrator\oradata\ora11g','H:\app\Administrator\oradata\DUP11R2'
backup location 'H:\app\Administrator\backup\ORA11G';


##########################
## Solution
##########################

### 11.2.0.1.0


Auxiliary Database (Dup11r2) is started up with spfile. Here we are doing duplication without connecting to target or catalog database.

While starting the duplication, RMAN will restore the spfile from the backup location we have used and it will set the dbname, controlfiles, log_file_name_convert and db_file_name_convert parameters in the newly restored spfile and then it will startup using the spfile.


C:\Users\Administrator>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed Dec 12 08:28:21 2012

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> sho parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      H:\APP\ADMINISTRATOR\PRODUCT\1
                                                 1.2.0\DBHOME_1\DATABASE\SPFILE
                                                 DUP11R2.ORA
SQL> shu immediate
ORA-01507: database not mounted


ORACLE instance shut down.
SQL>


So when duplicating a database without connecting to target or catalog, we should start the auxiliary database with pfile only.

RMAN-05542: Only UNTIL TIME can be used with DUPLICATE without TARGET and CATALOG connections

##########################
#  Error
##########################


RMAN-05542: Only UNTIL TIME can be used with DUPLICATE without TARGET and CATALOG connections

##########################
#  Full Error 
##########################


RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 12/12/2012 08:35:35
RMAN-05542: Only UNTIL TIME can be used with DUPLICATE without TARGET and CATALOG connections

##########################
#  Cause
##########################


Error occured while trying to duplicate a database of 11.2.0.1.0 version.

##########################
#  Command Executed
##########################


duplicate database to dup11r2
until scn 3587956
SPFILE
SET CONTROL_FILES='H:\app\Administrator\oradata\DUP11R2\control01.ctl'
set db_file_name_convert='H:\app\Administrator\oradata\ora11g','H:\app\Administrator\oradata\DUP11R2'
set log_file_name_convert='H:\app\Administrator\oradata\ora11g','H:\app\Administrator\oradata\DUP11R2'
backup location 'H:\app\Administrator\backup\ORA11G';

##########################
## Solution
##########################

### 11.2.0.1.0

Here we are doing duplication without connecting to target or catalog database. So we have to use UNTIL TIME parameter.

After changing UNTIL SCN to UNTIL TIME parameter, duplication process was successful.

duplicate database to dup11r2
until time "To_Date('12-DEC-2012 07:40:28','DD-MON-YYYY HH24:MI:SS')"
SPFILE
SET CONTROL_FILES='H:\app\Administrator\oradata\DUP11R2\control01.ctl'
set db_file_name_convert='H:\app\Administrator\oradata\ora11g','H:\app\Administrator\oradata\DUP11R2'
set log_file_name_convert='H:\app\Administrator\oradata\ora11g','H:\app\Administrator\oradata\DUP11R2'
backup location 'H:\app\Administrator\backup\ORA11G';

Create a new listener with different port numbers (Non Default Port)

##########################
# Create a new listener with different port numbers 
##########################

We are going to add a new listener named lsnr_10g_1533 Which is going to use port 1533

##########################
#  1) Check how Many Listeners are Running
##########################

+ASM @ my_server:/opt/oracle/product/10.2/db_a/network/admin
> ps -ef|grep tns
  oracle  4933     1   0   Jul 17 ?          16:23 /opt/oracle/product/10.2/db_a/bin/tnslsnr lsnr_10g -inherit
  oracle  4949     1   0   Jul 17 ?          33:49 /opt/oracle/product/10.2/db_a/bin/tnslsnr lsnr_10g_1532 -inherit
  oracle  4946     1   0   Jul 17 ?           6:17 /opt/oracle/product/10.2/db_a/bin/tnslsnr lsnr_10g_1531 -inherit
  oracle  4517  4062   0 23:27:17 pts/2       0:00 grep tns

##########################
#  2) Find the home where other Listeners are Running
##########################

Here Our listeners are running in ASM HOME

##########################
#  3) Edit Listener.ora
##########################

Add below entry in LISTENER.ORA file.

Here we are using a different Port number 1533. Default port is 1521. And also in SID_LIST_LSNR_10G_1533, we are specifying for which databases the listener has to listen.

LSNR_10G_1533 =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = my_server.com)(PORT = 1533)(QUEUESIZE=99))
    )
  )

SID_LIST_LSNR_10G_1533 =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = mydb_1.com)
      (ORACLE_HOME = /opt/oracle/product/10.2/db_a)
      (SID_NAME = mydb_1)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = mydb_2.com)
      (ORACLE_HOME = /opt/oracle/product/10.2/db_a)
      (SID_NAME = mydb_2)
    )
  )

##########################
## 4) Start the Listener
##########################

+ASM @ my_server:/opt/oracle/product/10.2/db_a/network/admin
> lsnrctl start LSNR_10G_1533

LSNRCTL for Solaris: Version 10.2.0.3.0 - Production on 05-DEC-2012 23:28:10

Copyright (c) 1991, 2006, Oracle.  All rights reserved.

Starting /opt/oracle/product/10.2/db_a/bin/tnslsnr: please wait...

TNSLSNR for Solaris: Version 10.2.0.3.0 - Production
System parameter file is /opt/oracle/product/10.2/db_a/network/admin/listener.ora
Log messages written to /opt/oracle/product/10.2/db_a/network/log/lsnr_10g_1533.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=my_server)(PORT=1533)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=my_server.com)(PORT=1533)(QUEUESIZE=99)))
STATUS of the LISTENER
------------------------
Alias                     LSNR_10G_1533
Version                   TNSLSNR for Solaris: Version 10.2.0.3.0 - Production
Start Date                05-DEC-2012 23:28:10
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /opt/oracle/product/10.2/db_a/network/admin/listener.ora
Listener Log File         /opt/oracle/product/10.2/db_a/network/log/lsnr_10g_1533.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=my_server)(PORT=1533)))
Services Summary...
Service "mydb_2.com" has 1 instance(s).
  Instance "mydb_2", status UNKNOWN, has 1 handler(s) for this service...
Service "mydb_1.com" has 1 instance(s).
  Instance "mydb_1", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

##########################
## 5) check Listener is running or not
##########################

+ASM @ my_server:/opt/oracle/product/10.2/db_a/network/admin
> ps -ef|grep tns
  oracle  4933     1   0   Jul 17 ?          16:23 /opt/oracle/product/10.2/db_a/bin/tnslsnr lsnr_10g -inherit
  oracle  4949     1   0   Jul 17 ?          33:49 /opt/oracle/product/10.2/db_a/bin/tnslsnr lsnr_10g_1532 -inherit
  oracle  4946     1   0   Jul 17 ?           6:17 /opt/oracle/product/10.2/db_a/bin/tnslsnr lsnr_10g_1531 -inherit
  oracle  4551     1   0 23:28:11 ?           0:00 /opt/oracle/product/10.2/db_a/bin/tnslsnr LSNR_10G_1533 -inherit

##########################
## 6) Test whether new listener working or not
##########################

Add below TNS entry in TNSNAMES.ora and try connecting the database with this newly created listener.

mydb_2_1533.com =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = my_server.com)(PORT = 1533))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = mydb_2.com)
    )
  )

##########################
## 7) Establish SQL session and check
##########################

+ASM @ my_server:/opt/oracle/product/10.2/db_a/network/admin
> sqlplus system/password@mydb_2_1533.com

SQL*Plus: Release 10.2.0.3.0 - Production on Wed Dec 5 23:35:18 2012

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> select name,open_mode from v$database;

NAME      OPEN_MODE
--------- ----------
mydb_2    READ WRITE

SQL> exit

TABLESPACE Status Not showing as OFFLINE when brought to OFFLINE state from READ ONLY mode

##########################
## TABLESPACE Status Not showing as OFFLINE when brought to OFFLINE state from READ ONLY mode 
##########################

Yesterday one of my friend was asking that when a TABLESPACE was brought into OFFLINE mode from READ ONLY mode, Command succeeded but still DBA_TABLESPACE shows as READ ONLY and DBA_DATA_FILES shows as AVAILABLE.

So i wanna check this. Lets see how we can find the correct status of the tablespace.

##########################
## Place the TABLESPACE in READ ONLY mode
##########################


SQL> Alter tablespace users read only;

Tablespace altered.

SQL> Select tablespace_name,status from dba_tablespaces where tablespace_name='USERS';

TABLESPACE_NAME                STATUS
------------------------------ ---------
USERS                          READ ONLY

SQL> select tablespace_name,status from dba_data_files where tablespace_name='USERS';

TABLESPACE_NAME                STATUS
------------------------------ ---------
USERS                          AVAILABLE



##########################
## Place the TABLESPACE in OFFLINE mode
##########################

SQL> alter tablespace users offline;

Tablespace altered.

SQL> Select tablespace_name,status from dba_tablespaces where tablespace_name='USERS';

TABLESPACE_NAME                STATUS
------------------------------ ---------
USERS                          READ ONLY

SQL> select tablespace_name,status from dba_data_files where tablespace_name='USERS';

TABLESPACE_NAME                STATUS
------------------------------ ---------
USERS                          AVAILABLE

So When a TABLESPACE mode is changed from READ-ONLY to OFFLINE, it doesnt show up in DBA_DATA_FILES or DBA_TABLESPACES.

##########################

## How to check the correct Status of the TABLESPACE
##########################

1. Use V$DATAFILE to know the correct status as the status of the tablespace is recorded in Controlfile.

Select tablespace_name,df.status, name "Datafile"from dba_data_files ddf,v$datafile df where ddf.file_id=df.file# and ddf.tablespace_name='USERS';

TABLESPACE_NAME                STATUS  Datafile

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

USERS                          OFFLINE H:\APP\ADMINISTRATOR\ORADATA\ORA11G\USERS01.DBF

SQL> select tablespace_name,status from dba_data_files where tablespace_name='USERS';

TABLESPACE_NAME                STATUS

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

USERS                          AVAILABLE

SQL> Select tablespace_name,status from dba_tablespaces where tablespace_name='USERS';

TABLESPACE_NAME                STATUS

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

USERS                          READ ONLY

Create Database Links in another User's name

##########################
## Create Database Links in another User's name
##########################

Here we are going to create a private database link in MY_ACC user account by logging as SYS user.

DB link name is MY_RPT.

Db link will be created in database remote_db and its username is remote_user.

##########################
## DB Link With Tnsnames entry
##########################

declare
uid number;
sqltext varchar2(1000) := 'create database link MY_RPT connect to remote_user identified by password using 'remote_db'';
myint integer;
begin
select user_id into uid from dba_users where username like 'MY_ACC';
myint:=sys.dbms_sys_sql.open_cursor();
sys.dbms_sys_sql.parse_as_user(myint,sqltext,dbms_sql.native,UID);
sys.dbms_sys_sql.close_cursor(myint);
end ;
/

##########################
## DB Link Without Tnsnames entry
##########################

Here TNS entry is not present, so creating the database link using tns entry address.

declare
uid number;
sqltext varchar2(1000) := 'create database link MY_RPT connect to remote_user identified by password using ''(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = host02-vip)(PORT = 1531))
    (ADDRESS = (PROTOCOL = TCP)(HOST = host03-vip)(PORT = 1531))
    (CONNECT_DATA =
      (SERVICE_NAME = remote_db)
    )
  )''';
myint integer;
begin
select user_id into uid from dba_users where username like 'MY_ACC';
myint:=sys.dbms_sys_sql.open_cursor();
sys.dbms_sys_sql.parse_as_user(myint,sqltext,dbms_sql.native,UID);
sys.dbms_sys_sql.close_cursor(myint);
end ;
/

Create Blackout using Emcli

Mostly We used to login to the Enterprise Manager to create blackout which will be slow when we are trying to access Client EM from Local machine.

##########################
## Easier Way to Create A blackout Using EMCLI :
##########################

Logon to the server where database is running and set the Agent envionment .

##########################
## Server Blackout
##########################

emcli create_blackout -name="Blackout_Name" -reason="reboot due to memory errors" -add_targets="server01:host" -schedule="duration:-1" 

##########################
## Server & and its associated databases Blackout
##########################

Use propagate_targets to blackout the host and its associated oracle services (database, agent, asm, etc.)

emcli create_blackout -name="Blackout_Name" -reason="reboot due to memory errors" -add_targets="server01:host" -schedule="duration:-1"  -propagate_targets

##########################
## Single Database Blackout
##########################

emcli create_blackout -name="Blackout_Name" -add_targets="db_name:oracle_database" -schedule="duration:30"

##########################
## Multiple Database Blackout
##########################

emcli create_blackout -name="Blackout_Name"-reason="Failover" -add_targets="db1_name:oracle_database;db2_name:oracle_database" -schedule="duration:30"

##########################
## Blackout for RAC Database
##########################

emcli create_blackout -name="Blackout_Name"-reason="Failover" -add_targets="instance_name:oracle_database;cluster_name:rac_database" -schedule="duration:30"

##########################
## Unlimited Blackout
##########################

emcli create_blackout -name="Blackout_Name"-reason="Failover" -add_targets="instance_name:oracle_database;cluster_name:rac_database" -schedule="frequency:once;duration:-1"

If we give duration = "-1" then it denotes that unlimited Duration

##########################
## Show List of Targets in the Blackout
##########################

emcli get_blackout_targets -name=Blackout_Name

agent11g @ myserver03:/u01/home/oracle/admin/scripts
> emcli get_blackout_targets -name=Blackout_Name
Target Name  Target Type      Status       Status ID
mydb_2     oracle_database  In Blackout  1
mydb1_2   oracle_database  In Blackout  1
mydb2_2     oracle_database  In Blackout  1
mydb3_2     oracle_database  In Blackout  1

##########################
## Check Whether database is in blackout
##########################

emcli get_blackouts -target=mydb_1:oracle_database

agent11g @ myserver03:/u01/home/oracle
> emcli get_blackouts -target=mydb_1:oracle_database
Name                              Created By  Status   Status ID  Next Start           Duration  Reason  Frequency  Repeat  Start Time           End Time  Previous End
 TZ Region        TZ Offset

Blackout_name     SYSMAN      Started  4          2012-08-28 16:42:19  -00:01    none    once       none    2012-08-28 16:42:19  none      none      
 America/Chicago  +00:00

If the above command produces output like above then db is under blackout. If no output then db is not under blackout

##########################
## Blackout Details
##########################

emcli get_blackout_details  -name=failover_03-dec-2012

agent11g @ myserver03:/u01/home/oracle/admin/scripts
> emcli get_blackout_details  -name=failover_03-dec-2012
Status             Status ID  Run Jobs  Next Start           Duration  Reason    Frequency  Repeat  Days  Months  Start Time           End Time  TZ Region   TZ Offset
Start In Progress  1          no        2012-12-03 04:35:24  -00:01    Failover  once       none    none  none    2012-12-03 04:35:24  none      US/Central  +00:00
agent11g @ myserver03:/u01/home/oracle/admin/scripts

##########################
## Stop Blackout
##########################

emcli stop_blackout -name="Blackout_name"

agent11g @ myserver03:/u01/home/oracle
>emcli stop_blackout -name="Blackout_name"
Blackout "Blackout-Aug 28  2012 4:42:11 PM" stopped successfully

##########################
## Delete Blackout
##########################

emcli delete_blackout -name=Blackout_name

PRCD-1229 : An attempt to access configuration of database mydb was rejected because its version 11.2.0.3.0 differs from the program version 11.2.0.2.0. Instead run the program from /u01/app/oracle/product/11.2/db_11.

##########################
## Error
##########################

mydb_1 @ siodserver4:/u01/app/oracle/diag/rdbms/mydb/mydb_1/trace
> srvctl status database -d mydb
PRCD-1027 : Failed to retrieve database mydb
PRCD-1229 : An attempt to access configuration of database mydb was rejected because its version 11.2.0.3.0 differs from the program version 11.2.0.2.0. Instead run the program from /u01/app/oracle/product/11.2/db_11.


##########################
## Cause
##########################

Error occured while trying to check the status of the database using srvctl command in 11.2.0.3.0 version

##########################
## Command Executed
##########################

srvctl status database -d mydb

##########################
## Solution
##########################

### 11.2.0.3.0

The above error has occurred because of the environment settings. Recently we have upgraded this database from 11.2.0.2.0 version to 11.2.0.3.0 version.

Eventhough CRS is updated with the latest 11.2.0.3.0 home, oratab file is still pointing to 11.2.0.2.0 home. so i have modified it to use 11.2.0.3.0 home and srvctl commands worked like a charm.

mydb_1 @ siodserver4:/u01/app/oracle/diag/rdbms/mydb/mydb_1/trace
> cat /etc/oratab |grep -i mydb
#mydb:/u01/app/oracle/product/11.2/db_4:N             # line added by Agent
#mydb_1:/u01/app/oracle/product/11.2/db_4:N   ## Commented by Sri on 30-nov-2012
#mydb_2:/u01/app/oracle/product/11.2/db_4:N   ## Commented by Sri on 30-nov-2012

mydb:/u01/app/oracle/product/11.2/db_11:N             # line added by Agent
mydb_1:/u01/app/oracle/product/11.2/db_11:N
mydb_2:/u01/app/oracle/product/11.2/db_11:N

mydb_1 @ siodserver4:/u01/app/oracle/diag/rdbms/mydb/mydb_1/trace
> srvctl status database -d mydb
Instance mydb_1 is running on node siodserver4
Online relocation: INACTIVE

If Oracle clusterware keys are not updated then use below command to update this information in cluster.

<11 .2.0.3_home=".2.0.3_home">/bin/srvctl upgrade database -d -o <11 .2.0.3_home=".2.0.3_home">

Command to check the database home :
-------------------------------------

+ASM2 @ siodserver4:/u01/app/oracle/diag/rdbms/mydb/mydb_1/trace
> srvctl config database -d mydb
Database unique name: mydb
Database name: mydb
Oracle home: /u01/app/oracle/product/11.2/db_11
Oracle user: oracle
Spfile: +mydb_DATA/mydb/spfilemydb.ora
Domain: corp.mycompany.com
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: mydb
Database instances:
Disk Groups: mydb_DATA,mydb_RECO
Mount point paths:
Services: mydb_svc
Type: RACOneNode
Online relocation timeout: 10
Instance name prefix: mydb
Candidate servers: siodserver3,siodserver4
Database is administrator managed

Tailor made DBA Hands on Training.

Hi All,

We offer Tailor made Oracle DBA Hands on Training with most of the real time scenarios.

Instead of giving you a list of topics to learn we offer you to Choose the topic you want to learn.

Attend ORACLE DBA trainings in Weekends and become an professional ORACLE DBA.

Trainings are conducted by Real time ORACLE DBA's. And also will help to easily crack the ORACLE DBA interview.

By,

StepIntoOracleDBA

Location  : Chennai
Mobile     : 9600156001

Trainings Offered :

1) Dataguard and its Techniques ( DG 1)
2) Dataguard - Advanced Concepts ( DG 2)
3) Oracle 11g New Features
4) Oracle 11g DB Administration
5) Oracle 10g DB Administration

Password File

How to Find location of Password File?
============================================================

Location & Naming in Windows :

$ORACLE_HOME/database/PWDdb_name.ora

Location & Naming in Unix :

$ORACLE_HOME/dbs/orapwdb_name.ora

Find Whether Password File is Available or not :

Select * from v$pwfile_users;

USERNAME                       SYSDB SYSOP SYSAS
------------------------------ ----- ----- -----
SYS                            TRUE  TRUE  FALSE

If V$pwfile_users shows output then password file is available and its being used. No Output means no Password file.

#########################################
# Test to Confirm this
#########################################

Now i'm going to rename the password file. In windows you will not be able to rename the file because it will be used. For testing purpose i shutdown the database and rename the file.

SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Renamed the Password File.

C:\Users\Administrator>set oracle_sid=ora11g

C:\Users\Administrator>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Thu Nov 22 20:04:23 2012

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area  753278976 bytes
Fixed Size                  1374724 bytes
Variable Size             251659772 bytes
Database Buffers          494927872 bytes
Redo Buffers                5316608 bytes
Database mounted.
Database opened.

Now quering v$pwfile_users didnt show any output.

SQL> select * from v$pwfile_users;

no rows selected

I have renamed the password file to its original name and queried the database. Now its shows the output.

SQL> select * from v$pwfile_users;

USERNAME                       SYSDB SYSOP SYSAS
------------------------------ ----- ----- -----
SYS                            TRUE  TRUE  FALSE

ORA-01274 - Resolve Archive Gaps in Standby Database

ORA-01274 Resolve Gaps in Standby Database when STANDBY_FILE_MANAGEMENT is set to MANUAL
============================================================

************** Step By Step Procedure **************

#########################################
# Issue
#########################################

Standby was not in SYNC with primary because STANDBY_FILE_MANAGEMENT is set to MANUAL. On 09-Nov-2012 two new datafiles were added in Primary, because of this manual

settings the files got created in $ORACLE_HOME/dbs location instead of Diskgroup.

#########################################
# Alert Log Errors
#########################################

Fri Nov 09 11:19:20 2012
File #6 added to control file as 'UNNAMED00006' because
the parameter STANDBY_FILE_MANAGEMENT is set to MANUAL
The file should be manually created to continue.
MRP0: Background Media Recovery terminated with error 1274
Errors in file /u01/app/oracle/diag/rdbms/STDBY_gvl/STDBY_1/trace/STDBY_1_pr00_22594.trc:
ORA-01274: cannot add datafile '+STDBY_DATA/STDBY_stl/datafile/STDBY_data01.272.798895157' - file could not be created


#########################################
# 1) Database name & Mode
#########################################

set lines 200
col name for a15
col instance_name for a15
col log_mode for a10
col open_mode for a10
col database_Role for a20
col protection_mode for a20
col protection_level for a20
col remote_archive for a15
col flashback_on for a12

Select name,instance_name,dbid,log_mode,open_mode,database_role,protection_mode,protection_level,remote_archive,flashback_on from v$database,v$instance;

NAME            INSTANCE_NAME         DBID LOG_MODE   OPEN_MODE  DATABASE_ROLE        PROTECTION_MODE      PROTECTION_LEVEL     REMOTE_ARCHIVE  FLASHBACK_ON
--------------- --------------- ---------- ---------- ---------- -------------------- -------------------- -------------------- --------------- ------------
STDBY        STDBY_1      1579933494 ARCHIVELOG MOUNTED    PHYSICAL STANDBY     MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE  ENABLED         NO

SQL> select * from v$dataguard_config;

DB_UNIQUE_NAME
------------------------------
STDBY_gvl
STDBY_stl


#########################################
# 2) Gaps in Primary & Standby
#########################################

###############
 Primary   >>>>>>>
###############

### Find GAps

SELECT THREAD# "Thread",SEQUENCE# "Last Sequence Generated" FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP

BY THREAD#) ORDER BY 1;

Select thread#,max(sequence#) from v$archived_log group by thread#;

   THREAD# MAX(SEQUENCE#)
---------- --------------
         1             97


###############
 Standby   >>>>>>>
###############

SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" FROM
(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;

    Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
         1                     97                    83         14

#########################################
# 3) Place Standby in MRP mode
#########################################

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

Database altered.

MRP process termined with the below error in Alert log.


Wed Nov 14 04:58:33 2012
MRP0: Background Media Recovery terminated with error 1111
Errors in file /u01/app/oracle/diag/rdbms/STDBY_gvl/STDBY_1/trace/STDBY_1_pr00_32449.trc:
ORA-01111: name for data file 6 is unknown - rename to correct file
ORA-01110: data file 6: '/u01/app/oracle/product/11.2/db_6/dbs/UNNAMED00006'
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01111: name for data file 6 is unknown - rename to correct file
ORA-01110: data file 6: '/u01/app/oracle/product/11.2/db_6/dbs/UNNAMED00006'
Slave exiting with ORA-1111 exception


#########################################
# 4) Check datafile Status
#########################################

###############
 Primary   >>>>>>>
###############

set lines 200
col name for a75
Select file#,name,bytes/1024/1024 "Size in MB",creation_time,status,last_time from v$datafile;

     FILE# NAME                                                                        Size in MB CREATION_ STATUS  LAST_TIME
---------- --------------------------------------------------------------------------- ---------- --------- ------- ---------
         1 +STDBY_DATA/STDBY_stl/datafile/system.256.796313651                          760 17-SEP-11 SYSTEM
         2 +STDBY_DATA/STDBY_stl/datafile/sysaux.257.796313653                          840 17-SEP-11 ONLINE
         3 +STDBY_DATA/STDBY_stl/datafile/undotbs1.258.796313653                         80 17-SEP-11 ONLINE
         4 +STDBY_DATA/STDBY_stl/datafile/users.259.796313653                             5 17-SEP-11 ONLINE
         5 +STDBY_DATA/STDBY_stl/datafile/undotbs2.266.796313803                         25 10-OCT-12 ONLINE
         6 +STDBY_DATA/STDBY_stl/datafile/STDBY_omcbc_data01.272.798895451                   1024 09-NOV-12 ONLINE
         7 +STDBY_DATA/STDBY_stl/datafile/STDBY_omcbc_indx01.273.798895897                   1024 09-NOV-12 ONLINE


###############
 Standby   >>>>>>>
###############


set lines 200
col name for a75
Select file#,name,bytes/1024/1024 "Size in MB",creation_time,status,last_time from v$datafile;

     FILE# NAME                                                                        Size in MB CREATION_ STATUS  LAST_TIME
---------- --------------------------------------------------------------------------- ---------- --------- ------- ---------
         1 +STDBY_DATA/STDBY_gvl/datafile/system.259.796323701                          750 17-SEP-11 SYSTEM
         2 +STDBY_DATA/STDBY_gvl/datafile/sysaux.262.796323701                          830 17-SEP-11 RECOVER
         3 +STDBY_DATA/STDBY_gvl/datafile/undotbs1.260.796323701                         80 17-SEP-11 ONLINE
         4 +STDBY_DATA/STDBY_gvl/datafile/users.263.796323701                             5 17-SEP-11 ONLINE
         5 +STDBY_DATA/STDBY_gvl/datafile/undotbs2.261.796323701                         25 10-OCT-12 ONLINE
         6 /u01/app/oracle/product/11.2/db_6/dbs/UNNAMED00006                                   0 09-NOV-12 RECOVER


#########################################
# 5) Check the file which is wrongly created
#########################################


set lines 200
col name for a75
select file#, name, status, creation_time from v$datafile where name like '%UNNAMED%';

     FILE# NAME                                                                        STATUS  CREATION_
---------- --------------------------------------------------------------------------- ------- ---------
         6 /u01/app/oracle/product/11.2/db_6/dbs/UNNAMED00006                          RECOVER 09-NOV-12

SQL> sho parameter standby

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
standby_file_management              string      MANUAL


select name,total_mb,free_mb from v$asm_diskgroup where state='CONNECTED';

NAME                                                                          TOTAL_MB    FREE_MB
--------------------------------------------------------------------------- ---------- ----------
STDBY_DATA                                                                   245823     242064
STDBY_RECO                                                                    65602      58545

#########################################
# 6) Rename/create Datafile to the correct Filename (Diskgroup)
#########################################

SQL>  alter database create datafile '/u01/app/oracle/product/11.2/db_6/dbs/UNNAMED00006' as '+STDBY_DATA\' size 1024m;

Database altered.

Verify the Filename is changed or not


set lines 200
col name for a75
Select file#,name,bytes/1024/1024 "Size in MB",creation_time,status,last_time from v$datafile;

     FILE# NAME                                                                        Size in MB CREATION_ STATUS  LAST_TIME
---------- --------------------------------------------------------------------------- ---------- --------- ------- ---------
         6 +STDBY_DATA/STDBY_gvl/datafile/STDBY_omcbc_data01.281.799315125                   1024 09-NOV-12 ONLINE



#########################################
# 7) Change the STANDBY_FILE_MANAGMENT to AUTO
#########################################

ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO scope=both;

SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO scope=both;

System altered.


#########################################
# 8) Start the MRP
#########################################

Alter database recover managed standby database disconnect from session;

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

Database altered.


#########################################
# 9) Check the MRP status
#########################################

set lines 200

Select process,client_process,thread#,sequence#,block#,blocks,status from v$managed_standby order by status;

PROCESS   CLIENT_P    THREAD#  SEQUENCE#     BLOCK#     BLOCKS STATUS
--------- -------- ---------- ---------- ---------- ---------- ------------
MRP0      N/A               1         84     106642     122977 APPLYING_LOG
ARCH      ARCH              1         97     124928       1886 CLOSING
ARCH      ARCH              1         96     131072        969 CLOSING
ARCH      ARCH              1         98     122880         98 CLOSING
ARCH      ARCH              0          0          0          0 CONNECTED
RFS       ARCH              0          0          0          0 IDLE
RFS       LGWR              1         99       1090          1 IDLE
RFS       UNKNOWN           0          0          0          0 IDLE

Now the logs started applying and GAPS are resolved now.

#########################################
# 10) Check the datafile status
#########################################

set lines 200
col name for a75
Select file#,name,bytes/1024/1024 "Size in MB",creation_time,status,last_time from v$datafile;

     FILE# NAME                                                                        Size in MB CREATION_ STATUS  LAST_TIME
---------- --------------------------------------------------------------------------- ---------- --------- ------- ---------
         1 +STDBY_DATA/STDBY_gvl/datafile/system.259.796323701                          760 17-SEP-11 SYSTEM
         2 +STDBY_DATA/STDBY_gvl/datafile/sysaux.262.796323701                          840 17-SEP-11 ONLINE
         3 +STDBY_DATA/STDBY_gvl/datafile/undotbs1.260.796323701                         80 17-SEP-11 ONLINE
         4 +STDBY_DATA/STDBY_gvl/datafile/users.263.796323701                             5 17-SEP-11 ONLINE
         5 +STDBY_DATA/STDBY_gvl/datafile/undotbs2.261.796323701                         25 10-OCT-12 ONLINE
         6 +STDBY_DATA/STDBY_gvl/datafile/STDBY_omcbc_data01.281.799315125                   1024 09-NOV-12 ONLINE
         7 +STDBY_DATA/STDBY_gvl/datafile/STDBY_omcbc_indx01.282.799315233                   1024 09-NOV-12 ONLINE



###############
 Standby   >>>>>>>
###############

SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" FROM
(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;

    Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
         1                     98                    98          0


SQL> sho parameter standby

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
standby_archive_dest                 string      ?/dbs/arch
standby_file_management              string      AUTO

Duplicate controlfile when files are in ASM

Duplicating a controlfile into ASM when Original controlfile is stored in ASM
============================================


 ******** Step By Step Procedure ********

#########################################
# 1) Database name & Disk Groups
#########################################

SQL> select name,open_mode,log_mode from v$database;

NAME      OPEN_MODE            LOG_MODE
--------- -------------------- ------------
MYDB   READ WRITE           ARCHIVELOG

Select name,total_mb,free_mb from v$asm_diskgroup where state='CONNECTED';

SQL> Select name,total_mb,free_mb from v$asm_diskgroup where state='CONNECTED';

NAME                             TOTAL_MB    FREE_MB
------------------------------ ---------- ----------
MYDB_DATA                      1167455    1144193
MYDB_RECO                       102518     100036

#########################################
# 2) Control files in the database
#########################################

SQL> sho parameter control

NAME                                 TYPE                             VALUE
------------------------------------ --------------------------------
control_files                        string                           +MYDB_RECO/MYDB/controlfile/current.258.798205861,
     +MYDB_RECO/MYDB/controlfile/current.259.798205863

#########################################
# 3) Add New controlfile to spfile
#########################################

SQL> sho parameter spfile

NAME                                 TYPE                             VALUE
------------------------------------ --------------------------------
spfile                               string                           +MYDB_DATA/MYDB/spfileMYDB.ora
     
If using spfile to start the instance:


Modify the spfile specifically the parameter control_files. In this example, a thrid controlfile is going to be created on different diskgroup MYDB_DATA.

alter system set control_files='+MYDB_RECO/MYDB/controlfile/current.258.798205861','+MYDB_RECO/MYDB/controlfile/current.259.798205863','+MYDB_DATA'
scope=spfile sid='*';

SQL> alter system set control_files='+MYDB_RECO/MYDB/controlfile/current.258.798205861','+MYDB_RECO/MYDB/controlfile/current.259.798205863','+MYDB_DATA'
scope=spfile sid='*';

System altered.

#########################################
# 4) Shutdown & Startup in Nomount
#########################################

Shutdown and Start the instance in NOMOUNT mode.

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

Total System Global Area 4409401344 bytes
Fixed Size                  2235528 bytes
Variable Size            2667578232 bytes
Database Buffers         1694498816 bytes
Redo Buffers               45088768 bytes
SQL>

#########################################
# 5) Duplicate Controlfile using RMAN
#########################################

restore controlfile from '+MYDB_RECO/MYDB/controlfile/current.258.798205861';

> rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Mon Nov 12 00:00:42 2012

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: MYDB (not mounted)

RMAN>

restore controlfile from '+MYDB_RECO/MYDB/controlfile/current.258.798205861';

RMAN> restore controlfile from '+MYDB_RECO/MYDB/controlfile/current.258.798205861';

Starting restore at 12-NOV-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=507 instance=MYDB_1 device type=DISK

channel ORA_DISK_1: copied control file copy
output file name=+MYDB_RECO/MYDB/controlfile/current.258.798205861
output file name=+MYDB_RECO/MYDB/controlfile/current.259.798205863
output file name=+MYDB_DATA/MYDB/controlfile/current.281.799113673
Finished restore at 12-NOV-12

RMAN>

Note that the command prints the name of the new created file: +MYDB_DATA/MYDB/controlfile/current.281.799113673

#########################################
# 6) Modify Controlfile parameter
#########################################

 Modify the control_file parameter with the complete path of the new file:

SQL> sho parameter control

NAME                                 TYPE                             VALUE
------------------------------------ --------------------------------
control_files                        string                           +MYDB_RECO/MYDB/controlfile/current.258.798205861,
     +MYDB_RECO/MYDB/controlfile/current.259.798205863,
     +MYDB_DATA

alter system set control_files='+MYDB_RECO/MYDB/controlfile/current.258.798205861','+MYDB_RECO/MYDB/controlfile/current.259.798205863',
'+MYDB_DATA/MYDB/controlfile/current.281.799113673' scope=spfile sid='*';

SQL> alter system set control_files='+MYDB_RECO/MYDB/controlfile/current.258.798205861','+MYDB_RECO/MYDB/controlfile/current.259.798205863',
'+MYDB_DATA/MYDB/controlfile/current.281.799113673' scope=spfile sid='*';

System altered.

SQL> sho parameter control

NAME                                 TYPE                             VALUE
------------------------------------ --------------------------------
control_files                        string                           +MYDB_RECO/MYDB/controlfile/current.258.798205861,
     +MYDB_RECO/MYDB/controlfile/current.259.798205863,
     +MYDB_DATA/MYDB/controlfile/current.281.799113673

#########################################
# 7) Mount and Open Database
#########################################

 Mount and Open the database

SQL> alter database mount;

Database altered.

SQL> alter database open;

Database altered.

#########################################
# 8) Validate controlfiles
#########################################

 Validate both controlfiles are present

select name from v$controlfile;

SQL> select name from v$controlfile;

NAME
------------------------------------------------------------------------------------------------------------
+MYDB_RECO/MYDB/controlfile/current.258.798205861
+MYDB_RECO/MYDB/controlfile/current.259.798205863
+MYDB_DATA/MYDB/controlfile/current.281.799113673
SQL>

Next time instance are restarted, will pick all the three control files.


SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 4409401344 bytes
Fixed Size                  2235528 bytes
Variable Size            2667578232 bytes
Database Buffers         1694498816 bytes
Redo Buffers               45088768 bytes
Database mounted.
Database opened.

SQL> select name,open_mode,log_mode from v$database;

NAME      OPEN_MODE            LOG_MODE
--------- -------------------- ------------
MYDB   READ WRITE           ARCHIVELOG

SQL> sho parameter spfile

NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- ------------------------------
spfile                               string                           +MYDB_DATA/MYDB/spfileMYDB.ora

SQL> sho parameter control

NAME                                 TYPE                             VALUE
------------------------------------ --------------------------------
control_files                        string                           +MYDB_RECO/MYDB/controlfile/current.258.798205861,
     +MYDB_RECO/MYDB/controlfile/current.259.798205863,
     +MYDB_DATA/MYDB/controlfile/current.281.799113673