NISCHAL's DBA WORLD: June 2008

Wednesday, June 18, 2008

About ur DB

select banner "Version of Your Data Base" from v$version;
set pause on
select name "Data Base Name" from v$database;

set pages 80
select log_mode "Your DB in " from v$database;

col "Parameters Mention in SP File" for a35
col value for a25
select name "Parameters Mention in SP File",value from v$spparameter
where ordinal=1 order by name;

col "Parameters of All Dumps" for a25
col value for a30
select upper(NAME) "Parameters of All Dumps",value from v$spparameter
where name in ('user_dump_dest','background_dump_dest','core_dump_dest');

select round(sum(bytes)/1024/1024,2) as "Size of DB in MB" from
(select sum(bytes) bytes
from dba_data_files
union all
select sum(bytes) bytes from dba_temp_files
union all
select sum(l.bytes) bytes from v$log l,v$logfile f where l.group#=f.group#);

select round(sum(bytes)/1024/1024,2) "Used Space of DB in MB's" from sys.sm$ts_used;

select round(sum(bytes)/1024/1024,2) "Free Space of DB in MB's" from sys.sm$ts_free;

select tablespace_name,bytes/1024/1024 "SIZE IN MB" from sm$ts_avail;

col "Default Temporary Tablespace" for a35
select property_value "Default Temporary Tablespace" from database_properties
where property_name='DEFAULT_TEMP_TABLESPACE';

select name "Control files Location in DB" from v$controlfile;

select count(*) as "Number of Redo Groups" from v$log;
col "Redo Members Location in DB" for a50

select group# "Group No.",member "Redo Members Location in DB" from v$logfile;

select username "Data Base Default Users" from dba_users where rownum<5;

select username "Users Created by SYS" from dba_users
where username not in ('SYS','SYSTEM','DBSNMP','OUTLN');

select sum(decode(object_type,'TABLE',1,0)) "SYS :- TABLES",
sum(decode(object_type,'VIEW',1,0)) "VIEWS",
sum(decode(object_type,'SEQUENCES',1,0)) "SEQUENCES",
sum(decode(object_type,'SYNONYMS',1,0)) "SYNONYMS"
from dba_objects where owner='SYS';

select sum(decode(object_type,'TABLE',1,0)) "SYSTEM :- TABLES",
sum(decode(object_type,'VIEW',1,0)) "VIEWS",
sum(decode(object_type,'SEQUENCES',1,0)) "SEQUENCES",
sum(decode(object_type,'SYNONYMS',1,0)) "SYNONYMS"
from dba_objects where owner='SYSTEM';

SELECT le.leseq "Current log sequence No",
100*cp.cpodr_bno/le.lesiz "Percent Full",
cp.cpodr_bno "Current Block No",
le.lesiz "Size of Log in Blocks"
FROM x$kcccp cp, x$kccle le
WHERE le.leseq =CP.cpodr_seq
AND bitand(le.leflg,24) = 8;

col b.tablespace for a15
col SIZE for a5
col SID_SERIAL for a10
col a.username for a15
col a.program for a15
SELECT b.tablespace,
ROUND(((b.blocks*p.value)/1024/1024),2)||'M' "SIZE",
a.sid||','||a.serial# SID_SERIAL,
a.username,a.program FROM sys.v_$session a,
sys.v_$sort_usage b,sys.v_$parameter p
WHERE p.name = 'db_block_size' AND a.saddr = b.session_addr
ORDER BY b.tablespace, b.blocks;

col sid_serial for a10
col orauser for a10
col program for a25
col undoseg for a10
col undo for a10
SELECT TO_CHAR(s.sid)||'-'||TO_CHAR(s.serial#) sid_serial,
NVL(s.username, 'None') orauser,
s.program,r.name undoseg,
t.used_ublk * TO_NUMBER(x.value)/1024||'K' "Undo"
FROM sys.v_$rollname r,
sys.v_$session s, sys.v_$transaction t, sys.v_$parameter x
WHERE s.taddr = t.addr AND r.usn = t.xidusn(+)
AND x.name = 'db_block_size';

set verify off
select sum(decode(object_type,'TABLE',1,0)) "USERS :- TABLES",
sum(decode(object_type,'VIEW',1,0)) "VIEWS",
sum(decode(object_type,'SEQUENCE',1,0)) "SEQUENCES",
sum(decode(object_type,'SYNONYM',1,0)) "SYNONYMS"
from dba_objects where owner='&USER';

RMAN cloning

The cloning method using RMAN is more suitable for production databases with large size and more number of tablespaces. It is a common request to create a duplicate database from a production database. This can be easily achieved using the RMAN DUPLICATE command.

Using the RMAN DUPLICATE command a DBA can create a duplicate database from a target database's backup while still retaining the original target database.

While it is possible to create a duplicate database that contains only a subset of the original database. This document will demonstrate how to create a completely identical database. Using this method allows DBA to test backup and recovery procedures developed on the production database.

Steps to be followed:

1.Create a parameter file for the clone database using the parameter file of the target database.

SQL> create pfile='/home/vamsi/OraHome1/dbs/initclon.ora' from spfile;
File created.

2.Create a password file for the clone database.

[vamsi@vamsi dbs]$ orapwd file='/home/vamsi/OraHome1/dbs/orapwclon'
password=change_on_install

3.Do the necessary changes to the parameter values in the pfile of clone insert the following parameters in the file.

db_file_name_convert='/data/cloning/target/data','/data/cloning/clon/data')
log_file_name_convert=('/data/cloning/target/redo','/data/cloning/clon/redo')
background_dump_dest='/data/cloning/clon/bdump'
compatible='9.2.0.0.0'
control_files='/data/cloning/clon/control/control01.ctl'
core_dump_dest='/data/cloningdb/target/cdump'
db_name='clon'
instance_name='clon'
log_archive_dest_1='LOCATION=/data/cloning/clon/arch/'
user_dump_dest='/data/cloning/clon/udump'

4. Configure the network files.

[vamsi@vamsi admin]$ vi listener.ora
LISTENER=(DESCRIPTION=
(ADDRESS=(PROTOCOL = TCP)(HOST = 192.9.200.217)(PORT = 1521))
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = target.com)
(ORACLE_HOME = /home/vamsi/OraHome1)
(SID_NAME = target)
)
(SID_DESC =
(GLOBAL_DBNAME = catalog.com)
(ORACLE_HOME = /home/vamsi/OraHome1)
(SID_NAME = catalog)
)
(SID_DESC =
(GLOBAL_DBNAME = clon.com)
(ORACLE_HOME = /home/vamsi/OraHome1)
(SID_NAME = clon)
)
)

[vamsi@vamsi admin]$ vi tnsnames.ora
CON_CLO =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.9.200.217)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = clon.com)
))
CON_CAT =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.9.200.217)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = catalog.com)
))
CON_TAR =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.9.200.217)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = target.com)
(SERVER=DEDICATED)
))

5. Create the necessary directory structure for the clone.

