DBCA fails with errors: [FATAL] [DBT-50000] Unable to check for available memory

DBCA fails with errors: [FATAL] [DBT-50000] Unable to check for available memory

Recently I have installed Oracle 19c Database binary to windows machine. And after that I started DBCA to create database.

In “Configuration options” steps I got memory error. i.e. [DBT-50000] Unable to check for available memory

Earlier I selected 8GB for my SGA but after this error I had selected 2GB only. But still was facing this issue.

Here is the screenshot for the same.

After searching oracle docs for known issue, I found, it’s a bug. One can find details about this bug on following oracle notes.

DBCA fails with errors: [FATAL] [DBT-50000] Unable to check for available memory in “Specify configuration option” (Doc ID 2631718.1)

Workaround for this issue is,

Run dbca with “-J-Doracle.assistants.dbca.validate.ConfigurationParams=false” like a bellow command,

Open CMD (Run as Administrator)

Run database configuration assistant with the following command

dbca -J-Doracle.assistants.dbca.validate.ConfigurationParams=false

Regards,

Mallik

Deinstall 2 node Clusterware Grid Infrastructure Setup

Deinstall 2 node Clusterware Grid Infrastructure Setup:

1. Run the deinstall command from node1:

[oracle@oraclenode1 logs]$ /u01/app/19.0.0.0/grid/deinstall/deinstall

Checking for required files and bootstrapping ...

Please wait ...

Location of logs /tmp/deinstall2021-03-07_04-37-30AM/logs/

############ ORACLE DECONFIG TOOL START ############

######################### DECONFIG CHECK OPERATION START #########################

## [START] Install check configuration ##

Checking for existence of the Oracle home location /u01/app/19.0.0.0/grid

Oracle Home type selected for deinstall is: Oracle Grid Infrastructure for a Cluster

Oracle Base selected for deinstall is: /u01/app/oracle

Checking for existence of central inventory location /u01/app/oraInventory

Checking for existence of the Oracle Grid Infrastructure home /u01/app/19.0.0.0/grid

The following nodes are part of this cluster: oraclenode1,oraclenode2

Active Remote Nodes are oraclenode2

Checking for sufficient temp space availability on node(s) : 'oraclenode1,oraclenode2'

## [END] Install check configuration ##

Traces log file: /tmp/deinstall2021-03-07_04-37-30AM/logs//crsdc_2021-03-07_04-37-44-AM.log

Network Configuration check config START

Network de-configuration trace file location: /tmp/deinstall2021-03-07_04-37-30AM/logs/netdc_check2021-03-07_04-37-45AM.log

Specify all RAC listeners (do not include SCAN listener) that are to be de-configured. Enter .(dot) to deselect all. [LISTENER]:

Network Configuration check config END

Asm Check Configuration START

ASM de-configuration trace file location: /tmp/deinstall2021-03-07_04-37-30AM/logs/asmcadc_check2021-03-07_04-37-45AM.log

Automatic Storage Management (ASM) instance is detected in this Oracle home /u01/app/19.0.0.0/grid.

ASM Diagnostic Destination : /u01/app/oracle

ASM Diskgroups : +DATA

