Purging Oracle ADR (Automatic Diagnostic Repository) logs

 

When ADR logs become unexpectedly large, or too many (as in too many databases for one host), then manual purging is necessary. Following Scripts will be of handy during this time.

#!/bin/ksh # Purge ADR logs # Call oraenv first (or at least set $ORACLE_HOME) # -- Customize the following variables D1=1440    # to purge older than one day D5=7200    # to purge older than five days D7=10080   # to purge older than seven days DD=        # set to your ADR_BASE (Ex. /app/oracle) HP=        # set to your ADR_HOME (Ex. diag/rdbms/<dbname>/<instname>) $ORACLE_HOME/bin/adrci <<_EOF set base $DD set homepath $HP show homes purge -age $D7 -type alert purge -age $D7 -type incident purge -age $D7 -type trace purge -age $D7 -type cdump quit _EOF

Daily check on the database before business volume picks up

Session Count

SQL>     SELECT a.inst_id, COUNT (1) "Session Count"      FROM sys.gv_$session a     GROUP BY a.inst_id SQL> /    INST_ID Session Count ---------- -------------          1            67          2           105

Long Operation – Time Remaining is greater than Zero.

SQL> l     SELECT * FROM sys.gv_$session_longops     WHERE  TIME_REMAINING > 0

DBMS Jobs

SELECT job,        SUBSTR (what, 1, 30),        schema_user,        last_date,        next_date,        broken,        NVL2 (this_date, 'Y', 'N') running   FROM dba_jobs s ORDER BY schema_user, last_date

Buffer Gets

Replace the v.sql text with the frequent SQL statements important to your application.

SELECT v.inst_id,        ROUND (v.buffer_gets / v.executions, 1) avggets,        v.executions,        v.module,        SUBSTR (v.sql_text, 1, 60) querysql   FROM sys.gv_$sql v WHERE v.buffer_gets > 0    AND v.executions > 0    AND v.executions IS NOT NULL    AND DECODE (executions, 0, NULL, (v.buffer_gets / v.executions)) > 10    AND (v.sql_text LIKE 'FROM TBL_ABC%'      OR  v.sql_text LIKE 'FROM TBL_XYZ%')  ORDER BY v.inst_id, avggets DESC;

When to use SET DBID in RMAN

Usage of SET DBID in RMAN

You should only run the SET DBID command in the following specialized circumstances:

• You are not connected to a recovery catalog and want to restore the control file or server parameter file (SPFILE)
• You are connected to a recovery catalog want to restore the control file, but the database name is not unique in the recovery catalog.
• The server parameter (SPFILE) file is lost and you want to restore it.

Example:

The following shell script uses the DBID to restore the control file because multiple target databases share the same DB_NAME in the catalog. After you have restored the target control file, you can mount the database to restore the rest of the database:

#!/usr/bin/tcsh
rman TARGET / CATALOG rman/rman@catdb <<EOF
STARTUP FORCE NOMOUNT;
SET DBID = 862893450; # needed to distinguish target from others with same DB_NAME
RESTORE CONTROLFILE; # assuming catalog has automatic channel allocation information
ALTER DATABASE MOUNT;
EOF

If  you do not set the SET DBID clause, system will report error below;

 “RMAN-20005: target database name is ambiguous” .

How to generate AWR Report from OEM Grid

 

1. Choose the Database for which, AWR report to be generated

2. Choose Performance -> Snaphosts -> Automatic Workload Repository -> Run AWR Report

3. Select Beginning Snapshot, Go to time  -> Click Go -> Click Ok

4. Select Ending Snapshot   Go to time -> Click Go -> Click Ok

Now, you will be seeing on the screen "Processing View report....."

This how you will get the AUTOMATIC WORKLOAD REPOSITORY report for your Database

If you are looking to find SQL ID in past consumed much of the resource or caused performance issues, use Performance –> Top Activity –> Historical View , chose the time frame

HP Data Protector integration with Oracle Architecture

 

