SQL> conn a1/a1
Connected.
SQL> create table stu
2 (
3 sno number(5),
4 sname varchar2(15),
5 adds varchar2(10)
6 );
Table created.
SQL> desc stu
Name Null? Type
----------------------- -------- -----------------------------------
SNO NUMBER(5)
SNAME VARCHAR2(15)
ADDS VARCHAR2(10)
Creating datafile:-
[vamsi@vamsi]$ vi stu.txt
10,sharan,a
34,manoj,b
76,ram,f
69,ajay,t
40 ragu,r
20krishr
:wq!
Creating control file:
[vamsi@vamsi]$ vi c1.ctl
load data
infile 'stu.txt'
badfile 'bad.txt'
discardfile 'dis.txt'
insert into table stu
fields terminated by ','
(sno,sname,adds)
:wq!
[vamsi@vamsi]$ sqlldr userid=a1/a1 control=c1.ctl
SQL*Loader: Release 9.2.0.1.0 - Production on Tue Apr 15 12:28:13 2008
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Commit point reached - logical record count 6
[vamsi@vamsi sqlldr]$ sqlplus '/as sysdba'
SQL*Plus: Release 9.2.0.1.0 - Production on Tue Apr 15 12:30:26 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> conn a1/a1
Connected.
SQL> select * from stu;
SNO SNAME ADDS
------- ---------- ----------
10 sharan a
34 manoj b
76 ram f
69 ajay t
[vamsi@vamsi sqlldr]$ vi bad.txt
40 ragu,r
20krishr
Monday, August 11, 2008
Flash back in Oracle 9i
sql>select username from dba_users;
USERNAME
------------------------------
SYS
SYSTEM
DBSNMP
OUTLN
SCOTT
A1
6 rows selected.
sql>conn a1/a1
Connected.
sql>select * from tab;
TNAME TABTYPE CLUSTERID
--------------------------- ------- ---------
BONUS TABLE
DEPT TABLE
EMP TABLE
SALGRADE TABLE
sql>select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---- ------ ----- ---- --------- -------- -------- --------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN ALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
sql>conn /as sysdba
Connected.
sql>grant execute on dbms_flashback to a1;
Grant succeeded.
sql>conn a1/a1
Connected.
sql>select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
122593
sql>delete from emp where deptno=10;
3 rows deleted.
sql>select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---- ------ ----- ---- --------- -------- -------- --------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN ALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
11 rows selected.
sql>execute dbms_flashback.enable_at_system_change_number(122592);
PL/SQL procedure successfully completed.
(or)
sql>execute dbms_flashback.enable_at_time(timestamp '2008-04-10
10:15:00');
PL/SQL procedure successfully completed.
sql>select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---- ------ ----- ---- --------- -------- -------- --------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN ALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
sql>execute dbms_flashback.disable;
PL/SQL procedure successfully completed.
sql>select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---- ------ ----- ---- --------- -------- -------- --------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN ALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
11 rows selected.
USERNAME
------------------------------
SYS
SYSTEM
DBSNMP
OUTLN
SCOTT
A1
6 rows selected.
sql>conn a1/a1
Connected.
sql>select * from tab;
TNAME TABTYPE CLUSTERID
--------------------------- ------- ---------
BONUS TABLE
DEPT TABLE
EMP TABLE
SALGRADE TABLE
sql>select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---- ------ ----- ---- --------- -------- -------- --------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN ALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
sql>conn /as sysdba
Connected.
sql>grant execute on dbms_flashback to a1;
Grant succeeded.
sql>conn a1/a1
Connected.
sql>select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
122593
sql>delete from emp where deptno=10;
3 rows deleted.
sql>select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---- ------ ----- ---- --------- -------- -------- --------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN ALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
11 rows selected.
sql>execute dbms_flashback.enable_at_system_change_number(122592);
PL/SQL procedure successfully completed.
(or)
sql>execute dbms_flashback.enable_at_time(timestamp '2008-04-10
10:15:00');
PL/SQL procedure successfully completed.
sql>select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---- ------ ----- ---- --------- -------- -------- --------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN ALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
sql>execute dbms_flashback.disable;
PL/SQL procedure successfully completed.
sql>select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---- ------ ----- ---- --------- -------- -------- --------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN ALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
11 rows selected.
Saturday, July 26, 2008
Oracle 10g
Download
Using Network_Link in Datapump
Oracle's export and import utilities have historically used a disk file as intermediate storage when unloading or reloading the database. For large databases, this "dump file" was an issue because operating system limits on file size could be exceeded, making export impossible.
Creative DBAs have used file compression utilities, such as compress on UNIX, to get the most capacity from the dump file. Later versions of import and export allowed the use of multiple dump files to get around the limits.
In Oracle 10g, the Data Pump version of import can eliminate the dump file entirely by importing directly from another database instance.
Example:
1. Create tnsnames for remote database from where the database has to be imported.[Target]
conn_151 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.9.200.151)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = wisedba.com)
)
)
2. Create a directory for user to access during Datapump job[Target]
SYS> create directory defdir as '/home/ez10g/wisedba';
SYS> grant read, write on directory defdir to scott;
3. Grant user to create database link[Target}
SYS> grant create database link to scott;
4. Create database link as Scott user (to the remote database)[Target]
SCOTT> Create database link conn_151
2 connect to scott identified by tiger
3 using 'conn_151';
5. Checking tables in scott user (in local database)[Target]
SCOTT> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BONUS TABLE
6. Import 'EMP' table from remote database (without creating any dump file)[Target]
$ impdp scott/tiger tables=emp directory=defdir network_link=conn_151
Import: Release 10.2.0.1.0 - Production on Monday, 02 April, 2007 18:04:53
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "SCOTT"."SYS_IMPORT_TABLE_01": scott/******** tables=emp directory=defdir network_link=conn_151
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 6 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . imported "SCOTT"."EMP" 114688 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SCOTT"."SYS_IMPORT_TABLE_01" successfully completed at 18:05:12
7. Check whether the table has been imported to local database
SCOTT> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
EMP TABLE <<-- Table has been imported
BONUS TABLE
Using Network_Link in Datapump
Oracle's export and import utilities have historically used a disk file as intermediate storage when unloading or reloading the database. For large databases, this "dump file" was an issue because operating system limits on file size could be exceeded, making export impossible.
Creative DBAs have used file compression utilities, such as compress on UNIX, to get the most capacity from the dump file. Later versions of import and export allowed the use of multiple dump files to get around the limits.
In Oracle 10g, the Data Pump version of import can eliminate the dump file entirely by importing directly from another database instance.
Example:
1. Create tnsnames for remote database from where the database has to be imported.[Target]
conn_151 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.9.200.151)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = wisedba.com)
)
)
2. Create a directory for user to access during Datapump job[Target]
SYS> create directory defdir as '/home/ez10g/wisedba';
SYS> grant read, write on directory defdir to scott;
3. Grant user to create database link[Target}
SYS> grant create database link to scott;
4. Create database link as Scott user (to the remote database)[Target]
SCOTT> Create database link conn_151
2 connect to scott identified by tiger
3 using 'conn_151';
5. Checking tables in scott user (in local database)[Target]
SCOTT> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BONUS TABLE
6. Import 'EMP' table from remote database (without creating any dump file)[Target]
$ impdp scott/tiger tables=emp directory=defdir network_link=conn_151
Import: Release 10.2.0.1.0 - Production on Monday, 02 April, 2007 18:04:53
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "SCOTT"."SYS_IMPORT_TABLE_01": scott/******** tables=emp directory=defdir network_link=conn_151
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 6 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . imported "SCOTT"."EMP" 114688 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SCOTT"."SYS_IMPORT_TABLE_01" successfully completed at 18:05:12
7. Check whether the table has been imported to local database
SCOTT> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
EMP TABLE <<-- Table has been imported
BONUS TABLE
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';
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.
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.
Subscribe to:
Posts (Atom)