[vamsi@vamsi clon]$ mkdir adump bdump cdump udump
[vamsi@vamsi clon]$ mkdir data control redo arch

6. Configure the RMAN to take backup of control file.

RMAN> CONFIGURE RETENTION POLICY TO REDUNDANCY 1;
new RMAN configuration parameters:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1;
new RMAN configuration parameters are successfully stored
starting full resync of recovery catalog
full resync complete

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored
starting full resync of recovery catalog
full resync complete

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR
DEVICE TYPE DISK TO '/data/cloning/clon/control/%F';
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE
DISK TO '/data/cloning/clone/control/%F';
new RMAN configuration parameters are successfully stored
starting full resync of recovery catalog
full resync complete

RMAN> CONFIGURE CHANNEL 1 DEVICE TYPE DISK FORMAT
'/data/cloning/clon/data/DB_%d_S_%s_P_%p_T_%t';
new RMAN configuration parameters:
CONFIGURE CHANNEL 1 DEVICE TYPE DISK FORMAT
'/data/cloning/clone/data/DB_%d_S_%s_P_%p_T_%t';
new RMAN configuration parameters are successfully stored
starting full resync of recovery catalog
full resync complete
released channel: ORA_DISK_1

7. Take complete backup of the target database using RMAN.

RMAN> backup database;
Starting backup at 25APR08
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/data/cloning/target/data/system01.dbf
input datafile fno=00002 name=/data/cloning/target/data/undotbs01.dbf
input datafile fno=00005 name=/data/cloning/target/data/example01.dbf
input datafile fno=00010 name=/data/cloning/target/data/xdb01.dbf
input datafile fno=00006 name=/data/cloning/target/data/indx01.dbf
input datafile fno=00009 name=/data/cloning/target/data/users01.dbf
input datafile fno=00003 name=/data/cloning/target/data/cwmlite01.dbf
input datafile fno=00004 name=/data/cloning/target/data/drsys01.dbf
input datafile fno=00007 name=/data/cloning/target/data/odm01.dbf
input datafile fno=00008 name=/data/cloning/target/data/tools01.dbf
channel ORA_DISK_1: starting piece 1 at 25APR08
channel ORA_DISK_1: finished piece 1 at 25APR08
piece handle=/data/cloning/clon/data/DB_TARGET_S_3_P_1_T_652995913
comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:55
Finished backup at 25APR08
Starting Control File and SPFILE Autobackup at 25APR08
piece handle=/data/cloning/clon/control/c30548414812008042500
comment=NONE
Finished Control File and SPFILE Autobackup at 25APR08

8. Startup the clone database to nomount phase and exit from the terminal.

[vamsi@vamsi clon]$ export ORACLE_SID=clone
[vamsi@vamsi clon]$ sqlplus "/as sysdba"

SQL*Plus: Release 9.2.0.1.0 Production
on Fri Apr 25 19:52:43 2008
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to an idle instance.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 235999352 bytes
Fixed Size 450680 bytes
Variable Size 201326592 bytes
Database Buffers 33554432 bytes
Redo Buffers 667648 bytes
SQL>exit

Disconnected from Oracle9i Enterprise Edition Release 9.2.0.1.0 Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 Production

9.Connect to the clone database through RMAN.

RMAN> connect auxiliary sys/change_on_install@con_clo
connected to auxiliary database: clone (not mounted)

10.Run the RMAN DUPLICATE DATABASE command.

RMAN> run{#Allocate the channel for the duplication work
allocate auxiliary channel ch1 type disk;
#Duplicate the target to clone
duplicate target database to clon;}

11. Startup the clone database.

[vamsi@vamsi ~]$ sqlplus "/as sysdba"
SQL*Plus: Release 9.2.0.1.0 Production
on Tue Apr 29 11:31:13 2008
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 Production

SQL> sho parameter db_name
___NAME_____TYPE_____VALUE_
__db_name____string______clon__

12. Create all temp files for the temporary tablespaces.

SQL> create temporary tablespace temp tempfile '/data/cloning/clon/data/temp01.dbf' size 100m
autoextend on;
Tablespace crated;

Conclusion :

Cloning is the easiest method to duplicate a production database, Depending on the requirement one of the above methods can be effectively used to clone a production database. Care should be taken while setting the paths for the clone database as there is a possibility of corrupting the production database by improper setting of the paths for the clone. ie. The clones path should not conflict with the production database path.

Monday, June 9, 2008

Transportable Tablespace

General You can use the transportable tablespaces feature to move a subset of an Oracle database and "plug" it in to another Oracle database, essentially moving tablespaces between the databases. The tablespaces being transported can be either dictionary managed or locally managed. Starting with Oracle9i, the transported tablespaces are not required to be of the same block size as the target database's standard block size. Transporting tablespaces is particularly useful for:

Moving data from OLTP systems to data warehouse staging systems Updating data warehouses and data marts from staging systems Loading data marts from central data warehouses Archiving OLTP and data warehouse systems efficiently Data publishing to internal and external customers Performing Tablespace Point-in-Time Recovery (TSPITR)

Moving data using transportable tablespaces can be much faster than performing either an export/import or unload/load of the same data, because transporting a tablespace only requires the copying of datafiles and integrating the tablespace structural information. You can also use transportable tablespaces to move index data, thereby avoiding the index rebuilds you would have to perform when importing or loading table data.

LIMITATIONS

Be aware of the following limitations as you plan for transportable tablespace use:

The source and target database must be on the same hardware platform. For example, you can transport tablespaces between Sun Solaris Oracle databases, or you can transport tablespaces between Windows NT Oracle databases. However, you cannot transport a tablespace from a Sun Solaris Oracle database to an Windows NT Oracle database. The source and target database must use the same character set and national character set. You cannot transport a tablespace to a target database in which a tablespace with the same name already exists. Transportable tablespaces do not support: Materialized views/replication Function-based indexes.

Demo:-
conn / as sysdba
set pagesize 20
col platform_name format a35

desc gv$transportable_platform

SELECT * FROM v$transportable_platform ORDER BY 1;

CREATE TABLESPACE tts
DATAFILE 'c:\temp\tts.dbf' size 10M
BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 64K
SEGMENT SPACE MANAGEMENT AUTO ONLINE;

CREATE OR REPLACE DIRECTORY trans_dir AS 'c:\tts';

GRANT READ, WRITE ON DIRECTORY trans_dir TO public;

SELECT tablespace_name, contents, status FROM dba_tablespaces;

ALTER USER amazon QUOTA UNLIMITED ON tts;

conn amazon/amazon

CREATE TABLE t1 (pid NUMBER(5), lname VARCHAR2(20)) TABLESPACE uwdata;

ALTER TABLE t1 ADD CONSTRAINT pk_t1 PRIMARY KEY (pid) USING INDEX PCTFREE 0 TABLESPACE tts;

CREATE TABLE t2 (pid NUMBER(5), lname VARCHAR2(20)) TABLESPACE tts;

