ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance

Try to find which parameter is deprecated.

col NAME format a30;
col VALUE format a10;

SQL> select p.name,p.value from v$parameter p, v$spparameter s where s.name=p.name and p.isdeprecated='TRUE' and s.isspecified='TRUE';

NAME VALUE
------------------------------ ----------
sec_case_sensitive_logon FALSE


NOTE: This parameter is deprecated so no need anymore.

Now reset the parameter:

alter system reset sec_case_sensitive_logon scope=spfile;

SQL> shutdown immediate;

SQL> startup;

ORA-30013: Undo Tablespace ‘UNDOTBS1’ Is Currently In Use

You might get this error, when you tried to drop the undo table. Its because , there are some active transactions in the undo. The solution is to find that transaction and kill the same.

set pagesize 200 

set lines 200 

set long 999 col username for a9 

SQL>SELECT a.name,b.status , d.username , d.sid , d.serial# FROM v$rollname a,v$rollstat b, v$transaction c , v$session d WHERE a.usn = b.usn AND a.usn = c.xidusn AND c.ses_addr = d.saddr AND a.name IN ( SELECT segment_name FROM dba_segments WHERE tablespace_name = 'UNDOTBS1' );

NAME STATUS USERNAME SID SERIAL# 

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

_SYSSMU691$ PENDING OFFLINE SCOTT 20 30

Now kill this sessions:

alter system kill session '20,30' immediate; 

system altered

The KILL SESSION command doesn't actually kill the session. It merely asks the session to kill itself. In some situations, like waiting for a reply from a remote database or rolling back transactions, the session will not kill itself immediately and will wait for the current operation to complete. In these cases the session will have a status of "marked for kill". It will then be killed as soon as possible.

Check the status to confirm:

SELECT sid, serial#, status, username FROM v$session;

You could also use IMMEDIATE clause:

ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

The IMMEDIATE clause does not affect the work performed by the command, but it returns control back to the current session immediately, rather than waiting for confirmation of the kill. Have a look at Killing Oracle Sessions.

Update If you want to kill all the sessions, you could just prepare a small script.

SELECT 'ALTER SYSTEM KILL SESSION '''||sid||','||serial#||''' IMMEDIATE;' FROM v$session;

OR in one go

