Redo log switch count per day

select to_char(first_time,'DD-MON') day,   

sum(decode(to_char(first_time,'hh24'),'00',1,0)) "00",   

sum(decode(to_char(first_time,'hh24'),'01',1,0)) "01",   

sum(decode(to_char(first_time,'hh24'),'02',1,0)) "02",   

sum(decode(to_char(first_time,'hh24'),'03',1,0)) "03",   

sum(decode(to_char(first_time,'hh24'),'04',1,0)) "04",   

sum(decode(to_char(first_time,'hh24'),'05',1,0)) "05",   

sum(decode(to_char(first_time,'hh24'),'06',1,0)) "06",   

sum(decode(to_char(first_time,'hh24'),'07',1,0)) "07",   

sum(decode(to_char(first_time,'hh24'),'08',1,0)) "08",   

sum(decode(to_char(first_time,'hh24'),'09',1,0)) "09",   

sum(decode(to_char(first_time,'hh24'),'10',1,0)) "10",   

sum(decode(to_char(first_time,'hh24'),'11',1,0)) "11",   

sum(decode(to_char(first_time,'hh24'),'12',1,0)) "12",   

sum(decode(to_char(first_time,'hh24'),'13',1,0)) "13",   

sum(decode(to_char(first_time,'hh24'),'14',1,0)) "14",   

sum(decode(to_char(first_time,'hh24'),'15',1,0)) "15",   

sum(decode(to_char(first_time,'hh24'),'16',1,0)) "16",   

sum(decode(to_char(first_time,'hh24'),'17',1,0)) "17",   

sum(decode(to_char(first_time,'hh24'),'18',1,0)) "18",   

sum(decode(to_char(first_time,'hh24'),'19',1,0)) "19",   

sum(decode(to_char(first_time,'hh24'),'20',1,0)) "20",   

sum(decode(to_char(first_time,'hh24'),'21',1,0)) "21",   

sum(decode(to_char(first_time,'hh24'),'22',1,0)) "22",   

sum(decode(to_char(first_time,'hh24'),'23',1,0)) "23",   

count(to_char(first_time,'MM-DD')) Switches_per_day   

from v$log_history   

where trunc(first_time) between trunc(sysdate) - 16 and trunc(sysdate)   

group by to_char(first_time,'DD-MON')    

order by to_char(first_time,'DD-MON') ; 

Which Tablespaces Do Not Have Enough Free Space

Below script find out which tablespaces don't have space in the database.

column  Today NEW_VALUE mhoy noprint format a1 trunc
column  Time NEW_VALUE mhora noprint format a1 trunc
column  inst NEW_VALUE minst noprint format a1 trunc

select upper(instance) inst
  from v$thread;

set pagesize 53
set linesize 120
set feedback off
ttitle left 'DB Report' -
       right mtoday skip 1 -
       left minst -
       right mtime skip 1 -
       center 'DATAFILES AND SIZES OF TABLESPACES' skip 3;
btitle skip 2 center 'Page :' format 999 sql.pno;

break on Tablespace on report
compute sum of KBTotal on report 
compute sum of KBUsed  on report
compute sum of KBFree  on report

column Tablespace format a12
column File_name  format a32
column status     format a10
column KBTotal    format 99,999,990
column KBUsed     format 99,999,990
column KBFree     format 99,999,990
column %Used      format 990.99
column %Free      format 990.99
column Extents    format 990
column MaxExtentKB format 999,999 

spool tbsp_free

SELECT TO_CHAR(sysdate, 'DD/MM/YY')   Today
     , TO_CHAR(sysdate, 'hh24:mi:ss') Time
     , df.tablespace_name          "Tablespace"
     , df.file_name                "File_name"
     , count(*)                    "Extents"
     , NVL(df.bytes,0)/1024        "KBTotal"
     , (NVL(df.bytes,0) - SUM(NVL(fs.bytes,0)))/1024 "KBUsed"
     , SUM(NVL(fs.bytes,0))/1024   "KBFree"
     , (((NVL(df.bytes,0) - SUM(NVL(fs.bytes,0)))/1024)*100)/((NVL(df.bytes,0)
/1024)) "%Used"
     , ((SUM(NVL(fs.bytes,0))/1024)*100) / (NVL(df.bytes,0)/1024)  "%Free"
     , MAX(NVL(fs.bytes,0))/1024   "MaxExtentKB"
  FROM dba_data_files df
     , dba_free_space fs
 WHERE df.file_id= fs.file_id(+)
 GROUP BY df.tablespace_name
        , df.file_name
        , df.bytes
 ORDER BY df.tablespace_name
