How to use the purge command in adrci

 

How to use the purge command in adrci

Active Diagnostic Repository (ADR), which serves as a common repository for all log files, tracefiles, incidents etc, that the database produces.

ADR has a "short" and a "long" purging policy. The default values are:

• 720 hours (30 days) for SHORTP_POLICY
• 8760 hours (356 days) for LONGP_POLICY

Each policy controls different types of content:

These files are controlled by the value of LONGP_POLICY:

ALERT
INCIDENT
SWEEP
STAGE
HM

These files are controlled by the value of SHORTP_POLICY:

TRACE
CDUMP
UTSCDMP
IPS

Sometimes it may be necessary to manually purge the ADR. The following example will delete all trace files older than 48 hours:

adrci> purge -age 48 -type trace

Other examples (2160 hrs = 90 days):

purge -age 2160 -type alert
purge -age 2160 -type incident
purge -age 2160 -type cdump
purge -age 2160 -type stage
purge -age 2160 -type sweep
purge -age 2160 -type hm

The policies can be adjusted according to need by using the following commands :

adrci> show control

ADR Home = /u01/oracle/diag/rdbms/proddb01/proddb01:

*************************************************************************
ADRID                SHORTP_POLICY        LONGP_POLICY
-------------------- -------------------- --------------------
597879840            720                  8760

Set a new policy (2 and 3 days, respectively):

set control (SHORTP_POLICY = 48)
set control (LONGP_POLICY = 72)

An example from one of my databaes:

adrci> show incident -mode basic

ADR Home = /u01/oracle/diag/rdbms/proddb01/proddb01:
*************************************************************************

INCIDENT_ID          PROBLEM_KEY                                                 CREATE_TIME
-------------------- ----------------------------------------------------------- ----------------------------------------
30858                ORA 603                                                     2014-03-19 11:26:41.287000 +01:00
30857                ORA 600 [kqlidchg0]                                         2014-03-19 11:26:38.226000 +01:00
27284                ORA 603                                                     2014-03-18 14:42:28.228000 +01:00
27283                ORA 600 [kqlidchg0]                                         2014-03-18 14:42:23.706000 +01:00
.
.
.
17044                ORA 603                                                     2013-09-26 15:37:30.870000 +02:00
17043                ORA 600 [kqlidchg0]                                         2013-09-26 15:37:27.255000 +02:00
17042                ORA 603                                                     2013-09-26 15:35:45.918000 +02:00
17041                ORA 600 [kqlidchg0]                                         2013-09-26 15:35:42.239000 +02:00

45 rows fetched

Remove incidents that are older than 90 days:

adrci> purge -age 2160 -type incident

Another check of the incident list shows that the number has been reduced:

adrci> show incident -mode basic

ADR Home = /u01/oracle/diag/rdbms/sergat/sergat:
*************************************************************************

INCIDENT_ID          PROBLEM_KEY                                                 CREATE_TIME
-------------------- ----------------------------------------------------------- ----------------------------------------
30858                ORA 603                                                     2014-03-19 11:26:41.287000 +01:00
30857                ORA 600 [kqlidchg0]                                         2014-03-19 11:26:38.226000 +01:00
27284                ORA 603                                                     2014-03-18 14:42:28.228000 +01:00
27283                ORA 600 [kqlidchg0]                                         2014-03-18 14:42:23.706000 +01:00
.
.
.
24 rows fetched

 

Enable and disable data guard broker

https://logic.edchen.org/how-to-stop-data-guard-services/

1. Login to primary db server

$ dgmgrl sys/Manager_1@sslcdb

DGMGRL> show configuration

2. Check the apply and transport status:

Disable Data Guard with Broker

DGMGRL> show database sslcdb

Database - SSLCDBDR

Role:               PRIMARY

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

@stanby

$ dgmgrl sys/Manager_1@sslcdbdr

DGMGRL> show database sslcdbdr

Database - SSLCDBDR

  Role:               PHYSICAL STANDBY

3. DGMGRL Stop Transport

disable transport service of the primary database.

dgmgrl sys/Manager_1@sslcdb

DGMGRL> EDIT DATABASE sslcdbdr SET STATE='TRANSPORT-OFF';

Succeeded.

4. DGMGRL Stop Apply

dgmgrl sys/Manager_1@sslcdbdr

DGMGRL> edit database SSLCDBDR set state='APPLY-OFF';

Succeeded.

5. Disable DG Configuration

