Database Administration

Since 11gR2 released we're able to duplicate database without connecting to target DB or CATALOG using backup where target database could be down. This kind of duplicate termed as “Backup-Based Duplication without a target and Recovery Catalog Connection”.

1- From RMAN prompt check control file autobackup is ON.

2- Connect to the target to get a new backup.

cmd > rman target /

rman > backup database plus archivelog;

3- Once it finished copy backup folder to your destination DB using normal OS copy.

4- Create your duplicate instance – windows only

cmd > oradim –NEW –SID destination database name

5- Create parameter file with following parameter.

Dn_name= destination database name

Db_unique_name= destination database name

Db_file_name_conevrt=’source datafile location’,’destination datafile location’

Log_file_name_convert=’source logfile location’,’destination logfile location’

6- Startup the destination DB in nomount stage.

sql > Startup nomount

7- Connect to the auxiliary through RMAN and restore the db.

cmd > rman auxiliary /

rman > duplicate database to DB_NAME backup location ‘backup PATH’;

Note:

As per metaling In release 11.2.0.2 there is a bug through an ora-600

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of Duplicate Db command at 01/09/2013 11:39:15

RMAN-06403: could not obtain a fully authorized session

RMAN-04006: error from auxiliary database: ORA-01034: ORACLE not available

ORA-27101: shared memory realm does not exist

RMAN-04014: startup failed: ORA-00600: internal error code, arguments: [17099], [], [], [], [], [], [], [], [], [], [], []

ORA-00600: internal error code, arguments: [KSMFPG5], [0xAEC0000], [], [], [], [], [], [], [], [], [], []

When you try to open the DB you will get following error:

ORA-19838: Cannot use this control file to open database

And when you show up db_name and db_unique_name parameters , you find old name for db_name so continue next steps.

8- Backup control file as trace.

sql > alter database backup controlfile to trace as ‘PATH(CNTRL.sql)’;

9- Edit controlfile trace to replace (REUSE to SET, OLD_DB_NAME to NEW_DB_NAME and remove all blank spaces and commented rows)

10- Restart database in mount stage and change the DB_NAME

sql > shutdown

sql > startup mount

sql > alter system set db_name=NEW_DB_NAME scope=spfile;

11- Shutdown the DB again and recreate control file after remove it through OS.

sql > shutdown

sql > @CNTRL.sql

12-Alter database open with resetlogs option.

sql > alter database open resetlogs

Note:

If you get below error go and check the alert file to find out which parameters are deprecated and remove them.

ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance

Congratulation you have duplicate your database successfully.

Database Growth

This script is designed to snapshoot the size of the database on a daily basis.

CREATE TABLE dba1.dbgrowth

(

SDate DATE NOT NULL,

FullDBS NUMBER,

DBS NUMBER,

UsedSP NUMBER,

FreeSP NUMBER

)

TABLESPACE USERS;

-- add primary key to sdate column

ALTER TABLE dba1.dbgrowth ADD(CONSTRAINT PK_DATABASE_GROWTH PRIMARY KEY (sdate));

--Fill the database growth table by select statement

Add this entry to script.sql

INSERT INTO dba1.dbgrowth

SELECT SYSDATE "Log Date",

(SUM (all_df.bytes) / 1024 / 1024 / 1024) "Full Database Size",

(df.p / 1024 / 1024 / 1024) "Database space",

(used.p / 1024 / 1024 / 1024) "Used space",

(free.p / 1024 / 1024 / 1024) "Free space"

FROM (SELECT bytes FROM v$datafile

UNION ALL

SELECT bytes FROM v$tempfile

UNION ALL

SELECT bytes FROM v$log) all_df,

(SELECT SUM (bytes) AS p FROM dba_data_files) df,

(SELECT SUM (bytes) AS p FROM dba_free_space) free,

(SELECT SUM (bytes) AS p FROM dba_segments) used

GROUP BY free.p, used.p, df.p;

Commit;

Add this entry to dbreads.sh

ORACLE_HOME=/u02/app/oracle/product/11.2.0/dbhome_1; export ORACLE_HOME

ORACLE_UNQNAME=imcpr; export ORACLE_UNQNAME

ORACLE_SID=imcpr2; export ORACLE_SID

