Concurrent request shows Inactive phase with No-Manager status

A concurrent request has a life cycle consisting of the following phases: Pending, Running, and Completed. During each phase, a request has a specific status. Listed below are the possible statuses for each phase:

• Pending Phase - Normal, Standby, Scheduled, Waiting
• Running Phase - Normal, Paused, Resuming, Terminating
• Completed Phase - Normal, Error, Warning, Cancelled, Terminated
• Inactive Phase - Disabled, On Hold, No Manager

If a concurrent request is on hold or unable to run when there are no active manager processes that can run the request, the request is placed in an Inactive phase.

Review the following points when the concurrent request is in Inactive phase with No Manager status.

1. Verify that Internal Concurrent Manager(ICM) is up and running. Use any one navigation mentioned below to check the status details of Internal Manager.

i) Oracle Applications Manager(OAM) > Site Map > Monitoring > Availability > Internal Concurrent Manager > View Status.

OR

ii) System Administrator Responsibility > Concurrent > Manager > Administer

2. Verify that there is at least one active concurrent manager with/without specialization rules that allow the concurrent program to run.

i) Run the following query to check whether any specialization rule defined for any concurrent manager that includes/excludes the concurrent program in question. Query returns 'no rows selected' when there are no Include/Exclude specialization rules of Program type for the given concurrent program.

select 'Concurrent program '||fcp.concurrent_program_name||' is ' ||decode(fcqc.include_flag,'I','included in ','E','excluded from ')||fcqv.user_concurrent_queue_name specialization_rule_details from fnd_concurrent_queues_vl fcqv,fnd_concurrent_queue_content fcqc,fnd_concurrent_programs fcp where fcqv.concurrent_queue_id=fcqc.concurrent_queue_id and fcqc.type_id=fcp.concurrent_program_id and fcp.concurrent_program_name='<PROGRAM_SHORT_NAME>';

Note: Program Short Name is visible when the program is queried in concurrent program definition form.

Example:
SQL> select 'Concurrent program '||fcp.concurrent_program_name||' is ' ||decode(fcqc.include_flag,'I','included in ','E','excluded from ')||fcqv.user_concurrent_queue_name specialization_rule_details from fnd_concurrent_queues_vl fcqv,fnd_concurrent_queue_content fcqc,fnd_concurrent_programs fcp where fcqv.concurrent_queue_id=fcqc.concurrent_queue_id and fcqc.type_id=fcp.concurrent_program_id and fcp.concurrent_program_name='OKCRAQE';

SPECIALIZATION_RULE_DETAILS
-----------------------------------------------------------------------------
Concurrent program OKCRAQE is included in Contracts Core Concurrent Manager
Concurrent program OKCRAQE is excluded from Standard Manager 

From the sample output above, it shows that the OKCRAQE(Listener for Events Queue) concurrent program has been excluded from the Standard Manager and included in Contracts Core Concurrent Manager. That means the concurrent request OKCRAQE can be run only by the Contracts Core Concurrent Manager which should be up and running to run and complete the OKCRAQE concurrent request.

Make sure that Concurrent Manager whose specialization rule includes the concurrent program is up and running. 

ii) Ensure that standard concurrent manager is up and running.

Follow the below step only when you have confirmed the previous points and the issue is still remaining as there may be an issue with concurrent request queue view.

3. Manually re-create the concurrent request queue view for concurrent managers by entering the following command as an applmgr user at operating system prompt.

FNDLIBR FND FNDCPBWV apps/<pwd> SYSADMIN 'System Administrator' SYSADMIN

Successful completion of the above command shows the message "View created successfully" in the generated log file.

The concurrent request queue view is used internally to map requests to managers. This view would be regenerated when concurrent managers are created, or specialization rules are altered.

Oracle Apps R12 Custom Top Creation

login as: root

Access denied

root@172.19.0.26's password:

Last login: Wed Jul 24 11:23:17 2013 from 172.19.0.77

1) Make the directory structure for your custom application files.

[root@server2 ~]# su - applvr12

[applvr12@server2 ~]$ mkdir XXUL

[applvr12@server2 ~]$ cd $APPL_TOP

[applvr12@server2 appl]$ mkdir xxul

[applvr12@server2 appl]$ mkdir xxul/12.0.0

[applvr12@server2 appl]$ mkdir xxul/12.0.0/admin

[applvr12@server2 appl]$ mkdir xxul/12.0.0/admin/sql

[applvr12@server2 appl]$ mkdir xxul/12.0.0/admin/odf

[applvr12@server2 appl]$ mkdir xxul/12.0.0/sql

[applvr12@server2 appl]$ mkdir xxul/12.0.0/bin

[applvr12@server2 appl]$ mkdir xxul/12.0.0/reports

[applvr12@server2 appl]$ mkdir xxul/12.0.0/reports/US

[applvr12@server2 appl]$ mkdir xxul/12.0.0/forms

[applvr12@server2 appl]$ mkdir xxul/12.0.0/forms/US

[applvr12@server2 appl]$ mkdir xxul/12.0.0/lib

[applvr12@server2 appl]$ mkdir xxul/12.0.0/out

[applvr12@server2 appl]$ mkdir xxul/12.0.0/log

2) Add the custom module into the environment

[applvr12@server2 ~]$ cd $APPL_TOP

[applvr12@server2 appl]$ echo "XXUL_TOP=/vr12/d01/oracle/vr12/apps/apps_st/appl/xxul/12.0.0" > customvr12_server2.env

[applvr12@server2 ~]$ echo "export XXUL_TOP" >> customvr12_server2.env

[applvr12@server2 ~]$ vi $CONTEXT_FILE

<AU_TOP oa_var="s_autop" oa_type="PROD_TOP" oa_enabled="FALSE">/vr12/d01/oracle/vr12/apps/apps_st/appl/au/12.0.0</AU_TOP>

Copy the above shown line and change it according to your custom_top. Below is an example

 <XXUL_TOP oa_var="s_xxultop" oa_type="PROD_TOP" oa_enabled="FALSE">/vr12/d01/oracle/vr12/apps/apps_st/appl/xxul/12.0.0</XXUL_TOP>

[applvr12@server2 appl]$ cd $APPL_TOP

[applvr12@server2 appl]$ vi admin/topfile.txt

Enter the following line at the end of the file

xxul    /vr12/d01/oracle/vr12/apps/apps_st/appl

[applvr12@server2 ~]$ echo $XXUL_TOP

/vr12/d01/oracle/vr12/apps/apps_st/appl/xxul/12.0.0

