Navneet's Blog

Moving Database from one ASM group to Another

September 13, 2012 by Navneet

In this blog, I am going to demonstrate how to move database from one location to another within ASM.

This situation arises for me because I have renamed one database and now wanted to move it to location in ASM matching database name.

Database was renamed (Using NID utility) from “BR91DMO” to “BRDMO”. Now we need to move the database files to the new location in ASM as well.
Source Database Files Location: +DATA/br91dmo
Target Database Files Location: +DATA/brdmo

1. Shutdown Immediate
2. Startup Database in Mount State (Since My database is in No archive log mode).
3. Connect to RMAN using Target Database.
4. Backup Database as copy.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 2137886720 bytes
Fixed Size 2228200 bytes
Variable Size 1224736792 bytes
Database Buffers 905969664 bytes
Redo Buffers 4952064 bytes
Database mounted.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

Connect To RMAN to take Backup. Since I am going to have “BRDMO” (Same as database name) directory under “+DATA” diskgroup, I will use ‘+DATA’ as format for the Backup command. It will create directory with database name under DATA.

RMAN> connect target /

connected to target database: BRDMO (DBID=2852526159, not open)

RMAN> backup as copy database format '+DATA';

Starting backup at 2012-09-13:01:43:59
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=394 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=8 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=202 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=580 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00142 name=+DATA/br91dmo/datafile/psindex.dbf
channel ORA_DISK_2: starting datafile copy
input datafile file number=00003 name=+DATA/br91dmo/datafile/psundo01.dbf
channel ORA_DISK_3: starting datafile copy
input datafile file number=00140 name=+DATA/br91dmo/datafile/psimage.dbf
channel ORA_DISK_4: starting datafile copy
input datafile file number=00001 name=+DATA/br91dmo/datafile/system01.dbf
output file name=+DATA/brdmo/datafile/system.7589.793849447 tag=TAG20120913T014403 RECID=25 STAMP=793849519
channel ORA_DISK_4: datafile copy complete, elapsed time: 00:01:15
channel ORA_DISK_4: starting datafile copy
.
.
.

5. Now, modify your initfile/spfile to change the location of controlfiles to new location i.e from “br91dmo” directory to “brdmo” directory. Also change location for other Parameters like ‘ADUMP’ etc…and make sure to create the directories which are not on ASM.
6. Now We have to restore controlfile from old location to New location using RMAN.

RMAN> connect target /

connected to target database: BRDMO (not mounted)

RMAN> restore controlfile from '+data/br91dmo/controlfile/Current.8124.775838355';

Starting restore at 2012-09-13:01:58:14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=201 device type=DISK

channel ORA_DISK_1: copied control file copy
output file name=+DATA/brdmo/controlfile/control01.ctl
output file name=+FRA/brdmo/controlfile/control02.ctl
Finished restore at 2012-09-13:01:58:16

RMAN> exit

7. Mount the database.
8. Connect to Rman using Target database.
9. Now just to confirm Issue following command “List copy of database”. You should see the backup copies of all the datafiles under ‘+data/brdmo….’ Location.

RMAN> list copy of database;

using target database control file instead of recovery catalog
List of Datafile Copies
=======================

Key File S Completion Time Ckp SCN Ckp Time
------- ---- - ------------------- ---------- -------------------
25 1 A 2012-09-13:01:45:19 27190070 2012-09-13:01:28:00
 Name: +DATA/brdmo/datafile/system.7589.793849447
 Tag: TAG20120913T014403

1 1 A 2012-09-13:01:33:24 27190070 2012-09-13:01:28:00
 Name: /backup/BRDMO/BRDMO_20120913_793848681_211_1
 Tag: TAG20120913T013116

31 2 A 2012-09-13:01:46:38 27190070 2012-09-13:01:28:00
 Name: +DATA/brdmo/datafile/sysaux.6601.793849571
 Tag: TAG20120913T014403