ALTER TABLE t2 ADD CONSTRAINT pk_t2 PRIMARY KEY (pid) USING INDEX PCTFREE 0 TABLESPACE uwdata;

conn / as sysdba

EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK('tts', TRUE);

SELECT * FROM TRANSPORT_SET_VIOLATIONS;

conn amazon/amazon

ALTER TABLE t1 MOVE TABLESPACE tts;

ALTER INDEX pk_t2 REBUILD TABLESPACE tts;

conn / as sysdba

EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK('tts', TRUE);

SELECT * FROM TRANSPORT_SET_VIOLATIONS;

ALTER TABLESPACE tts READ ONLY;

-- in o/s window

expdp system/manager
DUMPFILE=tts.dmp
DIRECTORY=trans_dir
TRANSPORT_TABLESPACES = tts

conn / as sysdba

DROP TABLESPACE tts INCLUDING CONTENTS AND DATAFILES;

-- in o/s window

impdp system/manager parfile=trans.par
/*
DUMPFILE=trans.dmp
DIRECTORY=trans_dir
REMAP_SCHEMA=amazon:uwclass
TRANSPORT_DATAFILES='c:\temp\tts.dbf'
*/

conn / as sysdba

SELECT tablespace_name, contents, status FROM dba_tablespaces;

Friday, June 6, 2008

Performance Tuning

Redo buffer Tune:-

set serveroutput on;
declare
val1 number(10,2);
val2 number(10,2);
val3 number(10,8);
begin
select value into val1 from v$sysstat Where name in ('redo entries');
select value into val2 from v$sysstat Where name in ('redo log space requests');
val3:=(val2/val1);
if val3<.0002 then dbms_output.put_line('The ratio (redo log space requests/redo entries)is ok 'val3); end if; if val3>.0002 then
dbms_output.put_line('Increase the size of the redo log buffer 'val3);
end if;
end;
/
set serveroutput off;
/
_______________________________________________________________________________
Buffer Cache tune:-

set serveroutput on;
DECLARE
DB_BLK NUMBER(10,2);
CONS_GET NUMBER(10,2);
PHY_RDS NUMBER(10,2);
FINAL NUMBER(10,2);
begin
Select value INTO DB_BLK From v$sysstat Where name in ('db block gets');
Select value INTO CONS_GET From v$sysstat Where name in ('consistent gets');
Select value INTO PHY_RDS From v$sysstat Where name in ('physical reads');
FINAL:=(1-(PHY_RDS/(DB_BLK+CONS_GET)))*100;
if final>90 then
dbms_output.put_line('The BUFFER CACHE HIT RATIO IS OK 'final);
end if;
if final<90 style="font-style: italic;">tune SORT_AREA_SIZE:-

Select name, value from v$sysstat
where name in ('sorts (memory)', 'sorts (disk)');

--If large number of sorts require I/O to disk,increase the initialisation parameter SORT_AREA_SIZE
_____________________________________________________________________________________

Tune Library cache:-

set serveroutput on;
DECLARE
hit NUMBER(10,2);
reload number(10,2);
begin
Select ((sum(pinhits) / sum(pins))*100) into hit From v$librarycache Where namespace in ('SQL AREA','TABLE PROCEDURE','BODY','TRIGGER');
select ((sum(reloads)/sum(pins))*100) into reload From v$librarycache Where namespace in ('SQL AREA','TABLE PROCEDURE','BODY','TRIGGER');
IF hit<85>85 then
dbms_output.put_line('THE hit ratio >85%. check shared pool and open cursor parameter 'hit);
end if;
dbms_output.put_line('*************************************************************************');
if reload>.02 then
dbms_output.put_line('The reload percent should be very low, 2% (i.e. 0.02) or less. this is ok'reload);
end if;
if reload<.02 then dbms_output.put_line('The reload >2% 'reload);
end if;
end;
/
set serveroutput off;
/
_____________________________________________________________________________________
Tune Dictionary cache:-

set serveroutput on;
DECLARE
ROWCACHE NUMBER(10,2);
BEGIN
Select ((sum(getmisses) / sum(gets))*100) INTO ROWCACHE From v$rowcache;
IF ROWCACHE<15>15 then
dbms_output.put_line('increase the initialisation parameter SHARED_POOL_SIZE 'rowcache);
end if;
END;
/
set serveroutput off;
/
____________________________________________________________________________________
How to find the Chained rows in table:-

1.We have to run the utlchain.sql file in the Schema
(where we want to find the chained rows) to create CHAINED_ROWS table.

SQL>@$ORACLE_HOME/rdbms/admin/utlchain.sql
Then it will create CHAINED_ROWS table.

2.Execute the below command :-
SQL>analyze table table_name list chained rows into CHAINED_ROWS;

3.Query the CHAINED_ROWS table to get the details of the chained rows.

(or)

SELECT 'Chained or Migrated Rows = 'value FROM v$sysstat
WHERE name = 'table fetch continued row';
_____________________________________________________________________________________
Tuning of Indexes:-

grant execute on dbms_stats to &User_name;
grant select on index_stats to &User_name;
connect &Username/&Password
exec dbms_stats.gather_schema_stats(ownname=>'&User_Name');
(or)
analyze index owner.index_name validate structure;

SELECT NAME,(DEL_LF_ROWS_LEN/LF_ROWS_LEN)*100 "Wasted Space"
FROM Index_stats WHERE name IN (select index_name from dba_indexes where owner like 'USER_NAME');

if the value is more then 20% of any Index, then rebuild the Index.
_____________________________________________________________________________________

Compiling all views in a Schema: -

set lines 130 pages 9999
spool comp_all_views
select 'alter view 'owner'.'view_name' compile;' from dba_views
where owner = 'user_name';
spool off
@comp_all_views.lst
____________________________________________________________________________________
To bring Table read_only:-

1. SQL>alter table (table_name) constraint (constraint_name) check(1=1);

2. SQL>alter table (table_name) disable validate constraint (constraint_name);

To bring Table read_write:-

1. SQL>alter table (table_name) enable validate constraint (constraint_name);
____________________________________________________________________________________
How to See the used & free space in Temporary Tablespace: -