[applvr12@server2 appl]$ exit

Logout

3) Create a Tablespace

[root@server2 ~]# su - oravr12

[oravr12@server2 ~]$ sqlplus '/as sysdba'

SQL> create tablespace XXUL datafile '/vr12/d02/oracle/vr12/db/apps_st/data/XXUL01.dbf' size 1g;

Tablespace created.

SQL> create user XXUL identified by XXUL

2  temporary tablespace temp

3  quota unlimited on XXUL;

User altered.

SQL> grant connect, resource to XXUL;

Grant succeeded.

4) Register your Oracle Schema.

Login to Applications with System Administrator responsibility

Navigate to Application-->Register

Application = XXUL Custom Application

Short Name = XXUL

Basepath = XXUL_TOP

Description = XXUL Custom Application

5) Register Oracle User

Naviate to Security-->Oracle-->Register

Database User Name = XXUL

Password = XXUL

Privilege = Enabled

Install Group = 0

Description = XXUL Custom Application User

6) Add Application to a Data Group

Navigate to Security-->Oracle-->DataGroup

7) Create custom request group

This will act as a placeholder for any custom reports we wish to make available for the

Custom Responsibility (which is defined at a later stage)

Navigate to Security-->responsibility-->Request

Group = XXUL Request Group

Application = XXUL Custom

Code = XXUL

Description = XXUL Custom Requests

We will not define any requests to add to the group at this stage, but you can add some

now if required.

8) Create custom menu

This will act as a placeholder for any menu items we wish to make available for the

Custom Responsibility (which is defined at a later stage). We will create two menus, one

for Core Applications and one for Self Service.

Navigate to Application-->Menu

Menu = XXUL_CUSTOM_MENU

User Menu Name = XXUL Custom Application

Menu Type =

Description = XXUL Custom Application Menu

Seq = 100

Prompt = View Requests

Submenu =

Function = View All Concurrent Requests

Description = View Requests

Seq = 110

Prompt = Run Requests

Submenu =

Function = Requests: Submit

Description = Submit Requests

9) Create new responsibility. Navigate to Security-->Responsibility-->Define

Responsibility Name = XXUL Custom

Application = XXUL Custom

Responsibility Key = XXULCUSTOM

Description = XXUL Custom Responsibility

Available From = Oracle Applications

Data Group Name = XXULGroup

Data Group Application = XXUL Custom

Menu = XXUL Custom Application

Request Group Name = XXUL Request Group

10) Add responsibility to user

Navigate to Security-->User-->Define

Add XXUL Custom responsibility to users as required.

ORA-01157

ORA-01157: cannot identify/lock data file 6 - see DBWR trace file

ORA-01110: data file 1: '/PRIM/u02/oradata/orcl/users01.dbf'

SQL> startup

ORACLE instance started.

Total System Global Area 1286066176 bytes

Fixed Size                                2287960 bytes

Variable Size                        452986536 bytes

Database Buffers               822083584 bytes

Redo Buffers                         8708096 bytes

Database mounted.

ORA-01157: cannot identify/lock data file 6 - see DBWR trace file

ORA-01110: data file 1: '/PRIM/u02/oradata/orcl/users01.dbf'

SQL> col error format a15

SQL> select * from v$recover_file;

     FILE# ONLINE  ONLINE_ ERROR                   CHANGE# TIME              CON_ID

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

         6      ONLINE  ONLINE  FILE NOT FOUND          0                                    0

SQL> col file_name format a40

SQL> col tablespace_name format a15

SQL> select a.file#,a.name as FILE_NAME,b.name as TABLESPACE_NAME,status from v$datafile a,v$tablespace b where a.ts#=b.ts#;

     FILE# FILE_NAME                                                            TABLESPACE_NAME STATUS

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

        1      /PRIM/u02/oradata/orcl/system01.dbf                             SYSTEM                SYSTEM

        3      /PRIM/u02/oradata/orcl/sysaux01.dbf                             SYSAUX                ONLINE

        4      /PRIM/u02/oradata/orcl/undotbs01.dbf                           UNDOTBS1           ONLINE

        6      /PRIM/u02/oradata/orcl/users01.dbf                                USERS                   ONLINE

        2      /PRIM/u02/oradata/orcl/example01.dbf                           EXAMPLE            ONLINE

SQL> alter database datafile '/PRIM/u02/oradata/orcl/users01.dbf' offline;

Database altered.

SQL> alter database open;

Database altered.

SQL> col file_name format a40

SQL> col tablespace_name format a15

SQL> select a.file#,a.name as FILE_NAME,b.name as TABLESPACE_NAME,status from v$datafile a,v$tablespace b where a.ts#=b.ts#;

     FILE# FILE_NAME                                             TABLESPACE_NAME STATUS

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

        1      /PRIM/u02/oradata/orcl/system01.dbf              SYSTEM               SYSTEM

        2      /PRIM/u02/oradata/orcl/example01.dbf           EXAMPLE            ONLINE

        3      /PRIM/u02/oradata/orcl/sysaux01.dbf              SYSAUX               ONLINE

        4      /PRIM/u02/oradata/orcl/undotbs01.dbf            UNDOTBS1          ONLINE

        6      /PRIM/u02/oradata/orcl/users01.dbf                 USERS                  OFFLINE

SQL> exit

Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

[oraprim@localhost ~]$ rman target / catalog rman/rman@oracat

Recovery Manager: Release 12.1.0.1.0 - Production on Tue Mar 22 12:35:52 2016

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

connected to target database: ORCL (DBID=1434527233)

connected to recovery catalog database

RMAN>list backup of datafile 6;

List of Backup Sets

===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

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

178     Full    1.26G      DISK        00:03:06     19-MAR-16     

        BP Key: 184   Status: AVAILABLE  Compressed: NO  Tag: TAG20160319T140522

        Piece Name: /RMAN_Backup/Backup_ORCL_09r0shh5_1_1_20160319_1

  List of Datafiles in backup set 178

  File LV Type Ckp SCN    Ckp Time  Name

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

  6       Full 1791582    19-MAR-16 /PRIM/u02/oradata/orcl/users01.dbf

RMAN> restore datafile 6 preview;

Starting restore at 22-MAR-16

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=48 device type=DISK

List of Backup Sets

===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

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