4 2 A 2012-09-13:01:35:17 27190070 2012-09-13:01:28:00
 Name: /backup/BRDMO/BRDMO_20120913_793848872_213_1
 Tag: TAG20120913T013116

10. Now Switch the datafiles to Copy.

RMAN> switch database to copy;

datafile 1 switched to datafile copy "+DATA/brdmo/datafile/system.7589.793849447"
datafile 2 switched to datafile copy "+DATA/brdmo/datafile/sysaux.6601.793849571"
datafile 3 switched to datafile copy "+DATA/brdmo/datafile/psundo.7583.793849447"
datafile 4 switched to datafile copy "+DATA/brdmo/datafile/psdefault.7725.793849697"
datafile 5 switched to datafile copy "+DATA/brdmo/datafile/amapp.7720.793849637"
datafile 6 switched to datafile copy "+DATA/brdmo/datafile/amarch.7592.793849775"
datafile 7 switched to datafile copy "+DATA/brdmo/datafile/amlarge.7677.793849685"
datafile 8 switched to datafile copy "+DATA/brdmo/datafile/amwork.7604.793849679"
datafile 9 switched to datafile copy "+DATA/brdmo/datafile/apapp.7582.793849645"
datafile 10 switched to datafile copy "+DATA/brdmo/datafile/aparch.7584.793849769"
datafile 11 switched to datafile copy "+DATA/brdmo/datafile/aplarge.7700.793849659"
datafile 12 switched to datafile copy "+DATA/brdmo/datafile/apwork.7701.793849621"
datafile 13 switched to datafile copy "+DATA/brdmo/datafile/arapp.7615.793849705"
datafile 14 switched to datafile copy "+DATA/brdmo/datafile/ararch.7624.793849771"
datafile 15 switched to datafile copy "+DATA/brdmo/datafile/arlarge.757.793849703"
datafile 16 switched to datafile copy "+DATA/brdmo/datafile/arwork.7709.793849625"
datafile 17 switched to datafile copy "+DATA/brdmo/datafile/aucapp.7678.793849743"
datafile 18 switched to datafile copy "+DATA/brdmo/datafile/auclrge.960.793849775"
datafile 19 switched to datafile copy "+DATA/brdmo/datafile/bcapp.7749.793849775"
datafile 20 switched to datafile copy "+DATA/brdmo/datafile/bdapp.7676.793849757"
datafile 21 switched to datafile copy "+DATA/brdmo/datafile/bdlarge.7745.793849771"
datafile 22 switched to datafile copy "+DATA/brdmo/datafile/biapp.761.793849737"
datafile 23 switched to datafile copy "+DATA/brdmo/datafile/bilarge.7757.793849729"

11. Now Connect to sql plus and Open the database.
12. Check datafile location from V$datafile and make sure all the files are pointing to correct location.
13. Perform following steps to add temporary file. Since temp file is there in the old location, we have to add one more tempfile and then drop the old one.

SQL> select name from v$tempfile;

NAME
--------------------------------------------------------------------------------
+DATA/br91dmo/tempfile/pstemp01.dbf

SQL> alter tablespace pstemp add tempfile '+DATA/brdmo/tempfile/pstemp02.dbf' size 1G autoextend on;

Tablespace altered.

SQL> drop tablespace temp including contents and datafiles;

Tablespace dropped.

SQL> alter tablespace pstemp drop tempfile '+DATA/br91dmo/tempfile/pstemp01.dbf';

Tablespace altered.

SQL> select name from v$tempfile;

NAME
--------------------------------------------------------------------------------
+DATA/brdmo/tempfile/pstemp02.dbf

14. Now we are left with Redo Log files.
For this, I just added few more redo log groups as per my requirement in the New location and then dropped the old one.

And finally We are done……

Posted in ASM | Leave a Comment »

Snapshot Standby database

September 6, 2012 by Navneet

What is Snapshot Standby database (from Oracle Docs):

