ORA-00205: error in identifying control file, check alert log for more info

 

As part of moving the control file from current ASM Diskgroup to new Diskgroup, I did the following;

1. Backup the current controlfile as copy and trace.

2. Backup the current control file

3. Reconfigure the controlfile location using ALTER SYSTEM

4. Restore the controlfile, it will create the controlfiles on the new diskgroup as given in step 3.

SQL> alter database mount; alter database mount *

ERROR at line 1:
ORA-00205: error in identifying control file, check alert log for more info

SQL> select * from v$controlfile; no rows selected

Cause:

Here, I have mistakenly given +DG_PERF_FLASH02 instead of +DG_PERF_FLASH_02

ALTER SYSTEM SET control_files='+DG_PERF_DATA_01','+DG_PERF_FLASH02' SCOPE=spfile sid='*';

It resulted as the control file restore failure

RMAN> RMAN> shutdown immediate; 2> 3> startup nomount; 4> 5> restore controlfile from '+DG_PERF_DATA_03/testDB/controlfile/current.486.839732817'; 6> 7> exit using target database control file instead of recovery catalog Oracle instance shut down connected to target database (not started) Oracle instance started Total System Global Area    2137886720 bytes Fixed Size                     2184304 bytes Variable Size               1476397968 bytes Database Buffers             654311424 bytes Redo Buffers                   4993024 bytes Starting restore at 20-FEB-14 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=216 device type=DISK RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of restore command at 02/20/2014 22:48:35 ORA-19504: failed to create file "+DG_PERF_FLASH02" ORA-17502: ksfdcre:4 Failed to create file +DG_PERF_FLASH02 ORA-15001: diskgroup "DG_PERF_FLASH02" does not exist or is not mounted ORA-15001: diskgroup "DG_PERF_FLASH02" does not exist or is not mounted ORA-19600: input file is control file  (+DG_PERF_DATA_01/testDB/controlfile/current.532.840062915) ORA-19601: output file is control file  (+DG_PERF_FLASH02) Recovery Manager complete. SQL*Plus: Release 11.2.0.3.0 Production on Thu Feb 20 22:48:35 2014 Copyright (c) 1982, 2011, Oracle.  All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options ALTER DATABASE MOUNT * ERROR at line 1: ORA-00205: error in identifying control file, check alert log for more info ALTER DATABASE OPEN * ERROR at line 1: ORA-01507: database not mounted SQL>

 

Resolution

Fixed the control_files parameter and restored the controlfile from the old ASM diskgroup.

ALTER SYSTEM SET control_files='+DG_PERF_DATA_01','+DG_PERF_FLASH_02' SCOPE=spfile sid='*';

ORA-00922: missing or invalid option on changing the user password

 

SQL> alter user SIVA identified by 1arkspur; alter user SIVA identified by 1arkspur * ERROR at line 1: ORA-00922: missing or invalid option

Solution:

Please try with double quotes for the password starting with number or having special characters

SQL> alter user SIVA identified by "1arkspur"; User altered.

Startup of the databases fail with ORA-00119, ORA-00132

 

Errors:

ORA-00119: invalid specification for system parameter REMOTE_LISTENER

ORA-00132: syntax error or unresolved network name 'usfsuad-scan.folsom.calwin.eds.com:60000'

SQL> startup mount ORA-00119: invalid specification for system parameter REMOTE_LISTENER ORA-00132: syntax error or unresolved network name ‘myDBsrvr-scan.siva.com:60000' SQL> exit

Resolution:

To fix the issue, make sure EZCONNECT is in effective sqlnet.ora:   NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

Oracle uses an easy connect naming method to register Oracle Instances in SCAN Listeners by default. Therefore EZCONNECT keyword should be present in sqlnet.ora file.

Alternative:

Change the remote_listener setting to a tnsnames resolvable name allows the database to  start.

Ex:  remote_listener='myDBsrvr-scan.siva.com:60000' to be changed to testDB.siva