178     Full    1.26G      DISK        00:03:06     19-MAR-16     

        BP Key: 184   Status: AVAILABLE  Compressed: NO  Tag: TAG20160319T140522

        Piece Name: /RMAN_Backup/Backup_ORCL_09r0shh5_1_1_20160319_1

  List of Datafiles in backup set 178

  File LV Type Ckp SCN    Ckp Time  Name

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

  6       Full 1791582    19-MAR-16 /PRIM/u02/oradata/orcl/users01.dbf

using channel ORA_DISK_1

List of Archived Log Copies for database with db_unique_name ORCL

=====================================================================

Key     Thrd Seq     S Low Time

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

281     1    12      A 19-MAR-16

        Name: /PRIM/u02/app/oraprim/product/12.1.0/dbhome_1/database/archive/Log_55812601_12_1_906746499.arc

279     1    13      A 19-MAR-16

        Name: /PRIM/u02/app/oraprim/product/12.1.0/dbhome_1/database/archive/Log_55812601_13_1_906746499.arc

Media recovery start SCN is 1791582

Recovery must be done beyond SCN 1791582 to clear datafile fuzziness

Finished restore at 22-MAR-16

RMAN> restore datafile 6;

Starting restore at 22-MAR-16

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_DISK_1: restoring datafile 00006 to /PRIM/u02/oradata/orcl/users01.dbf

channel ORA_DISK_1: reading from backup piece /RMAN_Backup/Backup_ORCL_09r0shh5_1_1_20160319_1

channel ORA_DISK_1: piece handle=/RMAN_Backup/Backup_ORCL_09r0shh5_1_1_20160319_1 tag=TAG20160319T140522

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

Finished restore at 22-MAR-16

RMAN> recover datafile 6;

Starting recover at 22-MAR-16

using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 12 is already on disk as file /PRIM/u02/app/oraprim/product/12.1.0/dbhome_1/database/archive/Log_55812601_12_1_906746499.arc

archived log for thread 1 with sequence 13 is already on disk as file /PRIM/u02/app/oraprim/product/12.1.0/dbhome_1/database/archive/Log_55812601_13_1_906746499.arc

archived log file name=/PRIM/u02/app/oraprim/product/12.1.0/dbhome_1/database/archive/Log_55812601_12_1_906746499.arc thread=1 sequence=12

archived log file name=/PRIM/u02/app/oraprim/product/12.1.0/dbhome_1/database/archive/Log_55812601_13_1_906746499.arc thread=1 sequence=13

media recovery complete, elapsed time: 00:00:00

Finished recover at 22-MAR-16

RMAN> exit

Recovery Manager complete.

[oraprim@localhost ~]$ sqlplus '/as sysdba'

SQL*Plus: Release 12.1.0.1.0 Production on Tue Mar 22 12:40:13 2016

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

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> select name, open_mode from v$database;

NAME     OPEN_MODE

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

ORCL       READ WRITE

SQL> col file_name format a40

SQL> col tablespace_name format a15

SQL> select a.file#,a.name as FILE_NAME,b.name as TABLESPACE_NAME,status from v$datafile a,v$tablespace b where a.ts#=b.ts#;

     FILE# FILE_NAME                                                            TABLESPACE_NAME STATUS

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

         1       /PRIM/u02/oradata/orcl/system01.dbf                            SYSTEM               SYSTEM

         2       /PRIM/u02/oradata/orcl/example01.dbf                         EXAMPLE            ONLINE

         3       /PRIM/u02/oradata/orcl/sysaux01.dbf                            SYSAUX               ONLINE

         4       /PRIM/u02/oradata/orcl/undotbs01.dbf                          UNDOTBS1          ONLINE

         6       /PRIM/u02/oradata/orcl/users01.dbf                               USERS                  OFFLINE

SQL> alter database datafile '/PRIM/u02/oradata/orcl/users01.dbf' online;

Database altered.

SQL> col file_name format a40

SQL> col tablespace_name format a15

SQL> select a.file#,a.name as FILE_NAME,b.name as TABLESPACE_NAME,status from v$datafile a,v$tablespace b where a.ts#=b.ts#;

      FILE# FILE_NAME                                                            TABLESPACE_NAME STATUS

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

         1       /PRIM/u02/oradata/orcl/system01.dbf                            SYSTEM               SYSTEM

         2       /PRIM/u02/oradata/orcl/example01.dbf                         EXAMPLE            ONLINE

         3       /PRIM/u02/oradata/orcl/sysaux01.dbf                            SYSAUX               ONLINE

         4       /PRIM/u02/oradata/orcl/undotbs01.dbf                          UNDOTBS1          ONLINE

         6       /PRIM/u02/oradata/orcl/users01.dbf                               USERS                  ONLINE  

ORA-01113

ORA-01113: file 1 needs media recovery

ORA-01110: data file 1: '/oradata/orcl/system01.dbf'

cause : An attempt was made to online or open a database with a file that is in need of media recovery.

Solution : First apply media recovery to the file and then open the database.

Resolution:

1) Start the DB with mount option.

SQL> startup mount

ORACLE instance started.

Total System Global Area 1603411968 bytes

Fixed Size                                2288872 bytes

Variable Size                      1040188184 bytes

Database Buffers               553648128 bytes

Redo Buffers                         7286784 bytes

Database mounted.

2) Find the name of the redo log file which belongs to the active group.

SQL> SELECT MEMBER FROM V$LOG G, V$LOGFILE F WHERE G.GROUP# = F.GROUP# AND

  2  G.STATUS = 'CURRENT';

MEMBER

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

/oradata/orcl/redo02.log

3)Using a backup control file, start the cancel based recovery. The system may suggest a non-existing archive log -- ignore it and specify the log file name {with full path} that you got in the above step

SQL> RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL
ORA-00279: change 21375038 generated at 09/21/2007 20:12:47 needed for thread 1
ORA-00289: suggestion : /export/pspp/oracle/dbs/arch1_4_633901491.dbf
ORA-00280: change 21375038 for thread 1 is in sequence #4

Specify log: {=suggested | filename | AUTO | CANCEL}
/oradata/orcl/redo02.log

Log applied.
Media recovery complete.

4) Finally open the database in RESETLOGS mode. It is recommended to reset the online logs after incomplete recovery or recovery with a backup control file

SQL> ALTER DATABASE OPEN RESETLOGS;
Database altered.

     Take a backup of the database as soon as the recovery is done.

bash profile and listener.ora file entries

After we've Installed a new database, we need to add entries in .bash_profile and listener.ora files as per our requirements. So here in this post I've shared the generally used entries. Add the below entries apart from already existing entries of your database.

.bash_profile entry