ASM diskstring : /dev/oracleasm/disks/*

Diskgroups will be dropped

De-configuring ASM will drop all the diskgroups and their contents at cleanup time. This will affect all of the databases and ACFS that use this ASM instance(s).

 If you want to retain the existing diskgroups or if any of the information detected is incorrect, you can modify by entering 'y'. Do you  want to modify above information (y|n) [n]:

Database Check Configuration START

Database de-configuration trace file location: /tmp/deinstall2021-03-07_04-37-30AM/logs/databasedc_check2021-03-07_04-37-45AM.log

Oracle Grid Management database was not found in this Grid Infrastructure home

Database Check Configuration END

######################### DECONFIG CHECK OPERATION END #########################

####################### DECONFIG CHECK OPERATION SUMMARY #######################

Oracle Grid Infrastructure Home is: /u01/app/19.0.0.0/grid

The following nodes are part of this cluster: oraclenode1,oraclenode2

Active Remote Nodes are oraclenode2

The cluster node(s) on which the Oracle home deinstallation will be performed are:oraclenode1,oraclenode2

Oracle Home selected for deinstall is: /u01/app/19.0.0.0/grid

Inventory Location where the Oracle home registered is: /u01/app/oraInventory

Following RAC listener(s) will be de-configured: LISTENER

ASM instance will be de-configured from this Oracle home

Oracle Grid Management database was not found in this Grid Infrastructure home

Do you want to continue (y - yes, n - no)? [n]: y

A log of this session will be written to: '/tmp/deinstall2021-03-07_04-37-30AM/logs/deinstall_deconfig2021-03-07_04-37-37-AM.out'

Any error messages from this session will be written to: '/tmp/deinstall2021-03-07_04-37-30AM/logs/deinstall_deconfig2021-03-07_04-37-37-AM.err'

######################## DECONFIG CLEAN OPERATION START ########################

Database de-configuration trace file location: /tmp/deinstall2021-03-07_04-37-30AM/logs/databasedc_clean2021-03-07_04-37-45AM.log

ASM de-configuration trace file location: /tmp/deinstall2021-03-07_04-37-30AM/logs/asmcadc_clean2021-03-07_04-37-45AM.log

ASM Clean Configuration START

ASM Clean Configuration END

Network Configuration clean config START

Network de-configuration trace file location: /tmp/deinstall2021-03-07_04-37-30AM/logs/netdc_clean2021-03-07_04-37-45AM.log

De-configuring RAC listener(s): LISTENER

De-configuring listener: LISTENER

    Stopping listener: LISTENER

    Listener stopped successfully.

    Unregistering listener: LISTENER

    Listener unregistered successfully.

Listener de-configured successfully.

De-configuring Naming Methods configuration file on all nodes...

Naming Methods configuration file de-configured successfully.

De-configuring Local Net Service Names configuration file on all nodes...

Local Net Service Names configuration file de-configured successfully.

De-configuring Directory Usage configuration file on all nodes...

Directory Usage configuration file de-configured successfully.

De-configuring backup files on all nodes...

Backup files de-configured successfully.

The network configuration has been cleaned up successfully.

Network Configuration clean config END

---------------------------------------->

The deconfig command below can be executed in parallel on all the remote nodes. Execute the command on  the local node after the execution completes on all the remote nodes.

Run the following command as the root user or the administrator on node "oraclenode2".

/u01/app/19.0.0.0/grid/crs/install/rootcrs.sh -force  -deconfig -paramfile "/tmp/deinstall2021-03-07_04-37-30AM/response/deinstall_OraGI19Home1.rsp"

Run the following command as the root user or the administrator on node "oraclenode1".

/u01/app/19.0.0.0/grid/crs/install/rootcrs.sh -force  -deconfig -paramfile "/tmp/deinstall2021-03-07_04-37-30AM/response/deinstall_OraGI19Home1.rsp" -lastnode

Press Enter after you finish running the above commands

<----------------------------------------

######################### DECONFIG CLEAN OPERATION END #########################

####################### DECONFIG CLEAN OPERATION SUMMARY #######################

There is no Oracle Grid Management database to de-configure in this Grid Infrastructure home

ASM instance was de-configured successfully from the Oracle home

Following RAC listener(s) were de-configured successfully: LISTENER

Oracle Clusterware is stopped and successfully de-configured on node "oraclenode2"

Oracle Clusterware is stopped and successfully de-configured on node "oraclenode1"

Oracle Clusterware is stopped and de-configured successfully.

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

############# ORACLE DECONFIG TOOL END #############

Using properties file /tmp/deinstall2021-03-07_04-37-30AM/response/deinstall_2021-03-07_04-37-37-AM.rsp

Location of logs /tmp/deinstall2021-03-07_04-37-30AM/logs/

############ ORACLE DEINSTALL TOOL START ############

####################### DEINSTALL CHECK OPERATION SUMMARY #######################

A log of this session will be written to: '/tmp/deinstall2021-03-07_04-37-30AM/logs/deinstall_deconfig2021-03-07_04-37-37-AM.out'

Any error messages from this session will be written to: '/tmp/deinstall2021-03-07_04-37-30AM/logs/deinstall_deconfig2021-03-07_04-37-37-AM.err'

######################## DEINSTALL CLEAN OPERATION START ########################

## [START] Preparing for Deinstall ##

Setting LOCAL_NODE to oraclenode1

Setting REMOTE_NODES to oraclenode2

Setting CLUSTER_NODES to oraclenode1,oraclenode2

Setting CRS_HOME to true

Setting oracle.installer.invPtrLoc to /tmp/deinstall2021-03-07_04-37-30AM/oraInst.loc

Setting oracle.installer.local to false

## [END] Preparing for Deinstall ##

Setting the force flag to false

Setting the force flag to cleanup the Oracle Base

Oracle Universal Installer clean START

Detach Oracle home '/u01/app/19.0.0.0/grid' from the central inventory on the local node : Done

Delete directory '/u01/app/19.0.0.0/grid' on the local node : Done

Delete directory '/u01/app/oraInventory' on the local node : Done

Delete directory '/u01/app/oracle' on the local node : Done

Detach Oracle home '/u01/app/19.0.0.0/grid' from the central inventory on the remote nodes 'oraclenode2' : Done

Delete directory '/u01/app/19.0.0.0/grid' on the remote nodes 'oraclenode2' : Done

Delete directory '/u01/app/oraInventory' on the remote nodes 'oraclenode2' : Done

Delete directory '/u01/app/oracle' on the remote nodes 'oraclenode2' : Done

Oracle Universal Installer cleanup was successful.

Oracle Universal Installer clean END

## [START] Oracle install clean ##

Clean install operation removing temporary directory '/tmp/deinstall2021-03-07_04-37-30AM' on node 'oraclenode2'

## [END] Oracle install clean ##

######################### DEINSTALL CLEAN OPERATION END #########################

####################### DEINSTALL CLEAN OPERATION SUMMARY #######################

Successfully detached Oracle home '/u01/app/19.0.0.0/grid' from the central inventory on the local node.

Successfully deleted directory '/u01/app/19.0.0.0/grid' on the local node.

Successfully deleted directory '/u01/app/oraInventory' on the local node.

Successfully deleted directory '/u01/app/oracle' on the local node.

Successfully detached Oracle home '/u01/app/19.0.0.0/grid' from the central inventory on the remote nodes 'oraclenode2'.

Successfully deleted directory '/u01/app/19.0.0.0/grid' on the remote nodes 'oraclenode2'.

Successfully deleted directory '/u01/app/oraInventory' on the remote nodes 'oraclenode2'.

Successfully deleted directory '/u01/app/oracle' on the remote nodes 'oraclenode2'.

Oracle Universal Installer cleanup was successful.

Run 'rm -r /etc/oraInst.loc' as root on node(s) 'oraclenode1,oraclenode2' at the end of the session.

Run 'rm -r /opt/ORCLfmap' as root on node(s) 'oraclenode1,oraclenode2' at the end of the session.

Oracle deinstall tool successfully cleaned up temporary directories.

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

############# ORACLE DEINSTALL TOOL END #############

2. Run the deconfig command on node2:

[root@oraclenode2 ~]# /u01/app/19.0.0.0/grid/crs/install/rootcrs.sh -force  -deconfig -paramfile "/tmp/deinstall2021-03-07_04-37-30AM/response/deinstall_OraGI19Home1.rsp"

Using configuration parameter file: /tmp/deinstall2021-03-07_04-37-30AM/response/deinstall_OraGI19Home1.rsp

The log of current session can be found at:

  /tmp/deinstall2021-03-07_04-37-30AM/logs/crsdeconfig_oraclenode2_2021-03-07_04-37-34AM.log

2021/03/07 04:38:33 CLSRSC-4006: Removing Oracle Trace File Analyzer (TFA) Collector.

2021/03/07 04:40:32 CLSRSC-4007: Successfully removed Oracle Trace File Analyzer (TFA) Collector.

2021/03/07 04:40:33 CLSRSC-336: Successfully deconfigured Oracle Clusterware stack on this node

[root@oraclenode2 ~]# ssh oraclenode1

root@oraclenode1's password:

Activate the web console with: systemctl enable --now cockpit.socket

Last login: Sun Mar  7 04:04:57 2021 from 172.29.0.26

[root@oraclenode1 ~]# 

3. Run the deconfig command on node1:

[root@oraclenode1 ~]# /u01/app/19.0.0.0/grid/crs/install/rootcrs.sh -force  -deconfig -paramfile "/tmp/deinstall2021-03-07_04-37-30AM/response/deinstall_OraGI19Home1.rsp" -lastnode

Using configuration parameter file: /tmp/deinstall2021-03-07_04-37-30AM/response/deinstall_OraGI19Home1.rsp

The log of current session can be found at:

  /tmp/deinstall2021-03-07_04-37-30AM/logs/crsdeconfig_oraclenode1_2021-03-07_04-44-04AM.log

CRS-2673: Attempting to stop 'ora.crsd' on 'oraclenode1'

CRS-2677: Stop of 'ora.crsd' on 'oraclenode1' succeeded

ASM de-configuration trace file location: /tmp/deinstall2021-03-07_04-37-30AM/logs/asmcadc_clean2021-03-07_04-46-54AM.log

ASM Clean Configuration START

ASM Clean Configuration END

ASM instance deleted successfully. Check /tmp/deinstall2021-03-07_04-37-30AM/logs/asmcadc_clean2021-03-07_04-46-54AM.log for details.

2021/03/07 04:48:20 CLSRSC-4006: Removing Oracle Trace File Analyzer (TFA) Collector.

2021/03/07 04:50:08 CLSRSC-4007: Successfully removed Oracle Trace File Analyzer (TFA) Collector.

2021/03/07 04:50:12 CLSRSC-336: Successfully deconfigured Oracle Clusterware stack on this node

2021/03/07 04:50:12 CLSRSC-559: Ensure that the GPnP profile data under the 'gpnp' directory in /u01/app/19.0.0.0/grid is deleted on each node before using the software in the current Grid Infrastructure home for reconfiguration.

[root@oraclenode1 ~]#

4. Remove the leftover files manually

[oracle@oraclenode1 logs]$ ps -ef|grep smon

oracle   2478714 2448867  0 04:52 pts/0    00:00:00 grep --color=auto smon

[oracle@oraclenode1 logs]$ cd /u01

[oracle@oraclenode1 u01]$ ll

total 0

drwxrwxr-x. 3 oracle oinstall 22 Mar  7 04:51 app

drwxrwxr-x. 3 oracle oinstall 17 Feb 15 20:23 patches

[oracle@oraclenode1 u01]$ cd app/

[oracle@oraclenode1 app]$ ll

total 0

drwxrwxr-x. 2 oracle oinstall 6 Mar  7 04:51 19.0.0.0

[oracle@oraclenode1 app]$ cd 19.0.0.0

[oracle@oraclenode1 19.0.0.0]$

[oracle@oraclenode1 19.0.0.0]$ ll

total 0

[oracle@oraclenode1 19.0.0.0]$ exit

logout

[root@oraclenode1 ~]# rm -r /etc/oraInst.loc

rm: remove regular file '/etc/oraInst.loc'? y

[root@oraclenode1 ~]# rm -r /opt/ORCLfmap

rm: descend into directory '/opt/ORCLfmap'? y

rm: descend into directory '/opt/ORCLfmap/prot1_64'? y

rm: descend into directory '/opt/ORCLfmap/prot1_64/bin'? y

rm: remove regular file '/opt/ORCLfmap/prot1_64/bin/fmputl'? y

rm: remove regular file '/opt/ORCLfmap/prot1_64/bin/fmputlhp'? y

rm: remove directory '/opt/ORCLfmap/prot1_64/bin'? y

rm: descend into directory '/opt/ORCLfmap/prot1_64/etc'? y

rm: remove regular file '/opt/ORCLfmap/prot1_64/etc/filemap.ora'? y

rm: remove directory '/opt/ORCLfmap/prot1_64/etc'? y

rm: remove directory '/opt/ORCLfmap/prot1_64/log'? y

rm: remove directory '/opt/ORCLfmap/prot1_64'? y

rm: remove directory '/opt/ORCLfmap'? y

[root@oraclenode1 ~]# 

[root@oraclenode2 ~]# ps -ef|grep smon

root     2316538 2288214  0 04:50 pts/1    00:00:00 grep --color=auto smon

[root@oraclenode2 ~]# cd /u01

[root@oraclenode2 u01]# ll

total 0

drwxrwxr-x. 3 oracle oinstall 22 Mar  7 04:49 app

[root@oraclenode2 u01]# cd app/

[root@oraclenode2 app]# ll

total 0

drwxrwxr-x. 2 oracle oinstall 6 Mar  7 04:49 19.0.0.0

[root@oraclenode2 app]# cd 19.0.0.0

[root@oraclenode2 19.0.0.0]# ll

total 0

[root@oraclenode2 19.0.0.0]# pwd

/u01/app/19.0.0.0

[root@oraclenode2 19.0.0.0]# rm -r /etc/oraInst.loc

rm: remove regular file '/etc/oraInst.loc'? y

[root@oraclenode2 19.0.0.0]# rm -r /opt/ORCLfmap

rm: descend into directory '/opt/ORCLfmap'? y

rm: descend into directory '/opt/ORCLfmap/prot1_64'? y

rm: descend into directory '/opt/ORCLfmap/prot1_64/bin'? y

rm: remove regular file '/opt/ORCLfmap/prot1_64/bin/fmputl'? y

rm: remove regular file '/opt/ORCLfmap/prot1_64/bin/fmputlhp'? y

rm: remove directory '/opt/ORCLfmap/prot1_64/bin'? y

rm: descend into directory '/opt/ORCLfmap/prot1_64/etc'? y

rm: remove regular file '/opt/ORCLfmap/prot1_64/etc/filemap.ora'? y

rm: remove directory '/opt/ORCLfmap/prot1_64/etc'? y

rm: remove directory '/opt/ORCLfmap/prot1_64/log'? y

rm: remove directory '/opt/ORCLfmap/prot1_64'? y

rm: remove directory '/opt/ORCLfmap'? y

[root@oraclenode2 19.0.0.0]#

Regards,

Mallik

SWITCH LOGFILE vs ARCHIVE LOG CURRENT

SWITCH LOGFILE vs ARCHIVE LOG CURRENT:

1. No-Wait vs Wait

The first difference is the responses. ALTER SYSTEM SWITCH LOGFILE is a no-wait statement, once it was issued by DBA, it'll return to the user and do the switching in the background. On the other side, ALTER SYSTEM ARCHIVE LOG CURRENT will wait for Archiver Process (ARCH) to complete the archiving and then return to user.

SQL> set timing on;

SQL> alter system switch logfile;

System altered.

Elapsed: 00:00:00.04

SQL> alter system archive log current;

System altered.

Elapsed: 00:00:00.12

2. Data Guard

A big difference can be seen in data guard environments, especially in 9i.

SQL> alter system archive log current;

System altered.

Elapsed: 00:01:46.87

As you can see, ALTER SYSTEM ARCHIVE LOG CURRENT takes almost 2 minutes for a 1GB redo logs to return. This is because it's waiting for the completion of network transferring and archiving on the remote standby database. Also, the larger redo size, the longer it takes.

3. RAC Database

For a cluster database, statement ALTER SYSTEM SWITCH LOGFILE affects only the current instance whereas ALTER SYSTEM ARCHIVE LOG CURRENT makes redo log switch on all nodes (threads).

Let's see all statuses of groups before switching.

SQL> select thread#, instance_name from v$instance;

   THREAD# INSTANCE_NAME

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

         1 TESTCDB1

SQL> select thread#, group#, status from v$log order by 1,2;

   THREAD#     GROUP# STATUS

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

         1          1 INACTIVE

         1          3 INACTIVE

         1          5 CURRENT

         2          2 CURRENT

         2          4 INACTIVE

         2          6 INACTIVE

6 rows selected.

ALTER SYSTEM SWITCH LOGFILE

First of all, we make a switch log file.

SQL> alter system switch logfile;

System altered.

SQL> select thread#, group#, status from v$log order by 1,2;

   THREAD#     GROUP# STATUS

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

         1          1 CURRENT

         1          3 INACTIVE

         1          5 ACTIVE

         2          2 CURRENT

         2          4 INACTIVE

         2          6 INACTIVE

6 rows selected.

ALTER SYSTEM ARCHIVE LOG CURRENT

By default, it switches redo groups on all nodes (threads).

SQL> alter system archive log current;

System altered.

SQL> select thread#, group#, status from v$log order by 1,2;

   THREAD#     GROUP# STATUS

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

         1          1 ACTIVE

         1          3 CURRENT

         1          5 INACTIVE

         2          2 ACTIVE

         2          4 CURRENT

         2          6 INACTIVE

6 rows selected.

Regards,

Mallik

Increasing the Redo Log size

Increasing the Redo Log size:

Increase the current redo logs from 50MB to 1GB 

Redo Log status:

CURRENT

Current redo log. This implies that the redo log is active. The redo log could be open or closed.

ACTIVE

Log is active but is not the current log. It is needed for crash recovery. It may be in use for block recovery. It may or may not be archived.

INACTIVE

Log is no longer needed for instance recovery. It may be in use for media recovery. It may or may not be archived.

1. Check Current Redo Logs

SQL> column group# format 99999;

SQL> column status format a10;

SQL> column mb format 99999;

SQL> select group#, status, bytes/1024/1024 mb from v$log;

GROUP# STATUS         MB

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

     1 CURRENT        50

     2 INACTIVE       50

     3 INACTIVE       50

2. Add 3 Groups with New Size (1GB)

SQL> alter database add logfile 

group 4 ('/u01/app/oracle/oradata/ORCL/redo04.log') size 1g, 

group 5 ('/u01/app/oracle/oradata/ORCL/redo05.log') size 1g, 

group 6 ('/u01/app/oracle/oradata/ORCL/redo06.log') size 1g;

Database altered.

SQL> select group#, status, bytes/1024/1024 mb from v$log;

GROUP# STATUS         MB

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

     1 CURRENT        50

     2 INACTIVE       50

     3 INACTIVE       50

     4 UNUSED       1024

     5 UNUSED       1024

     6 UNUSED       1024

6 rows selected.

3. Switch Logfile to New Groups

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> select group#, status, bytes/1024/1024 mb from v$log;

GROUP# STATUS         MB

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

     1 ACTIVE         50

     2 INACTIVE       50

     3 INACTIVE       50

     4 CURRENT      1024

     5 UNUSED       1024

     6 UNUSED       1024

6 rows selected.

4. Force a CheckPoint

SQL> alter system checkpoint;

System altered.

SQL> select group#, status, bytes/1024/1024 mb from v$log;

GROUP# STATUS         MB

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

     1 INACTIVE       50

     2 INACTIVE       50

     3 INACTIVE       50

     4 CURRENT      1024

     5 UNUSED       1024

     6 UNUSED       1024

6 rows selected.

5. Drop Group 1, 2, 3

SQL> alter database drop logfile group 1, group 2, group 3;

Database altered.

SQL> select group#, status, bytes/1024/1024 mb from v$log;

GROUP# STATUS         MB

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

     4 CURRENT      1024

     5 UNUSED       1024

     6 UNUSED       1024

6. Remove Redo Log Files

[oracle@test ~]$ rm -i /u01/app/oracle/oradata/ORCL/redo0[1-3].log

rm: remove regular file `/u01/app/oracle/oradata/ORCL/redo01.log'? y

rm: remove regular file `/u01/app/oracle/oradata/ORCL/redo02.log'? y

rm: remove regular file `/u01/app/oracle/oradata/ORCL/redo03.log'? y

7. Add Group 1, 2, 3 with New Size (1GB)

SQL> alter database add logfile 

group 1 ('/u01/app/oracle/oradata/ORCL/redo01.log') size 1g, 

group 2 ('/u01/app/oracle/oradata/ORCL/redo02.log') size 1g, 

group 3 ('/u01/app/oracle/oradata/ORCL/redo03.log') size 1g;

Database altered.

SQL> select group#, status, bytes/1024/1024 mb from v$log;

GROUP# STATUS         MB

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

     1 UNUSED       1024

     2 UNUSED       1024

     3 UNUSED       1024

     4 CURRENT      1024

     5 UNUSED       1024

     6 UNUSED       1024

6 rows selected.

8. Switch Logfile Several Times

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

System altered.

9. Check Status of All Redo Logs

SQL> select group#, status, bytes/1024/1024 mb from v$log;

GROUP# STATUS         MB

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

     1 ACTIVE       1024

     2 ACTIVE       1024

     3 ACTIVE       1024

     4 ACTIVE       1024

     5 ACTIVE       1024

     6 CURRENT      1024

6 rows selected.

SQL> column member format a40;

SQL> select group#, member from v$logfile;

GROUP# MEMBER

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

     1 /u01/app/oracle/oradata/ORCL/redo01.log

     2 /u01/app/oracle/oradata/ORCL/redo02.log

     3 /u01/app/oracle/oradata/ORCL/redo03.log

     4 /u01/app/oracle/oradata/ORCL/redo04.log

     5 /u01/app/oracle/oradata/ORCL/redo05.log

     6 /u01/app/oracle/oradata/ORCL/redo06.log

6 rows selected.

Regards,

Mallik

error while loading shared libraries: libclntshcore.so.12.1: cannot open shared object file: No such file or directory

error while loading shared libraries: libclntshcore.so.12.1: cannot open shared object file: No such file or directory

Issue:

sqlplus and rman and other binary executable are not working with error while loading shared libraries: libclntshcore.so.12.1: cannot open shared object file: No such file or directory

 

Error Message:

/orasw/oracle/product/12.1.0.2/db/bin/sqlplus: error while loading shared libraries: libclntshcore.so.12.1: cannot open shared object file: No such file or directory

/orasw/oracle/product/12.1.0.2/db/bin/rman: error while loading shared libraries: libclntshcore.so.12.1: cannot open shared object file: No such file or directory

[oracle@oracledb lib]$ sqlplus / as sysdba

sqlplus: error while loading shared libraries: libclntshcore.so.12.1: cannot open shared object file: No such file or directory

[oracle@oracledb lib]$ rman target /

rman: error while loading shared libraries: libclntshcore.so.12.1: cannot open shared object file: No such file or directory

[oracle@oracledb lib]$

Cause:

After Inverstigating found that the shared object file libclntshcore.so.12.1 was missing from the lib(/orasw/oracle/product/12.1.0.2/db/lib) location.

Solution:

[oracle@oracledb lib]$ pwd

/u01/app/oracle/product/12.2.0.1/dbhome_1/lib

[oracle@oracledb lib]$ ls -lrtr libclntshcore.so.12.1

ls: cannot access libclntshcore.so.12.1: No such file or directory

[oracle@oracledb lib]$

Copy from the existing Oracle Home and verify the file exist and has proper permission

[oracle@oracledb lib]$ ls -lrtr libclntshcore.so.12.1

-rwxr-xr-x. 1 oracle oinstall 8032696 Sep 27 19:28 libclntshcore.so.12.1

[oracle@oracledb lib]$

[oracle@oracledb lib]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Mon Sep 27 19:29:18 2021

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

Connected to:

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

SQL> Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

[oracle@oracledb lib]$ rman target /

Recovery Manager: Release 12.2.0.1.0 - Production on Mon Sep 27 19:29:24 2021

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

connected to target database: ORA12C (DBID=457334020)

RMAN>

Recovery Manager complete.

[oracle@oracledb lib]$

Regards,

Mallik

MAN Recovery Scenario - sysaux datafile corrupted or lost - What to do?

MAN Recovery Scenario - sysaux datafile corrupted or lost - What to do?

Scenarios:

1. Database with valid backups 
2. Database without valid backups

System datafile corrupted or lost happened:

1. When database is up and running 
2. When database is down 

High Level Steps:

RMAN - Database backup:

rman target /

CONFIGURE CONTROLFILE AUTOBACKUP ON;

RMAN> backup database plus archivelog;

RMAN> backup database format='/u01/backup/backup_%d_%T_%U';

RMAN> backup archivelog all format='/u01/backup/archive_%d_%T_%U';

RMAN> list backup of tablespace system;

Not Mandatory to shutdown:

SQL> shut immediate

SQL> shut abort;

SQL> startup mount;

col occupant_name format a30

col occupant_desc format a40

col schema_name format a15

set linesize 200

select occupant_name,occupant_desc,schema_name,space_usage_kbytes/1024 from v$sysaux_occupants;

Scenario 1: (When DB was up and running & has valid backup)

drop tablespace sysaux; >>> Not possible 

alter tablespace sysaux read only; >>> Not possible 

alter tablespace sysaux offline; >>> Not possible 

RMAN> alter database datafile 3 offline;

RMAN> restore datafile 3;

$ls -ld sysaux*

SQL> select file#,checkpoint_change#, status, recover from v$datafile_header;

RMAN> recover datafile 3;

RMAN> alter database datafile 3 online;

SQL> select checkpoint_change#, status, recover FROM v$datafile_header;

SQL> alter database open;

Scenario 2: (When DB was down and No valid backups)

SQL> startup; >>> Not possible

drop tablespace sysaux; >>> Not possible 

alter tablespace sysaux read only; >>> Not possible 

alter tablespace sysaux offline; >>> Not possible 

RMAN> alter database datafile 3 offline;

RMAN> restore datafile 3; >>> which will fail since we don’t have valid backup 

RMAN> recover datafile 3; >>> which will fail since we don’t have valid backup

RMAN> alter database datafile 3 online; >>> Fails with unable to find a file

sqlplus / as sysdba

alter database open;

select name, open_mode from v$database; >>> Able to open the DB 

Scenario 1: (When DB was up and running & has valid backup)

1. Check the database status:

[oracle@oraclelab1 ~]$ ps -ef|grep smon

oracle    8505     1  0 Feb03 ?        00:00:09 ora_smon_DEVDB

oracle    8665     1  0 13:28 ?        00:00:00 ora_smon_DB

oracle    9206     1  0 13:28 ?        00:00:00 ora_smon_DB12C

oracle   11704  9511  0 13:54 pts/1    00:00:00 grep --color=auto smon

[oracle@oraclelab1 ~]$

[oracle@oraclelab1 ~]$ . oraenv

ORACLE_SID = [DEVDB] ? DEVDB

The Oracle base remains unchanged with value /u01/app/oracle

[oracle@oraclelab1 ~]$ env |grep ORA

ORACLE_SID=DEVDB

ORACLE_BASE=/u01/app/oracle

ORACLE_HOME=/u01/app/oracle/product/19.0.0.0/dbhome_1

[oracle@oraclelab1 ~]$ 

2. Take valid RMAN backup:

[oracle@oraclelab1 ~]$ rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Thu Feb 10 13:55:42 2022

Version 19.3.0.0.0

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

connected to target database: DEVDB (DBID=996518900)

RMAN> list backup;

using target database control file instead of recovery catalog

specification does not match any backup in the repository

RMAN> backup database format='/u01/backup/backup_%d_%T_%U';

Starting backup at 10-FEB-22

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=42 device type=DISK

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00003 name=/u01/app/oracle/oradata/DEVDB/datafile/o1_mf_sysaux_jxwqbjbr_.dbf

input datafile file number=00012 name=/u01/app/oracle/oradata/DEVDB/bigtbs01.dbf

input datafile file number=00001 name=/u01/app/oracle/oradata/DEVDB/datafile/o1_mf_system_jxwqbjc1_.dbf

input datafile file number=00004 name=/u01/app/oracle/oradata/DEVDB/datafile/o1_mf_undotbs1_jxwqbjc5_.dbf

input datafile file number=00005 name=/u01/app/oracle/oradata/DEVDB/datafile/o1_mf_test1_jxwqbzfw_.dbf

input datafile file number=00008 name=/u01/app/oracle/oradata/DEVDB/datafile/o1_mf_test3_jxwqbzg1_.dbf

input datafile file number=00009 name=/u01/app/oracle/oradata/DEVDB/test4.dbf

input datafile file number=00011 name=/u01/app/oracle/oradata/DEVDB/test4_1.dbf

input datafile file number=00010 name=/u01/app/oracle/oradata/DEVDB/test5.dbf

input datafile file number=00007 name=/u01/app/oracle/oradata/DEVDB/datafile/o1_mf_users_jxwqbjc8_.dbf

input datafile file number=00002 name=/u01/app/oracle/oradata/DEVDB/test2.dbf

channel ORA_DISK_1: starting piece 1 at 10-FEB-22

channel ORA_DISK_1: finished piece 1 at 10-FEB-22

piece handle=/u01/backup/backup_DEVDB_20220210_e30lg705_1_1 tag=TAG20220210T135620 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15

Finished backup at 10-FEB-22

Starting Control File and SPFILE Autobackup at 10-FEB-22

piece handle=/u01/app/oracle/fast_recovery_area/DEVDB/autobackup/2022_02_10/o1_mf_s_1096293396_k09m1wdr_.bkp comment=NONE

Finished Control File and SPFILE Autobackup at 10-FEB-22

RMAN> backup archivelog all format='/u01/backup/archive_%d_%T_%U';

Starting backup at 10-FEB-22

current log archived

using channel ORA_DISK_1

channel ORA_DISK_1: starting archived log backup set

channel ORA_DISK_1: specifying archived log(s) in backup set

input archived log thread=1 sequence=308 RECID=306 STAMP=1096293417

channel ORA_DISK_1: starting piece 1 at 10-FEB-22

channel ORA_DISK_1: finished piece 1 at 10-FEB-22

piece handle=/u01/backup/archive_DEVDB_20220210_e50lg719_1_1 tag=TAG20220210T135657 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

Finished backup at 10-FEB-22

Starting Control File and SPFILE Autobackup at 10-FEB-22

piece handle=/u01/app/oracle/fast_recovery_area/DEVDB/autobackup/2022_02_10/o1_mf_s_1096293418_k09m2ldq_.bkp comment=NONE

Finished Control File and SPFILE Autobackup at 10-FEB-22

RMAN> list backup;

List of Backup Sets

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

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

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

415     Full    2.09G      DISK        00:00:10     10-FEB-22

        BP Key: 415   Status: AVAILABLE  Compressed: NO  Tag: TAG20220210T135620

        Piece Name: /u01/backup/backup_DEVDB_20220210_e30lg705_1_1

  List of Datafiles in backup set 415

  File LV Type Ckp SCN    Ckp Time  Abs Fuz SCN Sparse Name

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

  1       Full 6903774    10-FEB-22              NO    /u01/app/oracle/oradata/DEVDB/datafile/o1_mf_system_jxwqbjc1_.dbf

  2       Full 6903774    10-FEB-22              NO    /u01/app/oracle/oradata/DEVDB/test2.dbf

  3       Full 6903774    10-FEB-22              NO    /u01/app/oracle/oradata/DEVDB/datafile/o1_mf_sysaux_jxwqbjbr_.dbf

  4       Full 6903774    10-FEB-22              NO    /u01/app/oracle/oradata/DEVDB/datafile/o1_mf_undotbs1_jxwqbjc5_.dbf

  5       Full 6903774    10-FEB-22              NO    /u01/app/oracle/oradata/DEVDB/datafile/o1_mf_test1_jxwqbzfw_.dbf

  7       Full 6903774    10-FEB-22              NO    /u01/app/oracle/oradata/DEVDB/datafile/o1_mf_users_jxwqbjc8_.dbf

  8       Full 6903774    10-FEB-22              NO    /u01/app/oracle/oradata/DEVDB/datafile/o1_mf_test3_jxwqbzg1_.dbf

  9       Full 6903774    10-FEB-22              NO    /u01/app/oracle/oradata/DEVDB/test4.dbf

  10      Full 6903774    10-FEB-22              NO    /u01/app/oracle/oradata/DEVDB/test5.dbf

  11      Full 6903774    10-FEB-22              NO    /u01/app/oracle/oradata/DEVDB/test4_1.dbf

  12      Full 6903774    10-FEB-22              NO    /u01/app/oracle/oradata/DEVDB/bigtbs01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

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

416     Full    11.80M     DISK        00:00:00     10-FEB-22

        BP Key: 416   Status: AVAILABLE  Compressed: NO  Tag: TAG20220210T135636

        Piece Name: /u01/app/oracle/fast_recovery_area/DEVDB/autobackup/2022_02_10/o1_mf_s_1096293396_k09m1wdr_.bkp

  SPFILE Included: Modification time: 03-FEB-22

  SPFILE db_unique_name: DEVDB

  Control File Included: Ckp SCN: 6903787      Ckp time: 10-FEB-22

BS Key  Size       Device Type Elapsed Time Completion Time

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

417     375.50K    DISK        00:00:00     10-FEB-22

        BP Key: 417   Status: AVAILABLE  Compressed: NO  Tag: TAG20220210T135657

        Piece Name: /u01/backup/archive_DEVDB_20220210_e50lg719_1_1

  List of Archived Logs in backup set 417

  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time

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

  1    308     6902434    10-FEB-22 6903818    10-FEB-22

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

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

418     Full    11.80M     DISK        00:00:00     10-FEB-22

        BP Key: 418   Status: AVAILABLE  Compressed: NO  Tag: TAG20220210T135658

        Piece Name: /u01/app/oracle/fast_recovery_area/DEVDB/autobackup/2022_02_10/o1_mf_s_1096293418_k09m2ldq_.bkp

  SPFILE Included: Modification time: 03-FEB-22

  SPFILE db_unique_name: DEVDB

  Control File Included: Ckp SCN: 6903831      Ckp time: 10-FEB-22

RMAN> exit

Recovery Manager complete.

[oracle@oraclelab1 ~]$ 

3. Simulate the failure - by deleting the sysaux datafile:

[oracle@oraclelab1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Feb 10 13:57:34 2022

Version 19.3.0.0.0

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

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

SQL>

SQL> set pages 1000 lines 1000

SQL> select TABLESPACE_NAME,FILE_NAME from dba_data_files;

TABLESPACE_NAME                FILE_NAME

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

SYSTEM                         /u01/app/oracle/oradata/DEVDB/datafile/o1_mf_system_jxwqbjc1_.dbf

SYSAUX                         /u01/app/oracle/oradata/DEVDB/datafile/o1_mf_sysaux_jxwqbjbr_.dbf

UNDOTBS1                       /u01/app/oracle/oradata/DEVDB/datafile/o1_mf_undotbs1_jxwqbjc5_.dbf

USERS                          /u01/app/oracle/oradata/DEVDB/datafile/o1_mf_users_jxwqbjc8_.dbf

TEST1                          /u01/app/oracle/oradata/DEVDB/datafile/o1_mf_test1_jxwqbzfw_.dbf

TEST2                          /u01/app/oracle/oradata/DEVDB/test2.dbf

TEST3                          /u01/app/oracle/oradata/DEVDB/datafile/o1_mf_test3_jxwqbzg1_.dbf

TEST4                          /u01/app/oracle/oradata/DEVDB/test4.dbf

TEST4                          /u01/app/oracle/oradata/DEVDB/test4_1.dbf

TEST5                          /u01/app/oracle/oradata/DEVDB/test5.dbf

BIGTBS                         /u01/app/oracle/oradata/DEVDB/bigtbs01.dbf

11 rows selected.

SQL>

SQL> exit

Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

[oracle@oraclelab1 ~]$ rm /u01/app/oracle/oradata/DEVDB/datafile/o1_mf_sysaux_jxwqbjbr_.dbf

[oracle@oraclelab1 ~]$ ls -ld /u01/app/oracle/oradata/DEVDB/datafile/o1_mf_sysaux_jxwqbjbr_.dbf

ls: cannot access /u01/app/oracle/oradata/DEVDB/datafile/o1_mf_sysaux_jxwqbjbr_.dbf: No such file or directory

[oracle@oraclelab1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Feb 10 13:59:10 2022

Version 19.3.0.0.0

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

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

SQL>

SQL> create user mallik11 identified by mallik11;

User created.

SQL>

SQL> grant dba to mallik11;

Grant succeeded.

SQL> conn mallik11/mallik11;

Connected.

SQL> show user

USER is "MALLIK11"

SQL>

SQL>

SQL> create table test1 (SLNO number(10));

Table created.

SQL> select * from test1;

no rows selected

SQL> insert into test1 values(1);

insert into test1 values(1)

            *

ERROR at line 1:

ORA-01116: error in opening database file 3

ORA-01110: data file 3:

'/u01/app/oracle/oradata/DEVDB/datafile/o1_mf_sysaux_jxwqbjbr_.dbf'

ORA-27041: unable to open file

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

SQL> create table test2 (SLNO number(10));

Table created.

SQL> insert into test2 values(1);

insert into test2 values(1)

            *

ERROR at line 1:

ORA-01116: error in opening database file 3

ORA-01110: data file 3:

'/u01/app/oracle/oradata/DEVDB/datafile/o1_mf_sysaux_jxwqbjbr_.dbf'

ORA-27041: unable to open file

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

SQL> 

4. Check alert log & verify:

[oracle@oraclelab1 backup]$ locate alert_DEVDB.log

/u01/app/oracle/diag/rdbms/devdb/DEVDB/trace/alert_DEVDB.log

[oracle@oraclelab1 backup]$ tail -100f /u01/app/oracle/diag/rdbms/devdb/DEVDB/trace/alert_DEVDB.log

2022-02-10T14:02:25.265719+05:30

Errors in file /u01/app/oracle/diag/rdbms/devdb/DEVDB/trace/DEVDB_mz00_12572.trc:

ORA-01110: data file 3: '/u01/app/oracle/oradata/DEVDB/datafile/o1_mf_sysaux_jxwqbjbr_.dbf'

ORA-01565: error in identifying file '/u01/app/oracle/oradata/DEVDB/datafile/o1_mf_sysaux_jxwqbjbr_.dbf'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 7

Checker run found 1 new persistent data failures

2022-02-10T14:03:27.630686+05:30

Errors in file /u01/app/oracle/diag/rdbms/devdb/DEVDB/trace/DEVDB_mz00_12654.trc:

ORA-01110: data file 3: '/u01/app/oracle/oradata/DEVDB/datafile/o1_mf_sysaux_jxwqbjbr_.dbf'

ORA-01565: error in identifying file '/u01/app/oracle/oradata/DEVDB/datafile/o1_mf_sysaux_jxwqbjbr_.dbf'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 7

2022-02-10T14:08:06.341642+05:30

SQL> select occupant_name , occupant_desc , schema_name , space_usage_kbytes/1024 from v$sysaux_occupants ;

SQL> drop tablespace sysaux;

drop tablespace sysaux

*

ERROR at line 1:

ORA-13501: Cannot drop SYSAUX tablespace

SQL> alter tablespace sysaux read only;

alter tablespace sysaux read only

*

ERROR at line 1:

ORA-13505: SYSAUX tablespace can not be made read only

SQL>

SQL> select TABLESPACE_NAME,STATUS from dba_tablespaces;

TABLESPACE_NAME                STATUS

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

SYSTEM                         ONLINE

SYSAUX                         ONLINE

UNDOTBS1                       ONLINE

TEMP                           ONLINE

USERS                          ONLINE

TEST1                          ONLINE

TEST2                          ONLINE

TEST3                          ONLINE

TEST4                          ONLINE

TEST5                          ONLINE

BIGTBS                         ONLINE

11 rows selected.

SQL> alter tablespace sysaux offline;

alter tablespace sysaux offline

*

ERROR at line 1:

ORA-13560: SYSAUX tablespace cannot be brought offline; shut down if necessary

SQL> ALTER TABLESPACE SYSAUX OFFLINE NORMAL;

ALTER TABLESPACE SYSAUX OFFLINE NORMAL

*

ERROR at line 1:

ORA-13560: SYSAUX tablespace cannot be brought offline; shut down if necessary

SQL>

SQL> select FILE_ID,TABLESPACE_NAME,FILE_NAME from dba_data_files;

   FILE_ID TABLESPACE_NAME                FILE_NAME

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

         1 SYSTEM                         /u01/app/oracle/oradata/DEVDB/datafile/o1_mf_system_jxwqbjc1_.dbf

         3 SYSAUX                         /u01/app/oracle/oradata/DEVDB/datafile/o1_mf_sysaux_jxwqbjbr_.dbf

         4 UNDOTBS1                       /u01/app/oracle/oradata/DEVDB/datafile/o1_mf_undotbs1_jxwqbjc5_.dbf

         7 USERS                          /u01/app/oracle/oradata/DEVDB/datafile/o1_mf_users_jxwqbjc8_.dbf

         5 TEST1                          /u01/app/oracle/oradata/DEVDB/datafile/o1_mf_test1_jxwqbzfw_.dbf

         2 TEST2                          /u01/app/oracle/oradata/DEVDB/test2.dbf

         8 TEST3                          /u01/app/oracle/oradata/DEVDB/datafile/o1_mf_test3_jxwqbzg1_.dbf

         9 TEST4                          /u01/app/oracle/oradata/DEVDB/test4.dbf

        11 TEST4                          /u01/app/oracle/oradata/DEVDB/test4_1.dbf

        10 TEST5                          /u01/app/oracle/oradata/DEVDB/test5.dbf

        12 BIGTBS                         /u01/app/oracle/oradata/DEVDB/bigtbs01.dbf

11 rows selected.

SQL> alter database datafile 3 offline;

Database altered.

SQL> select FILE_ID,TABLESPACE_NAME,FILE_NAME from dba_data_files;

   FILE_ID TABLESPACE_NAME                FILE_NAME

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

         1 SYSTEM                         /u01/app/oracle/oradata/DEVDB/datafile/o1_mf_system_jxwqbjc1_.dbf

         3 SYSAUX                         /u01/app/oracle/oradata/DEVDB/datafile/o1_mf_sysaux_jxwqbjbr_.dbf

         4 UNDOTBS1                       /u01/app/oracle/oradata/DEVDB/datafile/o1_mf_undotbs1_jxwqbjc5_.dbf

         7 USERS                          /u01/app/oracle/oradata/DEVDB/datafile/o1_mf_users_jxwqbjc8_.dbf

         5 TEST1                          /u01/app/oracle/oradata/DEVDB/datafile/o1_mf_test1_jxwqbzfw_.dbf

         2 TEST2                          /u01/app/oracle/oradata/DEVDB/test2.dbf

         8 TEST3                          /u01/app/oracle/oradata/DEVDB/datafile/o1_mf_test3_jxwqbzg1_.dbf

         9 TEST4                          /u01/app/oracle/oradata/DEVDB/test4.dbf

        11 TEST4                          /u01/app/oracle/oradata/DEVDB/test4_1.dbf

        10 TEST5                          /u01/app/oracle/oradata/DEVDB/test5.dbf

        12 BIGTBS                         /u01/app/oracle/oradata/DEVDB/bigtbs01.dbf

11 rows selected.

SQL> 

SQL> select FILE_ID,TABLESPACE_NAME,FILE_NAME, status from dba_data_files;

   FILE_ID TABLESPACE_NAME                FILE_NAME                                    STATUS

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

         1 SYSTEM                         /u01/app/oracle/oradata/DEVDB/datafile/o1_mf_system_jxwqbjc1_.dbf             AVAILABLE

         3 SYSAUX                         /u01/app/oracle/oradata/DEVDB/datafile/o1_mf_sysaux_jxwqbjbr_.dbf             AVAILABLE

         4 UNDOTBS1                       /u01/app/oracle/oradata/DEVDB/datafile/o1_mf_undotbs1_jxwqbjc5_.dbf           AVAILABLE

         7 USERS                          /u01/app/oracle/oradata/DEVDB/datafile/o1_mf_users_jxwqbjc8_.dbf              AVAILABLE

         5 TEST1                          /u01/app/oracle/oradata/DEVDB/datafile/o1_mf_test1_jxwqbzfw_.dbf              AVAILABLE

         2 TEST2                          /u01/app/oracle/oradata/DEVDB/test2.dbf                                       AVAILABLE

         8 TEST3                          /u01/app/oracle/oradata/DEVDB/datafile/o1_mf_test3_jxwqbzg1_.dbf              AVAILABLE

         9 TEST4                          /u01/app/oracle/oradata/DEVDB/test4.dbf                                       AVAILABLE

        11 TEST4                          /u01/app/oracle/oradata/DEVDB/test4_1.dbf                                     AVAILABLE

        10 TEST5                          /u01/app/oracle/oradata/DEVDB/test5.dbf                                       AVAILABLE

        12 BIGTBS                         /u01/app/oracle/oradata/DEVDB/bigtbs01.dbf                                    AVAILABLE

11 rows selected.

SQL>

[oracle@oraclelab1 ~]$ ls -ld /u01/app/oracle/oradata/DEVDB/datafile/o1_mf_sysaux_jxwqbjbr_.dbf

ls: cannot access /u01/app/oracle/oradata/DEVDB/datafile/o1_mf_sysaux_jxwqbjbr_.dbf: No such file or directory

[oracle@oraclelab1 ~]$

5. Restore and Recover the sysaux datafile using valid backups:

[oracle@oraclelab1 ~]$ rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Thu Feb 10 14:16:18 2022

Version 19.3.0.0.0

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

connected to target database: DEVDB (DBID=996518900)

RMAN> restore datafile 3;

Starting restore at 10-FEB-22

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=29 device type=DISK

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 00003 to /u01/app/oracle/oradata/DEVDB/datafile/o1_mf_sysaux_jxwqbjbr_.dbf

channel ORA_DISK_1: reading from backup piece /u01/backup/backup_DEVDB_20220210_e30lg705_1_1

channel ORA_DISK_1: piece handle=/u01/backup/backup_DEVDB_20220210_e30lg705_1_1 tag=TAG20220210T135620

channel ORA_DISK_1: restored backup piece 1

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

Finished restore at 10-FEB-22

RMAN>

[oracle@oraclelab1 ~]$ cd /u01/app/oracle/oradata/DEVDB/datafile/

[oracle@oraclelab1 datafile]$ ls -ltrh

total 3.0G

-rw-r-----. 1 oracle oinstall  21M Feb  9 22:00 o1_mf_temp_jxwqgc0c_.tmp

-rw-r-----. 1 oracle oinstall 101M Feb 10 13:56 o1_mf_test1_jxwqbzfw_.dbf

-rw-r-----. 1 oracle oinstall 101M Feb 10 13:56 o1_mf_test3_jxwqbzg1_.dbf

-rw-r-----. 1 oracle oinstall 7.6M Feb 10 14:07 o1_mf_users_jxwqbjc8_.dbf

-rw-r-----. 1 oracle oinstall 336M Feb 10 14:15 o1_mf_undotbs1_jxwqbjc5_.dbf

-rw-r-----. 1 oracle oinstall 941M Feb 10 14:15 o1_mf_system_jxwqbjc1_.dbf

-rw-r-----. 1 oracle oinstall 1.5G Feb 10 14:16 o1_mf_sysaux_k09n7c1v_.dbf

[oracle@oraclelab1 datafile]$ rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Thu Feb 10 14:18:02 2022

Version 19.3.0.0.0

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

connected to target database: DEVDB (DBID=996518900)

RMAN> recover datafile 3;

Starting recover at 10-FEB-22

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=42 device type=DISK

starting media recovery

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

Finished recover at 10-FEB-22

RMAN> alter database datafile 3 online;

Statement processed

RMAN> 

[oracle@oraclelab1 backup]$ locate alert_DEVDB.log

/u01/app/oracle/diag/rdbms/devdb/DEVDB/trace/alert_DEVDB.log

[oracle@oraclelab1 backup]$ tail -100f /u01/app/oracle/diag/rdbms/devdb/DEVDB/trace/alert_DEVDB.log

2022-02-10T14:08:06.341642+05:30

drop tablespace sysaux

ORA-13501 signalled during: drop tablespace sysaux...

2022-02-10T14:08:17.041134+05:30

alter tablespace sysaux read only

ORA-13505 signalled during: alter tablespace sysaux read only...

2022-02-10T14:08:49.537798+05:30

Errors in file /u01/app/oracle/diag/rdbms/devdb/DEVDB/trace/DEVDB_mz00_13076.trc:

ORA-01110: data file 3: '/u01/app/oracle/oradata/DEVDB/datafile/o1_mf_sysaux_jxwqbjbr_.dbf'

ORA-01565: error in identifying file '/u01/app/oracle/oradata/DEVDB/datafile/o1_mf_sysaux_jxwqbjbr_.dbf'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 7

2022-02-10T14:09:07.656647+05:30

alter tablespace sysaux offline

ORA-13560 signalled during: alter tablespace sysaux offline...

2022-02-10T14:09:32.657823+05:30

ALTER TABLESPACE SYSAUX OFFLINE NORMAL

ORA-13560 signalled during: ALTER TABLESPACE SYSAUX OFFLINE NORMAL...

2022-02-10T14:14:14.576930+05:30

alter database datafile 3 offline

Completed: alter database datafile 3 offline

2022-02-10T14:14:14.782478+05:30

Errors in file /u01/app/oracle/diag/rdbms/devdb/DEVDB/trace/DEVDB_smon_8505.trc:

ORA-00604: error occurred at recursive SQL level 1

ORA-00376: file 3 cannot be read at this time

ORA-01110: data file 3: '/u01/app/oracle/oradata/DEVDB/datafile/o1_mf_sysaux_jxwqbjbr_.dbf'

2022-02-10T14:14:14.804047+05:30

Errors in file /u01/app/oracle/diag/rdbms/devdb/DEVDB/trace/DEVDB_mz00_14086.trc:

ORA-01110: data file 3: '/u01/app/oracle/oradata/DEVDB/datafile/o1_mf_sysaux_jxwqbjbr_.dbf'

ORA-01565: error in identifying file '/u01/app/oracle/oradata/DEVDB/datafile/o1_mf_sysaux_jxwqbjbr_.dbf'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 7

2022-02-10T14:14:20.823781+05:30

Unified Audit record write to audit trail table failed due to ORA-1658. Writing the record to OS spillover file.

2022-02-10T14:15:53.693433+05:30

Errors in file /u01/app/oracle/diag/rdbms/devdb/DEVDB/trace/DEVDB_mz00_14266.trc:

ORA-01110: data file 3: '/u01/app/oracle/oradata/DEVDB/datafile/o1_mf_sysaux_jxwqbjbr_.dbf'

ORA-01565: error in identifying file '/u01/app/oracle/oradata/DEVDB/datafile/o1_mf_sysaux_jxwqbjbr_.dbf'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 7

2022-02-10T14:16:43.599308+05:30

Full restore complete of datafile 3 /u01/app/oracle/oradata/DEVDB/datafile/o1_mf_sysaux_k09n7c1v_.dbf.  Elapsed time: 0:00:08

  checkpoint is 6903774

  last deallocation scn is 6879007

2022-02-10T14:16:54.139721+05:30

Errors in file /u01/app/oracle/diag/rdbms/devdb/DEVDB/trace/DEVDB_mz00_14361.trc:

ORA-01110: data file 3: '/u01/app/oracle/oradata/DEVDB/datafile/o1_mf_sysaux_k09n7c1v_.dbf'

ORA-01208: data file is an old version - not accessing current version

Checker run found 1 new persistent data failures

2022-02-10T14:17:54.334324+05:30

Errors in file /u01/app/oracle/diag/rdbms/devdb/DEVDB/trace/DEVDB_mz00_14448.trc:

ORA-01110: data file 3: '/u01/app/oracle/oradata/DEVDB/datafile/o1_mf_sysaux_k09n7c1v_.dbf'

ORA-01208: data file is an old version - not accessing current version

2022-02-10T14:18:09.496943+05:30

alter database recover datafile list clear

Completed: alter database recover datafile list clear

alter database recover

 if needed datafile 3

2022-02-10T14:18:09.500057+05:30

Media Recovery Start

2022-02-10T14:18:09.501718+05:30

Serial Media Recovery started

2022-02-10T14:18:09.545055+05:30

Recovery of Online Redo Log: Thread 1 Group 2 Seq 308 Reading mem 0

  Mem# 0: /u01/app/oracle/oradata/DEVDB/onlinelog/o1_mf_2_jw044ygn_.log

  Mem# 1: /u01/app/oracle/fast_recovery_area/DEVDB/onlinelog/o1_mf_2_jw044zmf_.log

2022-02-10T14:18:09.551263+05:30

Recovery of Online Redo Log: Thread 1 Group 3 Seq 309 Reading mem 0

  Mem# 0: /u01/app/oracle/oradata/DEVDB/onlinelog/o1_mf_3_jw044yj7_.log

  Mem# 1: /u01/app/oracle/fast_recovery_area/DEVDB/onlinelog/o1_mf_3_jw044zm5_.log

2022-02-10T14:18:09.922722+05:30

Media Recovery Complete (DEVDB)

Completed: alter database recover

 if needed datafile 3

2022-02-10T14:18:41.070655+05:30

alter database datafile 3 online

Completed: alter database datafile 3 online

2022-02-10T14:25:59.965374+05:30

6. Post verification:

[oracle@oraclelab1 datafile]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Feb 10 14:18:49 2022

Version 19.3.0.0.0

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

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

SQL> conn mallik11/mallik11;

Connected.

SQL> insert into test2 values(1);

1 row created.

SQL>

Scenario 2: (When DB was down and No valid backups)

1. Check the database status:

[oracle@oraclelab1 ~]$ ps -ef|grep smon

oracle    8505     1  0 Feb03 ?        00:00:09 ora_smon_DEVDB

oracle    8665     1  0 13:28 ?        00:00:00 ora_smon_DB

oracle    9206     1  0 13:28 ?        00:00:00 ora_smon_DB12C

oracle   11704  9511  0 13:54 pts/1    00:00:00 grep --color=auto smon

[oracle@oraclelab1 ~]$. oraenv

ORACLE_SID = [DEVDB] ? DB

The Oracle base remains unchanged with value /u01/app/oracle

[oracle@oraclelab1 ~]$

[oracle@oraclelab1 ~]$ env |grep ORA

ORACLE_SID=DB

ORACLE_BASE=/u01/app/oracle

ORACLE_HOME=/u01/app/oracle/product/19.0.0.0/dbhome_1

[oracle@oraclelab1 ~]$

2. check rman level no valid backups

[oracle@oraclelab1 ~]$ rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Thu Feb 10 13:36:04 2022

Version 19.3.0.0.0

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

connected to target database: DB (DBID=1857892946)

RMAN> list backup;

using target database control file instead of recovery catalog

specification does not match any backup in the repository

RMAN>

2. Simulate the failure by shutting down the DB and deleting the sysaux datafile:

[oracle@oraclelab1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Feb 10 13:56:37 2022

Version 19.3.0.0.0

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

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

SQL> set pages 1000 lines 1000

SQL> select TABLESPACE_NAME,FILE_NAME from dba_data_files;

TABLESPACE_NAME                FILE_NAME

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

USERS                          /u01/app/oracle/oradata/DB/datafile/o1_mf_users_jy5ypw5m_.dbf

UNDOTBS1                       /u01/app/oracle/oradata/DB/datafile/o1_mf_undotbs1_jy5ypv3f_.dbf

SYSTEM                         /u01/app/oracle/oradata/DB/datafile/o1_mf_system_jysf1q6o_.dbf

SYSAUX                         /u01/app/oracle/oradata/DB/datafile/o1_mf_sysaux_jy5yp1yv_.dbf

SQL> shut immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> exit

Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

[oracle@oraclelab1 ~]$ rm -rf /u01/app/oracle/oradata/DB/datafile/o1_mf_sysaux_jy5yp1yv_.dbf

[oracle@oraclelab1 ~]$ ls -ld /u01/app/oracle/oradata/DB/datafile/o1_mf_sysaux_jy5yp1yv_.dbf

ls: cannot access /u01/app/oracle/oradata/DB/datafile/o1_mf_sysaux_jy5yp1yv_.dbf: No such file or directory

[oracle@oraclelab1 ~]$ 

3. Try to start the DB which will report missing sysaux datafile:

[oracle@oraclelab1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Feb 10 13:59:43 2022

Version 19.3.0.0.0

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

Connected to an idle instance.

SQL> startup;

ORACLE instance started.

Total System Global Area 2432695144 bytes

Fixed Size                  8899432 bytes

Variable Size             788529152 bytes

Database Buffers         1627389952 bytes

Redo Buffers                7876608 bytes

Database mounted.

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

ORA-01110: data file 3:

'/u01/app/oracle/oradata/DB/datafile/o1_mf_sysaux_jy5yp1yv_.dbf'

SQL> 

[root@oraclelab1 ~]# locate alert_DB.log

/u01/app/oracle/diag/rdbms/db/DB/trace/alert_DB.log

[root@oraclelab1 ~]# tail -100f /u01/app/oracle/diag/rdbms/db/DB/trace/alert_DB.log

Starting background process PXMN

2022-02-10T13:59:52.902334+05:30

PXMN started with pid=30, OS id=12234

Starting background process FENC

2022-02-10T13:59:52.914050+05:30

FENC started with pid=31, OS id=12236

Starting background process MMON

2022-02-10T13:59:52.927196+05:30

MMON started with pid=32, OS id=12238

Starting background process MMNL

2022-02-10T13:59:52.943815+05:30

starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...

2022-02-10T13:59:52.944173+05:30

MMNL started with pid=33, OS id=12240

starting up 1 shared server(s) ...

Starting background process TMON

2022-02-10T13:59:52.992130+05:30

TMON started with pid=35, OS id=12246

Setting CPU count to 2

ORACLE_BASE from environment = /u01/app/oracle

2022-02-10T13:59:53.109922+05:30

ALTER DATABASE   MOUNT

2022-02-10T13:59:55.713915+05:30

Using default pga_aggregate_limit of 2048 MB

2022-02-10T13:59:57.203072+05:30

.... (PID:12247): Redo network throttle feature is disabled at mount time

2022-02-10T13:59:57.223266+05:30

Successful mount of redo thread 1, with mount id 1860126297

2022-02-10T13:59:57.224119+05:30

Database mounted in Exclusive Mode

Lost write protection disabled

.... (PID:12247): Using STANDBY_ARCHIVE_DEST parameter default value as USE_DB_RECOVERY_FILE_DEST [krsd.c:18157]

Completed: ALTER DATABASE   MOUNT

2022-02-10T13:59:57.358743+05:30

ALTER DATABASE OPEN

2022-02-10T13:59:57.427045+05:30

Errors in file /u01/app/oracle/diag/rdbms/db/DB/trace/DB_mz00_12260.trc:

ORA-01110: data file 3: '/u01/app/oracle/oradata/DB/datafile/o1_mf_sysaux_jy5yp1yv_.dbf'

ORA-01565: error in identifying file '/u01/app/oracle/oradata/DB/datafile/o1_mf_sysaux_jy5yp1yv_.dbf'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 7

2022-02-10T13:59:57.436879+05:30

Errors in file /u01/app/oracle/diag/rdbms/db/DB/trace/DB_dbw0_12211.trc:

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

ORA-01110: data file 3: '/u01/app/oracle/oradata/DB/datafile/o1_mf_sysaux_jy5yp1yv_.dbf'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 7

Ping without log force is disabled:

  instance mounted in exclusive mode.

4. Try to restore and recover sysaux datafile which is not possible since don't have valid backup:

SQL> drop tablespace sysaux;

drop tablespace sysaux

*

ERROR at line 1:

ORA-01109: database not open

SQL> alter tablespace sysaux read only;

alter tablespace sysaux read only

*

ERROR at line 1:

ORA-01109: database not open

SQL> select TABLESPACE_NAME,STATUS from dba_tablespaces;

select TABLESPACE_NAME,STATUS from dba_tablespaces

                                   *

ERROR at line 1:

ORA-01219: database or pluggable database not open: queries allowed on fixed

tables or views only

SQL> alter tablespace sysaux offline;

alter tablespace sysaux offline

*

ERROR at line 1:

ORA-01109: database not open

SQL> ALTER TABLESPACE SYSAUX OFFLINE NORMAL;

ALTER TABLESPACE SYSAUX OFFLINE NORMAL

*

ERROR at line 1:

ORA-01109: database not open

SQL>

[oracle@oraclelab1 rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Thu Feb 10 14:21:49 2022

Version 19.3.0.0.0

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

connected to target database: DB (DBID=1857892946, not open)

RMAN> alter database datafile 3 offline;

using target database control file instead of recovery catalog

Statement processed

RMAN> restore datafile 3;

Starting restore at 10-FEB-22

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=25 device type=DISK

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of restore command at 02/10/2022 14:22:08

RMAN-06026: some targets not found - aborting restore

RMAN-06023: no backup or copy of datafile 3 found to restore

RMAN> recover datafile 3;

Starting recover at 10-FEB-22

using channel ORA_DISK_1

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of recover command at 02/10/2022 14:22:15

RMAN-06094: datafile 3 must be restored

RMAN> exit

Recovery Manager complete.

[oracle@oraclelab1 ~]$

5. Try to make sysaux datafile offline and it will fail however still we are able to open the database but which is no use since we are unable to restore the sysaux datafile

RMAN> alter database datafile 3 online;

using target database control file instead of recovery catalog

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of sql statement command at 02/10/2022 15:19:33

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

ORA-01110: data file 3: '/u01/app/oracle/oradata/DB/datafile/o1_mf_sysaux_jy5yp1yv_.dbf'

RMAN>

[oracle@oraclelab1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Feb 10 14:22:22 2022

Version 19.3.0.0.0

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

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

SQL> 

SQL> alter database open;

Database altered.

SQL> select name, open_mode from v$database;

NAME      OPEN_MODE

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

DB        READ WRITE

SQL>

[root@oraclelab1 ~]# locate alert_DB.log

/u01/app/oracle/diag/rdbms/db/DB/trace/alert_DB.log

[root@oraclelab1 ~]# tail -100f /u01/app/oracle/diag/rdbms/db/DB/trace/alert_DB.log

2022-02-10T13:59:57.358743+05:30

ALTER DATABASE OPEN

2022-02-10T13:59:57.427045+05:30

Errors in file /u01/app/oracle/diag/rdbms/db/DB/trace/DB_mz00_12260.trc:

ORA-01110: data file 3: '/u01/app/oracle/oradata/DB/datafile/o1_mf_sysaux_jy5yp1yv_.dbf'

ORA-01565: error in identifying file '/u01/app/oracle/oradata/DB/datafile/o1_mf_sysaux_jy5yp1yv_.dbf'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 7

2022-02-10T13:59:57.436879+05:30

Errors in file /u01/app/oracle/diag/rdbms/db/DB/trace/DB_dbw0_12211.trc:

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

ORA-01110: data file 3: '/u01/app/oracle/oradata/DB/datafile/o1_mf_sysaux_jy5yp1yv_.dbf'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 7

Ping without log force is disabled:

  instance mounted in exclusive mode.

2022-02-10T13:59:57.458755+05:30

Errors in file /u01/app/oracle/diag/rdbms/db/DB/trace/DB_ora_12258.trc:

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

ORA-01110: data file 3: '/u01/app/oracle/oradata/DB/datafile/o1_mf_sysaux_jy5yp1yv_.dbf'

2022-02-10T13:59:57.461572+05:30

Errors in file /u01/app/oracle/diag/rdbms/db/DB/trace/DB_ora_12258.trc:

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

ORA-01110: data file 3: '/u01/app/oracle/oradata/DB/datafile/o1_mf_sysaux_jy5yp1yv_.dbf'

ORA-1157 signalled during: ALTER DATABASE OPEN...

Checker run found 1 new persistent data failures

2022-02-10T14:00:26.229324+05:30

TMON (PID:12246): STARTING ARCH PROCESSES

Starting background process ARC0

2022-02-10T14:00:26.248838+05:30

ARC0 started with pid=38, OS id=12385

Starting background process ARC1

2022-02-10T14:00:26.263011+05:30

ARC1 started with pid=39, OS id=12387

Starting background process ARC2

Starting background process ARC3

2022-02-10T14:00:26.280617+05:30

ARC2 started with pid=40, OS id=12389

2022-02-10T14:00:26.297428+05:30

ARC3 started with pid=41, OS id=12391

TMON (PID:12246): ARC0: Archival started

TMON (PID:12246): ARC1: Archival started

TMON (PID:12246): ARC2: Archival started

SQL> select TABLESPACE_NAME,STATUS from dba_tablespaces;

TABLESPACE_NAME                STATUS

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

SYSTEM                         ONLINE

SYSAUX                         ONLINE

UNDOTBS1                       ONLINE

TEMP                           ONLINE

USERS                          ONLINE

SQL>

Try to add new datafile which will not allow:

SQL> alter tablespace SYSAUX add datafile'/u01/app/oracle/oradata/DB/datafile/sysaux01.dbf' size 1G autoextend on next 1024M;

alter tablespace SYSAUX add datafile'/u01/app/oracle/oradata/DB/datafile/sysaux01.dbf' size 1G autoextend on next 1024M

*

ERROR at line 1:

ORA-28369: cannot add files to encryption-ready tablespace when offline

SQL>

Drop the datafile:

SQL> alter database datafile 3 offline drop;

Database altered.

SQL>

SQL> SELECT tablespace_name, encrypted FROM dba_tablespaces;

TABLESPACE_NAME                ENC

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

SYSTEM                         NO

SYSAUX                         NO

UNDOTBS1                       NO

TEMP                           NO

USERS                          NO

SQL>

[root@oraclelab1 ~]# locate alert_DB.log

/u01/app/oracle/diag/rdbms/db/DB/trace/alert_DB.log

[root@oraclelab1 ~]# tail -100f /u01/app/oracle/diag/rdbms/db/DB/trace/alert_DB.log

2022-02-10T14:21:57.024476+05:30

alter database datafile 3 offline

Completed: alter database datafile 3 offline

2022-02-10T14:22:08.724551+05:30

Errors in file /u01/app/oracle/diag/rdbms/db/DB/trace/DB_mz00_14914.trc:

ORA-01110: data file 3: '/u01/app/oracle/oradata/DB/datafile/o1_mf_sysaux_jy5yp1yv_.dbf'

ORA-01565: error in identifying file '/u01/app/oracle/oradata/DB/datafile/o1_mf_sysaux_jy5yp1yv_.dbf'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 7

Checker run found 1 new persistent data failures

2022-02-10T14:22:28.298242+05:30

alter database open

2022-02-10T14:22:28.304166+05:30

Ping without log force is disabled:

  instance mounted in exclusive mode.

Endian type of dictionary set to little

2022-02-10T14:22:28.320603+05:30

Redo log for group 1, sequence 43 is not located on DAX storage

Thread 1 opened at log sequence 43

  Current log# 1 seq# 43 mem# 0: /u01/app/oracle/oradata/DB/onlinelog/o1_mf_1_jy5yqwtr_.log

  Current log# 1 seq# 43 mem# 1: /u01/app/oracle/fast_recovery_area/DB/DB/onlinelog/o1_mf_1_jy5yqygd_.log

Successful open of redo thread 1

2022-02-10T14:22:28.334462+05:30

MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set

stopping change tracking

Undo initialization recovery: err:0 start: 609920243 end: 609920247 diff: 4 ms (0.0 seconds)

[14937] Successfully onlined Undo Tablespace 2.

Undo initialization online undo segments: err:0 start: 609920247 end: 609920316 diff: 69 ms (0.1 seconds)

Undo initialization finished serial:0 start:609920243 end:609920324 diff:81 ms (0.1 seconds)

Verifying minimum file header compatibility for tablespace encryption..

Verifying file header compatibility for tablespace encryption completed for pdb 0

Database Characterset is AL32UTF8

No Resource Manager plan active

FastStart is disabled because SYSAUX was offline during DB startup.  Restart DB with SYSAUX online

joxcsys_required_dirobj_exists: directory object exists with required path /u01/app/oracle/product/19.0.0.0/dbhome_1/javavm/admin/, pid 14937 cid 0

replication_dependency_tracking turned off (no async multimaster replication found)

 XDB UNINITIALIZED: XDB$SCHEMA not accessible

2022-02-10T14:22:29.321862+05:30

Starting background process AQPC

2022-02-10T14:22:29.342739+05:30

AQPC started with pid=44, OS id=14948

Starting background process CJQ0

2022-02-10T14:22:29.657602+05:30

CJQ0 started with pid=46, OS id=14952

Completed: alter database open

2022-02-10T14:22:30.221550+05:30

ORA-376 encountered when generating server alert SMG-4120

QPI: opatch file present, opatch

QPI: qopiprep.bat file present

Unable to obtain current patch information due to error: 20001, ORA-20001: Latest xml inventory is not loaded into table

ORA-06512: at "SYS.DBMS_QOPATCH", line 2327

ORA-06512: at "SYS.DBMS_QOPATCH", line 854

ORA-06512: at "SYS.DBMS_QOPATCH", line 937

ORA-06510: PL/SQL: unhandled user-defined exception

ORA-06512: at "SYS.DBMS_QOPATCH", line 932

ORA-00376: file 3 cannot be read at this time

ORA-01110: data file 3: '/u01/app/oracle/oradata/DB/datafile/o1_mf_sysaux_jy5yp1yv_.dbf'

ORA-06512: at "SYS.DBMS_QOPATCH", line 918

ORA-06512: at "SYS.DBMS_QOPATCH", line 2286

ORA-06512: at "SYS.DBMS_QOPATCH", line 817

ORA-06512: at "SYS.DBMS_QOPATCH", line 2309

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

Dumping current patch information

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

Unable to obtain current patch information due to error: 20001

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

2022-02-10T14:24:02.226404+05:30

Errors in file /u01/app/oracle/diag/rdbms/db/DB/trace/DB_mz01_15142.trc:

ORA-01110: data file 3: '/u01/app/oracle/oradata/DB/datafile/o1_mf_sysaux_jy5yp1yv_.dbf'

ORA-01565: error in identifying file '/u01/app/oracle/oradata/DB/datafile/o1_mf_sysaux_jy5yp1yv_.dbf'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 7

2022-02-10T14:25:02.504644+05:30

Errors in file /u01/app/oracle/diag/rdbms/db/DB/trace/DB_mz01_15226.trc:

ORA-01110: data file 3: '/u01/app/oracle/oradata/DB/datafile/o1_mf_sysaux_jy5yp1yv_.dbf'

ORA-01565: error in identifying file '/u01/app/oracle/oradata/DB/datafile/o1_mf_sysaux_jy5yp1yv_.dbf'

Regards,

Mallik