The components of this integration, as shown in Figure 8-1, are
SM The Data Protector Session Manager, which manages the backup and restore sessions.
MA The Data Protector General Media Agent, which reads and writes data from and to media
devices.
Data Protector MML The Data Protector Oracle Integration Media Management Library, which
is a set of routines that enables data transfer between the Oracle server and Data Protector. The
Data Protector MML links Data Protector and Oracle server software.
Ob2rman.pl The Data Protector Oracle Integration agent, which works with RMAN to manage
all aspects of the backup/recovery operations on the Oracle target database.
Backup API The Oracle-defined application programming interface.
IDB The Internal Database, where all the information about Data Protector sessions, including
session messages, objects, data, used devices, and media, is written.
RMAN The Oracle Recovery Manager.

Backup Process

When a backup is started with Data Protector, the following happens in the background:
1. Data Protector executes ob2rman.pl, which starts RMAN on the client and sends the
preconfigured RMAN script.
2. RMAN contacts the Oracle server, which contacts Data Protector via the MML interface
and initiates the backup.
3. During the backup session, the Oracle server reads data from the disk and sends it to
Data Protector for writing to the backup device.
4. Messages from the Data Protector backup session and messages generated by Oracle are
logged to the Data Protector database.

Using WinSCP in DOS Batch file to copy files from Unix Host to Windows Server

 

Here is the sample script I use to copy from Unix Host to Windows Host by DOS Batch will using WinSCP.

Main Script

@echo off del .\WinSCP_Copy.log /Q cd "D:\Documents and Settings\Siva\My Documents\SQL_Pack\WinSCP\D1_Copy.txt" /log="D:\Documents and Settings\Siva\My Documents\SQL_Pack\WinSCP\" cd D: call UnixBox_Copy.bat "C:\Program Files\WinSCP\WinSCP.exe" /console /script="D:\Documents and Settings\Siva\My Documents\SQL_Pack\WinSCP\UnixBox1_Copy_Script.txt" /log="D:\Documents and Settings\Siva\My Documents\SQL_Pack\WinSCP\WinSCP_Copy.log" @echo on

UnixBox_Copy.bat

set lan_dir=D:\Documents and Settings\Siva\My Documents\SQL_Pack\WinSCP set unix_dir1=Oracle_DB_Build set unix_dir2=Build_SubDir set uname=DBUser1 set pwd=1sJ8*&^! del .\UnixBox1_Copy_Script.txt  /Q   :: To execute the script "C:\Program Files\WinSCP\WinSCP.exe" /console /script="D:\Documents and Settings\Siva\My Documents\SQL_Pack\WinSCP\D1_Copy.txt" :: To switch to batch mode &  Automatically abort script on errors echo option batch abort                                              > .\UnixBox1_Copy_Script.txt                                                                  :: Disable overwrite confirmations that conflict with the previous echo option confirm off                                              >> .\UnixBox1_Copy_Script.txt                                                                 :: connect using a password echo open sftp://%uname%:%pwd%@unixDBsrvr1.production.siva.com      >> .\UnixBox1_Copy_Script.txt                                                        :: Change Local Directory cd "%lan_dir%" IF NOT EXIST "%unix_dir2%"  (mkdir "%lan_dir%\"%unix_dir2% ) ELSE ( GOTO _END ) echo lcd "%lan_dir%\%unix_dir2%" >> .\UnixBox1_Copy_Script.txt :: Force binary mode transfer echo option transfer binary                                              >> .\UnixBox1_Copy_Script.txt :: Download the file to local directory Y:\ echo get /app/oracle/Build_PRD/%unix_dir1%/%unix_dir2%/*.* -append       >> .\UnixBox1_Copy_Script.txt :: Disconnect echo close                                                                    >> .\UnixBox1_Copy_Script.txt :: Exit WinSCP echo exit                                                                     >> .\UnixBox1_Copy_Script.txt :: Exit WinSCP echo exit                                                                     >> .\UnixBox1_Copy_Script.txt :_END echo *** W A R N I N I N G *** Floder already exists pause ..

Sample UnixBox1_Copy_Script.txt, generated by  UnixBox_Copy.bat. The following Script is called on WinSCP as set of command to execute on WinSCP console.