LD_LIBRABRY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRABRY_PATH

CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH

BASE_PATH=/usr/sbin:$PATH:$HOME/bin; export BASE_PATH

PATH=$ORACLE_HOME/bin:$BASE_PATH; export PATH

                                  

listener.ora file entry

SID_LIST_ocl =

 (SID_LIST =

  (SID_DESC =

   (ORACLE_HOME=/u01/app/oraprim/product/12.1.0/dbhome_1)

   (SID_NAME=orcl)

  )

 )

Check if the DISPLAY variable is set in Linux environment

Most of the users have been getting Could not execute auto check for display colors using command /usr/bin/xdpyinfo. Check if the DISPLAY variable is set in Linux environment while checking requirement the oracle installations on  Linux 5.2 or later versions.

Below is the screenshot of the issue

I have tried plenty of options, even ignoring the pre-requisites.

Finally, i have fixed this issue through one Linux command

Steps to resolve this issue:

1) login into root user( su -l root)

2) execute this command xhost +SI:localuser:oracle

3) login to the oracle user

4) execute ./runInstaller

Now, it'll work fine.

Batchloader issue in oracle webcenter content

I am using Oracle webcenter content 11.1.1.7.0. To configure a JDBC Driver for standalone applications i ran DomainHome/ucm/cs/bin/SystemProperties and entered the details as follows
database driver classpath : /Integration/d02/app/oramdev/product/11.2.0/dbhome_1/jdbc/lib/ojdbc6.jar
jdbc driver name : oracle.jdbc.OracleDriver
jdbc connection string : jdbc:oracle:thin:@server2.yantro.com:1521/orcl.yantro.com
jdbc username : DEV_OCS
jdbc user password : schema password

then i ran ./BatchLoader and it throws the following error.

oramdev@server2 bin]$ ./BatchLoader
'DynamicConverter' component, version
'2013_01_16 (rev 23669) 11.1.7.6', require
s a missing feature. The feature
'ContentAccess' is not installed.
>componentloader/4 06.21
19:11:19.106 main !csComponentRequiresMiss
ingFeatures,DynamicConverter,2013_01_16 (rev 23669)
11.1.7.6,!syFeatureNotInstal
led\,ContentAccess\,\,0
>(internal)/3 06.21 19:11:31.953
main !$Unable to start the batch load
er.
(internal)/3 06.21 19:11:31.953
main at intradoc.common.Defau
ltTraceImplementor.appendStackTrace(DefaultTraceImplementor.java:327)
(internal)/3 06.21 19:11:31.953
main at intradoc.common.Defau
ltTraceImplementor.traceDumpException(DefaultTraceImplementor.java:446)
(internal)/3 06.21 19:11:31.953
main at intradoc.common.Defau
ltReportHandler.message(DefaultReportHandler.java:439)
(internal)/3 06.21 19:11:31.953
main at intradoc.common.Defau
ltReportDelegator.message(DefaultReportDelegator.java:140)
(internal)/3 06.21 19:11:31.953
main at intradoc.common.Repor
t.messageInternal(Report.java:172)
(internal)/3 06.21 19:11:31.953
main at intradoc.common.Repor
t.message(Report.java:154)
(internal)/3 06.21 19:11:31.953
main at intradoc.common.Repor
t.error(Report.java:427)
(internal)/3 06.21 19:11:31.953
main at BatchLoader.main(Batc
hLoader.java:104)
(internal)/3 06.21 19:11:31.953
main at sun.reflect.NativeMet
hodAccessorImpl.invoke0(Native Method)
(internal)/3 06.21 19:11:31.953
main at sun.reflect.NativeMet
hodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
(internal)/3 06.21 19:11:31.953
main at sun.reflect.Delegatin
gMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
(internal)/3 06.21 19:11:31.953
main at java.lang.reflect.Met
hod.invoke(Method.java:597)
(internal)/3 06.21 19:11:31.953
main at intradoc.loader.IdcCl
assLoader.invokeMain(IdcClassLoader.java:352)
(internal)/3 06.21 19:11:31.953
main at intradoc.loader.IdcCl
assLoader.startMain(IdcClassLoader.java:170)
(internal)/3 06.21 19:11:31.953
main at intradoc.loader.IdcCl
assLoader.main(IdcClassLoader.java:140)
(internal)/3 06.21 19:11:31.953
main Caused by: intradoc.data.DataExc
eption: !apFailedToInitialize
(internal)/3 06.21 19:11:31.953
main at intradoc.apps.shared.
AppLauncher.init(AppLauncher.java:252)
(internal)/3 06.21 19:11:31.953
main at BatchLoader.main(Batc
hLoader.java:82)
(internal)/3 06.21 19:11:31.953
main ... 7 more
(internal)/3 06.21 19:11:31.953
main Caused by: intradoc.common.Servi
ceException:
!csProviderUnableToInitialize,SystemDatabase
(internal)/3 06.21 19:11:31.953
main at intradoc.server.IdcSy
stemLoader.loadProviders(IdcSystemLoader.java:2371)
(internal)/3 06.21 19:11:31.953
main at intradoc.server.IdcSy
stemLoader.initProviders(IdcSystemLoader.java:2124)
(internal)/3 06.21 19:11:31.953
main at intradoc.server.IdcSy
stemLoader.finishInit(IdcSystemLoader.java:400)
(internal)/3 06.21 19:11:31.953
main at intradoc.server.IdcSy
stemLoader.init(IdcSystemLoader.java:335)
(internal)/3 06.21 19:11:31.953
main at intradoc.apps.shared.
StandAloneApp.initSystem(StandAloneApp.java:277)
(internal)/3 06.21 19:11:31.953
main at intradoc.apps.shared.
StandAloneApp.init(StandAloneApp.java:112)
(internal)/3 06.21 19:11:31.953
main at intradoc.apps.shared.
AppLauncher.init(AppLauncher.java:234)
(internal)/3 06.21 19:11:31.953
main ... 8 more
(internal)/3 06.21 19:11:31.953
main Caused by: intradoc.data.DataExc
eption:
!csJdbcUnableToCreateConnection,SystemDatabase,jdbc:oracle:thin:@server2
.yantro.com:1521/orcl.yantro.com
(internal)/3 06.21 19:11:31.953
main at intradoc.jdbc.JdbcCon
nectionUtils.getConnection(JdbcConnectionUtils.java:123)
(internal)/3 06.21 19:11:31.953
main at intradoc.jdbc.JdbcWor
kspace.init(JdbcWorkspace.java:84)
(internal)/3 06.21 19:11:31.953
main at intradoc.provider.Pro
vider.init(Provider.java:71)
(internal)/3 06.21 19:11:31.953
main at intradoc.server.IdcSy
stemLoader.loadProviders(IdcSystemLoader.java:2361)
(internal)/3 06.21 19:11:31.953
main ... 14 more
(internal)/3 06.21 19:11:31.953
main Caused by: java.sql.SQLException
: ORA-01017: invalid username/password;
logon denied
(internal)/3 06.21 19:11:31.953
main
(internal)/3 06.21 19:11:31.953
main at oracle.jdbc.driver.T4
CTTIoer.processError(T4CTTIoer.java:445)
(internal)/3 06.21 19:11:31.953
main at oracle.jdbc.driver.T4
CTTIoer.processError(T4CTTIoer.java:389)
(internal)/3 06.21 19:11:31.953
main at oracle.jdbc.driver.T4
CTTIoer.processError(T4CTTIoer.java:382)
(internal)/3 06.21 19:11:31.953
main at oracle.jdbc.driver.T4
CTTIfun.processError(T4CTTIfun.java:600)
(internal)/3 06.21 19:11:31.953
main at oracle.jdbc.driver.T4
CTTIoauthenticate.processError(T4CTTIoauthenticate.java:445)
(internal)/3 06.21 19:11:31.953
main at oracle.jdbc.driver.T4
CTTIfun.receive(T4CTTIfun.java:450)
(internal)/3 06.21 19:11:31.953
main at oracle.jdbc.driver.T4
CTTIfun.doRPC(T4CTTIfun.java:192)
(internal)/3 06.21 19:11:31.953
main at oracle.jdbc.driver.T4
CTTIoauthenticate.doOAUTH(T4CTTIoauthenticate.java:380)
(internal)/3 06.21 19:11:31.953
main at oracle.jdbc.driver.T4
CTTIoauthenticate.doOAUTH(T4CTTIoauthenticate.java:760)
(internal)/3 06.21 19:11:31.953
main at oracle.jdbc.driver.T4
CConnection.logon(T4CConnection.java:401)
(internal)/3 06.21 19:11:31.953
main at oracle.jdbc.driver.Ph
ysicalConnection.<init>(PhysicalConnection.java:546)
(internal)/3 06.21 19:11:31.953
main at oracle.jdbc.driver.T4
CConnection.<init>(T4CConnection.java:236)
(internal)/3 06.21 19:11:31.953
main at oracle.jdbc.driver.T4
CDriverExtension.getConnection(T4CDriverExtension.java:32)
(internal)/3 06.21 19:11:31.953
main at oracle.jdbc.driver.Or
acleDriver.connect(OracleDriver.java:521)
(internal)/3 06.21 19:11:31.953
main at java.sql.DriverManage
r.getConnection(DriverManager.java:582)
(internal)/3 06.21 19:11:31.953
main at java.sql.DriverManage
r.getConnection(DriverManager.java:154)
(internal)/3 06.21 19:11:31.953
main at intradoc.jdbc.JdbcCon
nectionUtils.getConnection(JdbcConnectionUtils.java:107)
(internal)/3 06.21 19:11:31.953
main ... 17 more