DGMGRL> DISABLE CONFIGURATION;

Disabled.

Enable Data Guard with Broker

6. Disable DG Configuration

DGMGRL> ENABLE CONFIGURATION;

Enableabled.

7. DGMGRL Start Transport

Enableable transport service of the primary database.

dgmgrl sys/Manager_1@sslcdb

DGMGRL> EDIT DATABASE sslcdbdr SET STATE='TRANSPORT-ON';

Succeeded.

8. DGMGRL Start Apply

dgmgrl sys/Manager_1@sslcdbdr

DGMGRL> edit database SSLCDBDR set state='APPLY-ON';

Succeeded.

SQL> show parameter dg_broker_start

NAME                                 TYPE        VALUE

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

dg_broker_start                      boolean     TRUE

SQL> select name, value from v$parameter where name = 'dg_broker_start';

NAME                       VALUE

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

dg_broker_start            TRUE

 SQL query to get DB info

spool dbinfo.html
set markup html on
select * from v$version;
select * from v$instance;
select * from v$parameter;
select * from v$resource_limit;
select * from dba_users;
select * from dba_registry;
SET linesize 235
col Parameter FOR a50
col SESSION FOR a28
col Instance FOR a55
col S FOR a1
col I FOR a1
col D FOR a1
col Description FOR a90

SELECT
a.ksppinm "Parameter",
decode(p.isses_modifiable,'FALSE',NULL,NULL,NULL,b.ksppstvl) "Session",
c.ksppstvl "Instance",
decode(p.isses_modifiable,'FALSE','F','TRUE','T') "S",
decode(p.issys_modifiable,'FALSE','F','TRUE','T','IMMEDIATE','I','DEFERRED','D') "I",
decode(p.isdefault,'FALSE','F','TRUE','T') "D",
a.ksppdesc "Description"
FROM x$ksppi a, x$ksppcv b, x$ksppsv c, v$parameter p
WHERE a.indx = b.indx AND a.indx = c.indx
AND p.name(+) = a.ksppinm
ORDER BY a.ksppinm;

set markup html off
spool off

 

ORA-10873: file 1 needs to be either taken out of backup mode or media recovered

Scenario :

Database went down due to power fluctuation and during that time hot backup was running.So once we started the database we face the following issue.

Error :

ORA-10873: file 1 needs to be either taken out of backup mode or media recovered

ORA-01110: data file 1: ‘D:\ORADB\RECTCDB\system01.dbf’

Cause :

Database is in backup mode while it went down.

Solution :

Step 1 : Sqlplus  ‘/as sysdba’

Step 2 : startup mount

Step 3 : Check backup file using below command.

select * from V$BACKUP

Step 4 : The following command can be used to take all of the data files out of hot backup mode:

SQL> ALTER DATABASE END BACKUP;

Step 5 :  SQL> Alter database open;

 

Enable and Disable the dataguard steps

Disable Data Guard

On Standby Database:
1. Cancel the Recovery job with following command

alter database recover managed standby database cancel;

2. Stop the destination

alter system set log_archive_dest_state_2=DEFER;


On Primary Database:
1. Stop the standby destination for transport archives.

ALTER SYSTEM SET log_archive_dest_state_2='DEFER' SCOPE=BOTH;


Enable the dataguard:
On Primary DB:
1. Enable the archive log transport:

ALTER SYSTEM SET log_archive_dest_state_2='ENABLE' SCOPE=BOTH;

On Standby DB:
1. Start the recovery process with following command:

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE disconnect from session;

2. Enable the log archive for switchover condition needed.

ALTER SYSTEM SET log_archive_dest_state_2='ENABLE' SCOPE=BOTH;

 
Shutdown and Start the Primary / Standby database dataguard.

https://dbaclass.com/article/remove-dataguard-configuration-from-primary-database/

 

Remove Dataguard Configuration From Primary Database:

    If you wish to remove the dataguard/standby setup from primary database i.e to make the primary database as standalone database without any standby database, then follow below steps.

1.Put primary database on maximum performance mode:

SQL> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE;

2. Remove below parameters from spfile of primary database:

FAL_SERVER
FAL_CLIENT
LOG_ARCHIVE_CONFIG
DB_FILE_NAME_CONVERT
LOG_FILE_NAME_CONVERT
LOG_ARCHIVE_DEST_n – pointing to standby database like _2 or _3
LOG_ARCHIVE_DEST_STATE_n 
STANDBY_ARCHIVE_DEST
STANDBY_FILE_MANAGEMENT

