One Can Succeed at Almost Anything For Which He Has Enthusiasm...

Friday, April 22, 2011

How full is the current redo log file?

Here is a query that can tell us how full the current redo log file is. This is useful when we need to predict when the next log file will be archived out.

SQL> SELECT le.leseq "Current log sequence No",
   100*cp.cpodr_bno/le.lesiz "Percent Full",
   cp.cpodr_bno "Current Block No",
   le.lesiz "Size of Log in Blocks"
   FROM x$kcccp cp, x$kccle le
   WHERE le.leseq =CP.cpodr_seq
   AND bitand(le.leflg,24) = 8 ;

Sample Output :
Current log sequence No Percent Full Current Block No Size of Log in Blocks
----------------------- ------------- ----------------- ---------------------
7 18.1982422 18635 102400

Enjoy   J J J

How to gather statistics on data dictionary objects in Oracle

Before Oracle database 10g ,Oracle explicitly recommended not to gather statistics on data dictionary objects . As of Oracle database 10g Oracle explicitly does recommend to gather statistics on data dictionary objects. As we might know, there is an automatically created SCHEDULER JOB in every 10g database which runs every night and checks for object which have either no statistics at all or for which the statistics have become STALE (which means stat at least 10% of the values have changed). This job is call GATHER_STATS_JOB and belongs to the autotask job class.

It uses a program which again call a procedure from built in package DBMS_STATS which does the statistics collection. This feature only works if the initialization parameter STATISTICS_LEVEL is set to TYPICAL at least (which is the DEFAULT in 10g) and it utilizes the TABLE MONITORING feature . TABLE MONITORING is enabled for all tables in 10g by DEFAULT.

One question may come in our mind that “Does this job also collect statistics on the data dictionary objects as well?” The answer is “YES, it does!” and here is the proof for this . First let us check if dbms_stats.gather_database_stats collect statistics for the data dictionary:

SQL> select count(*) from tab$;

COUNT(*)
--------------
1227
SQL> create table t2 (col1 number);
Table created.

SQL> select count(*) from tab$;

COUNT(*)
---------------
1228

SQL> select NUM_ROWS from dba_tables where table_name=’TAB$’;

NUM_ROWS
------------------
1213

SQL> exec dbms_stats.gather_database_stats;

PL/SQL procedure successfully completed.

SQL> select NUM_ROWS from dba_tables where table_name=’TAB$’;

NUM_ROWS
-------------------
1228

IT DOES! – and now let’s see if the job does also:

SQL> create table t3 (col1 number);

Table created.

SQL> create table t4 (col1 number);

Table created.

SQL> select NUM_ROWS from dba_tables where table_name=’TAB$’;

NUM_ROWS
--------------
1228

Now gather_stats_job run manually from DATABASE CONTROL !!!

SQL> select NUM_ROWS from dba_tables where table_name=’TAB$’;

NUM_ROWS
-----------------
1230

and IT ALSO DOES!

Even though there were not even 0.1% of the values changed it did! So when should we gather statistics for the data dictionary manually? Oracle recommends to collect them when a significant number of changes were applied to the data dictionary, like dropping significant numbers of part ions and creating new ones dropping tables, indexes, creating new ones and so on. But this only if it is a significant number of changes and we cannot wait for the next automatically scheduled job run.

Enjoy :-)

What is the difference between V$ and GV$, also V$ and V_$ ?

These “$” views are called dynamic performance views. They are continuously updated while a database is open and in use, and their contents relate primarily to performance. The actual dynamic performance views are identified by the prefix V_$ . Public synonyms for these views have the prefix V$ . We should access only the V$ objects, not the V_$ objects . Let's have look on the object type .
SQL>select owner, object_name, object_type from dba_objects where object_name like '%SESSION' and object_name like 'V%' ;

Output :

OWNER    OBJECT_NAME    OBJECT_TYPE
----------- --------------------- ------------------------
SYS V_$SESSION VIEW
SYS V_$HS_SESSION    VIEW
SYS V_$PX_SESSION    VIEW
SYS V_$DETACHED_SESSION VIEW
SYS V_$LOGMNR_SESSION VIEW
SYS V_$DATAPUMP_SESSION VIEW
SYS V$XS_SESSION    VIEW
PUBLIC    V$SESSION SYNONYM
PUBLIC    V$HS_SESSION    SYNONYM
PUBLIC    V$PX_SESSION    SYNONYM
PUBLIC    V$DETACHED_SESSION SYNONYM
PUBLIC    V$LOGMNR_SESSION SYNONYM
PUBLIC    V$DATAPUMP_SESSION SYNONYM
PUBLIC    V$XS_SESSION SYNONYM
Hence "v$xx" objects are synonym and "v$_xx" are the views.

Let's have a look on the difference between V$ view and GV$ views .