SELECT A.tablespace_name tablespace, D.mb_total,
SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
FROM v$sort_segment A,
(SELECT B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
FROM v$tablespace B, v$tempfile C
WHERE B.ts#= C.ts# GROUP BY B.name, C.block_size) D
WHERE A.tablespace_name = D.name
GROUP by A.tablespace_name, D.mb_total;

(or)

SELECT b.tablespace, b.segfile#, b.segblk#, b.blocks,
a.sid, a.serial#, a.username, a.osuser, a.status
FROM v$session a,v$sort_usage b
WHERE a.saddr = b.session_addr
ORDER BY b.tablespace, b.segfile#, b.segblk#, b.blocks;
____________________________________________________________________________________
How to calculate size of a table containing CLOBs and BLOBs datatype: -

select sum(bytes), s.owner, s.segment_name, s.segment_type
from dba_lobs l, dba_segments s
where s.segment_type = 'LOBSEGMENT'
and l.table_name = 'TABLE_NAME'
and s.segment_name = l.segment_name
and s.owner like 'USER_NAME'
group by s.owner,s.segment_name,s.segment_type;

-Will get exact LOBSegment size.
-If we want to get total size of the table (with LOBSegment): -

select sum(bytes), s.owner, s.segment_name, s.segment_type
from dba_lobs l, dba_segments s
where s.segment_type = 'LOBSEGMENT'
and l.table_name = 'TABLE_NAME'
and s.segment_name = l.segment_name
and s.owner like 'USER_NAME'
group by s.owner,s.segment_name,s.segment_type;
--------(+) add both outputs--------
select bytes from dba_segments where segment_name like 'TABLE_NAME'
and owner like 'USER_NAME';
----------------------------------------------------------------------------------------------------------------------------
Checking CPU utilization for all users: -

select ss.username,se.SID,VALUE/100 cpu_usage_seconds
from v$session ss, v$sesstat se, v$statname sn
where se.STATISTIC# = sn.STATISTIC#
and NAME like '%CPU used by this session%'
and se.SID = ss.SID
and ss.status='ACTIVE'
and ss.username is not null
order by VALUE desc;
_______________________________________________________________
Table size (with fragmentation):
select table_name,round((blocks*8),2)'kb' "size" from user_tables where table_name = 'TABLE_NAME';

Actual data in table:
select table_name,round((num_rows*avg_row_len/1024),2)'kb' "size" from user_tables where table_name = 'TABLE_NAMEA';

Actual fragmentation size:
select table_name,round((blocks*8),2) - round((num_rows*avg_row_len/1024),2) "Size in KB" from user_tables where table_name = 'TABLE_NEME';
_______________________________________________________________

Query to get number of partition rows for table: -

select partition_name,num_rows from dba_tab_partitions
where table_name = '& TABLE_NAME'
and table_owner = '&TABLE_OWNER';

(It will output upto LAST_ANALYZED time)
&

Declare
Cursor c1 is
select table_name,partition_name from all_tab_partitions where table_name ='&TABLE_NAME';
v_sql varchar2(2000);
temp_var number:=0;
BEGIN
for rec in c1
Loop
execute immediate 'select count(*) from '||rec.table_name||' partition('||rec.partition_name||')' into temp_var;
dbms_output.put_line('The Partition '||rec.partition_name||' of table '||rec.table_name||' has '||temp_var||' rows');
END LOOP;
END;

&

select 'select count(*) from '||&TABLE_OWNER||'.'||&TABLE_NAME||'partition ('||PARTITION_NAME||');'
from dba_tab_partitions
where table_name = '& TABLE_NAME'
and table_owner = '&TABLE_OWNER';

Thursday, June 5, 2008

Resumeable Operation In Oracle 9i

In Oracle 9i, you can suspend or resume operations that run out of space, or reach space limitations after executing for long time. In Oracle 9i we can switch a transaction into 'resumable' mode. This means that any errors arising due to lack of space will not cause a transaction to fail, but to be suspended. When the space problem is fixed, the operation/transaction resumes automatically as if there was no problem earlier.
Example:-
consider you created table and inserted 20,000 records, after 20,000 record space limitation is reached whether it may be max extent crossed its limit or may be lacking of space in table spaces. Due to this circumstances normally we will get an error or transaction will be rolled back. But using resume option the transaction can be suspended for certain seconds, after fixing space problem, the transaction will be resumed automatically and transaction will proceed further.
Consider you have created tablespace test of datafile size 1m,

SYS> CREATE TABLESPACE test
DATAFILE <'path/filename'> SIZE 1m;
Tablespace created

For the normal user to use Resumable operation the superuser should grant permission

SYS> GRANT RESUMABLE TO
eg:
SYS> GRANT RESUMABLE TO SCOTT;
Grant succeeded
SYS>conn scott/tiger
connected
issue alter session enable resumable timeout
SCOTT> ALTER SESSION ENABLE RESUMABLE TIMEOUT 60;
session altered
SCOTT> CREATE TABLE test1
TABLESPACE test
AS SELECT * from user_objects;
Table created
SCOTT> insert into test1 select * from user_objects;
7 rows created
SCOTT>insert into test1 select * from test1;
14 rows created
SCOTT>/
28 rows created
SCOTT>
114688 rows created.
. .upto 1m of tablespace transaction proceeds later transaction will be in waiting stage
SCOTT>/

The transaction will be suspended until 60 seconds, within that 60 second increase the size of tablespace and the error message will be written to alert log file as (Tue Jan 29 13:50:26 2008
statement in resumable session 'User SCOTT(22), Session 9, Instance 1' was suspended due to
ORA01653:
unable to extend table SCOTT.TEST by 2048 in tablespace TEST1
Tue Jan 29 14:27:22 2008
statement in resumable session 'User SCOTT(22), Session 9, Instance 1' was suspended due to
ORA30036:
unable to extend segment by 256 in undo tablespace 'UNDO1'
if not you increase the size of tablespace, the transaction will get rolled backed and you receive an error message
SCOTT>insert into test select * from test
*
ERROR at line 1:
ORA30032:
the suspended (resumable) statement has timed out
ORA01653:
unable to extend table SCOTT.TEST by 256 in tablespace TEST1
If you resolved problem within 60 second the transaction gets continued.

DBNEWID(nid) utility

changing only dbname(without dbid):-

1) startup your database in mount state:-
SQL> startup mount;

2) use nid utility to change database name:-
$nid target="sys as sysdba" dbname=new_db_name logfile=logfile_name.log
setname=yes
password:****** (sys password)
(or)
$nid target="sys/sys_password" dbname=new_db_name logfile=logfile_name.log
setname=yes

3) verify the logfile:-
$vi logfile_name.log

4) shutdown & copy spfile to pfile in sql prompt:-
SQL>shutdown;
SQL>create pfile from spfile;

5) go to the dbs directory & remove the password file and recreate it
(because it will become invalid):-
$rm orapwSID
$orapwd file=orapwSID password=new_password

6) in that dbs directory change the pfile with new database name:-
$vi initSID.ora
db_name=new_dbname

wq!

7) startup your database normal:-
SQL>startup;

8) verify the db_name parameter:-
SQL>show parameter db_name
_________________________________________________________________________________________________
changing database name (with dbid):-

1) startup your database in mount state:-
SQL> startup mount;

2) use nid utility to change database name:-
$nid target="sys as sysdba" dbname=new_db_name logfile=logfile_name.log
setname=no
password:****** (sys password)
(or)
$nid target="sys/sys_password" dbname=new_db_name logfile=logfile_name.log
setname=no

3) verify the logfile:-
$vi logfile_name.log

4) shutdown & copy spfile to pfile in sql prompt:-
SQL>shutdown;
SQL>create pfile from spfile;