A snapshot standby database is a fully updatable standby database that is created by converting a physical standby database into a snapshot standby database. A snapshot standby database receives and archives, but does not apply, redo data from a primary database. Redo data received from the primary database is applied when a snapshot standby database is converted back into a physical standby database, after discarding all local updates to the snapshot standby database.

A snapshot standby database typically diverges from its primary database over time because redo data from the primary database is not applied as it is received. Local updates to the snapshot standby database will cause additional divergence. The data in the primary database is fully protected however, because a snapshot standby can be converted back into a physical standby database at any time, and the redo data received from the primary will then be applied.

A snapshot standby database provides disaster recovery and data protection benefits that are similar to those of a physical standby database. Snapshot standby databases are best used in scenarios where the benefit of having a temporary, updatable snapshot of the primary database justifies additional administrative complexity and increased time to recover from primary database failures.

Below is an example of How to convert Physical Standby Database into Snapshot Standby database.

1. Login to Standby database.

</pre>
TESTDB) (/app/oracle/product/11.1.0/db_1)

oracle@houoradb1 $ ss

SQL*Plus: Release 11.1.0.7.0 - Production on Fri Aug 31 16:18:48 2012

Copyright (c) 1982, 2008, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options
<pre>

2. Check if the Flashback on standby is Turned ON?

</pre>
SQL> set lines 10000

SQL> select flashback_on from v$database;

FLASHBACK_ON

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

NO
<pre>

Since we are using Snapshot Standby database feature, we dont need to Turn on Flashback as it will be taken care by the process only.

Check the Size for Flash Recovery Area and if not set, set it to appropriate Size. This will be used by Database to hold the Guaranteed Restore Point.

</pre>
SQL> show parameter db_recovery

NAME                                 TYPE        VALUE

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

db_recovery_file_dest                string      /dr/flash_recovery_area

db_recovery_file_dest_size           big integer 20G

SQL> archive log list

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            /arclog/TESTDB/arch

Oldest online log sequence     6661

Next log sequence to archive   0

Current log sequence           6663

QL> select open_mode from v$database;

OPEN_MODE

----------

MOUNTED

SQL> select PROCESS,CLIENT_PROCESS,THREAD#,SEQUENCE#,BLOCK# from v$managed_stand                                                                              by where process = 'MRP0' or client_process='LGWR';

PROCESS   CLIENT_P    THREAD#  SEQUENCE#     BLOCK#

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

RFS       LGWR              1       6663      40217

MRP0      N/A               1       6663      40216

SQL>  select NAME,SCN,TIME from v$restore_point;

no rows selected
<pre>

3. Convert the Database to Snapshot Standby database.

First of all cancel the Media Recovery.

</pre>
SQL> alter database recover managed standby database cancel;

Database altered.
<pre>

Convert the Standby database to Snapshot Standby database.

</pre>
SQL> alter database convert to snapshot standby;

Database altered.
<pre>

4. Shutdown the database

</pre>
SQL> shutdown immediate

ORA-01507: database not mounted

ORACLE instance shut down.
<pre>

5. Startup the database.

</pre>
SQL> startup mount

ORACLE instance started.

Total System Global Area 1068937216 bytes

Fixed Size                  2166536 bytes

Variable Size             515899640 bytes

Database Buffers          545259520 bytes

Redo Buffers                5611520 bytes

Database mounted.

SQL> select flashback_on from v$database;

FLASHBACK_ON

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

RESTORE POINT ONLY

SQL> select NAME,SCN,TIME from v$restore_point;

NAME                                                                           S                                                                              CN TIME

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

SNAPSHOT_STANDBY_REQUIRED_08/31/2012 16:22:12                                                                                                                 5.9743E+12 31-AUG-12 04.22.12.000000000 PM

SQL> alter database open;

Database altered.

SQL> select controlfile_type,open_mode from v$database;

CONTROL OPEN_MODE

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

