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.

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

ORA-28040: No matching authentication protocol

After Oracle database server upgraded from 11.2.0.4 to 12.2.0.1, client application connections throw the following errors:

at org.apache.commons.dbcp.BasicDataSource.createPoolableConnectionFactory(BasicDataSource.java:1549)
at org.apache.commons.dbcp.BasicDataSource.createDataSource(BasicDataSource.java:1388)
at org.apache.commons.dbcp.BasicDataSource.getConnection(BasicDataSource.java:1044)
at org.springframework.orm.hibernate3.LocalDataSourceConnectionProvider.getConnection(LocalDataSourceConnectionProvider.java:83)
at org.hibernate.jdbc.ConnectionManager.openConnection(ConnectionManager.java:446)
... 27 more
Caused by: java.sql.SQLException: ORA-28040: No matching authentication protocol

SOLUTION

Add the following line into sqlnet.ora file of ORACLE_HOME:

SQLNET.ALLOWED_LOGON_VERSION_SERVER=8

Then client application connects to new database working fine.

ORA-02421: missing or invalid schema authorization identifier

SQL>  alter session set current_schema='sysman';
 alter session set current_schema='sysman'
                                  *
ERROR at line 1:
ORA-02421: missing or invalid schema authorization identifier

The right syntax should be :

SQL>   alter session set current_schema=sysman;

Session altered.


Oracle Fails to Drop Unused Columns

When trying to drop an Oracle table unused columns, the sql command executes and shows successful, but actually the unused columns are not dropped.

SQL>select * from dba_UNUSED_COL_TABS where owner='TESTER' and table_name='TRANSACTIONS';

OWNE    TABLE_NAME           COUNT
------- -------------------- ----------
TESTER  TRANSACTIONS         1

SQL> ALTER TABLE TRANSACTIONS DROP UNUSED COLUMNS;

Table altered.

SQL>select * from dba_UNUSED_COL_TABS where owner='TESTER' and table_name='TRANSACTIONS';

OWNE    TABLE_NAME           COUNT
------- -------------------- ----------
TESTER  TRANSACTIONS         1

Further investigation shows the table is created with compression :

SQL>select OWNER, TABLE_NAME, COMPRESSION, COMPRESS_FOR from dba_tables where table_name='TRANSACTIONS';

OWNER    TABLE_NAME   COMPRESS  COMPRESS_FOR
-------- ------------ --------- -------------
TESTER    TRANSACTIONS ENABLED    ADVANCED

Change table to uncompressing status without moving data:

SQL>  alter table TRANSACTIONS NOCOMPRESS;

Table altered.

SQL> ALTER TABLE TRANSACTIONS  DROP UNUSED COLUMNS;
ALTER TABLE TRANSACTIONS DROP UNUSED COLUMNS
*
ERROR at line 1:
ORA-39726: unsupported add/drop column operation on compressed tables

Have to move table with nocompress option, then drop unused columns successfully.

SQL> alter table TRANSACTIONS move nocompress;

Table altered.

SQL> ALTER TABLE TRANSACTIONS  DROP UNUSED COLUMNS;

Table altered.

SQL>select * from dba_UNUSED_COL_TABS where owner='TESTER' and table_name='TRANSACTIONS';

no rows selected