References:

    11gR2 Database Fails to Start With Error ORA-00132: syntax error or unresolved network name 'scanname:1521' [ID 1318026.1]

    SCAN: ORA-00119, ORA-00132 Incorrect Value for REMOTE_LISTENER [ID 1254929.1]

ORA-00257: archiver is stuck. CONNECT INTERNAL only, until freed

ORA-00257: archiver is stuck. CONNECT INTERNAL only, until freed

Cause: The ARCH process received an error while trying to archive a redo log file. If the problem is not resolved soon, the database will stop executing transactions.
The most likely cause of this message is that the destination device is out of space to store the redo log file.

Action: Check the archiver trace file for a detailed description of the problem. Also, verify that the device specified in the initialization parameter ARCHIVE_LOG_DEST is set up properly for archiving.

ORA-00258: manual archiving in NOARCHIVELOG mode must identify log

 

Error: ORA-00258: manual archiving in NOARCHIVELOG mode must identify log

Cause: An attempt was made to archive a redo log file manually without specifying the sequence number, group number, or filename while the database was in NOARCHIVELOG mode.

Action: The name, group number, or thread and sequence number of redo log files must be specified to archive redo log files manually while the database is in NOARCHIVELOG mode.

Source: http://docs.oracle.com/cd/A58617_01/server.804/a58312/newch2a6.htm

How to find Primary Key of a table

 

SELECT cols.table_name, cols.column_name, cols.position, cons.status, cons.owner FROM all_constraints cons, all_cons_columns cols WHERE cols.table_name = 'TABLENAME' AND cons.constraint_type = 'P' AND cons.constraint_name = cols.constraint_name AND cons.owner = cols.owner AND cols.owner = 'sivaora' AND cons.owner = 'sivaora' ORDER BY cols.table_name, cols.position

This can be useful when getting error
ORA-00001: unique constraint (constraint_name) violated

ORA-00955: name is already used by an existing object

Cause: An attempt was made to create a database object (such as a table, view, cluster, index, or synonym) that already exists. A user's database objects must have distinct names.

Action: Enter a unique name for the database object or modify or drop the existing object so it can be reused.

Cross verify to find the existing object by the given name

SELECT   *   FROM   ALL_OBJECTS where Object_name like '%obj_name%'

ORA-00235 controlfile fixed table inconsistent due to concurrent update

Error: controlfile fixed table inconsistent due to concurrent update

---

Cause: Concurrent update activity on a controlfile caused a query on a controlfile fixed table to read inconsistent information.

Action: Retry the operation.

---

Reference:  Oracle Support [ID 48816.1]

ORA-16037: user requested cancel of managed recovery operation

 

Cause:
The managed standby database recovery operation has been canceled per user request.

Option 1:

When the command alter database recover managed standby database cancel issued

Option 2:

When the Primary database of Dataguard got shutdown.

Resolution:
           No action needed

EM Alert: target is broken, Suspended on Agent Unreachable

Error: EM Alert:Suspended on Agent Unreachable

Diagnosis
/app/oracle/product/em/agent11g/sysman/log/emagent.log

2011-07-27 16:12:34,737 Thread-4083678112 target {CLUSTERNAME, cluster} is broken: cannot compute dynamic properties in time. (00155)
2011-07-27 16:12:37,342 Thread-4099427232 target {+ASM1_nodename02.domain.com, osm_instance} is broken: cannot compute dynamic properties in time. (00155)
2011-07-27 16:14:04,597 Thread-4093127584 target {RACDB.DOMAIN.COM, rac_database} is broken: cannot compute dynamic properties in time. (00155)

/app/oracle/product/em/agent11g/sysman/log/emagent.trc