alter system reset LOG_ARCHIVE_CONFIG scope=spfile;
alter system reset DB_FILE_NAME_CONVERT scope=spfile;
alter system reset LOG_FILE_NAME_CONVERT scope=spfile;
alter system reset STANDBY_FILE_MANAGEMENT scope=spfile;
alter system reset FAL_SERVER scope=spfile;
alter system reset FAL_CLIENT scope=spfile;
alter system reset LOG_ARCHIVE_DEST_STATE_2 scope=spfile;
alter system reset LOG_ARCHIVE_DEST_2 scope=spfile; --- destination pointing to standby
alter system reset STANDBY_ARCHIVE_DEST scope=spfile;

3. Drop all standby log files from primary:

SELECT GROUP# FROM V$STANDBY_LOG;

alter database drop standby logfile group 6;
alter database drop standby logfile group 7;
alter database drop standby logfile group 8;
alter database drop standby logfile group 9;

4. Restart the database to reflect the parameters

shutdown immediate;
startup

Now the standby database is completely separated from primary.
If you want to use the standby database as a standalone database, then follow below steps on standby

SQL> ALTER DATABASE ACTIVATE PHYSICAL STANDBY DATABASE;
SQL> SHUTDOWN IMMEDIATE;
SQL>STARTUP

You can change dbid using NID utility( as dbid would be same as primary)

 

ORA-02396: exceeded maximum idle time, please connect again.

Resolution

To resolve the above error there are three approaches:

1.Set the idle time to unlimited on the Oracle database. 
alter profile <profile_name> limit idle_time UNLIMITED;

before

After 

 

DBMS_SCHEDULER. 

Default time zone change.

DBMS_SCHEDULER or DBMS_JOB And DST / Timezones Explained (Doc ID 467722.1)

SQL> ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT ='DD/MM/YYYY HH24:MI:SS TZR TZD';

Session altered.

SQL> select dbms_scheduler.stime from dual;

STIME

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

06/10/2021 07:09:12 CST6CDT CDT

SELECT *  FROM   dba_scheduler_global_attribute  WHERE  attribute_name = 'DEFAULT_TIMEZONE';  

SQL> exec DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE('default_timezone','Asia/Colombo');

 

ADRCI : DIA-48216: Field Len Exceeds Max Field Length

Detect place :
1. Find path:
SELECT VALUE FROM V$DIAG_INFO WHERE NAME LIKE 'Diag Trace%';

2. Find problematic line:
sed ‘s/\(.*\)<msg.*\(<msg.*\)/\1\2/' < log.xml | diff -w log.xml –

3. Remove it :

 RMAN Restore Preview

$ rman target /

RMAN> list backup;

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

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

15526   Full    6.77M      DISK        00:00:01     15-SEP-21

        BP Key: 15615   Status: AVAILABLE  Compressed: YES  Tag: TAG20210915T234616

        Piece Name: +RECO/SSLCDB/BACKUP_FILES/c-4188050246-20210915-05

  SPFILE Included: Modification time: 15-SEP-21

  SPFILE db_unique_name: SSLCDB

  Control File Included: Ckp SCN: 67374431842   Ckp time: 15-SEP-21

RMAN>  

run{

set until scn 67374431842;

restore database preview;

}

List of Archived Log Copies for database with db_unique_name SSLCDB

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

Key     Thrd Seq     S Low Time

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

92560   1    47166   A 15-SEP-21

        Name: +RECO/SSLCDB/ARCHIVELOG/2021_09_15/thread_1_seq_47166.3007.1083368767

92562   1    47167   A 15-SEP-21

        Name: +RECO/SSLCDB/ARCHIVELOG/2021_09_16/thread_1_seq_47167.3005.1083369733

recovery will be done up to SCN 67374431842

Media recovery start SCN is 67374208371

Recovery must be done beyond SCN 67374426191 to clear datafile fuzziness

Finished restore at 16-SEP-21

RMAN>

Restore Preview Reports RMAN-05119 and Recover Database Reports RMAN-06054 ( Multi-tenant ) (Doc ID 2233445.1)

Error seems to be a wrong notification which misleads the recovery process.  Ignore the error (or apply the patch 20315311) and proceed with the 'alter database open' after the recover database.   

Sample output of the errors 

Assuming you have valid backup of database and archivelogs, Oracle recommends validating the integrity of the backup by running 'restore database preview'.

