Hello Friends, 

After Many try, I was finally able to fix the issues of ORA-01109: database not open.

 

 

SQL> ALTER PLUGGABLE DATABASE PROD OPEN;
ALTER PLUGGABLE DATABASE PROD OPEN
*
ERROR at line 1:
ORA-01109: database not open


SQL> ALTER PLUGGABLE DATABASE PROD SAVE STATE;
ALTER PLUGGABLE DATABASE PROD SAVE STATE
*
ERROR at line 1:
ORA-01109: database not open


SQL> SELECT name, open_mode FROM v$pdbs;

NAME                           OPEN_MODE
------------------------------ ----------
PDB$SEED                       MOUNTED
PROD                           MOUNTED

SQL> select status from v$instance;

STATUS
------------
MOUNTED

SQL> ALTER DATABASE OPEN
  2  ;
ALTER DATABASE OPEN
*
ERROR at line 1:
ORA-00742: Log read detects lost write in thread 1 sequence 4052 block 13510
ORA-00312: online log 2 thread 1: 'D:\APP\SACHINSINGH\ORADATA\PWR\REDO02.LOG'


SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC
---------- ---------- ---------- ---------- ---------- ---------- ---
STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME     CON_ID
---------------- ------------- --------- ------------ --------- ----------
         1          1       4051   52428800        512          1 NO
INACTIVE             264955227 20-JUL-20    265056263 20-JUL-20          0

         3          1       4050   52428800        512          1 NO
INACTIVE             264847618 20-JUL-20    264955227 20-JUL-20          0

         2          1       4052   52428800        512          1 NO
CURRENT              265056263 20-JUL-20   2.8147E+14                    0


SQL>  recover database until cancel;
ORA-00279: change 265056264 generated at 07/20/2020 19:31:45 needed for thread
1
ORA-00289: suggestion :
D:\APP\SACHINSINGH\PRODUCT\12.1.0\DBHOME_1\RDBMS\ARC0000004052_0932425956.0001
ORA-00280: change 265056264 for thread 1 is in sequence #4052


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
D:\APP\SACHINSINGH\product\12.1.0\dbhome_1\NETWORK\log
ORA-00308: cannot open archived log
'D:\APP\SACHINSINGH\product\12.1.0\dbhome_1\NETWORK\log'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 5) Access is denied.

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: 'D:\APP\SACHINSINGH\ORADATA\PWR\SYSTEM01.DBF'


ORA-01112: media recovery not started


SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: 'D:\APP\SACHINSINGH\ORADATA\PWR\SYSTEM01.DBF'



SQL> select * from v$Logfile;

    GROUP# STATUS  TYPE
---------- ------- -------
MEMBER
--------------------------------------------------------------------------------
IS_     CON_ID
--- ----------
         3         ONLINE
D:\APP\SACHINSINGH\ORADATA\PWR\REDO03.LOG
NO           0

         2         ONLINE
D:\APP\SACHINSINGH\ORADATA\PWR\REDO02.LOG
NO           0

    GROUP# STATUS  TYPE
---------- ------- -------
MEMBER
--------------------------------------------------------------------------------
IS_     CON_ID
--- ----------

         1         ONLINE
D:\APP\SACHINSINGH\ORADATA\PWR\REDO01.LOG
NO           0


SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC
---------- ---------- ---------- ---------- ---------- ---------- ---
STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME     CON_ID
---------------- ------------- --------- ------------ --------- ----------
         1          1       4051   52428800        512          1 NO
INACTIVE             264955227 20-JUL-20    265056263 20-JUL-20          0

         3          1       4050   52428800        512          1 NO
INACTIVE             264847618 20-JUL-20    264955227 20-JUL-20          0

         2          1       4052   52428800        512          1 NO
CURRENT              265056263 20-JUL-20   2.8147E+14                    0


SQL> alter database clear unarchived logfile group 2;
alter database clear unarchived logfile group 2
*
ERROR at line 1:
ORA-01624: log 2 needed for crash recovery of instance pwr (thread 1)
ORA-00312: online log 2 thread 1: 'D:\APP\SACHINSINGH\ORADATA\PWR\REDO02.LOG'


SQL> recover database until cancel;
ORA-00279: change 265056264 generated at 07/20/2020 19:31:45 needed for thread
1
ORA-00289: suggestion :
D:\APP\SACHINSINGH\PRODUCT\12.1.0\DBHOME_1\RDBMS\ARC0000004052_0932425956.0001
ORA-00280: change 265056264 for thread 1 is in sequence #4052


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
D:\APP\SACHINSINGH\ORADATA\PWR\REDO02.LOG
Log applied.
Media recovery complete.
SQL> alter database open resetlogs;

Database altered.

SQL> select status from v$instance;

STATUS
------------
OPEN

SQL> ALTER PLUGGABLE DATABASE ALL OPEN;

Pluggable database altered.

SQL> SELECT name, open_mode FROM v$pdbs;

NAME                           OPEN_MODE
------------------------------ ----------
PDB$SEED                       READ ONLY
PROD                           READ WRITE

SQL>