and moreover it shows the following
uanble to start batch loader. Failed to initialize.unable to initialize the system provider 'systemdatabase'. Unable to create the database connection for the 'systemdatabase' with connection string
'jdbc:oracle:thin:@server2.yantro.com:1521/orcl.yantro.com'. Please make sure that the connection string,user and password are correct.
java.sql.SQLException:ORA-01017:invalid username/password;login denied

Main issue here is : !csProviderUnableToInitialize,SystemDatabase and the steps to solve the issue are

1. Navigate to <domain_home>/ucm/cs/bin/SystemProperties applet

2. Navigate to database tab and select the Oracle Thin Driver

3. leave the classpath field blank

4. For the db connection , provide the db host name , port and service name details

5. Set the UCM schema user id and password and update

6. Run these 2 sql queries on UCM DB schema :

update users set dpasswordencoding='' where dname like 'sysadmin';

update users set dpassword='idc' where dname like 'sysadmin';

7. Then launch BatchLoader and verify it works fine .

 

RMAN: CONTROL FILE RECOVERY SCENARIOS

Every Oracle Database has a control file as one of its important file. It is a small binary file that records the physical structure of the database.

The control file includes:

– Database name
– Names and locations of associated datafiles and redo log files
– The timestamp of the database creation
– The current log sequence number
– Checkpoint information

Without the control file, the database cannot be mounted and recovery is difficult. The control file must be available for writing by the Oracle Database server whenever the database is open.

By default, at least one copy of the control file is created during database creation. On some operating systems the default is to create multiple copies. You should create two or more copies of the control file during database creation.

You can also create control files later, if you lose control files or want to change particular settings in the control files. Remember that anytime you restore a control file from a backup, you are required to perform media recovery on your entire database and then open it with the open resetlogs command. This is true even if you don’t restore any datafiles (because the control file’s SCN is no longer synchronized with the SCNs in the datafiles and online redo log files).

Below are some of the recovery scenarios related to control files.

---

CASE 1: LOST ALL CONTROL FILES AND USING FLASH RECOVER AREA (FRA) BUT NOT RECOVERY CATALOG

— CASE 1 – SCENARIO 1: AUTOBACKUP OF CONTROL FILE IS ENABLED
— CASE 1 – SCENARIO 2: AUTOBACKUP OF CONTROL FILE IS NOT ENABLED

CASE 2: LOST ALL CONTROL FILES AND USING RECOVERY CATALOG BUT NOT FLASH RECOVER AREA (FRA)

CASE 3: LOST ALL CONTROL FILES AND NOT USING EITHER RECOVERY CATALOG OR FLASH RECOVER AREA (FRA)

CASE 4: LOST ONLY ONE CONTROL FILE

CASE 5: RECREATING THE CONTROL FILES (FOR DATABASE CLONING)

---

Let us look at all the above cases with some example:

Database Version used: 11gR2 (11.2.0.4)

OS used: RHEL 6

Target Database Name: ORCL

RMAN Database Name: catdb

CASE 1: LOST ALL CONTROL FILES AND USING FLASH RECOVER AREA (FRA) BUT NOT RECOVERY CATALOG

SCENARIO 1: AUTOBACKUP OF CONTROL FILE IS ENABLED

It is highly recommended to enable autobackup of the control file

