Oracle Metalink script Note 131704.1

Database Scripts Library Index [ID 131704.1]

Oracle Database growth per month

col Month format a30
select to_char(creation_time, 'RRRR Month') "Month",sum(bytes)/1024/1024 "Growth in Meg" from sys.v_$datafile
where creation_time > SYSDATE-365
group by creation_time



col year format a20
select to_char(CREATION_TIME,'RRRR') year, to_char(CREATION_TIME,'MM') month, round(sum(bytes)/1024/1024/1024) GB
from v$datafile
group by to_char(CREATION_TIME,'RRRR'), to_char(CREATION_TIME,'MM')
order by 1, 2;


YEAR MONTH GB
-------------------- ------------------------------ ----------
2005 02 6
2005 04 21
2006 04 1
2006 09 2
2007 01 8
2007 03 2
2007 07 2
2008 01 2
2008 06 2
2008 10 2
2009 07 0
2009 12 2
2010 09 2
2010 12 2
2011 05 2
2011 08 2
2011 10 2

Oracle Active Session dump and loader

Advanced ASH Usage
ASH Dump data

SQL> alter system set events 'immediate trace name ashdump level 10';
SQL> oradebug setmypid
SQL> oradebug dump ashdump 10;


SQL> oradebug unlimit

SQL> oradebug setmypid
Statement processed.

SQL> oradebug dump ashdump 10;
Statement processed.

or

SQL> alter session set events 'immediate trace name ashdump level 10';




SQL> oradebug tracefile_name
/opt/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_30527.trc


it's structure is slightly different than that of the V$ACTIVE_SESSION_HISTORY view).
So, here is the DDL to create the ashdump table:



Drop table ashdump;


SQL> connect system/sys
Connected.

SQL>


========================


Release 11.2.0.1.0

SQL> CREATE TABLE ashdump AS SELECT * FROM SYS.WRH$_ACTIVE_SESSION_HISTORY WHERE rownum < 0 ----<<<<<<<<<<<<<< Table created.




sqlldr userid=system/sys control='/home/oracle/ashldr.ctl' data='/opt/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_30527.trc'

Release 11.2.0.1.0





control file ashldr.ctl <<<<<<

load data
infile * "str '\n####\n'"
append
into table ashdump
fields terminated by ',' optionally enclosed by '"'
(
SNAP_ID CONSTANT 0 ,
DBID ,
INSTANCE_NUMBER ,
SAMPLE_ID ,
SAMPLE_TIME TIMESTAMP ENCLOSED BY '"' AND '"' "TO_TIMESTAMP(:SAMPLE_TIME ,'MM-DD-YYYY HH24:MI:SSXFF')" ,
SESSION_ID ,
SESSION_SERIAL# ,
SESSION_TYPE ,
USER_ID ,
SQL_ID ,
SQL_CHILD_NUMBER ,
SQL_OPCODE ,
FORCE_MATCHING_SIGNATURE ,
TOP_LEVEL_SQL_ID ,
TOP_LEVEL_SQL_OPCODE ,
SQL_PLAN_HASH_VALUE ,
SQL_PLAN_LINE_ID ,
SQL_PLAN_OPERATION# ,
SQL_PLAN_OPTIONS# ,
SQL_EXEC_ID ,
SQL_EXEC_START DATE 'MM/DD/YYYY HH24:MI:SS' ENCLOSED BY '"' AND '"' ":SQL_EXEC_START" ,
PLSQL_ENTRY_OBJECT_ID ,
PLSQL_ENTRY_SUBPROGRAM_ID ,
PLSQL_OBJECT_ID ,
PLSQL_SUBPROGRAM_ID ,
QC_INSTANCE_ID ,
QC_SESSION_ID ,
QC_SESSION_SERIAL# ,
EVENT_ID ,
SEQ# ,
P1 ,
P2 ,
P3 ,
WAIT_TIME ,
TIME_WAITED ,
BLOCKING_SESSION ,
BLOCKING_SESSION_SERIAL# ,
BLOCKING_INST_ID ,
CURRENT_OBJ# ,
CURRENT_FILE# ,
CURRENT_BLOCK# ,
CURRENT_ROW# ,
TOP_LEVEL_CALL# ,
CONSUMER_GROUP_ID ,
XID ,
REMOTE_INSTANCE# ,
TIME_MODEL ,
SERVICE_HASH ,
PROGRAM ,
MODULE ,
ACTION ,
CLIENT_ID ,
MACHINE ,
PORT ,
ECID
)



SQL> oradebug tracefile_name
/opt/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_30527.trc





Having created the table, you will need to edit the trace file before you can actually load it, removing the header and footer records (or you can change the ashldr.ctl file, whichever you prefer). After removing the header, you then use SQL*Loader to load the trace file contents into the physical file as seen in this example:


edit this file /opt/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_30527.trc

delete the line


oracle@apt-amd-02:~> sqlldr userid=system/sys control='/home/oracle/ashldr.ctl' data='/opt/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_30527.trc'

SQL*Loader: Release 11.2.0.1.0 - Production on Mon Sep 12 15:15:24 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

Commit point reached - logical record count 18
Commit point reached - logical record count 27

Oracle Standby parameter check

Oracle Standby Parameter
Standby parameter check


set linesize 200
col name format a30
col value format a80
select NAME,nvl(VALUE,'Not Set') value from v$parameter where
name in (
'standby_file_management',
'log_file_name_convert',
'db_file_name_convert',
'fal_client',
'fal_server',
'log_archive_format',
'remote_login_passwordfile',
'log_archive_dest_state_n',
'log_archive_dest_n',
'control_files',
'log_archive_config',
'service_names',
'db_unique_name',
'db_name',
'db_unique_name','dg_broker_start') or name like 'log_archive_dest%'
/




NAME VALUE
------------------------------ --------------------------------------------------------------------------------
control_files /opt/oracle/oradata/db/control01.ctl, /opt/oracle/oradata/db/control02.ctl
, /opt/oracle/oradata/db/control03.ctl

db_file_name_convert Not Set
log_file_name_convert Not Set
log_archive_config Not Set
log_archive_dest Not Set
log_archive_dest_1 LOCATION=/opt/oracle/admin/db/arch/ MANDATORY
log_archive_dest_2 service=prod lgwr net_timeout=30
log_archive_dest_3 Not Set
log_archive_dest_4 Not Set
log_archive_dest_5 Not Set
log_archive_dest_6 Not Set
log_archive_dest_7 Not Set
log_archive_dest_8 Not Set
log_archive_dest_9 Not Set
log_archive_dest_10 Not Set
log_archive_dest_state_1 enable
log_archive_dest_state_2 enable
log_archive_dest_state_3 enable
log_archive_dest_state_4 enable
log_archive_dest_state_5 enable
log_archive_dest_state_6 enable
log_archive_dest_state_7 enable
log_archive_dest_state_8 enable
log_archive_dest_state_9 enable
log_archive_dest_state_10 enable
log_archive_format log%d_%t_%s.arc
fal_client STANDBY
fal_server Not Set
standby_file_management MANUAL
remote_login_passwordfile EXCLUSIVE
service_names db
db_name db
db_unique_name db
dg_broker_start FALSE

34 rows selected.