5) go to the dbs directory & remove the password file and recreate it
(because it will become invalid):-
$rm orapwSID
$orapwd file=orapwSID password=new_password

6) in that dbs directory change the pfile with new database name:-
$vi initSID.ora
db_name=new_dbname

wq!

7) startup your database normal:-
SQL>startup;

8) verify the db_name parameter:-
SQL>show parameter db_name

Tuesday, June 3, 2008

Crontab
DB Verify,nid

Enabling rlwrap in Linux system

File - rlwrap-0.30.tar.gz
Location - http://utopia.knoware.nl/%7Ehlub/uck/rlwrap/

1. Download the file and gunzip the file :-
$gunzip rlwrap-0.30.tar.gz
Later you will get .tar file.

2. Un-tar the file :-
$tar –xvf rlwrap-0.30.tar

3. run the configure file:-
$./configure

4. execute the following commands:-
$make
$make check
$make install

5. make the bellow entry in .bash_profile file:-
alias sqlplus=”rlwrap sqlplus”

Monday, June 2, 2008

RMAN

Download

resetting RMAN configure parameters to default:-

CONFIGURE RETENTION POLICY CLEAR;
CONFIGURE BACKUP OPTIMIZATION CLEAR;
CONFIGURE DEFAULT DEVICE TYPE CLEAR;
CONFIGURE CONTROLFILE AUTOBACKUP CLEAR;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK CLEAR;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE SBT CLEAR;
CONFIGURE DEVICE TYPE DISK CLEAR;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK CLEAR;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE SBT CLEAR;
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK CLEAR;
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE SBT CLEAR;
CONFIGURE CHANNEL DEVICE TYPE DISK CLEAR;
CONFIGURE CHANNEL DEVICE TYPE SBT CLEAR;
CONFIGURE MAXSETSIZE CLEAR;
CONFIGURE SNAPSHOT CONTROLFILE NAME CLEAR;
__________________________________________________________________________________________________
Steps:-

RMAN

TARGET DATABASE (DB_NAME = tree, IP = 192.9.200.174)

SQL>startup;
ORACLE instance started.
Total System Global Area 97588504 bytes
Fixed Size 451864 bytes
Variable Size 46137344 bytes
Database Buffers 50331648 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.

SQL>sho parameter remote_login_passwordfile;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
remote_login_passwordfile string EXCLUSIVE

[oracle9i@admin]$ Listener.ora
tar =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.9.200.174)(PORT = 8000))
)
SID_LIST_tar =
(SID_LIST =
(SID_DESC =
(SID_NAME = tree)
(GLOBAL_DBNAME = tree.com)
(ORACLE_HOME = /home/oracle9i/OraHome1)
)
)
:wq!

[oracle9i@admin]$ lsnrctl start tar
LSNRCTL for Linux: Version 9.2.0.4.0 - Production on 27-MAY-2008 16:54:47
Copyright (c) 1991, 2002, Oracle Corporation. All rights reserved.
Starting /home/oracle9i/OraHome1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 9.2.0.4.0 - Production
System parameter file is /home/oracle9i/OraHome1/network/admin/listener.ora
Log messages written to /home/oracle9i/OraHome1/network/log/tar.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.9.200.174)(PORT=8000)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.9.200.174)(PORT=8000)))
STATUS of the LISTENER
------------------------
Alias tar
Version TNSLSNR for Linux: Version 9.2.0.4.0 - Production
Start Date 27-MAY-2008 16:54:47
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security OFF
SNMP OFF
Listener Parameter File /home/oracle9i/OraHome1/network/admin/listener.ora
Listener Log File /home/oracle9i/OraHome1/network/log/tar.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.9.200.174)(PORT=8000)))
Services Summary...
Service "tree.com" has 1 instance(s).
Instance "tree", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

RECOVERY CATALOG DATABASE (DB_NAME = mCreating control file:oon, IP = 192.9.200.175)

SQL>startup;
ORACLE instance started.
Total System Global Area 303109300 bytes
Fixed Size 451764 bytes
Variable Size 234881024 bytes
Database Buffers 67108864 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.

SQL>create tablespace rman_ts datafile '/home/vamsi/moon/data/rman1.dbf' size 50m;
Tablespace created.

SQL>create user rman identified by rman
default tablespace rman_ts
temporary tablespace temp1
quota unlimited on rman_ts
quota 0 on system;
User created.

SQL>grant connect,resource to rman;
Grant succeeded.

SQL>grant recovery_catalog_owner to rman;
Grant succeeded.

SQL>conn rman/rman
Connected.

SQL>select * from tab;
no rows selected

[vamsi@vamsi admin]$ Listener.ora
cat =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.9.200.175)(PORT = 9000))
)
SID_LIST_cat =
(SID_LIST =
(SID_DESC =
(SID_NAME = moon)
(GLOBAL_DBNAME = moon.com)
(ORACLE_HOME = /home/vamsi/OraHome1)
)
)

[vamsi@vamsi admin]$ lsnrctl start cat
LSNRCTL for Linux: Version 9.2.0.4.0 - Production on 27-MAY-2008 16:53:14
Copyright (c) 1991, 2002, Oracle Corporation. All rights reserved.
Starting /home/vamsi/OraHome1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 9.2.0.4.0 - Production
System parameter file is /home/vamsi/OraHome1/network/admin/listener.ora
Log messages written to /home/vamsi/OraHome1/network/log/cat.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.9.200.175)(PORT=9000)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.9.200.175)(PORT=9000)))

STATUS of the LISTENER

Alias cat
Version TNSLSNR for Linux: Version 9.2.0.4.0 - Production
Start Date 27-MAY-2008 16:53:14
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security OFF
SNMP OFF
Listener Parameter File /home/vamsi/OraHome1/network/admin/listener.ora
Listener Log File /home/vamsi/OraHome1/network/log/cat.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.9.200.175)(PORT=9000)))
Services Summary...
Service "moon.com" has 1 instance(s).
Instance "moon", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

RMAN

[vamsi@vamsi admin]$ tnsmanes.ora
con_tar =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.9.200.174)(PORT = 8000))
)
(CONNECT_DATA =
(SERVICE_NAME = tree.com)
(SID_NAME=tree)
)
)

con_cat =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.9.200.175)(PORT = 9000))
)
(CONNECT_DATA =
(SERVICE_NAME = moon.com)
(SID_NAME=moon)
)
)
:wq!

[vamsi@vamsi admin]$ tnsping con_tar
TNS Ping Utility for Linux: Version 9.2.0.4.0 - Production on 27-MAY-2008 18:44:10
Copyright (c) 1997 Oracle Corporation. All rights reserved.
Used parameter files:
/home/vamsi/OraHome1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.9.200.174)(PORT = 8000))) (CONNECT_DATA = (SERVICE_NAME = tree.com) (SID_NAME=tree)))
OK (10 msec)