We had two control files, one at FRA location and another in Non-FRA location. We lost both of the control files

Control file 1: /u01/oracle/DB11G/oradata/orcl/control01.ctl,
COntrol file 2: /u01/oracle/DB11G/fast_recovery_area/orcl/control02.ctl

Now you cannot start the database since the oracle process is unable to locate the control files.

SYS@orcl > startup

ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size 2260088 bytes
Variable Size 671089544 bytes
Database Buffers 390070272 bytes
Redo Buffers 5517312 bytes
ORA-00205: error in identifying control file, check alert log for more info

Let us start the restore process of control file using RMAN

RMAN> connect target /

connected to target database (not started)

RMAN> startup nomount;

Oracle instance started

Total System Global Area 1068937216 bytes

Fixed Size 2260088 bytes
Variable Size 671089544 bytes
Database Buffers 390070272 bytes
Redo Buffers 5517312 bytes

NOTE: Now since you enabled autobackups of your control file and also used a flash recovery area, you don’t have to explicitly provide RMAN with the name and location of backup files or your target database identifier (DBID). This is the simplest method that RMAN provides for restoring a control file.

RMAN uses the value of your operating system ORACLE_SID variable to look in the default location for control file backups in the flash recovery area

RMAN> restore controlfile from autobackup;

Starting restore at 19-FEB-2016 12:50:00
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=134 device type=DISK

..
…
Finished restore at 19-FEB-2016 12:50:04

NOTE: As we have restored a control file from a backup, we are required to perform media recovery on entire database and then open it with the open resetlogs command. This is true although we didn’t restore any datafiles (because the control file’s SCN is no longer synchronized with the SCNs in the datafiles and online redo log files).

---

 What will happen if you try to open database at this stage without doing recovery:

RMAN> alter database open resetlogs;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 02/19/2016 13:11:39
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: ‘/u01/oracle/DB11G/oradata/orcl/system01.dbf’

---

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

RMAN> recover database;

RMAN> alter database open resetlogs;

database opened

CASE 1: LOST ALL CONTROL FILES AND USING FLASH RECOVER AREA (FRA) BUT NOT RECOVERY CATALOG

SCENARIO 2: AUTOBACKUP OF CONTROL FILE IS NOT ENABLED

Remember that control files autobackup OFF doesn’t mean you don’t have control files backup. RMAN will by default back up your control file anytime you back up datafile 1 (which is SYSTEM data file), regardless of whether you have the autobackup of your control file feature enabled.

When you don’t have the autobackup of your control file enabled, then by default RMAN will place the backup of your control file in FRA directory path.
When you restore your control file and when the autobackup feature has not been enabled, then RMAN is unable to determine by itself the default location. You must directly tell RMAN from which backup piece to restore the control file.

Now important thing is to find which backup piece contains the backup of your control file. If we have the RMAN output log from a backup, then we will be easily able to find it.

For example, here is the partial output of RMAN messages during database backup:
including current control file in backup set

..

including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 19-FEB-2016 13:02:58
channel ORA_DISK_1: finished piece 1 at 19-FEB-2016 13:03:01
piece handle=/u01/oracle/DB11G/fast_recovery_area/ORCL/backupset/2016_02_19/o1_mf_ncsnf_TAG20160219T130101_9jb702jq_.bkp tag=TAG20160219T130101 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 19-FEB-2016 13:03:01

…
So the backup piece which contains control file is o1_mf_ncsnf_TAG20160219T130101_9jb702jq_.bkp.

Let us start the restore process now.

$ rman

RMAN> connect target /

connected to target database (not started)

RMAN> startup nomount;

Oracle instance started

Total System Global Area 1068937216 bytes

Fixed Size 2260088 bytes
Variable Size 671089544 bytes
Database Buffers 390070272 bytes
Redo Buffers 5517312 bytes

RMAN> restore controlfile from ‘/u01/oracle/DB11G/fast_recovery_area/ORCL/backupset/2016_02_19/o1_mf_ncsnf_TAG20160219T130101_9jb702jq_.bkp';

Starting restore at 19-FEB-2016 13:10:28
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=134 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
output file name=/u01/oracle/DB11G/oradata/orcl/control01.ctl
output file name=/u01/oracle/DB11G/fast_recovery_area/orcl/control02.ctl
Finished restore at 19-FEB-2016 13:10:31

NOTE: As we have restored a control file from a backup, we are required to perform media recovery on entire database and then open it with the open resetlogs command. This is true although we didn’t restore any datafiles (because the control file’s SCN is no longer synchronized with the SCNs in the datafiles and online redo log files).

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

RMAN> recover database;

Starting recover at 19-FEB-2016 13:12:36
….
..
using channel ORA_DISK_1

starting media recovery

..
.
media recovery complete, elapsed time: 00:00:01
Finished recover at 19-FEB-2016 13:12:42

RMAN> alter database open resetlogs;

database opened

CASE 2: LOST ALL CONTROL FILES AND USING RECOVERY CATALOG BUT NOT FLASH RECOVER AREA (FRA)

When you are using a recovery catalog it is simpler to restore the control files. All you need to do is ensure that you connect to both your target database and the recovery catalog. Then issue startup nomount, and issue the restore controlfile command.

$ rman

Recovery Manager: Release 11.2.0.4.0 – Production on Wed Feb 19 13:26:11 2016

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

RMAN> connect target /

connected to target database (not started)

RMAN> connect catalog rman/rman@catdb

connected to recovery catalog database

RMAN> startup nomount;

Oracle instance started

Total System Global Area 1068937216 bytes

Fixed Size 2260088 bytes
Variable Size 671089544 bytes
Database Buffers 390070272 bytes
Redo Buffers 5517312 bytes

RMAN> restore controlfile;

Starting restore at 19-FEB-2016 13:26:46
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=134 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: reading from backup piece /backups/orcl/controlfile_orcl_c-1279650270-20160219-02
channel ORA_DISK_1: piece handle=/backups/orcl/controlfile_orcl_c-1279650270-20160219-02 tag=TAG20160219T131851
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
output file name=/u01/oracle/DB11G/oradata/orcl/control01.ctl
output file name=/u01/oracle/DB11G/fast_recovery_area/orcl/control02.ctl
Finished restore at 19-FEB-2016 13:26:50

Say If AUTOBACKUP OF CONTROL FILE WAS NOT ENABLED, then also RMAN ‘restore controlfile;’ comannd would have located the right backup piece to get control file.

As shown below:

RMAN> restore controlfile;