$ORACLE_HOME/bin/sqlplus / as sysdba @/u02/app/oracle/DBScripts/dbreads.sql

Schedule this job as daily script on crontab

00 01 * * * /u02/app/oracle/DBScripts/dbreads.sh -cron -silent start

Tablespace Growth

This script is designed to snapshoot the details of all tablespaces.

create table dba1.tsreads as

Select trunc(SYSDATE) "Log Date",ts.tablespace_name, ts.status, ts.contents,

size_info.megs_alloc "Size MB", size_info.megs_free "Free MB", size_info.megs_used "Used MB",

size_info.pct_free "Used%", size_info.pct_used "Free%"

From

(

select a.tablespace_name,

round(a.bytes_alloc / 1024 / 1024) megs_alloc,

round(nvl(b.bytes_free, 0) / 1024 / 1024) megs_free,

round((a.bytes_alloc - nvl(b.bytes_free, 0)) / 1024 / 1024) megs_used,

round((nvl(b.bytes_free, 0) / a.bytes_alloc) * 100) Pct_Free,

100 - round((nvl(b.bytes_free, 0) / a.bytes_alloc) * 100) Pct_used

from ( select f.tablespace_name,

sum(f.bytes) bytes_alloc,

sum(decode(f.autoextensible, 'YES',f.maxbytes,'NO', f.bytes)) maxbytes

from dba_data_files f

group by tablespace_name) a,

( select f.tablespace_name,

sum(f.bytes) bytes_free

from dba_free_space f

group by tablespace_name) b

where a.tablespace_name = b.tablespace_name (+)

union all

select h.tablespace_name,

round(sum(h.bytes_free + h.bytes_used) / 1048576) megs_alloc,

round(sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) / 1048576) megs_free,

round(sum(nvl(p.bytes_used, 0))/ 1048576) megs_used,

round((sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) / sum(h.bytes_used + h.bytes_free)) * 100) Pct_Free,

100 - round((sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) / sum(h.bytes_used + h.bytes_free)) * 100) pct_used

from sys.v_$TEMP_SPACE_HEADER h, sys.v_$Temp_extent_pool p, dba_temp_files f

where p.file_id(+) = h.file_id

and p.tablespace_name(+) = h.tablespace_name

and f.file_id = h.file_id

and f.tablespace_name = h.tablespace_name

group by h.tablespace_name

) size_info,

sys.dba_tablespaces ts

where ts.tablespace_name = size_info.tablespace_name

dbreads.sh

dbreads.sql

Scheduled once every day

Table Growth

This script is designed to snapshoot the biggest tables from number of rows or size aspects.

create table dba1.tbreads as

Select trunc(sysdate) "Log Date", segment_name,round(sum(bytes)/1024/1024/1024,4) "GB", b.num_rows

from sys.dba_segments a, DBA_TABLES b

Where segment_type in ('TABLE', 'TABLE PARTITION', 'TABLE SUBPARTITION')

and a.OWNER = 'PHOENIX'

and a.owner=b.owner

and a.segment_name=b.table_name

group by segment_name,b.num_rows

having round(sum(bytes)/1024/1024/1024,2)>2 or b.num_rows>2000000

order by 2 desc

dbreads.sh

dbreads.sql

Scheduled once every day

Number & Active Sessions Load

This script is designed to snapshoot the load and number of session every hour.

Create table dba1.sessionstat as

SELECT SYSDATE "Log Date",

COUNT (*) AS imcpr1,

(SELECT COUNT (*) AS imcpr2 FROM gv$session WHERE inst_id = 2) imcpr2,

round((select value from Gv$osstat where stat_name='LOAD' AND INST_ID=1),2) LOAD1,

round((select value from Gv$osstat where stat_name='LOAD' AND INST_ID=2),2) LOAD2

FROM gv$session

WHERE inst_id = 1

GROUP BY inst_id;

And then insert the same statement

Sessionstat.sh

Sessionstat.sql

Schedule every one hour

CPU Utilization

This script is designed to snapshot the CPU usage every hour.

create table dba1.cpureads as