Due to a code defect, 'restore database preview' command returns the error RMAN-05119 although valid backups exist. 

 

nls length semantics must be set to char

CHAR length semantics are required for the AL32UTF8 character set.

https://www.ibm.com/support/pages/char-length-semantics-are-required-al32utf8-character-set

The following command can be executed when logged in as SYSTEM to make this change:

alter system set NLS_LENGTH_SEMANTICS = CHAR scope = both;

 Renaming or Moving Oracle Files

https://oracle-base.com/articles/misc/renaming-or-moving-oracle-files

SQL> SELECT member FROM v$logfile;

MEMBER
--------------------------------------------------------------------------------

/u02/oradata/PRODCDB/redo010.log
/u01/oradata/PRODCDB/redo011.log
/u02/oradata/PRODCDB/redo020.log
/u01/oradata/PRODCDB/redo021.log
/u02/oradata/PRODCDB/redo030.log
/u01/oradata/PRODCDB/redo031.log


SQL> shutdown immediate

HOST mv /u01/oradata/PRODCDB/redo011.log /u02/oradata/PRODCDB/redo011.log

HOST mv /u01/oradata/PRODCDB/redo021.log /u02/oradata/PRODCDB/redo021.log

HOST mv /u01/oradata/PRODCDB/redo031.log /u02/oradata/PRODCDB/redo031.log


SQL> STARTUP MOUNT

ALTER DATABASE RENAME FILE '/u01/oradata/PRODCDB/redo011.log' TO '/u02/oradata/PRODCDB/redo011.log';

ALTER DATABASE RENAME FILE '/u01/oradata/PRODCDB/redo021.log' TO '/u02/oradata/PRODCDB/redo021.log';


ALTER DATABASE RENAME FILE '/u01/oradata/PRODCDB/redo031.log' TO '/u02/oradata/PRODCDB/redo031.log';


SQL> ALTER DATABASE OPEN;

SQL> SELECT member FROM v$logfile;

MEMBER
--------------------------------------------------------------------------------

/u02/oradata/PRODCDB/redo010.log
/u02/oradata/PRODCDB/redo011.log
/u02/oradata/PRODCDB/redo020.log
/u02/oradata/PRODCDB/redo021.log
/u02/oradata/PRODCDB/redo030.log
/u02/oradata/PRODCDB/redo031.log

 

Startup Fails With ORA-01012: Not Logged On

Problem Description
Users cant able to login to database server. Though it allow sysdba to login, it does not allow to run any query. Sometimes it will show database is connected to an idle instance. But when we startup database it shall throw error ORA-01081: cannot start already-running .

[oracle@mezon01 ~]$ sqlplus sys/System_2018@IIRDB

SQL*Plus: Release 11.2.0.4.0 Production on Thu May 24 08:03:07 2018

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

ERROR:
ORA-12520: TNS:listener could not find available handler for requested type of
server

 

Enter user-name: sys
Enter password:
ERROR:
ORA-00020: maximum number of processes (1500) exceeded

 

Enter user-name:
ERROR:
ORA-01017: invalid username/password; logon denied

 

SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus
[oracle@mezon01 ~]$ sqlplus -prelim “/as sysdba”

SQL*Plus: Release 11.2.0.4.0 Production on Thu May 24 08:04:06 2018

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

SQL> show parameter sga_t;
ORA-01012: not logged on
Process ID: 0
Session ID: 0 Serial number: 0

 

SQL> startup;
ORA-01012: not logged on
SQL> shutdown immediate;
ORA-01012: not logged on
SQL> show parameter sga_max;
ORA-01012: not logged on
Process ID: 0
Session ID: 0 Serial number: 0