GV$ views are called 'Global Dymanic Performance views' and retrieve information about all started instances accessing one RAC database. In contrast, standard dynamic performance views (V$ views) retrieve information about the local instance only. For each of the V$ views available, there is a corresponding GV$ view except for a few exceptions. In addition to the V$ information, each GV$ view possesses an additional column name INST_ID. The INST_ID column displays the instance number from which the associated V$ view information is obtained.

GV$ views use a special form of parallel execution. The parallel execution coordinator runs on the instance that the client connects to and one slave is allocated in each instance to query the underlying V$ view for that instance.

Enjoy :-)

Track Redo Generation per hours and days

Here is the scripts for Tracking Redo Generation per Hours and by Days .

Track redo generation by day

SQL>select trunc(completion_time) rundate ,count(*) logswitch

,round((sum(blocks*block_size)/1024/1024)) “REDO PER DAY (MB)”

from v$archived_log group by trunc(completion_time) order by 1;

Sample Output :

RUNDATE LOGSWITCH REDO PER DAY (MB)

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

18-APR-11 2 1

19-APR-11 5 230

20-APR-11 36 1659

21-APR-11 14 175

22-APR-11 5 147

Track the Amount of Redo Generated per Hour :

SQL> SELECT Start_Date, Start_Time, Num_Logs,

Round(Num_Logs * (Vl.Bytes / (1024 * 1024)),2) AS Mbytes, Vdb.NAME AS Dbname

FROM (SELECT To_Char(Vlh.First_Time, 'YYYY-MM-DD') AS Start_Date,

To_Char(Vlh.First_Time, 'HH24') || ':00' AS Start_Time, COUNT(Vlh.Thread#) Num_Logs

FROM V$log_History Vlh

GROUP BY To_Char(Vlh.First_Time, 'YYYY-MM-DD'),

To_Char(Vlh.First_Time, 'HH24') || ':00') Log_Hist,

V$log Vl , V$database Vdb

WHERE Vl.Group# = 1

ORDER BY Log_Hist.Start_Date, Log_Hist.Start_Time;

Sample Output :

START_DATE START NUM_LOGS MBYTES DBNAME

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

2011-04-18 16:00 1 50 NOIDA

2011-04-18 17:00 2 100 NOIDA

2011-04-19 00:00 1 50 NOIDA

2011-04-19 09:00 1 50 NOIDA

2011-04-19 14:00 1 50 NOIDA

2011-04-19 20:00 1 50 NOIDA

2011-04-19 23:00 1 50 NOIDA

2011-04-20 06:00 1 50 NOIDA

2011-04-20 10:00 5 250 NOIDA

2011-04-20 11:00 8 400 NOIDA

2011-04-20 12:00 21 1050 NOIDA

2011-04-20 14:00 1 50 NOIDA

2011-04-21 09:00 1 50 NOIDA

2011-04-21 13:00 3 150 NOIDA

2011-04-21 15:00 1 50 NOIDA

2011-04-21 17:00 8 40 NOIDA

2011-04-21 22:00 1 50 NOIDA

2011-04-22 00:00 1 50 NOIDA

2011-04-22 05:00 1 50 NOIDA

2011-04-22 14:00 2 100 NOIDA

Enjoy :-)

Recovery from complete loss of all online redo log files using RMAN

The best practices dictates that to avoid such scenarios, we should be multiplexing the online redo log files. Each group should have at least 2 members and each member should be located on a different physical disk.

Check the number of total redologs files

SQL> select member from v$Logfile;

MEMBER

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

D:\ORACLE\ORADATA\NOIDA\REDO01.LOG

D:\ORACLE\ORADATA\NOIDA\REDO03.LOG

D:\ORACLE\ORADATA\NOIDA\REDO02.LOG

If one or all of the online redo logfiles are delete then the database hangs and in the alert log file we can see the following error message:

ORA-00313: open failed for members of log group 1 of thread 1

ORA-00312: online log 1 thread 1: 'D:\ORACLE\ORADATA\NOIDA\REDO01.LOG'

ORA-27041: unable to open file

OSD-04002: unable to open file

O/S-Error: (OS 2) The system cannot find the file specified

The file is missing at the operating system level.

Using RMAN we can recover from this error by restoring the database from the backup and recovering to the last available archived redo logfile.

SQL> select group#,sequence#,first_change#,status from v$log

GROUP# SEQUENCE# FIRST_CHANGE# STATUS

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

1 61 1997353 CURRENT

3 60 1955915 INACTIVE

2 59 1919750 INACTIVE

Shutdown the database

SQL> shut immediate

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

Total System Global Area 318046208 bytes

Fixed Size 1332920 bytes

Variable Size 255854920 bytes

Database Buffers 54525952 bytes

Redo Buffers 6332416 bytes

Database mounted.

SQL> exit

Using RMAN connect to the target database:

C:\>rman target sys/ramtech@noida

Recovery Manager: Release 11.1.0.6.0 - Production on Fri Apr 22 14:49:25 2011

connected to target database: NOIDA (DBID=1502483083, not open)

RMAN> restore database;