CURRENT READ WRITE
<pre>

6. Do some work in Database ( Some changes.)


SQL> create user nupneja identified by nupneja;

User created.

SQL> grant connect,resource to nupneja;

Grant succeeded.

SQL> conn nupneja/nupneja

Connected.

SQL> create table snapshot_test as select * from all_tables;

Table created.

SQL> select count(*) from snapshot_test;

COUNT(*)

----------

104

SQL> conn / as sysdba

Connected.

SQL> archive log list

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            /arclog/TESTDB/arch

Oldest online log sequence     1

Next log sequence to archive   1

Current log sequence           1

SQL> show parameter db_recovery

NAME                                 TYPE        VALUE

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

db_recovery_file_dest                string      /dr/flash_recovery_area

db_recovery_file_dest_size           big integer 20G

SQL> alter system switch logfile;

System altered.

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64                                                                              bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

6. Now convert snapshot standby database to Physical Standby database


(TESTDB) (/arclog/TESTDB/arch)

oracle@houoradb1 $ ss

SQL*Plus: Release 11.1.0.7.0 - Production on Fri Aug 31 16:30:59 2012

Copyright (c) 1982, 2008, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> <strong>shutdown immediate</strong>

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> <strong>startup mount</strong>

ORACLE instance started.

Total System Global Area 1068937216 bytes

Fixed Size                  2166536 bytes

Variable Size             528482552 bytes

Database Buffers          532676608 bytes

Redo Buffers                5611520 bytes

Database mounted.

<strong>SQL> alter database convert to physical standby;</strong>

<strong> </strong>

Database altered.

<strong>SQL> shutdown immediate</strong>

ORA-01507: database not mounted

ORACLE instance shut down.

<strong>SQL> startup mount</strong>

ORACLE instance started.

Total System Global Area 1068937216 bytes

Fixed Size                  2166536 bytes

Variable Size             528482552 bytes

Database Buffers          532676608 bytes

Redo Buffers                5611520 bytes

Database mounted.

<strong>SQL></strong> <strong>select open_mode,controlfile_type ,flashback_on from v$database;</strong>

<strong> </strong>

OPEN_MODE  CONTROL FLASHBACK_ON

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

MOUNTED    STANDBY NO

<strong>SQL> select name,scn from v$restore_point;</strong>

no rows selected

7. Open standby database in Read only mode to check if the changes that we made are rolled back

</pre>
<table width="100%" border="1" cellspacing="0" cellpadding="0">
<tbody>
<tr>
<td valign="top">database recover managed standby database cancel;Database altered.
SQL> alter database opn read only;

alter database opn read only

*

ERROR at line 1:

ORA-02231: missing or invalid option to ALTER DATABASE

SQL> alter database open read only;

Database altered.

SQL> select username from dba_users where username='NUPNEJA';

no rows selected

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

Total System Global Area 1068937216 bytes

Fixed Size                  2166536 bytes

Variable Size             528482552 bytes

Database Buffers          532676608 bytes

Redo Buffers                5611520 bytes

Database mounted.
<pre>

You see all the changes have been discarded from the standby database. Now you can go ahead check the routine stuff like Logs are getting applied on Standby database as usual etc…

Hope the small demo will be helpful to others….

Thanks….

Posted in Dataguard | Leave a Comment »

SGA Current Resize Operations

March 15, 2012 by Navneet

</pre>
21:41:18 SYSTEM/bitst/BITST>set lines 10000
21:41:29 SYSTEM/bitst/BITST>column current_size format 999999
21:41:29 SYSTEM/bitst/BITST>column min_size format 9999999
21:41:29 SYSTEM/bitst/BITST>column max_size format 9999999
21:41:29 SYSTEM/bitst/BITST>
21:41:29 SYSTEM/bitst/BITST>select component,current_size/(1024*1024*1024) current_size ,min_size/(1
024*1024*1024) min_size ,max_size/(1024*1024*1024) max_size from v$sga_dynamic_components
21:41:29 2 /

