Sequence of steps performed when opening up a database

After issuing of SHUTDOWN ABORT command, when opening up database following step are performed

1. SPFile / PFile:             Server Parameter file ( SP File ) or the initialization parameter (initSID.ora / PFILE ) file is read
2. Memory :                      SGA is allocated
3. Background Process: Background process are started
4. Control File :                Control file is read
5. Redo Log File:              Redo Log file is read
6. Data File:                       Data files are checked for consistency
7. Instance Recovery:   Instance Recovery is started

ksh: =: is not an identifier

 

Error:  ksh: =: is not an identifier
-------------------------------------------------------------------------------------------
Cause:  Space between VariableName, = and Value

Resolution: Remove the space betwen Variablename, = and Value

Example

oracle@myDBSrvr1: export ORACLE_HOME = /app/oracle/product/11g/db_1 ksh: =: is not an identifier oracle@myDBSrvr1: export ORACLE_HOME=/app/oracle/product/11g/db_1

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]

OEM Grid Agent command to list it’s targets

Here is the command to find the target of an OEM Agent.

./emctl config agent listtargets

Here is the example of the command

oracle@dbsrvr1:/app/oracle/product/em/agent11g/bin $ ./emctl config agent listtargets Oracle Enterprise Manager 11g Release 1 Grid Control 11.1.0.1.0 Copyright (c) 1996, 2010 Oracle Corporation.  All rights reserved. [dbsrvr1.production.dorvin.org:3872, oracle_emd] [usrncwd, cluster] [LIS_RCATP_01_dbsrvr1.production.dorvin.org, oracle_listener] [LISTENER_dbsrvr1_dbsrvr1.production.dorvin.org, oracle_listener] [pla31p.dorvin_pla31p1, oracle_database] [son49p.dorvin, rac_database] [+ASM1_dbsrvr1.production.dorvin.org, osm_instance] [dbsrvr1.production.dorvin.org, host] [rcatp.dorvin, oracle_database]

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

How to find the Master node in RAC

 

The command is oracle@DB_Node2:/home/oracle $ ocrconfig –showbackup
Since, the OCR Automatic backups are taken only by master node. We can verify with following command.

DB_Node1     2011/08/07 22:50:25     /app/crs/product/11g/cdata/PRDDB_cluster/backup00.ocr DB_Node1     2011/08/07 18:50:24     /app/crs/product/11g/cdata/PRDDB_cluster/backup01.ocr DB_Node1     2011/08/07 14:50:24     /app/crs/product/11g/cdata/PRDDB_cluster/backup02.ocr DB_Node1     2011/08/06 06:50:16     /app/crs/product/11g/cdata/PRDDB_cluster/day.ocr DB_Node1     2011/07/26 06:48:45     /app/crs/product/11g/cdata/PRDDB_cluster/week.ocr

Here the Master node is DB_Node1

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

How to find what Shell being used on the server

 

siva@mydbsrvr1:/home/oracle $ echo $SHELL /usr/bin/ksh

You can find out what shell you are using by the command: echo $SHELL

Environment Variables in Unix
By prefixing $ to the variable name, you can evaluate it in any command

How to list hidden files in Unix

On ls command use the –a option.
-a Shows you all files, even files that are hidden (these files begin with a dot.)

ls -a

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

Flash Recovery Area – Size determination

Ideally, the flash recovery area should be large enough to hold a copy of all of your

1. data files
2. control files,
3. the online redo logs
4. the archived redo log files

needed to recover your database using the data file backups kept under your retention policy.
Setting the size of DB_FILE_RECOVERY_DEST_SIZE must be based on following factors.
1) your flash back retention target,
2) what all files you are storing in flashback and
3) if that includes backup then the retention policy for them or how often you move them to tape

Oracle Internal Space management mechanism for Falshback recovery area itself is designed in such way that if you define your db_recovery_file_dest_size and DB_FLASHBACK_RETENTION_TARGET at a optimal value, you wont need any further administration or management.

