Author: dbalifeeasy

ORA-00392 from ALTER DATABASE OPEN RESETLOGS

After restoring the database, the following errors appear when opening resetlogs:

SQL>  alter database open resetlogs;
 alter database open resetlogs
*
ERROR at line 1:
ORA-00392: log 2 of thread 1 is being cleared, operation not allowed
ORA-00312: online log 2 thread 1: '+DATAC1/TESTDB/ONLINELOG/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          0   52428800        512          1 YES CLEARING               1682491 26-JUL-21      1682509 26-JUL-21          0
         3          1          0   52428800        512          1 YES CLEARING               1682488 26-JUL-21      1682491 26-JUL-21          0
         2          1          0   52428800        512          1 NO  CLEARING_CURRENT       1682509 26-JUL-21   2.8147E+14                    0

CAUSE

For some reason, “alter database open resetlogs” is abnormally abrupted leaving the redo log status as CLEARING/CLEARING_CURRENT in control file.

SOLUTION

SQL> alter database clear unarchived logfile group 1 ;
Database altered.

SQL>alter database clear unarchived logfile group 2 ;
Database altered.

SQL>alter database clear unarchived logfile group 3 ;
Database altered.

SQL>alter database open resetlogs;
Database altered.

If the above method is not working , then need recreate the control file with RESETLOGS option.

SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS '/tmp/control_file.sql' resetlogs ;

Check the above control_file.sql to make sure all online redo logs directories exist.

SQL> STARTUP FORCE NOMOUNT
SQL> @/tmp/control_file.sql

controlfile created

SQL> RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL ;

Type <CANCEL> when prompted

Finally open database resetlogs successfully.

SQL> ALTER DATABASE OPEN RESETLOGS ;
Database altered.

What Ports are Used by Oracle ONS Resource

$ crsctl stat res ora.ons -p

NAME=ora.ons
TYPE=ora.ons.type
ACL=owner:grid:rwx,pgrp:oinstall:r-x,other::r--
ACTIONS=
ACTION_SCRIPT=
ACTION_TIMEOUT=60
AGENT_FILENAME=%CRS_HOME%/bin/oraagent%CRS_EXE_SUFFIX%
ALLOW_PUBLISH=
ALLOW_PUBLISH_NODES=1
ALLOW_UNSECURE_SUBSCRIBER=yes
AUTO_START=always
CHECK_INTERVAL=60
CHECK_TIMEOUT=0
CLEAN_TIMEOUT=60
CSS_CRITICAL=no
DEBUG_COMP=
DEBUG_FILE=
DELETE_TIMEOUT=60
DESCRIPTION=Oracle ONS resource
EM_PORT=2016
ENABLED=1
GEN_LAST_STARTED=racnode1_1626153918
GEN_ORACLE_CONFIG_HOME=
GEN_ORACLE_CONFIG_HOME@SERVERNAME(racnode1)=/u01/app/19.0.0.0/grid
GEN_ORACLE_CONFIG_HOME@SERVERNAME(racnode2)=/u01/app/19.0.0.0/grid
GEN_ORACLE_CONFIG_HOME@SERVERNAME(racnode3)=/u01/app/19.0.0.0/grid
GEN_ORACLE_CONFIG_HOME@SERVERNAME(racnode4)=/u01/app/19.0.0.0/grid
IGNORE_TARGET_ON_FAILURE=no
INSTANCE_FAILOVER=1
INTERMEDIATE_TIMEOUT=0
LOAD=1
LOCAL_PORT=6100
LOGGING_LEVEL=1
LOG_COMP=
LOG_FILE=
MEMBER_ID=0
MODIFY_TIMEOUT=60
NLS_LANG=
OFFLINE_CHECK_INTERVAL=0
ONS_CLIENTCLUSTER_NAME=
REMOTE_HOSTS=
REMOTE_PORT=6200
RESOURCE_GROUP=
RESTART_ATTEMPTS=3
RESTART_DELAY=0
SCRIPT_TIMEOUT=60
SERVER_CATEGORY=ora.hub.category
START_CONCURRENCY=0
START_DEPENDENCIES=hard(ora.net1.network) pullup(ora.net1.network)
START_TIMEOUT=300
STOP_CONCURRENCY=0
STOP_DEPENDENCIES=hard(intermediate:ora.net1.network)
STOP_TIMEOUT=300
TARGET_DEFAULT=default
TYPE_VERSION=2.1
UPTIME_THRESHOLD=1d
USER_WORKLOAD=no
USE_EVM=true
USE_SSL=1
USR_ORA_ENV=
WORKLOAD_CPU=0
WORKLOAD_CPU_CAP=0
WORKLOAD_MEMORY_MAX=0
WORKLOAD_MEMORY_TARGET=0

ORA-12919: Can not drop the default permanent tablespace

SQL> drop tablespace users including contents and datafiles;
drop tablespace users including contents and datafiles
*
ERROR at line 1:
ORA-12919: Can not drop the default permanent tablespace


SQL> alter database default tablespace user1;

Database altered.

SQL>  drop tablespace users including contents and datafiles;

Tablespace dropped.

USERNAME                       DEFAULT_TABLESPACE
------------------------------ ------------------------------
ORACLE_OCM                     USER1
OJVMSYS                        USER1
SYSKM                          USER1
XS$NULL                        USER1
GSMCATUSER                     USER1
MDDATA                         USER1
AUDSYS                         USER1
DIP                            USER1
SYSDG                          USER1
SPATIAL_CSW_ADMIN_USR          USER1
SPATIAL_WFS_ADMIN_USR          USER1
GSMUSER                        USER1
SYSBACKUP                      USER1
SYSTEM                         SYSTEM
SYS                            SYSTEM

How To Change SYS Password On ExaCC

Changing Oracle Database SYS user password is more complicated in Exacc Gen 2 environment.

The following should be considered, and takes action appropriately when changing SYS user password:

Subscribe to get access

Read more of this content when you subscribe today.

Subscribe