with AASSTAT as (

select

decode(n.wait_class,'User I/O','User I/O',

'Commit','Commit',

'Wait') CLASS,

sum(round(m.time_waited/m.INTSIZE_CSEC,3)) AAS,

BEGIN_TIME ,

END_TIME

from gv$waitclassmetric m,

gv$system_wait_class n

where m.wait_class_id=n.wait_class_id

and n.wait_class != 'Idle'

and n.inst_id=&&1 and m.inst_id=&&1

group by decode(n.wait_class,'User I/O','User I/O', 'Commit','Commit', 'Wait'), BEGIN_TIME, END_TIME

union

select 'CPU_ORA_CONSUMED' CLASS,

round(value/100,3) AAS,

BEGIN_TIME ,

END_TIME

from gv$sysmetric

where metric_name='CPU Usage Per Sec'

and group_id=2

and inst_id=&&1

union

select 'CPU_OS' CLASS ,

round((prcnt.busy*parameter.cpu_count)/100,3) AAS,

BEGIN_TIME ,

END_TIME

from

( select value busy, BEGIN_TIME,END_TIME from gv$sysmetric where metric_name='Host CPU Utilization (%)' and group_id=2 and inst_id=&&1 ) prcnt,

( select value cpu_count from gv$parameter where name='cpu_count' and inst_id=&&1) parameter

union

select

'CPU_ORA_DEMAND' CLASS,

nvl(round( sum(decode(session_state,'ON CPU',1,0))/60,2),0) AAS,

cast(min(SAMPLE_TIME) as date) BEGIN_TIME ,

cast(max(SAMPLE_TIME) as date) END_TIME

from gv$active_session_history ash

where inst_id=&&1 and SAMPLE_TIME >= (select BEGIN_TIME from gv$sysmetric where metric_name='CPU Usage Per Sec' and group_id=2 and inst_id=&&1 )

and SAMPLE_TIME < (select END_TIME from gv$sysmetric where metric_name='CPU Usage Per Sec' and group_id=2 and inst_id=&&1 )

)

select trunc(sysdate) "LOG_DATE", &&1 INST_ID,

to_char(BEGIN_TIME,'HH:MI:SS') BEGIN_TIME,

to_char(END_TIME,'HH:MI:SS') END_TIME,

( decode(sign(CPU_OS-CPU_ORA_CONSUMED), -1, 0, (CPU_OS - CPU_ORA_CONSUMED )) +

CPU_ORA_CONSUMED +

decode(sign(CPU_ORA_DEMAND-CPU_ORA_CONSUMED), -1, 0, (CPU_ORA_DEMAND - CPU_ORA_CONSUMED ))) CPU_TOTAL,

decode(sign(CPU_OS-CPU_ORA_CONSUMED), -1, 0, (CPU_OS - CPU_ORA_CONSUMED )) CPU_OS,

CPU_ORA_CONSUMED CPU_ORA,

decode(sign(CPU_ORA_DEMAND-CPU_ORA_CONSUMED), -1, 0, (CPU_ORA_DEMAND - CPU_ORA_CONSUMED )) CPU_ORA_WAIT,

COMMIT,

READIO,

WAIT

from (

select trunc(sysdate) "LOG_DATE", &&1 INST_ID,

min(BEGIN_TIME) BEGIN_TIME,

max(END_TIME) END_TIME,

sum(decode(CLASS,'CPU_ORA_CONSUMED',AAS,0)) CPU_ORA_CONSUMED,

sum(decode(CLASS,'CPU_ORA_DEMAND' ,AAS,0)) CPU_ORA_DEMAND,

sum(decode(CLASS,'CPU_OS' ,AAS,0)) CPU_OS,

sum(decode(CLASS,'Commit' ,AAS,0)) COMMIT,

sum(decode(CLASS,'User I/O' ,AAS,0)) READIO,

sum(decode(CLASS,'Wait' ,AAS,0)) WAIT

from AASSTAT)
-- Reference; http://www.kylehailey.com/oracle-cpu-time/

Then add entry in crontab to run every hour

Cpureads.sql

Database Administration

Tuesday, April 29, 2014

How to Get the DBID when Instance in NOMOUNT State

Monday, April 14, 2014

Duplicate Database Using RMAN

Saturday, March 1, 2014

Customised Capture Statistic

Database Growth

Tablespace Growth

Table Growth

Number & Active Sessions Load

CPU Utilization

About Me