Starting restore at 19-FEB-2016 13:59:42
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=133 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: reading from backup piece /u01/oracle/DB11G/fast_recovery_area/ORCL/backupset/2016_02_19/o1_mf_ncsnf_TAG20160219T133923_9jb97f0z_.bkp
channel ORA_DISK_1: piece handle=/u01/oracle/DB11G/fast_recovery_area/ORCL/backupset/2016_02_19/o1_mf_ncsnf_TAG20160219T133923_9jb97f0z_.bkp tag=TAG20160219T133923
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
output file name=/u01/oracle/DB11G/oradata/orcl/control01.ctl
output file name=/u01/oracle/DB11G/fast_recovery_area/orcl/control02.ctl
Finished restore at 19-FEB-2016 13:59:45

NOTE: As we have restored a control file from a backup, we are required to perform media recovery on entire database and then open it with the open resetlogs command. This is true although we didn’t restore any datafiles (because the control file’s SCN is no longer synchronized with the SCNs in the datafiles and online redo log files).

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

RMAN> recover database;

Starting recover at 19-FEB-2016 13:28:17

….
..
using channel ORA_DISK_1

starting media recovery

..
.
media recovery complete, elapsed time: 00:00:01
Finished recover at 19-FEB-2016 13:28:25

RMAN> alter database open resetlogs;

database opened
new incarnation of database registered in recovery catalog
starting full resync of recovery catalog
full resync complete

CASE 3: LOST ALL CONTROL FILES AND NOT USING EITHER RECOVERY CATALOG OR FLASH RECOVER AREA (FRA)

Let us assume our backup strategy doesn’t take advantage of either a flash recovery area or a recovery catalog.

If you’re using a flash recovery area with the autobackup of your control file enabled, then you shouldn’t need the DBID when restoring the control file. If you’re neither using a flash recovery area nor using a recovery catalog, then you might have to know your DBID before you restore the control file.

RMAN uses the DBID to uniquely identify databases. The DBID helps RMAN identify the correct RMAN backup piece from which to restore the control file.

If you are trying to restore a control file without setting DBID, then it will give you the error related to DBID as follows:

RMAN> restore controlfile from autobackup;

Starting restore at 19-FEB-2016 14:20:22
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=134 device type=DISK

AUTOBACKUP search with format “%F” not attempted because DBID was not set

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 02/19/2016 14:20:24
RMAN-06172: no AUTOBACKUP found or specified handle is not a valid copy or piece

[/stextbox]

So you need to first find the DBID for your target database, so that RMAN should be aware of the database for which restore is required.

Two of the easy methods are described below.

METHOD 1: Getting the DBID from an Autobackup File

If you check any of  the RMAN backup log or check the RMAN generated autobackup files, you can easily find DBID

For example below is RMAN log snippet:

Starting Control File and SPFILE Autobackup at 19-FEB-2016 14:17:52
piece handle=/backups/orcl/controlfile_orcl_c-1279650270-20160219-05 comment=NONE
Finished Control File and SPFILE Autobackup at 19-FEB-2016 14:17:55

..

The auto generated control file is :  /backups/orcl/controlfile_orcl_c-1279650270-20160219-05

As you can see in the name

c                          >> refers to control file backup
1279650270 >> DBID
20160219        >> refers to DATE when backup was created

METHOD 2: Dumping Files

If any of the datafiles, online redo log files, or archived redo log files are physically available, you can use the SQL alter system dump statement to write the DBID to a trace file.

Your database does not have to be mounted for this to work. For example, here is the syntax for taking a datafile dump:

SQL> connect / as sysdba

SQL> startup nomount;

SQL> alter system dump datafile ‘/u01/oracle/DB11G/oradata/orcl/system01.dbf’ block min 1 block max 10;

Use this syntax to take a dump of an archived redo log file or online redo log file:

SQL> alter system dump logfile ‘<log file name>';

The trace file with the DBID will be in your user dump destination. If you search for the string “Db ID,” you should find something similar to this output:

Db ID=1279650270=0x4c45e9de, Db Name=’ORCL’

Now since you got the DBID, we can start the control file restore

$ rman

Recovery Manager: Release 11.2.0.4.0 – Production on Wed Feb 19 14:41:00 2016

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

RMAN> connect target /

connected to target database (not started)

RMAN> startup nomount;

Oracle instance started

Total System Global Area 1068937216 bytes

Fixed Size 2260088 bytes
Variable Size 671089544 bytes
Database Buffers 390070272 bytes
Redo Buffers 5517312 bytes

RMAN> set dbid 1279650270;

executing command: SET DBID

RMAN> set controlfile autobackup format for device type disk to ‘/backups/orcl/controlfile_orcl_%F';

executing command: SET CONTROLFILE AUTOBACKUP FORMAT

RMAN> restore controlfile from autobackup;

Starting restore at 19-FEB-2016 14:41:40
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=134 device type=DISK

channel ORA_DISK_1: looking for AUTOBACKUP on day: 20160219
channel ORA_DISK_1: AUTOBACKUP found: /backups/orcl/controlfile_orcl_c-1279650270-20160219-06
channel ORA_DISK_1: restoring control file from AUTOBACKUP /backups/orcl/controlfile_orcl_c-1279650270-20160219-06
channel ORA_DISK_1: control file restore from AUTOBACKUP complete
output file name=/u01/oracle/DB11G/oradata/orcl/control01.ctl
output file name=/u01/oracle/DB11G/fast_recovery_area/orcl/control02.ctl
Finished restore at 19-FEB-2016 14:41:44

In above case, we used below RMAN command because in our database the controlfile autobackup is manualy configured to store in non-default location

RMAN> set controlfile autobackup format for device type disk to ‘/backups/orcl/controlfile_orcl_%F';

if you have not done any such RMAN manual configuration then for your database the control file autobackup will go to default location: $ORACLE_HOME/dbs and you don’t need to use above command then.

Also,  In the above case it was assumed that the autobackup of control file was enabled

If autobackup of control file was not enabled then we will have to use below command instead to provide complete path of control file backup.

RMAN> restore controlfile from ‘/backups/orcl/controlfile_orcl_c-1279650270-20160219-05′;

Also in this scenario, you will not need be required to set the DBID because you’re pointing the RMAN at a specific backup file which contians DBID details.

NOTE: As we have restored a control file from a backup, we are required to perform media recovery on entire database and then open it with the open resetlogs command. This is true although we didn’t restore any datafiles (because the control file’s SCN is no longer synchronized with the SCNs in the datafiles and online redo log files).

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

