In Part 1 I mentioned three dictionary enhancements;
1- OS Statistics and Finding System CPU Utilization with 10g
2- BLOCKING_SESSION_STATUS and BLOCKING_SESSION columns on V$SESSION
3- Identify Tracing Enabled Sessions
And in In Part 2 I mentioned four more dictionary enhancements;
4- Tuning and monitoring enhancements; latch free and enqueue examples
5- What are the valid values for Oracle parameters?
6- Oracle Active Session History(ASH) samples
7- New views for usage pattern of the database
Now I will continue with some DBA stuff :)
8- Rman Views V$RMAN_BACKUP_JOB_DETAILS, V$RMAN_BACKUP_TYPE, V$RMAN_OUTPUT
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 ;
SESSION_KEY INPUT_TYPE STATUS START_TIME END_TIME HRS
———– ————- ——– ————– ————- ——-
1 DATAFILE FULL COMPLETED 03/25/05 00:48 03/25/05 00:48 .00
4 DB FULL COMPLETED 03/27/05 02:09 03/27/05 02:11 .04
7 DB FULL FAILED 03/27/05 02:18 03/27/05 02:24 .10
col ins format a10
col outs format a10
select SESSION_KEY,
OPTIMIZED,
COMPRESSION_RATIO,
INPUT_BYTES_PER_SEC_DISPLAY ins,
OUTPUT_BYTES_PER_SEC_DISPLAY outs,
TIME_TAKEN_DISPLAY
from V$RMAN_BACKUP_JOB_DETAILS
order by session_key;
SESSION_KEY OPT COMPRESSION_RATIO INS OUTS TIME_TAKEN
———– — —————– ———- ———- ———-
1 NO 2.23776224 3.33M 1.49M 00:00:06
4 NO 1.31065794 6.92M 5.28M 00:02:16
7 NO 1.32363058 3.68M 2.78M 00:06:00
select * from V$RMAN_BACKUP_TYPE;
WEIGHT INPUT_TYPE
———- ————-
1 BACKUPSET
2 SPFILE
3 CONTROLFILE
4 ARCHIVELOG
5 DATAFILE INCR
6 DATAFILE FULL
7 DB INCR
8 RECVR AREA
9 DB FULL
select output
from v$rman_output
where session_key = 4
order by recid;
OUTPUT
———————————————————————-
connected to target database: TEST (DBID=1849323268)
Starting backup at 27-MAR-05
using target database controlfile instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=201 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/u01/app/oracle/oradata/TEST/system01.dbf
input datafile fno=00003 name=/u01/app/oracle/oradata/TEST/sysaux01.dbf
...
9- Using V$RECOVERY_FILE_DEST and V$FLASH_RECOVERY_AREA_USAGE
The V$RECOVERY_FILE_DEST and V$FLASH_RECOVERY_AREA_USAGE views can help you determine whether you have allocated enough space for your flash recovery area.
SELECT * FROM V$RECOVERY_FILE_DEST;
NAME SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES
-------------- ----------- ---------- ----------------- ---------------
/mydisk/rcva 5368709120 109240320 256000 28
select * from V$FLASH_RECOVERY_AREA_USAGE;
FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
------------ ------------------ ------------------------- ---------------
CONTROLFILE 0 0 0
ONLINELOG ,98 0 2
ARCHIVELOG 0 0 0
BACKUPPIECE 0 0 0
IMAGECOPY 0 0 0
FLASHBACKLOG 0 0 0
6 rows selected.
10- Tablespace Usage Statistics in dba_tablespace_usage_metrics
select * from dba_tablespace_usage_metrics;
TABLESPACE_NAME USED_SPACE TABLESPACE_SIZE USED_PERCENT
------------------------------ ---------- --------------- ------------
SYSAUX 60672 4194302 1,44653389
SYSTEM 53872 76800 70,1458333
TEMP 0 4194302 0
UNDO 160 64000 ,25
USERS 16456 655360 2,51098633
5 rows selected.
Also for the history information DBA_HIST_TBSPC_SPACE_USAGE view can be used.
11- DB Startup time log
col instance_name format a15
col startup_time format a25
SELECT instance_name,startup_time
FROM dba_hist_database_instance;
INSTANCE_NAME STARTUP_TIME
--------------- -------------------------
xe 21/11/2006 11:18:58,000
xe 28/11/2006 08:00:29,000
xe 27/11/2006 15:34:06,000
xe 30/11/2006 08:14:06,000
xe 05/12/2006 08:53:35,000
xe 06/12/2006 16:00:00,000
xe 07/12/2006 09:21:17,000
...
12- Redo log file size advice
The size of the redo log files can influence performance, because the behavior of the database writer and archiver processes depend on the redo log sizes. Generally, larger redo log files provide better performance. Undersized log files increase checkpoint activity and reduce performance. V$INSTANCE_RECOVERY monitors the mechanisms available to users to limit recovery I/O.
OPTIMAL_LOGFILE_SIZE column shows redo log file size (in megabytes) that is considered optimal based on the current setting of FAST_START_MTTR_TARGET. It is recommended that the user configure all online redo logs to be at least this value.
select optimal_logfile_size from v$instance_recovery;
OPTIMAL_LOGFILE_SIZE
--------------------
43 -- size in MB
13- Supported Platforms Dictionary Views
V$TRANSPORTABLE_PLATFORM displays all platforms that support cross-platform tablespace transport. Specifically, it lists all platforms supported by the RMAN CONVERT TABLESPACE command, along with the endianness of each platform.
V$DB_TRANSPORTABLE_PLATFORM displays all platforms to which the database can be transported using the RMAN CONVERT DATABASE command. The transportable database feature only supports transports of the same endian platform. Therefore, V$DB_TRANSPORTABLE_PLATFORM displays fewer rows than V$TRANSPORTABLE_PLATFORM.
col PLATFORM_NAME format a35
col ENDIAN_FORMAT format a15
select PLATFORM_NAME , ENDIAN_FORMAT from v$transportable_platform ;
PLATFORM_NAME ENDIAN_FORMAT
----------------------------------- ---------------
Solaris[tm] OE (32-bit) Big
Solaris[tm] OE (64-bit) Big
Microsoft Windows IA (32-bit) Little
Linux IA (32-bit) Little
AIX-Based Systems (64-bit) Big
HP-UX (64-bit) Big
HP Tru64 UNIX Little
HP-UX IA (64-bit) Big
Linux IA (64-bit) Little
HP Open VMS Little
Microsoft Windows IA (64-bit) Little
IBM zSeries Based Linux Big
Linux 64-bit for AMD Little
Apple Mac OS Big
Microsoft Windows 64-bit for AMD Little
Solaris Operating System (x86) Little
IBM Power Based Linux Big
17 rows selected.
col PLATFORM_NAME format a35
col ENDIAN_FORMAT format a15
select PLATFORM_NAME , ENDIAN_FORMAT from V$DB_TRANSPORTABLE_PLATFORM ;
PLATFORM_NAME ENDIAN_FORMAT
----------------------------------- -------------
Microsoft Windows IA (32-bit) Little
Linux IA (32-bit) Little
HP Tru64 UNIX Little
Linux IA (64-bit) Little
HP Open VMS Little
Microsoft Windows IA (64-bit) Little
Linux 64-bit for AMD Little
Microsoft Windows 64-bit for AMD Little
Solaris Operating System (x86) Little
9 rows selected.
The v$database data dictionary view also adds two columns, platform ID and platform name:
select name, platform_name from v$database;
NAME PLATFORM_NAME
--------- ------------------------------
XE Microsoft Windows IA (32-bit)
1 row selected.
To transport a tablespace from one platform to another, datafiles on different platforms must be in the same endian format (byte ordering).
To use RMAN TRANSPORT TABLESPACE, start the RMAN client, connecting to the source database and, if used, the recovery catalog. Then enter the TRANSPORT TABLESPACE command, specifying the required arguments. For example, to transport the tablespaces tbs_2 and tbs_3, use the TRANSPORT TABLESPACE command as follows:
transport tablespace tbs_2, tbs_3
tablespace destination '/disk1/transportdest'
auxiliary destination '/disk1/auxdest'
;
If the query returns a row, the platform supports cross-platform tablespace transport.
SELECT d.PLATFORM_NAME, ENDIAN_FORMAT
FROM V$TRANSPORTABLE_PLATFORM tp, V$DATABASE d
WHERE tp.PLATFORM_NAME = d.PLATFORM_NAME;
-- The following is the query result from the source platform
PLATFORM_NAME ENDIAN_FORMAT
------------------------- --------------
Solaris[tm] OE (32-bit) Big
-- The following is the result from the target platform
PLATFORM_NAME ENDIAN_FORMAT
------------------------- --------------
Microsoft Windows NT Little
You can see that the endian formats are different and thus a conversion is necessary for transporting the tablespace set.
14- List of all oracle background processes
SELECT NAME, description FROM v$bgprocess ;
NAME DESCRIPTION
----- -------------------------------------------
PMON process cleanup
DIAG diagnosibility process
FMON File Mapping Monitor Process
PSP0 process spawner 0
LMON global enqueue service monitor
LMD0 global enqueue service daemon 0
LMS0 global cache service process 0
LMS1 global cache service process 1
LMS2 global cache service process 2
LMS3 global cache service process 3
LMS4 global cache service process 4
LMS5 global cache service process 5
LMS6 global cache service process 6
LMS7 global cache service process 7
LMS8 global cache service process 8
LMS9 global cache service process 9
LMSa global cache service process 10
LMSb global cache service process 11
LMSc global cache service process 12
LMSd global cache service process 13
LMSe global cache service process 14
..
15- Naming Restore Points
In Oracle 10.2 and above restore points can be named, previously required SCN or timestamp. Feature requires Archiving and Flashback
To enable flashback set the following parameters:
ALTER SYSTEM SET db_recovery_file_dest_size = 1000M;
ALTER SYSTEM SET db_recovery_file_dest = ‘’;
ALTER SYSTEM SET db_flashback_retention_target = 1440;
-- Enable flashback mode
SHUTDOWN IMMEDIATE
STARTUP MOUNT
ALTER DATABASE FLASHBACK ON;
ALTER DATABASE OPEN;
Restore points can be;
– Guaranteed restore points
– FLASHBACK must be enabled before creation
– Must be manually deleted
– Can restore beyond time specified by DB_FLASHBACK_RETENTION_TARGET parameter
Normal restore points
– Can be created before FLASHBACK is enabled
– Can be automatically deleted
– Cannot restore beyond time specified by DB_FLASHBACK_RETENTION_TARGET parameter
-- To create a restore point
CREATE RESTORE POINT restore_point1;
CREATE RESTORE POINT restore_point2 GUARANTEE FLASHBACK DATABASE;
-- To drop a restore point
DROP RESTORE POINT restore_point1;
-- To flashback database to a restore point
SHUTDOWN IMMEDIATE
STARTUP MOUNT
FLASHBACK DATABASE TO RESTORE POINT restore_point1;
ALTER DATABASE OPEN RESETLOGS;
Flashback table to restore point
FLASHBACK TABLE TO RESTORE POINT restore_point3;
New dynamic performance view; V$RESTORE_POINT
Name Type
SCN NUMBER
DATABASE_INCARNATION# NUMBER
GUARANTEE_FLASHBACK_DATABASE VARCHAR2(3)
STORAGE_SIZE NUMBER
TIME TIMESTAMP(9)
NAME VARCHAR2(128)
Additional Note 1 – OEM Warning
The Enterprise Manager for Oracle 10g comes with a number of packages which need an additional license if you want to use them. These packages are as follows:
– Database Diagnostics Pack
– Database Diagnostics Pack
– Configuration Management Pack
Additional Note 2 – Some usefull synonyms
CAT Synonym for USER_CATALOG
CLU Synonym for USER_CLUSTERS
COLS Synonym for USER_TAB_COLUMNS
DICT Synonym for DICTIONARY
column table_name format a25
column column_name format a25
column comments format a35
SELECT table_name, column_name, comments
FROM dict_columns
WHERE table_name like '%QUEUE%' and comments is not null
ORDER BY table_name, column_name, comments ;
TABLE_NAME COLUMN_NAME COMMENTS
------------------------- ------------------------- -----------------------------------
ALL_APPLY_ENQUEUE DESTINATION_QUEUE_NAME Name of the queue where events sati
sfying the rule will be enqueued
ALL_APPLY_ENQUEUE RULE_NAME Name of the rule
ALL_APPLY_ENQUEUE RULE_OWNER Owner of the rule…
...
Additional Note 3 – {ORACLE_HOME}/rdbms/admin
catalog.sql Builds the data dictionary views
catblock.sql Creates views that dynamically display lock dependency graphs
caths.sql Installs packages for administering heterogeneous services
catio.sql Allows I/O to be traced on a table-by-table basis
…
Check this list; http://www.psoug.org/reference/files.html
Additional Note 4 – Data dictionary flash file :)
Data dictionary for Oracle 10g
Testing Information : the scripts mentioned are tested on Oracle Database 10g Express Edition Release 10.2.0.2.0
Refences Used : Oracle® Database Reference 10g Release 2 (10.2)
Oracle® Database New Features Guide 10g Release 2 (10.2)
How Oracle 10g makes life easier in a day to day administration.
Oracle 10g Enterprise Manager Packages usage and licensing
New Cross-Platform Transportable Tablespaces
Julian Dyke Presentations
Extract(Read) > Transform(Test&Apply) > Load(Learn) Blog 