option batch abort                                                                                                                option confirm off                                                                                                               open sftp://DBUser1:1sJ8*&^!@unixDBsrvr1.production.siva.com     lcd "D:\Documents and Settings\Siva\My Documents\SQL_Pack\WinSCP\Build_SubDir" option transfer binary                                              get /app/oracle/Build_PRD/Oracle_DB_Build/Build_SubDir/*.* –append     close                                                                    exit

How to register a database to use RMAN catalog using OEM Grid 11g

 

1. Login Oracle Enterprise Manager.
2. Select Target –> Chose the database
3. Go Availability tab and click "Recovery Catalog Settings" under Backup/Recovery Setup.
4. Choose "Use Recovery Catalog" option.
5. Click "Add Recovery Catalog" button to add recovery catalog.
6. And fill required fields such as host, port,sid, username, etc

 

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;

How to find top 10 largest sized tables in Oracle

col    owner format a15 col    segment_name format a30 col    segment_type format a15 col    gb format 999,999,999 select  owner ,    segment_name ,    segment_type ,    gb from    (     select    owner     ,    segment_name     ,    segment_type     ,    bytes / 1024 / 1024 / 1024 "gb"     from    dba_segments     where segment_type = 'TABLE'     order    by bytes desc     ) where    rownum < 10

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;

Query to check the database backup details

 

The query will return the backup status and timings of database backups -

Run the query run in the database, not on the catalog.

Login as sysdba -

This script will report on all backups – full, incremental and archivelog backups -

col STATUS format a9 col hrs format 999.99 select SESSION_KEY, INPUT_TYPE, STATUS, to_char(START_TIME,'mm/dd/yy hh24:mi') start_time, to_char(END_TIME,'mm/dd/yy hh24:mi')   end_time, elapsed_seconds/3600                   hrs from V$RMAN_BACKUP_JOB_DETAILS order by session_key;

Generate DDL to create user – reverse engineer

 

Query

SQL> SET long 200000000 SQL> SELECT dbms_metadata.get_ddl('USER','SIVA') from dual;

Output

DBMS_METADATA.GET_DDL('USER','SIVA') --------------------------------------------------------------------------------    CREATE USER "SIVA" IDENTIFIED BY VALUES 'S:1A6727A1D45324587FCFCFEAD63BBD96B 8DBBE0C6D1754259FE954EB9459;0FE66EDD5C4FF3D5'       DEFAULT TABLESPACE "DATA01"       TEMPORARY TABLESPACE "TEMP"

Query to determine which privileges, grants, roles, and tablespace quotas are granted to the users

 

SET lines 120 pages 100 select * from DBA_ROLE_PRIVS where GRANTEE = 'SIVA'; select * from DBA_SYS_PRIVS where GRANTEE = 'SIVA'; select * from DBA_TAB_PRIVS where GRANTEE = ‘SIVA’'; select * from  dba_ts_quotas where username = 'SIVA';

Query to find list of users not schemas

 

select username from dba_users minus select distinct owner from dba_objects ;

Oracle Query to find listing of Schema Owners

select distinct owner from dba_objects

In Oracle Schema defined as;

A schema is a collection of logical structures of data, or schema objects.
A schema is owned by a database user and has the same name as that user.

Oracle 11g Interactive Quick Reference guide

 

I just came across an essential Oracle 11g Interactive Quick Reference guide – which has the key dictionary views, background processes and architecture diagram.
Hope you may find it useful.  It can be downloaded from

http://www.oracle.com/webapps/dialogue/ns/dlgwelcome.jsp?p_ext=Y&p_dlg_id=9575302&src=7027600&Act=54

RMAN-06169: could not read file header for datafile nn error reason 4

 

RMAN-06169: could not read file header for datafile nn error reason 4
RMAN-06169: could not read file header for datafile nn error reason 4
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup command at 10/20/2011 07:41:54
RMAN-06056: could not access datafile nn

The reason code of 4 meant for DBWR could not find the file. After investigating found that the new data file added on locally for a RAC database. That data file not available on other node cause this backup failure.

To resolve this, the locally create data file moved to ASM.