SQL> EXİT
SP2-0042: unknown command “EXİT” – rest of line ignored.
SQL> exit
Disconnected from ORACLE
[oracle@mezon01 ~]$ ps -ef | grep IIRDB
oracle 6096 1 0 08:05 ? 00:00:00 ora_pmon_IIRDB
oracle 6098 1 0 08:05 ? 00:00:00 ora_psp0_IIRDB
oracle 6136 1 1 08:05 ? 00:00:01 ora_vktm_IIRDB
oracle 6140 1 0 08:05 ? 00:00:00 ora_gen0_IIRDB
oracle 6142 1 0 08:05 ? 00:00:00 ora_diag_IIRDB
oracle 6144 1 0 08:05 ? 00:00:00 ora_dbrm_IIRDB
oracle 6146 1 0 08:05 ? 00:00:00 ora_ping_IIRDB
oracle 6148 1 0 08:05 ? 00:00:00 ora_acms_IIRDB
oracle 6150 1 0 08:05 ? 00:00:00 ora_dia0_IIRDB
oracle 6152 1 0 08:05 ? 00:00:00 ora_lmon_IIRDB
oracle 6162 1 0 08:05 ? 00:00:00 ora_lmd0_IIRDB
oracle 6164 1 0 08:05 ? 00:00:00 ora_rms0_IIRDB
oracle 6166 1 0 08:05 ? 00:00:00 ora_lmhb_IIRDB
oracle 6172 1 4 08:05 ? 00:00:04 ora_mman_IIRDB
oracle 6174 1 0 08:05 ? 00:00:00 ora_dbw0_IIRDB
oracle 6176 1 0 08:05 ? 00:00:00 ora_dbw1_IIRDB
oracle 6178 1 0 08:05 ? 00:00:00 ora_dbw2_IIRDB
oracle 6180 1 0 08:05 ? 00:00:00 ora_dbw3_IIRDB
oracle 6182 1 0 08:05 ? 00:00:00 ora_lgwr_IIRDB
oracle 6184 1 0 08:05 ? 00:00:00 ora_ckpt_IIRDB
oracle 6186 1 0 08:05 ? 00:00:00 ora_smon_IIRDB
oracle 6188 1 0 08:05 ? 00:00:00 ora_reco_IIRDB
oracle 6190 1 0 08:05 ? 00:00:00 ora_rbal_IIRDB
oracle 6192 1 0 08:05 ? 00:00:00 ora_asmb_IIRDB
oracle 6194 1 0 08:05 ? 00:00:00 ora_mmon_IIRDB
oracle 6198 1 0 08:05 ? 00:00:00 ora_mmnl_IIRDB
oracle 6200 1 0 08:05 ? 00:00:00 ora_d000_IIRDB
oracle 6202 1 0 08:05 ? 00:00:00 ora_s000_IIRDB
oracle 6204 1 0 08:05 ? 00:00:00 ora_mark_IIRDB
oracle 6206 1 0 08:05 ? 00:00:00 ora_ocf0_IIRDB
oracle 6325 1 0 08:05 ? 00:00:00 ora_o000_IIRDB
oracle 10089 28710 0 08:07 pts/1 00:00:00 grep IIRDB
oracle 20902 25965 0 May23 ? 00:04:20 /oracle/product/11.2.0.4/db/jdk/bin/java -server -Xmx384M -XX:MaxPermSize=400M -XX:MinHeapFreeRatio=20 -XX:MaxHeapFreeRatio=40 -DORACLE_HOME=/oracle/product/11.2.0.4/db -Doracle.home=/oracle/product/11.2.0.4/db/oc4j -Doracle.oc4j.localhome=/oracle/product/11.2.0.4/db/mezon01_IIRDB/sysman -DEMSTATE=/oracle/product/11.2.0.4/db/mezon01_IIRDB -Doracle.j2ee.dont.use.memory.archive=true -Djava.protocol.handler.pkgs=HTTPClient -Doracle.security.jazn.config=/oracle/product/11.2.0.4/db/oc4j/j2ee/OC4J_DBConsole_mezon01_IIRDB/config/jazn.xml -Djava.security.policy=/oracle/product/11.2.0.4/db/oc4j/j2ee/OC4J_DBConsole_mezon01_IIRDB/config/java2.policy -Djavax.net.ssl.KeyStore=/oracle/product/11.2.0.4/db/sysman/config/OCMTrustedCerts.txt-Djava.security.properties=/oracle/product/11.2.0.4/db/oc4j/j2ee/home/config/jazn.security.props -DEMDROOT=/oracle/product/11.2.0.4/db/mezon01_IIRDB -Dsysman.md5password=true -Drepapi.oracle.home=/oracle/product/11.2.0.4/db -Ddisable.checkForUpdate=true -Doracle.sysman.ccr.ocmSDK.websvc.keystore=/oracle/product/11.2.0.4/db/jlib/emocmclnt.ks -Dice.pilots.html4.ignoreNonGenericFonts=true -Djava.awt.headless=true -jar /oracle/product/11.2.0.4/db/oc4j/j2ee/home/oc4j.jar -config /oracle/product/11.2.0.4/db/oc4j/j2ee/OC4J_DBConsole_mezon01_IIRDB/config/server.xml
oracle 25965 1 0 May23 ? 00:00:12 /oracle/product/11.2.0.4/db/perl/bin/perl /oracle/product/11.2.0.4/db/bin/emwd.pl dbconsole /oracle/product/11.2.0.4/db/mezon01_IIRDB/sysman/log/emdb.nohup

 