COMPONENT CURRENT_SIZE MIN_SIZE MAX_SIZE
---------------------------------------------------------------- ------------ -------- --------
shared pool 3 3 3
large pool 0 0 0
java pool 0 0 0
streams pool 0 0 0
DEFAULT buffer cache 5 5 5
KEEP buffer cache 0 0 0
RECYCLE buffer cache 0 0 0
DEFAULT 2K buffer cache 0 0 0
DEFAULT 4K buffer cache 0 0 0
DEFAULT 8K buffer cache 0 0 0
DEFAULT 16K buffer cache 0 0 0

COMPONENT CURRENT_SIZE MIN_SIZE MAX_SIZE
---------------------------------------------------------------- ------------ -------- --------
DEFAULT 32K buffer cache 0 0 0
Shared IO Pool 0 0 0
ASM Buffer Cache 0 0 0

14 rows selected.
<pre>

Posted in Uncategorized | Leave a Comment »

Script to check the Parameter Value

March 15, 2012 by Navneet

</pre>
set lines 132
col name format a30
col val format a30
col isdefault format a5
col DESCRIPTION format a31 head 'Description|Update Comment'
col ses_mod format a11 head 'SessM|sysM'
col mod format a11 head 'Modifiable|Adjustable'
col adj format a5 head 'ADJ'
col update_comment format a30

select name,value val,isdefault,
 RPAD(ISSES_MODIFIABLE,10)||' '||rpad(ISSYS_MODIFIABLE,10) ses_mod,
 RPAD(ISMODIFIED,10)||' '||rpad(ISADJUSTED,10) mod,
 rpad(description,30)||' '||update_comment description
from v$parameter2
where name like lower('&name%')
order by 1
/
<pre>

Posted in Uncategorized | Leave a Comment »

Shrink Space in Table in Oracle

March 15, 2012 by Navneet

You use online segment shrink to reclaim fragmented free space below the high water mark in an Oracle Database segment. The benefits of segment shrink are these:

Compaction of data leads to better cache utilization, which in turn leads to better online transaction processing (OLTP) performance.

The compacted data requires fewer blocks to be scanned in full table scans, which in turns leads to better decision support system (DSS) performance.I am taking a simple example of a table in which there are lot of blocks allocated but in actuall if we check, data is less than the space consumed.

</pre>
19:40:44 NUPNEJA/fsprd/FSPRD1>select owner||'.'||table_name table_name,
19:41:37 2 blocks,
19:41:37 3 num_rows,
19:41:37 4 avg_row_len,
19:41:37 5 round(((blocks*8/1024)),2) "TOTAL_SIZE",
19:41:37 6 round((num_rows*avg_row_len/1024/1024),2) "ACTUAL_SIZE",
19:41:37 7 round(((blocks*8/1024)-(num_rows*avg_row_len/1024/1024)),2) "FRAGMENTED_SPACE"

19:41:37 8 from dba_tables
19:41:38 9 where owner='SYSADM'
19:41:38 10 and blocks > 10*(8*1024)
19:41:38 11 and table_name='PSPRCSPARMS'
19:41:38 12 order
19:41:38 13 by 7 desc
19:41:38 14 /

TABLE_NAME BLOCKS NUM_ROWS AVG_ROW_LEN TOTAL_SIZE(MB) ACTUAL_SIZE(MB)
------------------------------ ---------- ---------- ----------- -------------- ---------------
FRAGMENTED_SPACE(MB)
--------------------
SYSADM.PSPRCSPARMS 1421484 85507 344 11,105 28
11,077

1 row selected.
<pre>

So if you can see the result of above query, there is lot of space that is unused in the table.

Now, before we do Shrink of table, we need to enable Row movement of table so the if required rows with in the tables can be moved.

</pre>
17:14:11 NUPNEJA/fssup/FSSUP>ALTER TABLE PSPRCSPARMS ENABLE ROW MOVEMENT;