2011-07-29 12:03:35,253 Thread-3985230752 ERROR upload: 1 Failure(s) in a row or XML error for C0002000.xml, retcode = -2, we give up
2011-07-29 12:03:48,271 Thread-4035967904 ERROR pingManager: Did not receive valid response to ping "ERROR-Loader/Receiver disabled on this OMS"
2011-07-29 12:03:48,359 Thread-4035967904 ERROR pingManager: Did not receive valid response to ping "ERROR-Loader/Receiver disabled on this OMS"
2011-07-29 12:05:18,641 Thread-3994700704 ERROR pingManager: Did not receive valid response to ping "ERROR-Loader/Receiver disabled on this OMS"
2011-07-29 12:06:18,875 Thread-3985230752 ERROR pingManager: Did not receive valid response to ping "ERROR-Loader/Receiver disabled on this OMS"
2011-07-29 12:08:36,023 Thread-3985230752 WARN upload: FxferSend: received http error in header from repository: https://em.domain.com:1159/em/upload
ERROR-300|Loader/Receiver disabled on this OMS
2011-07-29 12:08:36,024 Thread-3985230752 ERROR upload: Failed to upload file C0002002.xml: HTTP error.
Response received: ERROR-300|Loader/Receiver disabled on this OMS
2011-07-29 12:08:36,175 Thread-3985230752 WARN upload: FxferSend: received http error in header from repository: https://em.domain.com:1159/em/upload
ERROR-300|Loader/Receiver disabled on this OMS

Solution:

Click the configure button and go through the steps without making any changes.

Target {database instance, oracle_database} is broken: cannot compute dynamic properties in time [ID 739993.1] which advised to change the dynamicPropsComputeTimeout property in the emd.properties

ORA-06512: at "DBSNMP.BSLN_INTERNAL", line 2073

 

Error Message on Trace file

*** SERVICE NAME:(SYS$USERS) 2011-07-24 00:00:18.201 *** MODULE NAME:(DBMS_SCHEDULER) 2011-07-24 00:00:18.201 *** ACTION NAME:(BSLN_MAINTAIN_STATS_JOB) 2011-07-24 00:00:18.201 ORA-12012: error on auto execute of job 11762 ORA-06502: PL/SQL: numeric or value error ORA-06512: at "DBSNMP.BSLN_INTERNAL", line 2073 ORA-06512: at line 1

Cause:

Table DBSNMP.BSLN_BASELINES contains inconsistent information. In this case, after database cloning, existing records in table "DBSNMP.BSLN_BASELINES" conflict with new baseline information inserted in the database.

The DBSNMP user needs to be dropped and re-created.

Solution:

SQL> @?/rdbms/admin/catnsnmp.sql -- drop user SQL> @?/rdbms/admin/catsnmp.sql -- create user

Reference: Oracle ID 736627.1

ORA-1652: unable to extend temp segment by 128 in tablespace TEMP

Error:
ORA-1652: unable to extend temp segment by 128 in tablespace TEMP Fri Jul 11 01:09:12 2010

What does that error means:
This error is fairly self explanatory - we cannot get enough space for a temporary segment. The size reported in the error message is the number of contiguous free Oracle blocks that cannot be found in the listed tablespace.

How to approach the ORA-1652 error

There are two views in the database that help keep track of the free space in the temporary tablespace: v$sort_usage and v$tempseg_usage (from 9i onwards).

Check the status of the sort segment utilization :

select TABLESPACE_NAME,TOTAL_BLOCKS,USED_BLOCKS,FREE_BLOCKS from v$sort_segment; TABLESPACE_NAME                 TOTAL_BLOCKS USED_BLOCKS FREE_BLOCKS ------------------------------- ------------ ----------- ----------- TEMP                                 1310592           0     1310592

If USED_BLOCKS = TOTAL_BLOCKS, find which user and statement is using the temporary sort segment by following;

How Do You Find Who And What SQL Is Using Temp Segments

SELECT a.username, a.sid, a.serial#, a.osuser, b.tablespace, b.blocks, c.sql_text FROM v$session a, v$tempseg_usage b, v$sqlarea c WHERE a.saddr = b.session_addr AND c.address= a.sql_address AND c.hash_value = a.sql_hash_value ORDER BY b.tablespace, b.blocks;

Resolution

There are two ways of solving this error:

1. Add more tempfiles, increase the size of the current ones or enable auto extend and the most important:

2. Tune the queries/statements so that the sort operations are done in memory and not on the disk.

ORA-21779: duration not active

SMON generated the following errors in the alert log file :

SMON: following errors trapped and ignored:
ORA-21779: duration not active

ORA-06512: at line 1
Drop transient type:  SYSTPfQMEpjI7QJ7gQ6wVAuBAng==ÿ��

As per Oracle : SMON: Following Errors Trapped And Ignored ORA-21779 [ID 988663.1]

Cause of the error:
This error is caused by SMON not able to clean up some transient types and this problem has been reported a few times in Oracle10gR2

Solution

Other then producing large trace files,this error has no impact,SMON is erroring whilst evaluating a SYSTEM trigger whilst executing cleanup of TYPEs which are no longer needed,so currently we have the following options:

Option 1 --Bounce the DB and see if the error is persistent
OR
Option 2--Also Dev in similar bugs suggested to set the following event at system level to delay the cleanup of these types:

alter system set events '22834 trace name context forever, level 1';

This do not affect database functionality and the only problem is the the alert.log is filling up with many error messages (also after restarting the instance the error should not occur again).

The command "alter system flush shared_pool" could also resolve the problem.

As transient types are created when compiling cursors into memory then it looks like a good idea to execute"alter system flush shared_pool" as this cannot create any problems.

Option 3: Drop the transient type

The existence of the Transient type can be verified by query;

SELECT * FROM dba_types WHERE type_name LIKE 'SYS%==' AND length(type_name) = 29

 

select 'drop type "'||s.username||'"."'||o.name||'" FORCE;' from obj$ o, type$ t, dba_users s where o.oid$ = t.tvoid and bitand(t.properties,8388608) = 8388608 and (sysdate-o.ctime) > 0.0007 and o.owner#=s.user_id

DROP TYPE "TRACS_USER"."SYSTPZd71k+XjoyfgQG8KQW8u9g=="

What is this transient type?

How was it created?

Why can't SMON drop it while I, a DBA user, can?

When selecting from a table function which has return type ANYDATASET oracle creates dynamically a type object which matches the actual return type. and sql will cast it automatically to this actual return type, no ANYDATASET output is returned by the select. this mechanism is introduced with oracle 10 in general the smon should cleanup those types.

ORA-00257: archiver error. Connect internal only, until freed.

 

Error:

ORA-00257: archive error, Connect internal only, until freed.
followed by
The database is not available due to the following conditions: Stuck Archiver
followed by
Warning: Disk group +DG_UAT_FLASH_01 is 92% used

*Cause: 
The archiver process received an error while trying to archive a redo log.  If the problem is not resolved soon, the database will stop executing transactions. The most likely cause of this message is the destination device is out of space to store the redo log file.

*Action: 
Check archiver trace file for a detailed description of the problem. Also verify that the device specified in the initialization  parameter  ARCHIVE_LOG_DEST is set up properly for archiving.

Method   1. Without increasing DB_RECOVERY_FILE_DEST_SIZE.

Approach 1: Move the archive log from the FRA to some other location to free up FRA

Step 1: Check whether the database is in archive log mode and automatic archiving is enabled

SQL> archive log list

Database log mode            Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     5559
Current log sequence           5561

Step 2:  As the archive destination is defined by USE_DB_RECOVERY_FILE_DEST, find the archive destination by

SQL> show parameter db_recovery_file_dest;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      +DG_UAT_FLASH_01
db_recovery_file_dest_size           big integer 110G

Note the value for db_recovery_file_dest_size. Here it is 11G.

Step 3:  Find the space used in flash recovery area by :

SQL> SELECT * FROM V$RECOVERY_FILE_DEST;

NAME
--------------------------------------------------------------------------------
SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES
----------- ---------- ----------------- ---------------
+DG_UAT_FLASH_01
1.1811E+11 3.0249E+10        2.7808E+10            1140