[vamsi@vamsi admin]$ tnsping con_cat
TNS Ping Utility for Linux: Version 9.2.0.4.0 - Production on 27-MAY-2008 18:44:24
Copyright (c) 1997 Oracle Corporation. All rights reserved.
Used parameter files:
/home/vamsi/OraHome1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.9.200.175)(PORT = 9000))) (CONNECT_DATA = (SERVICE_NAME = moon.com) (SID_NAME=moon)))
OK (0 msec)

[vamsi@vamsi admin]$ rman
Recovery Manager: Release 9.2.0.4.0 - Production
Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.

RMAN> connect target sys/sys@ con_tar
connected to target database: TREE (DBID=3948310515)

RMAN> connect catalog rman/rman@ con_cat
connected to recovery catalog database
Recovery catalog not installed

RMAN> create catalog;
recovery catalog created

RMAN> register database;
database registered in recovery catalog
starting full resync of recovery catalog
full resync complete

RMAN> report schema;
Report of database schema
File K-bytes Tablespace RB segs Datafile Name
---- ---------- -------------------- ------- -------------------
1 307200 SYSTEM YES /home/oracle9i/tree/data/sys.dba
2 20480 TREE1 NO /home/oracle9i/tree/data/t1.dbf
3 20480 TREE2 NO /home/oracle9i/tree/data/t2.dbf
4 51200 UNDO1 YES /home/oracle9i/tree/data/undo01.dbf
5 20480 TREE3 NO /home/oracle9i/tree/data/t3.dbf

RMAN> show all;
RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
RMAN configuration has no stored or default parameters
RMAN configuration has no stored or default parameters
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/home/vamsi/OraHome1/dbs/snapcf_tree.f'; # default

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored
starting full resync of recovery catalog
full resync complete

RMAN> CONFIGURE DEVICE TYPE DISK PARALLELISM 3;
new RMAN configuration parameters:
CONFIGURE DEVICE TYPE DISK PARALLELISM 3;
new RMAN configuration parameters are successfully stored
starting full resync of recovery catalog
full resync complete

RMAN> configure channel device type disk format '/home/vamsi/rman/tree_%T_%U';
old RMAN configuration parameters:
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/home/vamsi/rman/%U';
new RMAN configuration parameters:
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/home/vamsi/rman/tree_%T_%U';
new RMAN configuration parameters are successfully stored
starting full resync of recovery catalog
full resync complete

RMAN> CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/home/vamsi/rman/snapcf_tree.f';
snapshot controlfile name set to: /home/vamsi/rman/snapcf_tree.f
new RMAN configuration parameters are successfully stored

RMAN> show all;
RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 3;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT ' /home/vamsi/rman/tree_%T_%U ';
RMAN configuration has no stored or default parameters
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO ' /home/vamsi/rman/snapcf_tree.f ';

RMAN> backup database;
Starting backup at 27-MAY-08
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=12 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=13 devtype=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: sid=14 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00004 name=/home/oracle9i/tree/data/undo01.dbf
input datafile fno=00002 name=/home/oracle9i/tree/data/t1.dbf
channel ORA_DISK_1: starting piece 1 at 27-MAY-08
channel ORA_DISK_2: starting full datafile backupset
channel ORA_DISK_2: specifying datafile(s) in backupset
input datafile fno=00001 name=/home/oracle9i/tree/data/sys.dba
input datafile fno=00003 name=/home/oracle9i/tree/data/t2.dbf
channel ORA_DISK_2: starting piece 1 at 27-MAY-08
channel ORA_DISK_1: finished piece 1 at 27-MAY-08
piece handle=/home/vamsi/rman/tree_20080527_01jhej5p_1_1 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
channel ORA_DISK_2: finished piece 1 at 27-MAY-08
piece handle=/home/vamsi/rman/tree_20080527_02jhej5p_1_1 comment=NONE
channel ORA_DISK_2: backup set complete, elapsed time: 00:00:22
Finished backup at 27-MAY-08

Starting Control File and SPFILE Autobackup at 27-MAY-08
piece handle=/home/vamsi/OraHome1/dbs/c-3948310515-20080527-00 comment=NONE
Finished Control File and SPFILE Autobackup at 27-MAY-08

RMAN> backup datafile 1;
Starting backup at 27-MAY-08
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/home/oracle9i/tree/data/sys.dba
channel ORA_DISK_1: starting piece 1 at 27-MAY-08
channel ORA_DISK_1: finished piece 1 at 27-MAY-08
piece handle=/home/vamsi/rman/tree_20080527_04jhejea_1_1 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
Finished backup at 27-MAY-08
Starting Control File and SPFILE Autobackup at 27-MAY-08
piece handle=/home/vamsi/OraHome1/dbs/c-3948310515-20080527-01 comment=NONE
Finished Control File and SPFILE Autobackup at 27-MAY-08

RMAN> list backup;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
23 Full 364K DISK 00:00:05 27-MAY-08
BP Key: 25 Status: AVAILABLE Tag: TAG20080527T181426
Piece Name: /home/vamsi/rman/tree_20080527_01jhej5p_1_1
List of Datafiles in backup set 23
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
2 Full 66571 27-MAY-08 /home/oracle9i/tree/data/t1.dbf
4 Full 66571 27-MAY-08 /home/oracle9i/tree/data/undo01.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
24 Full 90M DISK 00:00:15 27-MAY-08
BP Key: 26 Status: AVAILABLE Tag: TAG20080527T181426
Piece Name: /home/vamsi/rman/tree_20080527_02jhej5p_1_1
List of Datafiles in backup set 24
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 66572 27-MAY-08 /home/oracle9i/tree/data/sys.dba
3 Full 66572 27-MAY-08 /home/oracle9i/tree/data/t2.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
32 Full 808K DISK 00:00:00 27-MAY-08
BP Key: 33 Status: AVAILABLE Tag:
Piece Name: /home/vamsi/OraHome1/dbs/c-3948310515-20080527-00
SPFILE Included: Modification time: 27-MAY-08

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
37 Full 90M DISK 00:00:08 27-MAY-08
BP Key: 38 Status: AVAILABLE Tag: TAG20080527T181859
Piece Name: /home/vamsi/rman/tree_20080527_04jhejea_1_1
List of Datafiles in backup set 37
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 66665 27-MAY-08 /home/oracle9i/tree/data/sys.dba

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
41 Full 808K DISK 00:00:00 27-MAY-08
BP Key: 42 Status: AVAILABLE Tag:
Piece Name: /home/vamsi/OraHome1/dbs/c-3948310515-20080527-01
SPFILE Included: Modification time: 27-MAY-08

Differential incremental Backup
form:- n=n (or) n=n-1

cumulative incremental Backup
form:- n=n-1

Database Cloning

Download

Sunday, June 1, 2008

Oracle 9i

Very IMP

Data Guard

Connect
Physical Standby
using RMAN
Standby DB

Connect
_____________________________________________________________________________________________________
Creation of oracle 9i standby datbase through RMAN:-

PRE-REQUISITES: RMAN catalog in use and use of external media management layer
configure on both machines.