/
spool off
ttitle off
btitle off

Redo generated during an Online/Hot Backup

Q) Why is excessive redo generated during an Online/Hot Backup?

A) The excessive redo generated is because of additional information logged into the online redo log during a hot backup the first time a block is modified in a tablespace that is in hot backup mode. In hot backup mode only two things are different:

+ The first time a block is changed in a datafile that is in hot backup mode, the ENTIRE BLOCK is written to the redo log files, not just the changed bytes. Normally only the changed bytes (a redo vector) is written. In hot backup mode, the entire block is logged the FIRST TIME. 

This is because we can get into a situation where the process copying the datafile and DBWR are working on the same block simultaneously. Lets say they are and the OS blocking read factor is 512bytes (the OS reads 512 bytes from disk at a time). The backup program goes to read an 8k Oracle block. The OS gives it 4k. Meanwhile -- DBWR has asked to rewrite this block. the OS schedules the DBWR write to occur right now. The entire 8k block is rewritten. 

The backup program starts running again (multi-tasking OS here) and reads the last 4k of the block. The backup program has now gotten an impossible block, i.e the head and tail are from two points in time. We cannot deal with that during recovery. Hence, We log the entire block image so that during recovery, this block is totally rewritten from redo and is consistent with itself at least. We can recover it from there.

+ The datafile headers which contain the SCN of the last completed checkpoint are NOT updated while a file is in hot backup mode. This lets the recovery process understand what archive redo log files might be needed to fully recover this file. To limit the effect of this additional logging, we should ensure we only place one tablepspace at a time in backup mode and bring the tablespace out of backup mode as soon as we have backed it up. This will reduce the number of blocks that may have to be logged to the minimum possible.

Please note, When we place a tablespace in hot backup mode, Oracle will log extra information for a block the first time it is modified whilst the tablespace it belongs to is in hot backup mode.
Say tablespace X containing file 55 is put into hot backup mode.

We modify block 123 in file 55, this generates redo. Oracle will log the ENTIRE block image instead of just changed bytes.

We perform a commit.

Someone else modifies blocks 123 and 124 in file 55. Oracle will log just changed bytes for block 123 but a full block image copy for 124.

Oracle works with database blocks in 2, 4, 8, 16, or 32k pieces. Consider 8k is what we are using.

Operating systems read and write files using their own chunk size, Say 512 bytes (1/2 k) for example.

So, We are copying a datafile, We are using the OS to perform that. We are reading it in 512 byte chunks.

We start reading block 55 in file 5 using "copy". We got 1/2 of the way through it (meaning we read 4k of data). We get pre-empted and along comes DBWR who decides to lay down a new version of that block on disk (checkpoint, flush dirty buffers). DBWR was lucky enough to write all 8k. Our copy picks back up and reads the next 4k of the block but it is 4k of the block at a totally different point in time.
This is known as a fractured block which happens frequently on a system with many users reading and writing the same file at the same time.

Now, Consider restoring this copy we have a block that is half at time a and half at time b, We cannot recover that block UNLESS we just happened to keep the full block image from the first change after starting the backup elsewhere which is what we did. So, we are able to recover this block using that image.
Note that if we use RMAN, this isn't an issue. RMAN is not affected by OS vs DB block sizes it knows how to read an Oracle datafile safely, with RMAN backups, we don't put a tablespace into backup mode, it is not necessary.

Lets say "block 5" in file "55" was backed up "fractured".

Further, We know the first time block 5 was modified AFTER the backup began we logged the modified image of block 5 in the redo log buffer (and hence into the online redo log files upon commit and hence into the archives upon log switch).

Now, We fail and need to restore this file 55. We go to the backups and we lay down this old copy of file 55. We start processing the archives/online redo logs to catch this file up. We hit the first change for block 5 after the backup was taken and get the entire block image for block 5. So, even if block 5 was totally corrupt on disk say fractured, binary zeros we have block 5 as it should be after that first update. We lay down that entire block and then apply the incremental updates from the arch/redo log files to catch it up.

End to End Application Tracing