Step 4:  If SPACE_USED is equal to SPACE_LIMIT of db_recovery_file_dest, move the archive logs to different destination.

Step 5:  Archive all the log files

SQL> alter system archive log all;

Step 6:  Just switch the logs to verify:

SQL> alter system switch logfile;

Second Approach: Delete the Archive Log , when it is not needed to free up the DB_RECOVERY_FILE_DEST
Alternatively, we can solve this type of problem without increasing DB_RECOVERY_FILE_DEST_SIZE is to delete (archive log) files from DB_RECOVERY_FILE_DEST if we are sure we have backups and the archived logs are no longer necessary.

$rman target / RMAN>delete archivelog until time 'SYSDATE-1';

Method 2:  By increasing DB_RECOVERY_FILE_DEST_SIZE.

Step 1:  See the path of flash recovery area.

SQL> show parameter db_recovery_file_dest;

Step 2: Disable the Flash Recovery Area

SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='';

Step 3: Increase the Flash Recovery Area

SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 200g;

Step 4: Enable the Flash Recovery Area

SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST = ‘+DG_UAT_FLASH_01’;

ORA-00942: table or view does not exist or Warning: View altered with compilation errors.

Error:

SQL> ALTER VIEW HR.TMP_TABLE_REORG COMPILE; Warning: View altered with compilation errors.

How to find out the cause:

Use the query below to find the query which makes the View

SQL> set long 10000 SQL> set pages 0 SQL> select dbms_metadata.get_ddl('VIEW','TMP_TABLE_REORG','HR') from dual;

Output will look like;

SQL>  select "TABLE_NAME","COLUMN_NAME","CONSTRAINT_NAME","T_TABLE_NAME","START_TIME  ","END_TIME","RELOAD_START_TIME","RELOAD_END_TIME","OBJECT_SIZE_MB","T_F_CW02TMP _MB","N_REQ_CW02TMP_MB","PRE_ORIG_ROWCNT","PRE_TMP_ROWCNT","POST_ORIG_ROWCNT","
P  OST_TMP_ROWCNT" from REORG_USR.tmp_table_reorg;
OST_TMP_ROWCNT" from REORG_USR.tmp_table_reorg

Try to run the “Select statement” which makes the query. You will get error message as; It means that physical table that view depends on does not exist cause the issue.

ORA-00942: table or view does not exist

Error: ORA-04031

 

Error:
ORA-04031: unable to allocate 67280 bytes of shared memory ("large pool","unknown object","large pool","PX msg pool")

Cause:
An ORA-04031 error occurs in any of the memory pools in the SGA when Oracle cannot find a memory chunk large enough to satisfy an internal allocation request on behalf of a user's operation.

Resolution:

1. Increased the SGA_TARGET value.
2. Changed cursor_sharing parameter to SIMILAR
3. Recommended using bind variables instead of literals.

ORA-28031: maximum of 148 enabled roles exceeded

Error :

While connect to database as a normal user it fails with
ORA-28031: maximum of 148 enabled roles exceeded

Cause:

In a database at one time 148 user-defined roles can be enabled. Enabling more than 148 user-defined roles causes ORA-28031.

Resolution

At first time you can think about max_enabled_roles parameter but max_enabled_roles parameter has nothing to do if you enable 148 user-defined roles.

Possible that Data Import may also cause this.

In fact this parameter is deprecated. It is retained for backward compatibility only. You can see this parameter settings by,

SQL> show parameter max_enable
NAME TYPE VALUE
------------------------------------ --------------------------------- ------------------------------
max_enabled_roles integer 150

Though this parameter is set to 150 and you have enabled 148 user defined roles but additional user-defined roles can't be enabled because of the actual number of roles that users can enable is 148. So max_enabled_roles is 2 plus 148, because each user has two additional roles, PUBLIC and the user's own role.
As there is limit to 148 so you have to disable fewer roles or drop some roles and reorganize your database role policy.

You can enable or disable roles by using the SET ROLE statement for your current session. Also regroup the roles.