If a Flash Recovery Area is configured, then the database uses an internal algorithm to delete files from the Flash Recovery Area that are no longer needed because they are redundant, orphaned, and so forth. The backups with status OBSOLETE form a subset of the files deemed eligible for deletion by the disk quota rules.

When space is required in the Flash Recovery Area, then the following files are deleted:
a) Any backups which have become obsolete as per the retention policy.
b) Any files in the Flash Recovery Area which has been already backed up
to a tertiary device such as tape.
c) Flashback logs may be deleted from the Flash Recovery Area to make space available for other required files.

How to delete control file

 

Verify the file specification information for the current control file(s): SQL> show parameters control_files

If your are using pfile, change CONTROL_FILES to point to the remaining control files. Make sure that the full file specification is used. vi $ORACLE_HOME/dbs/init<SID>.ora. CONTROL_FILES = (/oradata/<sid>/data1/control_01.ctl, /oradata/<sid>/data3/control_02.ctl) If you are using spfile, you may modify the CONTROL_FILES parameter using the ALTER SYSTEM SET command. SQL> alter system set control_files = /oradata/<sid>/data1/control_01.ctl, /oradata/<sid>/data3/control_02.ctl scope=spfile;

Shutdown and startup the database.

Verify the file specification information for the current control file(s): SQL> show parameters control_files If desired, delete the control file from the O/S.

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.

Steps to set database on flashback mode in RAC for Oracle 11g

 

---- Check if flashback on?

SELECT flashback_on, log_mode FROM v$database;

--- Check for Flashback Parameters existing

set linesize 121 col name format a30 col value format a30 SELECT name, value FROM gv$parameter WHERE name LIKE '%flashback%';

---- Stop the RAC Database , it will stop all the instances of the DB

srvctl stop database -d sfodb

--- Start the DB in mount state

srvctl start instance -d sfodb -i sfo38u1 -o mount

-- Set the Flashback on

alter database flashback on;

--- Open the database

alter database open;

--- Start the remaining instances of the RAC

srvctl start instance -d sfodb -i sfo38u2 -o mount exclusive

-- Verify the flashback settings

select INST_ID,Name, FLASHBACK_ON from gv$database;

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’;

OEM OMS 10g Server Commands to start, stop and know the status (opmnctl)

Check status of the GRID management server

1) export ORACLE_HOME=/app/oracle/product/10g/oms10g/opmn 2) cd $ORACLE_HOME/bin 3) ./opmnctl status

oracle@omsrvrap$  export ORACLE_HOME=/app/oracle/product/10g/oms10g/opmn
oracle@omsrvrap$  cd $ORACLE_HOME/bin
oracle@omsrvrap$  opmnctl status

Processes in Instance: EnterpriseManager0.omsrvrap.dbosmsrvr.siva.com
-------------------+--------------------+---------+---------
ias-component      | process-type       |     pid | status 
-------------------+--------------------+---------+---------
LogLoader          | logloaderd         |     N/A | Down   
HTTP_Server        | HTTP_Server        |    9146 | Alive  
dcm-daemon         | dcm-daemon         |     N/A | Down   
OC4J               | home               |    9147 | Alive  
OC4J               | OC4J_EM            |    9148 | Alive  
OC4J               | OC4J_EMPROV        |    9150 | Alive  
OC4J               | OCMRepeater        |    9151 | Alive  
WebCache           | WebCache           |    9152 | Alive  
WebCache           | WebCacheAdmin      |    9153 | Alive  
DSA                | DSA                |     N/A | Down   

Stop the GRID management server

1) export ORACLE_HOME=/app/oracle/product/10g/oms10g/opmn 2) cd $ORACLE_HOME/bin 3) ./opmnctl stopall