PRIMARY SITE: ITLINUXDEVBLADE07
STANDBY SITE:ITLINUXDEVBLADE08
DATABASE: dgtest9i.
Login to catalog and set configuration from primary database:
Export ORACLE_SID=dgtest9i

rman target / catalog rman/rman@rmanp
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE DEFAULT DEVICE TYPE TO \’SBT_TAPE\’;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO
\’/u01/ORACLE/dgtest9i/arch/%F\’;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE \’SBT_TAPE\’ TO \’%F\’;
CONFIGURE DEVICE TYPE \’SBT_TAPE\’ PARALLELISM 1;
CONFIGURE DEVICE TYPE DISK PARALLELISM 1;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE \’SBT_TAPE\’ TO 1;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1;
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE \’SBT_TAPE\’ TO 1;
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1;
CONFIGURE CHANNEL DEVICE TYPE \’SBT_TAPE\’ PARMS \’ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/tdpo.dgtest9id.opt)\’
FORMAT \’%d.%T.%s.%p.%c.%t\’;
CONFIGURE MAXSETSIZE TO UNLIMITED;
CONFIGURE SNAPSHOT CONTROLFILE NAME TO \’/opt/oracle/product9204/dbs/snapcf_dgtest9i.f\’;
PRIMARY SITE-ITLINUXDEVBLADE07:
Additional entries for init.ora on primary site for Dataguard.
*.remote_login_passwordfile=\’EXCLUSIVE\’
*.db_file_name_convert=\’/opt/oracle/dgtest9i/\’,\’/opt/oracle/oradata/dgtest9i/\’
*.log_file_name_convert=\’/opt/oracle/ dgtest9i/\’,\’/opt/oracle oradata/dgtest9i/\’
*.fal_client=\’DGTEST9I_BLADE07\’*.fal_server=\’DGTEST9I_BLADE08\’
*.log_archive_dest_1=\’LOCATION=/opt/oracle/dgtest9i/arch\’
*.standby_archive_dest=/opt/oracle/dgtest9i/arch
*.log_archive_dest_2=\’SERVICE=dgtest9i_blade08 lgwr sync affirm nodelay\’
*.log_archive_dest_state_1=\’ENABLE\’
*.log_archive_dest_state_2=\’ENABLE\’………
*.log_archive_format=\’arch%s.log\’
*.log_archive_start=TRUE
*.standby_file_management=\’AUTO\’
STANDBY SITE: ITLINUXBLADE08:
*.remote_login_passwordfile=\’EXCLUSIVE\’
*.db_file_name_convert=\’/opt/oracle/oradata/dgtest9i/\’,\’/opt/oracle/dgtest9i/\’
*.log_file_name_convert=\’/opt/oracle/oradata/dgtest9i/\’,\’/opt/oracle/dgtest9i/\’
*.fal_client=\’DGTEST9I_BLADE08\’
*.fal_server=\’DGTEST9I_BLADE07\’
*.log_archive_dest_1=\’LOCATION=/opt/oracle/dgtest9i/arch\’
*.standby_archive_dest=/opt/oracle/dgtest9i/arch
*.log_archive_dest_2=\’SERVICE=dgtest9i_blade07 lgwr sync affirm nodelay\’
*.log_archive_dest_state_1=\’ENABLE\’
*.log_archive_dest_state_2=\’DEFER\’………
*.log_archive_format=\’arch%s.log\’
*.log_archive_start=TRUE
*.standby_file_management=\’AUTO\’
2. Create orapwd file in $ORACLE_HOME/dbs
> orapwd file=orapwdgtest9i entries=10 password=oracle
3. Make changes to tnsnames.ora and listener.ora to include log_shipping entries
on both machines and both listeners should be running
4. Export ORACLE_SID=dgtest9i ( on standby site)
SQL> startup nomount ;
PRIMARY SITE- ITLINUXDEVBLADE07:

Four scripts will be used….Please create these .rcv files in /opt/oracle/rman/scripts
area.
cd /opt/oracle/rman/scripts.
Create the four scripts…
1. backup_database.rcv
run {
allocate channel ch1 type \’SBT_TAPE\’
parms
\’ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/tdpo.dgtest9id.opt)\’
format \’d_%d_%u_%p_%c\’;
backup current controlfile
for standby;
release channel ch1 ;
}
2. standby_control_file.rcv
run {
allocate channel ch1 type \’SBT_TAPE\’
parms
\’ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/tdpo.dgtest9id.opt)\’
format \’d_%d_%u_%p_%c\’;
backup current controlfile
for standby;
release channel ch1 ;
}
3. arch_backup_range.rcv
run {
allocate channel ch1 type \’SBT_TAPE\’
parms
\’ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/tdpo.dgtest9id.opt)\’
format \’d_%d_%u_%p_%c\’;
backup archivelog
from logseq
1 until logseq
32 thread 1 ;
release channel ch1 ;
}
4. makeme_standby.rcv
run {
allocate channel ch1 type \’sbt_tape\’
parms
\’ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/tdpo.dgtest9id.opt)\’;
allocate auxiliary channel aux1 type \’sbt_tape\’
parms
\’ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/tdpo.dgtest9id.opt)\’;
set limit channel ch1 kbytes=3145728
readrate
200;
set until sequence = 32 thread =1;
duplicate target database for standby
dorecover
;
release channel ch1 ;
release channel aux1 ;
}
Now start the process on the PRIMARY site.
1. BACKUP DATABASE PLUS ARCHIVELOGS….
rman target / catalog rman/rman@rmanp cmdfile=backup_database.rcv
After backup is complete.
2. CREATE STANDBY CONTROLFILE.
rman target / catalog rman/rman@rmanp cmdfile=standby_control_file.rcv
After standby controlfile is created.
3. SWITCH A FEW LOGS.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
SQL> alter system switch logfile
2 /
System altered.
SQL> /
System altered.
SQL>
cd $ARCV
-rw-r—– 1 oracle dba 13312 Apr 22 12:35 arch30.log
-rw-r—– 1 oracle dba 2560 Apr 22 12:40 arch31.log
-rw-r—– 1 oracle dba 1536 Apr 22 12:40 arch32.log …..LAST LOG
FILE…NOTE DOWN THIS NUMBER.
4. BACKUP ARCHIVE LOGS
[oracle@itlinuxdevblade07 arch]$rman target / catalog rman/rman@rmanp cmdfile=arch_backup_range.rcv
( Last log seq should be as per last log in archive area)
After archivelogs are backed up…
[oracle@itlinuxdevblade07 rman]$ sqlplus rman/rman@rmanp
SQL*Plus: Release 9.2.0.4.0 - Production on Sat Apr 22 12:46:00 2006
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.3.0 - Production
SQL> Select sequence# from rc_backup_redolog where db_name=\’DGTEST9I\’;
Last few…
30
31
32
96 rows selected.
SQL>
Make sure listeners used for log-shippng on both sides are up for alias
dgtest_blade07 and dgtest_blade08..
Check connection to standby database….which should be in NOMOUNT state on
ITLINUXDEVBLADE08.
[oracle@itlinuxdevblade07 admin]$ rman target / auxiliary
sys/oracle@DGTEST9I_BLADE08
Recovery Manager: Release 9.2.0.4.0 - 64bit Production
Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
connected to target database: DGTEST9I (DBID=227642821)
connected to auxiliary database: dgtest9i (not mounted) …..IMPORTANT to
confirm primary is not being touched.
RMAN>EXIT
Make sure path of db directory(dgtest9i) exists for area mentioned in
db_file_name_convert and log_file_name_convert in ITLINUXDEVBLADE08 machine….i.e
/opt/oracle/dgtest9i.
Now you are ready to create standby…
NOW RUN THE ACTUAL CREATION OF STANDBY….
[oracle@itlinuxdevblade07 rman]$ rman target / catalog rman/rman@rmanp
auxiliary sys/oracle@DGTEST9I_BLADE08 cmdfile=make_standby.rcv