The following needs to be done by someone having SYSDBA credentials, This will most likely be your DBA.

 

What is End to End Application Tracing

End to End Application Tracing becomes possible with the use of the CLIENT_IDENTIFIER attribute in V$SESSION dynamic performance view.  Once the system is set up to capture the login information by way of a trigger, this column can identify the client session through all application tiers to the database server.  Tracing based on the CLIENT_IDENTIFIER solves the problem of knowing which session to trace, especially if there are multiple applications accessing the same database instance.

 

Where to look for trace files

select value from v$parameter where name = 'user_dump_dest';

 

What to include in trace files

alter system set statistics_level = typical;

alter system set timed_statistics = true;

 

How to update the CLIENT_IDENTIFIER attribute in V$SESSION

 

The CLIENT_IDENTIFIER column in the V$SESSION dynamic performance view must to be updated with the client information attribute before it can be used for tracing and the best way to do this is to use a logon trigger.  The person having SYSDBA credentials has to create a logon trigger at the system level so that all subsequent logons are identified at the time connection is made to the database.  In the logon trigger the following function and stored procedures are utilized:

•             SYS_CONTEXT – Function returns the value of parameter associated with the context namespace in the current instance

•             DBMS_APPLICATION_INFO.SET_MODULE – Procedure sets the name of the module that is currently running to a new module

•             DBMS_SESSION.SET_IDENTIFIER – Procedure sets the client ID in the session

 

Here is an example of a logon trigger that identifies the Operating System user account (os_user), the user account on the database, and the name of the host from where the connection was made:

 

create or replace trigger database_logon_trigger
after logon
on database
    declare
        adtClientIdentifier varchar2(64);
    begin
        dbms_application_info.set_module(module_name => 'database_logon_trigger', action_name => 'select from dual');
        select sys_context('userenv', 'os_user') ||':'|| user ||':'|| sys_context('userenv', 'host') into adtClientIdentifier from dual;
        dbms_session.set_identifier(adtClientIdentifier );
        dbms_application_info.set_module(null,null);
    end;
/

 

Once the logon trigger is created, all subsequent logons will be identified using the above information. 

Note: There is no need to restart the database server as the database trigger is active upon creation.  If the database trigger was created while the application server was connected, the application server will have to be restarted so that the Client Identifier information can be captured upon connection.  As long as the database trigger is not dropped, this feature will be enabled indefinitely, and it poses no impact to performance.

The following query will show current connections with the required information to begin tracing:

select sid, serial#, username, client_identifier, service_name, module from v$session where username is not null order by 3,4,1;

 

SID         SERIAL#                USERNAME CLIENT_IDENTIFIER                SERVICE_NAME                     MODULE

515         33,469   TEST       TEST:TEST:NA\L1234      TESTDB.TESTORA.com  

1,265     22,825   TEST       TEST:TEST:NA\L1234      TESTDB.TESTORA.com   TOAD background query session

2,778     53,565   TEST       TEST:TEST:NA\L1234      TESTDB.TESTORA.com   TOAD 11.5.0.56

How to enable tracing

Tracing is enabled by using the CLIENT_ID_TRACE_ENABLE procedure in the DBMS_MONITOR package.  The value for the CLIENT_ID parameter is obtained from the V$SESSION view indicated above. 

execute dbms_monitor.client_id_trace_enable (client_id => '<client_identifier>', waits => TRUE, binds => TRUE);

 

Note: Tracing should be left on only long enough to capture the SQL needed for identifying the problem.  Do not leave it on for extended period of time as the trace file will become too large and cumbersome to use.

For example:

SQL> execute dbms_monitor.client_id_trace_enable (client_id => ' TEST:TEST:NA\L1234’, waits => TRUE, binds => TRUE);

PL/SQL procedure successfully completed

How to verify if tracing is enabled

      select trace_type, primary_id, waits, binds from dba_enabled_traces;

How to disable tracing

execute dbms_monitor.client_id_trace_disable (client_id => '<client_identifier>');

How does one merge all the trace files into a single file

    Oracle provides a utility called trcsess written in Java which merges the trace information into a single output file based on criteria.  The trcsess utility consolidates trace output from selected trace files based on several criteria:

•             Session ID – Consolidates the trace information for the session specified.  The session identifier is a combination of session index and session serial number, such as 21.2371.

 

•             Client ID – Consolidates the trace information given client ID.

•             Service name – Consolidates the trace information for the given service name.

Note: You can locate these values in the V$SESSION view.

Using a command prompt on Windows or a terminal window on UNIX/Linux, navigate to the user_dump_dest directory and issue the following command:

trcsess output=<output file name> clientid=<client_identifier> service=<service name> *.trc

Note:  The utility will look through all files having .trc file extension and extract only the content that is relevant to the client_identifier using the service name.

  For example:

> trcsess output=TEST_TRC.trc clientid= TEST:TEST:NA\L1234 service=SYS$USERS *.trc

How does one extract the SQL statements from the output file

Oracle provides a utility called TKPROF which formats the contents of the trace file and places the output into a readable output file.  The various formatting options available within TKPROF is beyond the scope of this document.  To extract the SQL statements, issue the following command using a command prompt on Windows or a terminal window on UNIX/Linux:

tkprof tracefile outputfile sys=no record=filename

For example:

> tkprof TEST_TRC.trc  TEST_tkprof.rpt sys=no record=TEST_sql.txt

 

When Starting the Database with Srvctl – ORA-29760

When starting a database  with srvctl,

srvctl start database -d test

the errors are returned:
PRCR-1079 : Failed to start resource ora.test.db
CRS-5017: The resource action "ora.test.db start" encountered the following error:
ORA-29760: instance_number parameter not specified
. For details refer to "(:CLSN00107:)" in "/oragi/product/11.2.0.2/grid/log/node1/agent/crsd/oraagent_oracle/oraagent_oracle.log".

PRCR-1079 : Failed to start resource ora.test.db
CRS-5017: The resource action "ora.test.db start" encountered the following error:
ORA-29760: instance_number parameter not specified
. For details refer to "(:CLSN00107:)" in "/oragi/product/11.2.0.2/grid/log/node2/agent/crsd/oraagent_oracle/oraagent_oracle.log".



Issue:
    

The database and instance names have been defined in lowercase in the cluster registry (OCR), which is why srvctl start database is being passed the argument 'test'.

However, in the spfile or pfile  the instance names have been defined in uppercase:

TEST1.instance_number=1
TEST2.instance_number=2

Solution:

Srvctl is case sensitive so you need to ensure that the instance and database definitions set in the spfile are the same case as those in the OCR and as are used in the srvctl commands. If they are not then either:

1. modify the spfile:
    SQL>create pfile from spfile;
    edit the pfile to alter all definitions to the correct case
    SQL>create spfile from pfile <path to pfile>;
or

2. modify the definitions in the OCR:
   srvctl remove instance -d test -i test1
   srvctl remove instance -d test -i test2
   srvctl remove database -d test
   srvctl add database -d TEST -o $ORACLE_HOME
   srvctl add instance -d TEST -i TEST1 -n <node1>
   srvctl add instance -d TEST -i TEST2 -n <node2>

NLS_CHARACTERSET is different from exporting NLS_CHARACTERSET

The following table gives a overview of often used NLS_CHARACTERSET combinations:

Source NLS_CHARACTERSET	Target NLS_CHARACTERSET	Corruption can happen?
NLS_CHARACTERSET is the same as target	NLS_CHARACTERSET is the same as source	No
US7ASCII	xx8ISO8859Px / xx8MSWIN12xx	No
US7ASCII	JA16xxxx / KO16xxxx / ZHS16xxxx / ZHT16xxxx	No
US7ASCII	AL32UTF8 / UTF8	No
xx8ISO8859Px	xx8MSWIN12xx	Yes, probability is very low.  NOT possible for WE8ISO8859P1 to WE8MSWIN1252
xx8MSWIN12xx	xx8ISO8859Px	Yes, probability is very low.
xx8ISO8859Px / xx8MSWIN12xx	AL32UTF8 / UTF8	Yes
JA16xxxx / KO16xxxx / ZHS16xxxx / ZHT16xxxx	AL32UTF8 / UTF8	Yes
AL32UTF8 / UTF8	xx8ISO8859Px / xx8MSWIN12xx /JA16xxxx, KO16xxxx / ZHS16xxxx / ZHT16xxxx	Yes
AL32UTF8	UTF8	Normally NO, Yes if you have "convertible" in csscan - wich is very rare.
UTF8	AL32UTF8	Normally NO, Yes if you have "convertible" in csscan - which is very rare.