BEGIN
  FOR r IN (select sid,serial# from v$session where username='user')
  LOOP
      EXECUTE IMMEDIATE 'alter system kill session ''' || r.sid  || ',' 
        || r.serial# || ''' immediate';
  END LOOP;
END;

/

ORA-01194: file 1 needs more recovery to be consistent

Error Log

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: '/u01/app/oracle/oradata/RTS_NEW/system_new.dbf'

SOLUTION

[oracle@localhost ~]$ sqlplus / as sysdba

SQL> shutdown abort;

SQL> startup nomount;

SQL> alter database mount;

SQL> alter database open;

SQL> recover database using backup controlfile until cancel; 

ORA-01507: database not mounted

SQL> shutdown abort;

SQL> startup mount;

SQL> recover database using backup controlfile until cancel; 

... Specify log: {<RET>=suggested | filename | AUTO | CANCEL} AUTO

Above command will apply all the available archive logs automatically. Now try to open database with resetlogs:

SQL> alter database open resetlogs;

If the error persists due to insufficient archive logs, do the following workaround:

SQL> shutdown immediate 

ORA-01109: database not open 

Database dismounted. 

ORACLE instance shut down.

Startup database in mount mode:

SQL> startup mount 

ORACLE instance started. 

Total System Global Area 530288640 bytes 

Fixed Size 2131120 bytes 

Variable Size 310381392 bytes 

Database Buffers 209715200 bytes 

Redo Buffers 8060928 bytes 

Database mounted.

Change “_allow_resetlogs_corruption” parameter to TRUE and undo_management parameter to MANUAL:

SQL> ALTER SYSTEM SET "_allow_resetlogs_corruption"= TRUE SCOPE = SPFILE; 

SQL> ALTER SYSTEM SET undo_management=MANUAL SCOPE = SPFILE;

After doing above changes, shutdown database, and startup:

SQL> shutdown immediate 

ORA-01109: database not open Database dismounted. 

ORACLE instance shut down.

SQL> startup mount 

ORACLE instance started. 

Total System Global Area 530288640 bytes 

Fixed Size 2131120 bytes 

Variable Size 310381392 bytes 

Database Buffers 209715200 bytes 

Redo Buffers 8060928 bytes 

Database mounted.

Now try resetlogs:

SQL> alter database open resetlogs; 

Database altered.

SQL> shutdown immediate 

ORA-01109: database not open 

Database dismounted. 

ORACLE instance shut down.

SQL> startup

When database started successfully and up then perform the following steps
Create new undo tablespace and set “undo_tablespace” parameter to the new undo tablespace and change “undo_management” parameter to AUTO:

Undo space once allocated will be available for reuse but will not be deallocated to the OS. The best way to shrink Undo tablespace is to switch to a new Undo tablespace and drop the old Undo tablespace. 

The steps are:

Create a new undo tablespace of the same size (larger or smaller) depending on your database requirements.

SQL> show parameter control;

SQL> CREATE UNDO TABLESPACE undo datafile '/u01/app/oracle/oradata/sh/undo_df1.dbf' size 200m autoextend on maxsize 30G;

Tablespace created.

SQL> alter system set undo_tablespace = undo scope=spfile; 

System altered.

SQL> alter system set undo_management=auto scope=spfile; 

System altered.

Now bounce your database.

SQL> shutdown immediate 

SQL> startup

Cheers!! Database started successfully.

Check the status of the undo segments and determine if all the segments in the old undo tablespace are offline.

SQL> select tablespace_name, status, count(*) from dba_rollback_segs group by tablespace_name, status;

If there are Undo segments with a status other than OFFLINE in the tablespace to be dropped, we need to wait till they become OFFLINE. You may have to wait for the duration of the tuned_undoretention (from v$undostat) to ensure all Undo segments have become OFFLINE.

For example:

SQL> select status,segment_name from dba_rollback_segs where status not in ('OFFLINE') and tablespace_name='UNDO';

Bringing Rollback Segment ONLINE OR OFFLINE.

ALTER ROLLBACK SEGMENT UNDO3 ONLINE;

ALTER ROLLBACK SEGMENT UNDO3 OFFLINE;

If does change rollback segment to OFFLINE mode.

To change your database to NOARCHIVE mode, you can do the following:

ALTER SYSTEM SET LOG_ARCHIVE_START=FALSE SCOPE=SPFILE;

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE NOARCHIVELOG;
ALTER DATABASE OPEN;

SQL> select status,segment_name from dba_rollback_segs where status not in ('OFFLINE') and tablespace_name='UNDO';

At this point, you can do a ALTER ROLLBACK SEGMENT <segment_name> OFFLINE for each rollback segment you want offline and then do a ALTER ROLLBACK SEGMENT <segment_name> ONLINE for each rollback segment you want online.

To switch back to ARCHIVELOG mode, perform the following:

ALTER SYSTEM SET LOG_ARCHIVE_START=TRUE SCOPE=SPFILE;

SHUTDOWN IMMEDIATE;

STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;

Verify with ARCHIVE LOG LIST;

Archive any logs with ARCHIVE LOG ALL;

If all the Undo segments in the old Undo tablespace to the dropped is of status OFFLINE, then drop the tablespace.

SQL> select tablespace_name, status, count(*) from dba_rollback_segs group by tablespace_name, status;

Verify and then drop:

SQL> drop tablespace [tablespace_name] including contents and datafiles;

For example:

SQL> drop tablespace UNDOTBS1 including contents and datafiles;

If face below errors by taking expdp command.

ORA-39014: One or more workers have prematurely exited.
ORA-39029: worker 1 with process name "DW00" prematurely terminated
ORA-31671: Worker process DW00 had an unhandled exception.
ORA-00600: internal error code, arguments: [17183], [0x7FAD49A1E198], [], [], [], [], [], [], [], [], [], []

To solve the problem, the value of the open_cursors parameter should be increased and the value of the parameter “_optimizer_cost_based_transformation” should be set to off.

SQL> alter system set open_cursors=1024 scope=both sid='*';

System altered.

SQL> alter system set "_optimizer_cost_based_transformation"=off scope=both sid='*';

System altered.

If all above are not possible then

First

export data with exp command

[oracle@localhost ~]$ exp

then create dummy schema and test to import data in it.

 

[oracle@localhost ~]$ imp t/t@sh file=/u01/dumps/exp_sh_25092021.dmp statistics=none

ORA-01033: ORACLE Initialization or Shutdown in progress

CAUSE

when database was not shutdown properly for example hanged database or abnormal shutdown of server due to power etc.

SOLUTION

First Try:

[oracle@localhost ~]$ sqlplus / as sysdba

SQL> shutdown;
OR
SQL> shutdown immediate;

SQL> startup mount;

Then note the error massege and do accordingly.

SQL> alter database recover database until cancel;

if the above command fails try

SQL> recover database using backup controlfile until cancel;

on success type

SQL> ALTER DATABASE OPEN RESETLOGS;

Then stop and restart oracle service.

Second Try:

[oracle@localhost ~]$ sqlplus / as sysdba

SQL> shutdown abort;

SQL> startup nomount

SQL> alter database mount;

SQL> alter database open;

SQL> alter database open RESETLOGS;
alter database open RESETLOGS
*
ERROR at line 1:
ORA-01245: offline file 1 will be lost if RESETLOGS is done
ORA-01110: data file 1: '/u01/app/oracle/oradata/sr/system01.dbf'

RECOVER DATAFILE

SQL> alter database datafile '/u01/app/oracle/oradata/gh/system01.dbf' offline drop;

SQL> alter database datafile '/u01/app/oracle/oradata/gh/system01.dbf' online;

Database altered.

SQL> alter database open NORESETLOGS;

Database altered.

SQL> shutdown;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup;
ORACLE instance started.

Total System Global Area 2042241024 bytes
Fixed Size 1337548 bytes
Variable Size 553649972 bytes
Database Buffers 1476395008 bytes
Redo Buffers 10858496 bytes
Database mounted.
Database opened.

SQL>

ORA-39213: Metadata processing is not available OR ORA-39006: internal error

SOLUTION

Connect sql as sysdba

[oracle@localhost ~]$ sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Sun Sep 19 19:39:15 2021

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

Enter user-name: / as sysdba

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> SELECT comp_id, version, status FROM dba_registry;

SQL>  execute dbms_metadata_util.load_stylesheets

PL/SQL procedure successfully completed.

SQL> exit

[oracle@localhost ~]$