RMAN> recover database;

Starting recover at 19-FEB-2016 14:28:17

….
..
using channel ORA_DISK_1

starting media recovery

..
.
media recovery complete, elapsed time: 00:00:01
Finished recover at 19-FEB-2016 14:28:25

RMAN> alter database open resetlogs;

database opened

CASE 4: LOST ONLY ONE CONTROL FILE AND YOU HAD MULTIPLEXED CONTROL FILE

Before Oracle can start up normally, it must be able to locate and open each of the control files identified by the control_files initialization parameter. The control files are identical copies of each other. If a multiplexed control file becomes damaged, you can either modify the control_files initialization parameter to match the locations of the remaining good control files or copy a good control file to the location of the damaged or missing control file. If you have one good copy of a control file, you can use that to replace the damaged control files. This minimizes the need to restore your control file from a backup.

Say our database was down and when we tried to start it we got the below error:

SYS@orcl > startup
ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size 2260088 bytes
Variable Size 671089544 bytes
Database Buffers 390070272 bytes
Redo Buffers 5517312 bytes
ORA-00205: error in identifying control file, check alert log for more info

and below will be the error that you will see in the  alert log at this stage>

Wed Feb 19 12:07:58 2016

ALTER DATABASE MOUNT
ORA-00210: cannot open the specified control file
ORA-00202: control file: ‘/u01/oracle/DB11G/oradata/orcl/control01.ctl’
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory

It means oracle was unable to access one of the control file.

OR

Say our database was running and somehow we lost one of the control file.  The database will start throwing errors like below:

SYS@orcl > select name from v$database

*
ERROR at line 1:
ORA-00210: cannot open the specified control file
ORA-00202: control file: ‘/u01/oracle/DB11G/oradata/orcl/control01.ctl’
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

Now shutdown immediate won’t work and you will have to resort to shutdown abort to shutdown the database instance:

SYS@orcl > shutdown immediate;

ORA-00210: cannot open the specified control file
ORA-00202: control file: ‘/u01/oracle/DB11G/oradata/orcl/control01.ctl’
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

SYS@orcl > shutdown abort;

ORACLE instance shut down.

Now either you copy the good control file that we have available to the missing control file location or modify the control_files parameter in the initialization file or spfile to remove the lost control file information from spfile/pfile.

Method 1: Copy the good control file that we have available to the missing control file location

$ cp /u01/oracle/DB11G/fast_recovery_area/orcl/control02.ctl /u01/oracle/DB11G/oradata/orcl/control01.ctl

STARTUP the database normally now

$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed Feb 19 12:05:17 2016

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

Connected to an idle instance.

SYS@orcl > startup
ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size 2260088 bytes
Variable Size 671089544 bytes
Database Buffers 390070272 bytes
Redo Buffers 5517312 bytes
Database mounted.
Database opened.

Method 2: modify the control_files parameter in the initialization file or spfile

Just starting database in nomount mode is enough to  modify the control_files parameter in the initialization file or spfile

SQL> startup nomount;

SQL> alter system set control_files=’/u01/oracle/DB11G/fast_recovery_area/orcl/control02.ctl’ scope=spfile;

SYS@orcl > alter system set control_files=’/u01/oracle/DB11G/fast_recovery_area/orcl/control02.ctl’ scope=spfile;

System altered.

SYS@orcl > shutdown immediate;

ORA-01507: database not mounted
ORACLE instance shut down.

SYS@orcl > startup

ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size 2260088 bytes
Variable Size 671089544 bytes
Database Buffers 390070272 bytes
Redo Buffers 5517312 bytes
Database mounted.
Database opened.

SYS@orcl > show parameter control_files

NAME TYPE VALUE
———————————— ———– ——————————
control_files string /u01/oracle/DB11G/fast_recovery_area/orcl/control02.ctl

So we now have only one control file in our database, which is not recommended. You should multiplex this control file as soon as possible to save your self form any possible disaster.

CASE 5: RECREATING THE CONTROL FILES (FOR DATABASE CLONING)

Here we are not using RMAN for control file restore. You are cloning your database to another database and you will be using a new database name for it. This means we can not use the same binary control file and we need to recreate it.

You can use the output of the following command from source database to re-create your control file:

SQL> alter database backup controlfile to trace;

The previous command generates a trace file that is placed in your user dump directory

There are two SQL statements in the trace file: one with noresetlogs and another with resetlogs. You’ll have to edit the trace file so that it contains only the appropriate SQL statement that you require.

For our database cloning we will use the SQL statement with RESETLOG option

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE “ORCL” RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 4 (
‘/u01/oracle/DB11G/oradata/ORCL/onlinelog/o1_mf_4_9hl96kyw_.log’,
‘/u01/oracle/DB11G/fast_recovery_area/ORCL/onlinelog/o1_mf_4_9hl96l67_.log’
) SIZE 100M BLOCKSIZE 512,
GROUP 5 (
‘/u01/oracle/DB11G/oradata/ORCL/onlinelog/o1_mf_5_9hl934pt_.log’,
‘/u01/oracle/DB11G/fast_recovery_area/ORCL/onlinelog/o1_mf_5_9hl934xp_.log’
) SIZE 100M BLOCKSIZE 512,
GROUP 6 (
‘/u01/oracle/DB11G/oradata/ORCL/onlinelog/o1_mf_6_9hl972k3_.log’,
‘/u01/oracle/DB11G/fast_recovery_area/ORCL/onlinelog/o1_mf_6_9hl972tr_.log’
) SIZE 100M BLOCKSIZE 512
— STANDBY LOGFILE
DATAFILE
‘/u01/oracle/DB11G/oradata/orcl/system01.dbf’,
‘/u01/oracle/DB11G/oradata/orcl/sysaux01.dbf’,
‘/u01/oracle/DB11G/oradata/orcl/undotbs01.dbf’,
‘/u01/oracle/DB11G/oradata/orcl/users01.dbf’,
‘/u01/oracle/DB11G/oradata/orcl/example01.dbf’
CHARACTER SET AL32UTF8
;

After editing the control file trace file as per requirement, move it to target server and execute it as sysdba

You need to:

> change ‘REUSE’ to ‘SET’
> change database name ‘ORCL’ to the target database name
> change location of logfiles and datafiles.

SQL> connect / as sysdba

SQL> shutdown immediate;

SQL> @control_file_trace.trc

You should now have control files re-created in every location identified by your control_files initialization parameter in your target database and you can go ahead with the database recovery steps..