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.

RMAN-06445: cannot connect to recovery catalog after NOCATALOG has been used

 

Error: RMAN-06445: cannot connect to recovery catalog after NOCATALOG has been used

Cause:

1. We can not connect to a catalog, after we started RMAN with the NOCATALOG option.

2. If we executed some command that used control file to show the results, and after this if we try to connect to the catalog.

For example As, 'list backup summary;' and after this, We try 'connect catalog rman/rman@rmandb;', this return the RMAN-06445 error.

Alternatively use rman target / catalog rman/rmanpwd@rmandb;

RMAN-08138: WARNING: archived log not deleted - must create more backups

 

Error: RMAN-08138: WARNING: archived log not deleted - must create more backups
        archived log file

Resolution:
1. Connect to Catalog database'
2. CROSSCHECK BACKUP

Crosscheck command determine if files managed by RMAN, such as archived log, datafile copies and backup pieces still exist on disk or tape.

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

ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired ORA-06512: at

 

Error:

ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired ORA-06512: at "SIV.PKG1_CHG_DB_CNFG",

Investigation:
Find the locking sessions

select do.owner,do.object_name,do.object_type,dl.session_id,vs.serial#, vs.program,vs.machine,vs.osuser from dba_locks dl,dba_objects do,v$session vs where do.object_name ='SIV.PKG1_CHG_DB_CNFG' and do.object_type='TABLE' and dl.lock_id1 =do.object_id and vs.sid = dl.session_id;

Solution:
Kill the session locking the object then resubmit

alter system kill sessions 'sid,serial#' immediate;

ORA-38729: Not enough flashback database log data to do FLASHBACK.

Just check the oldest flashback time available>

select oldest_flashback_time from v$flashback_database_log;

Cause:

We need to have the required archive logs in the archive destination at the time of flashback.

Like, there could be a  backup script is archiving all the archivelogs and purging it from the ASM, hence, we cannot flashback with the error that it cannot locate the log files.
 
Resolution:
 
That is, if the flashback database command does not work in SQLPlus because a needed archive log was deleted after an RMAN backup, execute the flashback database command from RMAN. RMAN will flashback the database, restore the missing archive log(s) and apply the logs as needed.

Then 'alter database open resetlogs ;' is executed after the flashback database as normal.

 

Ref: https://support.oracle.com Document ID 560686.1.

OEM Collection Status : Disabled by Upload Manager

 

oracle@DBSRVR1:/app/oracle/product/11g/db_1/network/admin $ oemagent status Oracle Enterprise Manager 11g Release 1 Grid Control 11.1.0.1.0 Copyright (c) 1996, 2010 Oracle Corporation.  All rights reserved. --------------------------------------------------------------- Agent Version     : 11.1.0.1.0 OMS Version       : 11.1.0.1.0 Protocol Version  : 11.1.0.0.0 Agent Home        : /app/oracle/product/em/agent11g/DBSRVR1.production.siva.com Agent binaries    : /app/oracle/product/em/agent11g Agent Process ID  : 16164 Parent Process ID : 18735 Agent URL         : https://DBSRVR1.production.siva.com:3872/emd/main Repository URL    : https://GRIDCTLSRVR.siva.com:1159/em/upload Started at        : 2011-10-03 12:25:14 Started by user   : oracle Last Reload       : 2011-10-03 12:25:14 Last successful upload                       : 2011-10-05 18:32:35 Total Megabytes of XML files uploaded so far :  1400.06 Number of XML files pending upload           :      390 Size of XML files pending upload(MB)         :    51.97 Available disk space on upload filesystem    :    26.53% Collection Status                            : Disabled by Upload Manager Last attempted heartbeat to OMS              : 2011-10-05 20:44:10 Last successful heartbeat to OMS             : 2011-10-05 18:32:15 --------------------------------------------------------------- Agent is Running and Ready

Causes:

1. There are too many files backlogged
2. There is not enough free space on the directory
3. The size of the files backlogged is too large.
4. It could be of network issue between OEM Agent and OMS Server
   

Please check the emd.properties file to override the defaults that automatically disable collection

Disk group has used % of safely usable free space

OEM Alert Message looks like;

Message=Disk group DISGKGROUP_FLASH_01 has used 75.08% of safely usable free space. Metric=Disk Group Used % of Safely Usable Metric value=75.08 Disk Group Name=DISGKGROUP_FLASH_01 Severity=Warning Target Name=+ASM1_DBSRVR.siva.com Target type=Automatic Storage Management Host=dbsrvr.siva.com Occurred At=Sep 29, 2011 11:14:52 PM IST Acknowledged=No Notification Rule Name=DBSrvr ASM Health Check Notification Rule Owner=DBA

 

By default:
This metric will generate a warning alert if the Disk Group is using 90% of the Safely usable space and
Critical warning for 100 %. Threshold limit can be changed to generate alert at different values.

This metric is collected at a time interval of 15 minutes.

Metric Collection:
This metric is collected from the view V$ASM_DISKGROUP_STAT

Used % of Safely Usable = 100 - (usable_file_mb/usable_total_mb)*100 Where usable_total_mb = total_mb - required_mirror_free_mb)/redundancy_factor

total_mb and required_mirror_free_mb will come from the V$ASM_DISKGROUP_STAT  view column and

Redundancy Factor	Disk Group Type
1	External
2	Normal
3	High

Query:

select 100-(FREE_MB/TOTAL_MB)*100 as "Used % Safely Usable Area",  Name from v$asm_diskgroup_stat

Alternatively connect to ASM and check the free space;

ASMCMD> lsdg

Workaround:
If the Archive Log file occupies the ASM Disk group, move the files or delete files to free up Disk group.

Permanent Resolution:
New disks can be added in a disk group to avoid the alerts.
Go to the Disk Group general page ---> and click on add button to add a new disk to a disk group.

find: missing conjunction

$  find . -type d –name siva*

Resolution:  Use single quote on the string in –name clause

Ex:   $  find . -type d –name ‘siva*’

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-00980: synonym translation is no longer valid

 

Error:  ORA-00980: synonym translation is no longer valid

Cause:

1. Synonym created on non-existing object by mistake.

2. The synonyms referencing the object is dropped but synonym is not dropped

3. Dropped a user, but the synonyms referencing the objects owned by that dropped user.

Query to find the invalid synonyms

select * from dba_synonyms s where table_owner not in('SYSTEM','SYS') and db_link is null and not exists (select 1 from dba_objects o where s.table_owner=o.owner and s.table_name=o.object_name);

select 'drop '||decode (s.owner,'PUBLIC','PUBLIC SYNONYM ','SYNONYM'||s.owner||'.')||s.synonym_name||';' from dba_synonyms s where table_owner not in('SYSTEM','SYS') and db_link is null and not exists (select 1 from dba_objects o where s.table_owner=o.owner and s.table_name=o.object_name)

The query above generate the script to drop the synonyms whose translation is no longer valid

 

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

ORA-19511: Error received from media manager layer

 

RMAN Backup Failed with

Error Message:
ORA-19506: failed to create sequential file, name="<SYS00S_1846:751266012:1:33>.df", parms=""
ORA-27028: skgfqcre: sbtbackup returned error
ORA-19511: Error received from media manager layer, error text:
   Vendor specific error: OB2_StartObjectBackup() failed ERR(-17)

Cause:
The root cause of the backup failures was that the VLS ran out of tapes. 

Resolution:
Free up tapes from expired backups or add new tapes. 
Reboot the VLS to get the backups working again.