Table altered.
<pre>

Now we will do shrink of table, in two steps

1. we will first do Shrink compact —- This will not reset the High watermark for the table and it will also not lock the table for the duration compact shrink command will run. It will require lock on table only at last of this command when this will actually release the space. DML and other operations during this period will be allowed on the table except for a short period when there will be lock on the table.

2. Now we will run the command to shrink the table which will now not take much of a time to reset the high watermark as space is already been released.

</pre>
17:14:34 NUPNEJA/fssup/FSSUP>alter table SYSADM.PSPRCSPARMS shrink space compact;

Table altered.

17:30:19 NUPNEJA/fssup/FSSUP>alter table SYSADM.PSPRCSPARMS shrink space;

Table altered.
<pre>

After doing this, you need to gather the stats on the table so that DBA_TABLES view can be updated (BLOCKS,EMPTY_BLOCKS)

</pre>
17:32:26 NUPNEJA/fssup/FSSUP>SELECT BLOCKS FROM DBA_TABLES WHERE TABLE_NAME=UPPER('PSPRCSPARMS');

BLOCKS
----------
4370

1 row selected.

17:32:34 NUPNEJA/fssup/FSSUP>SELECT BLOCKS
17:32:58 2 FROM DBA_SEGMENTS
17:32:58 3 WHERE OWNER=UPPER('SYSADM') AND SEGMENT_NAME = UPPER('PSPRCSPARMS');

BLOCKS
----------
4456

1 row selected.
<pre>

Disable the row movement.

</pre>
17:33:00 NUPNEJA/fssup/FSSUP>alter table PSPRCSPARMS disable row movement;

Table altered.
<pre>

I also checked for the status of Indexes on the table after this operation which shows index was valid.


17:33:56 NUPNEJA/fssup/FSSUP>/

INDEX_NAME TABLE_NAME STATUS FUNCIDX_ INDEX_TYPE
 ------------------------------ ------------------------------ -------- -------- --------------------
 PS_PSPRCSPARMS PSPRCSPARMS VALID NORMAL

1 row selected.
<pre>

Thanks…..

Posted in Uncategorized | Leave a Comment »

Calculating number of archive logs/log switches per hour

September 16, 2010 by Navneet

Today came a requirement to see how frequently log switch is taking place and how much of archive logs are being generated and at what rate…

I certainly went through the alert log first and found it too be very time exauhsting…so then looked for the better option..

Then came accross the view V$LOG_HISTORY…and it did the job for me…

Used the following query to fetch the number of archive logs created hourly..

  SELECT * FROM (
SELECT * FROM (
SELECT   TO_CHAR(FIRST_TIME, 'DD/MON') AS "DAY",
 TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '00', 1, 0)), '99') "00:00 hr"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '01', 1, 0)), '99') "01:00 hr"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '02', 1, 0)), '99') "02:00 hr"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '03', 1, 0)), '99') "03:00 hr"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '04', 1, 0)), '99') "04:00 hr"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '05', 1, 0)), '99') "05:00 hr"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '06', 1, 0)), '99') "06:00 hr"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '07', 1, 0)), '99') "07:00 hr"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '08', 1, 0)), '99') "08:00 hr"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '09', 1, 0)), '99') "09:00 hr"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '10', 1, 0)), '99') "10:00 hr"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '11', 1, 0)), '99') "11:00 hr"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '12', 1, 0)), '99') "12:00 hr"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '13', 1, 0)), '99') "13:00 hr"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '14', 1, 0)), '99') "14:00 hr"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '15', 1, 0)), '99') "15:00 hr"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '16', 1, 0)), '99') "16:00 hr"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '17', 1, 0)), '99') "17:00 hr"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '18', 1, 0)), '99') "18:00 hr"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '19', 1, 0)), '99') "19:00 hr"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '20', 1, 0)), '99') "20:00 hr"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '21', 1, 0)), '99') "21:00 hr"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '22', 1, 0)), '99') "22:00 hr"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '23', 1, 0)), '99') "23:00 hr"