————————————————————————————————————————————-

[oracle@mezon01 ~]$ sysresv
IPC Resources for ORACLE_SID “IIRDB” :
Shared Memory:
ID KEY
100270098 0x00000000
100302867 0x00000000
100335643 0x14203f64
Semaphores:
ID KEY
136445992 0xf1e96e54
136478764 0xf1e96e55
136511533 0xf1e96e56
136544302 0xf1e96e57
136577071 0xf1e96e58
136609840 0xf1e96e59
136642609 0xf1e96e5a
136675378 0xf1e96e5b
136708147 0xf1e96e5c
Oracle Instance alive for sid “IIRDB”
[oracle@mezon01 ~]$ ipcrm -m 100270098
[oracle@mezon01 ~]$ ipcrm -m 100302867
[oracle@mezon01 ~]$ ipcrm -m 100335643
[oracle@mezon01 ~]$
[oracle@mezon01 ~]$
Changes
Oracle has been forcefully shutdown at OS level or crashed.

CAUSE
An orphaned shared memory segment belonging to the ORACLE_SID still exists from a previous instance startup.

The command
ps -ef | grep $ORACLE_SID

shows no processes but with ORACLE_SID set the Oracle ‘sysresv’ utility shows a shared memory segment for a non-existing instance, e.g.

$ sysresv

IPC Resources for ORACLE_SID “IIRDB” :

Shared Memory:
ID              KEY
100270098 0x00000000
100302867 0x00000000
100335643 0x14203f64

Semaphores:
ID              KEY
No semaphore resources used
Oracle Instance not alive for sid “IIRDB”

Solution
On OS level, remove the orphaned shared memory segment using:

ipcrm -m <problem shared memory id>

ipcrm -m 100270098
ipcrm -m 100302867
ipcrm -m 100335643

$sqlplus ‘/as sysdba’

Connected to an idle instance.

SQL> startup Oracle instance started
Total System Global Area   10689474560 bytes
Fixed Size                     2237776 bytes
Variable Size               6375344816 bytes
Database Buffers            4294967296 bytes
Redo Buffers                  16924672 bytes
Oracle Instance Started.
Oracle Database Opened.

 ORA-01102: cannot mount database in EXCLUSIVE mode (During database startup)

SQL> startup

ORACLE instance started.

Total System Global Area 285212672 bytes

Fixed Size 1218992 bytes

Variable Size 79693392 bytes

Database Buffers 201326592 bytes

Redo Buffers 2973696 bytes

ORA-01102: cannot mount database in EXCLUSIVE mode

Cause: There are some client shadow processes hanging. Although the lk<SID> file is deleted the hanging processes still have a lock on the open  file handle. This prevents the database to startup although a new lk<SID> file can be created successfully.

 

An oracle process (background or shadow process) that exists while the instance is not started (crashed or not cleanly stopped) can have a lock on a file while this file is actually removed from the system. This is because on UNIX there is still a lock on the open file handle.

 

Solution:

Verify if there are existing oracle processes for the database:

 

[oracle@servername ~]$  ps -ef | grep $ORACLE_SID

oracle    1525     1  0 10:13 ?        00:00:03 ora_j000_GRIDDB3

oracle    1527     1  0 10:13 ?        00:00:01 ora_j001_GRIDDB3

oracle    4198     1  0 10:27 ?        00:00:00 oracleGRIDDB3 (LOCAL=NO)

oracle    7479     1  0 May30 ?        00:00:00 oracleGRIDDB3 (LOCAL=NO)

.

.

Kill the hanging processes to release the lock on the file handle:

 

[oracle@servername ~]$ kill -9 `ps -ef | grep $ORACLE_SID | grep -v grep | awk '{print $2}'`

eg: kill -9 `ps -ef | grep testcdb | grep -v grep | awk '{print $2}'`

 

[oracle@sservername ~]$  ps -ef | grep $ORACLE_SID

oracle   11990 11927  0 10:50 pts/1    00:00:00 grep GRIDDB3