oracle@omsrvrap$  export ORACLE_HOME=/app/oracle/product/10g/oms10g/opmn
oracle@omsrvrap$  cd $ORACLE_HOME/bin
oracle@omsrvrap$  opmnctl status

opmnctl: stopping opmn and all managed processes...

Start the GRID management server

1) export ORACLE_HOME=/app/oracle/product/10g/oms10g/opmn 2) cd $ORACLE_HOME/bin 3) ./opmnctl startall

oracle@omsrvrap$  export ORACLE_HOME=/app/oracle/product/10g/oms10g/opmn
oracle@omsrvrap$  cd $ORACLE_HOME/bin
oracle@omsrvrap$  opmnctl status

opmnctl: starting opmn and all managed processes...

OEM Grid 10g Agent commands to start, stop and know the status. ( emctl )

Oracle10g OEM GRID Agent ( emctl )Commands

Check status of the GRID control agent

1) export ORACLE_HOME=/app/oracle/product/10g/agent10g 2) cd $ORACLE_HOME/bin 3) ./emctl status agent

oracle@dbsrvr1$ export ORACLE_HOME=/app/oracle/product/10g/agent10g
oracle@dbsrvr1$ cd $ORACLE_HOME/bin
oracle@dbsrvr1$ ./emctl status agent
Oracle Enterprise Manager 10g Release 5 Grid Control 10.2.0.5.0.
Copyright (c) 1996, 2009 Oracle Corporation. All rights reserved.
---------------------------------------------------------------
Agent Version : 10.2.0.5.0
OMS Version : 10.2.0.5.0
Protocol Version : 10.2.0.5.0
Agent Home : /app/oracle/product/10g/agent10g
Agent binaries : /app/oracle/product/10g/agent10g
Agent Process ID : 20124
Parent Process ID : 20080
Agent URL : https://dbsrvr1.dbsrvr.siva.com:3872/emd/main/
Repository URL : https://hp6600ap.dbsrvr.siva.com:1159/em/upload
Started at : 2010-03-09 12:04:07
Started by user : oracle
Last Reload : 2010-03-09 12:08:39
Last successful upload : 2009-07-09 14:26:42
Total Megabytes of XML files uploaded so far : 291.89
Number of XML files pending upload : 0
Size of XML files pending upload(MB) : 0.00
Available disk space on upload filesystem : 35.13%
Last successful heartbeat to OMS : 2010-03-09 12:26:07
Agent is Running and Ready

STOP the GRID control agent

1) export ORACLE_HOME=/app/oracle/product/10g/agent10g 2) cd $ORACLE_HOME/bin 3) ./emctl stop agent

oracle@dbsrvr1$ export ORACLE_HOME=/app/oracle/product/10g/agent10g
oracle@dbsrvr1$ cd $ORACLE_HOME/bin
oracle@dbsrvr1$ ./emctl stop agent
Oracle Enterprise Manager 10g Release 5 Grid Control 10.2.0.5.0.
Copyright (c) 1996, 2009 Oracle Corporation. All rights reserved.
Stopping agent ... stopped.

START the GRID control agent

1) export ORACLE_HOME=/app/oracle/product/10g/agent10g 2) cd $ORACLE_HOME/bin 3) ./emctl start agent

.

oracle@dbsrvr1$ export ORACLE_HOME=/app/oracle/product/10g/agent10g
oracle@dbsrvr1$ cd $ORACLE_HOME/bin
oracle@dbsrvr1$ /emctl start agent
Oracle Enterprise Manager 10g Release 5 Grid Control 10.2.0.5.0.
Copyright (c) 1996, 2009 Oracle Corporation. All rights reserved.
Starting agent ................................ started.

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

 

OEM Agent Log Files

The OEM Agent files are placed on AGENT_HOME/sysman/log/ by default.

OEM Agent Log File	Management Agent log file	emagent.log
OEM Agent Trace File	Management Agent trace file	emagent.trc
OEM Agent startup log file	Management Agent startup log file	emagent.nohup