FROM V$LOG_HISTORY
WHERE extract(year FROM FIRST_TIME) = extract(year FROM sysdate)
GROUP BY TO_CHAR(FIRST_TIME, 'DD/MON')
) ORDER BY TO_DATE(extract(year FROM sysdate) || DAY, 'YYYY DD/MON') DESC
) WHERE ROWNUM < 8
11:08:45 SYSTEM>
11:08:45 SYSTEM>/

DAY           00:00 hr    01:00 hr    02:00 hr    03:00 hr    04:00 hr    05:00 hr    06:00 hr    07:00 hr    08:00 hr    09:00 hr    10:00 hr    11:00 hr    12:00 hr    13:00 hr    14:00 hr    15:00 hr    16:00 hr    17:00 hr    18:00 hr    19:00 hr    20:00 hr    21:00 hr    22:00 hr    23:00 hr
---------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
16/SEP              19           0           0           0           0           0           0        0      0           0           0           0           0           0           0           0           0           0           0           0           0           0           0           0
15/SEP              26          20          24          34          18           5          15       10     19           9          14           5           7           4           6           7          14           4           7           8           6          18          16          20
14/SEP              26          25          22          37          16           5          17       10     41          15          13           6          22          11           9           7          14           4           8           7           6          18          15          20
13/SEP              27          22          21          37          19           8          18       12     21          13          16           7          10          11           9           8          30           6           8           7           6          10           9          20
12/SEP              20          19          16          35          16           4          13        7     20           3          10           2           3           2           1           2          10           2           4          10          11           6           7          18
11/SEP              27          19          17          32          14           3          11        5     16           2          12           2           4           2           4           3          10           3           6           6           7           3           9          15
10/SEP              22          16          24          37          18           5          19       11     23          11          21          22           8           7           7           7          12           3           8           7           4           7           6          16

7 rows selected.

Hope it helps…

There is another one for the same cause but rather than hourly it will show the result day wise..

11:08:49 SYSTEM>
11:11:44 SYSTEM>col c1 format a10 heading "Month"
11:11:44 SYSTEM>col c2 format a25 heading "Archive Date"
11:11:44 SYSTEM>col c3 format 999 heading "Switches"
11:11:44 SYSTEM>
11:11:44 SYSTEM>compute AVG of C on A
11:11:44 SYSTEM>compute AVG of C on REPORT
11:11:44 SYSTEM>
11:11:44 SYSTEM>break on A skip 1 on REPORT skip 1
11:11:44 SYSTEM>
11:11:44 SYSTEM>select
11:11:44        to_char(trunc(first_time), 'Month') c1,
11:11:44        to_char(trunc(first_time), 'Day : DD-Mon-YYYY') c2,
11:11:44        count(*) c3
11:11:44     from
11:11:44        v$log_history
11:11:44     where
11:11:44        trunc(first_time) > last_day(sysdate-100) +1
11:11:44     group by
11:11:44       trunc(first_time);