Recovery Manager: Release 9.2.0.4.0 - 64bit Production
Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
connected to target database: DGTEST9I (DBID=227642821)
connected to recovery catalog database
connected to auxiliary database: dgtest9i (not mounted)
RMAN> run {
2> allocate channel ch1 type \’sbt_tape\’ parms \’ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/tdpo.dgtest9id.opt)\’;
3> allocate auxiliary channel aux1 type \’sbt_tape\’ parms \’ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/tdpo.dgtest9id.opt)\’;
4> set limit channel ch1 kbytes=3145728 readrate 200;
5> set until sequence = 32 thread =1;
> duplicate target database for standby dorecover
7> ;
8> release channel ch1 ;
9> release channel aux1 ;
10> }
11>
allocated channel: ch1
channel ch1: sid=10 devtype=SBT_TAPE
channel ch1: Data Protection for Oracle: version 5.2.4.0
allocated channel: aux1
channel aux1: sid=14 devtype=SBT_TAPE
channel aux1: Data Protection for Oracle: version 5.2.4.0
executing command: SET until clause
Starting Duplicate Db at 22-APR-06
printing stored script: Memory Script
{
restore clone standby controlfile to clone_cf;
replicate clone controlfile from clone_cf;
sql clone \’alter database mount standby database\’;
}
executing script: Memory Script
Starting restore at 22-APR-06
channel aux1: starting datafile backupset restore
channel aux1: restoring controlfile
output filename=/opt/oracle/dgtest9i/control01.ctl
channel aux1: restored backup piece 1
piece handle=d_DGTEST9I_2chh5e82_1_1 tag=TAG20060422T123930 params=NULL
channel aux1: restore complete
Finished restore at 22-APR-06
replicating controlfile
input filename=/opt/oracle/dgtest9i/control01.ctl
sql statement: alter database mount standby database
printing stored script: Memory Script
{
set until scn 1361050;
set newname for datafile 1 to
\”/opt/oracle/dgtest9i/system01.dbf\”;
set newname for datafile 2 to
\”/opt/oracle/dgtest9i/undotbs01.dbf\”;
set newname for datafile 3 to
\”/opt/oracle/dgtest9i/users01.dbf\”;
restore
check readonly
clone database
;
}
executing script: Memory Script
executing command: SET until clause
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 22-APR-06
channel aux1: starting datafile backupset restore
channel aux1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /opt/oracle/dgtest9i/system01.dbf
restoring datafile 00002 to /opt/oracle/dgtest9i/undotbs01.dbf
restoring datafile 00003 to /opt/oracle/dgtest9i/users01.dbf
channel aux1: restored backup piece 1
piece handle=DGTEST9I.20060422.70.1.2.588427511 tag=TAG20060422T120511 params=NULL
channel aux1: restore complete
Finished restore at 22-APR-06
printing stored script: Memory Script
{
switch clone datafile all;
}
executing script: Memory Script
datafile 2 switched to datafile copy
input datafilecopy recid=32 stamp=588433731
filename=/opt/oracle/dgtest9i/undotbs01.dbf
datafile 3 switched to datafile copy
input datafilecopy recid=33 stamp=588433731
filename=/opt/oracle/dgtest9i/users01.dbf
printing stored script: Memory Script
{
set until scn 1361050;
recover
standby
clone database
delete archivelog
;
}
executing script: Memory Script
executing command: SET until clause
Starting recover at 22-APR-06
starting media recovery
channel aux1: starting archive log restore to default destination
channel aux1: restoring archive log
archive log thread=1 sequence=29
channel aux1: restored backup piece 1
piece handle=DGTEST9I.20060422.71.1.2.588427538 tag=TAG20060422T120538 params=NULL
channel aux1: restore complete
archive log filename=/opt/oracle/dgtest9i/arch/arch29.log thread=1 sequence=29
channel clone_default: deleting archive log(s)
archive log filename=/opt/oracle/dgtest9i/arch/arch29.log recid=1
stamp=588433731
channel aux1: starting archive log restore to default destination
channel aux1: restoring archive log
archive log thread=1 sequence=30
channel aux1: restored backup piece 1
piece handle=DGTEST9I.20060422.74.1.1.588429302 tag=TAG20060422T123502 params=NULL
channel aux1: restore complete
archive log filename=/opt/oracle/dgtest9i/arch/arch30.log thread=1 sequence=30
channel clone_default: deleting archive log(s)
archive log filename=/opt/oracle/dgtest9i/arch/arch30.log recid=2
stamp=588433735
channel aux1: starting archive log restore to default destination
channel aux1: restoring archive log
archive log thread=1 sequence=31
channel aux1: restored backup piece 1
piece handle=d_DGTEST9I_2ehh5eg0_1_1 tag=TAG20060422T124344 params=NULL
channel aux1: restore complete
archive log filename=/opt/oracle/dgtest9i/arch/arch31.log thread=1 sequence=31
channel clone_default: deleting archive log(s)
archive log filename=/opt/oracle/dgtest9i/arch/arch31.log recid=3
stamp=588433737
media recovery complete
Finished recover at 22-APR-06
Finished Duplicate Db at 22-APR-06
released channel: ch1
released channel: aux1
Recovery Manager complete.
[oracle@itlinuxdevblade07 rman]$sql
Now create standby redologs in standby and primary sites.
Sql>
Alter database add standby logfile ‘ /opt/oracle/dgest9i/standby01,log’ size
10m;
The size of the standby redologs must be the same as the primary online redologs.
Create one more standby redolog that number of online redologs.
Next on standby …recover managed standby database disconnect;…to start
managed recovery.
The database protection_mode will be maximum performance.
Select protection_mode from v$database;
To change to maximum availability, a restart of the primary database is
required.
To change the protection_mode to maximum availability:
Shutdown primary.
shutdown immediate;
startup mount;
Alter database set standby database to maximize availability;
alter database open;
Warning: The alter database open command will fail if there are no standby logs
created on standby site when database protection_mode is maximum availability.