Starting restore at 22-APR-11

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_DISK_1: restoring datafile 00001 to D:\ORACLE\ORADATA\NOIDA\SYSTEM01.DBF

channel ORA_DISK_1: restoring datafile 00002 to D:\ORACLE\ORADATA\NOIDA\SYSAUX01.DBF

channel ORA_DISK_1: restoring datafile 00003 to D:\ORACLE\ORADATA\NOIDA\UNDOTBS01.DBF

channel ORA_DISK_1: restoring datafile 00004 to D:\ORACLE\ORADATA\NOIDA\USERS01.DBF

channel ORA_DISK_1: restoring datafile 00005 to D:\ORACLE\ORADATA\NOIDA\EXAMPLE01.DBF

channel ORA_DISK_1: restoring datafile 00006 to D:\ORACLE\ORADATA\NOIDA\TRANS.DBF

channel ORA_DISK_1: reading from backup piece D:\ORACLE\FLASH_RECOVERY_AREA\NOIDA\BACKUPSET\2011_04_21\O1_MF_NNNDF_TAG20110421T134444_6TZSWBRW_.BKP

channel ORA_DISK_1: piece handle=D:\ORACLE\FLASH_RECOVERY_AREA\NOIDA\BACKUPSET\2011_04_21\O1_MF_NNNDF_TAG20110421T134444_6TZSWBRW_.BKP tag=TAG20110421T134444

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:03:05

Finished restore at 22-APR-11

RMAN> recover database until sequence 61;

Starting recover at 22-APR-11

using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 47 is already on disk as file D:\ARCHIVE\ARC00047_0748802215.001

archived log for thread 1 with sequence 48 is already on disk as file D:\ARCHIVE\ARC00048_0748802215.001

archived log for thread 1 with sequence 49 is already on disk as file D:\ARCHIVE\ARC00049_0748802215.001

archived log for thread 1 with sequence 50 is already on disk as file D:\ARCHIVE\ARC00050_0748802215.001

archived log for thread 1 with sequence 51 is already on disk as file D:\ARCHIVE\ARC00051_0748802215.001

archived log for thread 1 with sequence 52 is already on disk as file D:\ARCHIVE\ARC00052_0748802215.001

archived log for thread 1 with sequence 53 is already on disk as file D:\ARCHIVE\ARC00053_0748802215.001

archived log for thread 1 with sequence 54 is already on disk as file D:\ARCHIVE\ARC00054_0748802215.001

archived log for thread 1 with sequence 55 is already on disk as file D:\ARCHIVE\ARC00055_0748802215.001

archived log for thread 1 with sequence 56 is already on disk as file D:\ARCHIVE\ARC00056_0748802215.001

archived log for thread 1 with sequence 57 is already on disk as file D:\ARCHIVE\ARC00057_0748802215.001

archived log for thread 1 with sequence 58 is already on disk as file D:\ARCHIVE\ARC00058_0748802215.001

archived log for thread 1 with sequence 59 is already on disk as file D:\ARCHIVE\ARC00059_0748802215.001

archived log for thread 1 with sequence 60 is already on disk as file D:\ARCHIVE\ARC00060_0748802215.001

archived log file name=D:\ARCHIVE\ARC00047_0748802215.001 thread=1 sequence=47

archived log file name=D:\ARCHIVE\ARC00048_0748802215.001 thread=1 sequence=48

archived log file name=D:\ARCHIVE\ARC00049_0748802215.001 thread=1 sequence=49

archived log file name=D:\ARCHIVE\ARC00050_0748802215.001 thread=1 sequence=50

archived log file name=D:\ARCHIVE\ARC00051_0748802215.001 thread=1 sequence=51

archived log file name=D:\ARCHIVE\ARC00052_0748802215.001 thread=1 sequence=52

archived log file name=D:\ARCHIVE\ARC00053_0748802215.001 thread=1 sequence=53

archived log file name=D:\ARCHIVE\ARC00054_0748802215.001 thread=1 sequence=54

archived log file name=D:\ARCHIVE\ARC00055_0748802215.001 thread=1 sequence=55

archived log file name=D:\ARCHIVE\ARC00056_0748802215.001 thread=1 sequence=56

archived log file name=D:\ARCHIVE\ARC00057_0748802215.001 thread=1 sequence=57

archived log file name=D:\ARCHIVE\ARC00058_0748802215.001 thread=1 sequence=58

archived log file name=D:\ARCHIVE\ARC00059_0748802215.001 thread=1 sequence=59

archived log file name=D:\ARCHIVE\ARC00060_0748802215.001 thread=1 sequence=60

media recovery complete, elapsed time: 00:02:01

Finished recover at 22-APR-11

RMAN> alter database open resetlogs;

database opened

RMAN>exit

The recovery process creates the online redo logfiles at the operating system level also.

Since we have done an incomplete recover with open resetlogs, we should take a fresh complete backup of the database.

NOTE: Please make sure you remove all the old archived logfiles from the archived area.

Enjoy JJJ