Month      Archive Date              Switches
---------- ------------------------- --------
July       Friday    : 02-Jul-2010        234
July       Saturday  : 03-Jul-2010        214
July       Sunday    : 04-Jul-2010        184
July       Monday    : 05-Jul-2010        234
July       Tuesday   : 06-Jul-2010        303
July       Wednesday : 07-Jul-2010        312
July       Thursday  : 08-Jul-2010        341
July       Friday    : 09-Jul-2010        284
July       Saturday  : 10-Jul-2010        210
July       Sunday    : 11-Jul-2010        192
July       Monday    : 12-Jul-2010        313
July       Tuesday   : 13-Jul-2010        327
July       Wednesday : 14-Jul-2010        330
July       Thursday  : 15-Jul-2010        280
July       Friday    : 16-Jul-2010        239
July       Saturday  : 17-Jul-2010        228
July       Sunday    : 18-Jul-2010        197
July       Monday    : 19-Jul-2010        253
July       Tuesday   : 20-Jul-2010        283
July       Wednesday : 21-Jul-2010        285
July       Thursday  : 22-Jul-2010        269
July       Friday    : 23-Jul-2010        270
July       Saturday  : 24-Jul-2010        232
July       Sunday    : 25-Jul-2010        198
July       Monday    : 26-Jul-2010        276
July       Tuesday   : 27-Jul-2010        325
July       Wednesday : 28-Jul-2010        290
July       Thursday  : 29-Jul-2010        290
July       Friday    : 30-Jul-2010        268
July       Saturday  : 31-Jul-2010        311
August     Sunday    : 01-Aug-2010        213
August     Monday    : 02-Aug-2010        278
August     Tuesday   : 03-Aug-2010        327
August     Wednesday : 04-Aug-2010        323
August     Thursday  : 05-Aug-2010        315
August     Friday    : 06-Aug-2010        311
August     Saturday  : 07-Aug-2010        244
August     Sunday    : 08-Aug-2010        218
August     Monday    : 09-Aug-2010        383
August     Tuesday   : 10-Aug-2010        368
August     Wednesday : 11-Aug-2010        355
August     Thursday  : 12-Aug-2010        318
August     Friday    : 13-Aug-2010        311
August     Saturday  : 14-Aug-2010        360
August     Sunday    : 15-Aug-2010        214
August     Monday    : 16-Aug-2010        305
August     Tuesday   : 17-Aug-2010        323
August     Wednesday : 18-Aug-2010        301
August     Thursday  : 19-Aug-2010        322
August     Friday    : 20-Aug-2010        299
August     Saturday  : 21-Aug-2010        262
August     Sunday    : 22-Aug-2010        219
August     Monday    : 23-Aug-2010        304
August     Tuesday   : 24-Aug-2010        335
August     Wednesday : 25-Aug-2010        331
August     Thursday  : 26-Aug-2010        316
August     Friday    : 27-Aug-2010        252
August     Saturday  : 28-Aug-2010        248
August     Sunday    : 29-Aug-2010        235
August     Monday    : 30-Aug-2010        310
August     Tuesday   : 31-Aug-2010        341
September  Wednesday : 01-Sep-2010        331
September  Thursday  : 02-Sep-2010        338

Month      Archive Date              Switches
---------- ------------------------- --------
September  Friday    : 03-Sep-2010        313
September  Saturday  : 04-Sep-2010        221
September  Sunday    : 05-Sep-2010        221
September  Monday    : 06-Sep-2010        288
September  Tuesday   : 07-Sep-2010        322
September  Wednesday : 08-Sep-2010        385
September  Thursday  : 09-Sep-2010        364
September  Friday    : 10-Sep-2010        321
September  Saturday  : 11-Sep-2010        232
September  Sunday    : 12-Sep-2010        241
September  Monday    : 13-Sep-2010        355
September  Tuesday   : 14-Sep-2010        374
September  Wednesday : 15-Sep-2010        316
September  Thursday  : 16-Sep-2010         20
77 rows selected.

11:11:47 SYSTEM>

Posted in Oracle | Leave a Comment »

Deferred Segment Creation—-Oracle 11gR2 new feature.

September 15, 2010 by Navneet

Hi…

Today i tested the new feature of Oracle 11gR2 that is Deferred Segment Creation….

In the previous versions, whenever any segment is created, it consist of atleast one Extent…But starting from Oracle 11gR2, it has changed…or we can say we have the option to create an empty segment or we should say object with no extents initially..Extents get allocated once object has some data..and we gets an entry for segments then…

What it does is, it will create the segment only when data is inserted..before that object has entry to the data dictionary but no corresponding segment is there..

Here is a small demo for this..