Steps for Cloning Oracle database with hot backup

Cloning Oracle database with hot backup

1.First get the details of datafiles and archivelog location that present in the original database using the below command.

SQL> Select name from v$datafile;
SQL> archive log list;

2.Get the latest SCN by using the below command.

SQL> select max(first_change#) chng
2 from v$archived_log
3 /

CHNG
———-
424485

3.Bring the database in begin backup mode.

SQL> Alter database begin backup;

Database altered.

Note :- Make sure once you start begin backup mode there shouldn’t be No RMAN backup run on the database till you end the backup mode. Because till 10G if begin backup mode and RMAN backup is start at the same time there is a change of undo segment corruption and its a BUG reported by oracle support and I too faced the same problem, to resolve this we need downtime so be cautious.

4.Check the status whether the datafiles are in backup mode.

SQL> select * from v$backup;

FILE# STATUS CHANGE# TIME
———- —————— ———- ———
1 ACTIVE 424935 22-FEB-12
2 ACTIVE 424935 22-FEB-12

5.Now copy the datafiles physically from the original location to the destination.
Example :
SQL> select name from v$datafile;

NAME
——————————————————————————–
/u04/app/oracle/product/10.2.0/oradata/test/system01.dbf
/u04/app/oracle/product/10.2.0/oradata/test/undotbs01.dbf
SQL>exit
$ cd /u04/app/oracle/product/10.2.0/oradata/test/
$ ls -lrt
-rw-r—– 1 oracle staff 209723392 Feb 22 12:29 undotbs01.dbf
-rw-r—– 1 oracle staff 429924352 Feb 22 12:29 system01.dbf
$ cp *.dbf /u03/oradata/clonedb/data

Note :- If you want to clone the database to the different server refer the end of the command for SCP (server level copy command)

6.After copying all the datafiles to destination location stop the backup mode in the original database.

SQL> alter database end backup;

Database altered.

7. Check the status of the datafiles.

SQL> select * from v$backup;

FILE# STATUS CHANGE# TIME
———- —————— ———- ———
1 NOT ACTIVE 424935 22-FEB-12
2 NOT ACTIVE 424935 22-FEB-12

8.Archive the current logfile.

SQL> alter system archive log current;

System altered.

9.Get the details of archivelogs that will be needed for recovery while bringing up the clone database.

SQL> select name
2 from v$archived_log
3 where first_change# >= &change_no (424485)
4 order by name
5 /

Enter value for change_no: 424485
old 3: where first_change# >= &change_no
new 3: where first_change# >= 424485 (Enter the no which we got already ref #2)

NAME
——————————————————————————-
/u04/app/oracle/product/10.2.0/oradata/test/arch/1_26_775911007.dbf
/u04/app/oracle/product/10.2.0/oradata/test/arch/1_27_775911007.dbf

Copy the above listed archivelog files to the clone database archivelog location. (These logs are need for point in time recovery since we are cloning the database using hot backup)

10.Create Pfile from the spfile.

SQL> show parameter spfile

NAME     TYPE       VALUE
———————————— ———– ——————————
spfile      string        /u04/app/oracle/product/10.2.0/dbs/spfileprim.ora
SQL> create pfile from spfile;

File created.

11. In original database generate CREATE CONTROLFILE statement by typing the following command.

SQL>alter database backup controlfile to trace;
This will create a trace file containing the “CREATE CONTROLFILE” command to recreate the controlfile in text form.

12.Create the necessary directory on the clone database (destination database) server on your desired location.

Example :- mkdir udump adump cdump bdump arch

udump – user dump destination
bdump – background dump destination
adump – audit dump destination
cdump – core dump destination
arch – Archive log destination

13. Now, go to the USER_DUMP_DEST directory on the original Database server and open the latest trace file.The trace file will have the form “ora_NNNN.trc with NNNN being a number. This file will contain steps and as well as CREATE CONTROLFILE statement. Copy the CREATE CONTROLFILE statement and paste it in a notepad.

14. Edit the file

FROM: CREATE CONTROLFILE REUSE DATABASE “olddbname” RESETLOGS …
TO: CREATE CONTROLFILE SET DATABASE “newdbname” RESETLOGS …

Change the word ‘REUSE’ to ‘set’ and the ‘olddbname’ to ‘newdbname’. Also change the datafiles location parameter to clone database location.

15. Now copy the pfile from the original database server to the clone database server and placed it under the $ORACLE_HOME/dbs location. Now open the parameter file in clone database and change the following parameters with the respective current location.

CONTROL FILES
BACKGROUND_DUMP_DEST
USER_DUMP_DEST
CORE_DUMP_DEST
LOG_ARCHIVE_DEST_1

16. In clone database SERVER export ORACLE_SID environment variable and start the instance

$export ORACLE_SID=clone database name
$sqlplus
Enter User:/ as sysdba
SQL> startup nomount pfile=’init.clonedb.ora’;

17.Run create controlfile script to create the controlfile

SQL>@createcontrolfile.sql

Control file created.

18.Check the status of the Database.

SQL> select name,open_mode from v$database;

NAME OPEN_MODE
——— ———-
CLONE MOUNTED

19. Also check whether the clone database is pointing to its datafiles and dump files (just for verification)

SQL> select name from v$datafile;

NAME
——————————————————————————–
/u03/oradata/clone/data/system01.dbf
/u03/oradata/clone/data/undotbs01.dbf
/u03/oradata/clone/data/sysaux01.dbf
/u03/oradata/clone/data/users01.dbf

SQL> show parameter dump

NAME TYPE VALUE
————————- ———– ——————————
background_core_dump   string          partial
background_dump_dest    string         /u03/oradata/clone/bdump
core_dump_dest                   string         /u03/oradata/clone/cdump
max_dump_file_size           string         UNLIMITED
shadow_core_dump           string          partial
user_dump_dest                  string          /u03/oradata/clone/udump

20. Now media recovery is needed because we are cloning from the HOT backup. Follow the below setps.

SQL> recover database using BACKUP CONTROLFILE until cancel;

ORA-00279: change 424935 generated at 02/22/2012 12:29:27 needed for thread 1
ORA-00289: suggestion : /u03/oradata/clone/arch/1_27_775911007.dbf
ORA-00280: change 424935 for thread 1 is in sequence #27

21. Now we need to apply the necessary archive log files. (Refer the point no 9 & apply all the archivelog files )

Specify log: {=suggested | filename | AUTO | CANCEL}
/u03/oradata/clone/arch/1_27_775911007.dbf (Give the archive location and file name)
ORA-00279: change 425120 generated at 02/22/2012 12:37:58 needed for thread 1
ORA-00289: suggestion : /u03/oradata/clone/arch/1_28_775911007.dbf
ORA-00280: change 425120 for thread 1 is in sequence #28
ORA-00278: log file ‘/u03/oradata/clone/arch/1_27_775911007.dbf’ no longer
needed for this recovery

22.Once applied all the neceesary archive log files give cancel .

Specify log: {=suggested | filename | AUTO | CANCEL}
CANCEL
Media recovery cancelled.

23.Now open the database with open reset logs.

SQL> alter database open resetlogs;

Database altered.

SQL> select name,open_mode from v$database;

NAME OPEN_MODE
——— ———-
CLONE READ WRITE

24. Get the latest SCN no in the clone database

SQL> select max(first_change#) chng
2 from v$archived_log
3 /

CHNG
———-
424488 (It will matches with the No which we got it on the Point no #2 )

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

Command for Server copy (SCP)

If you want to clone the database on the different server, for copying the file from source to destination we need to use SCP comand.

Syntax:

$ scp your_username@remotehost.edu:foobar.txt /some/local/directory

Where, username is your Clone DB server login user id
remotehost is CLONE DB host name
foobar.txt is your filename
/some/local/directory is your destination location
Example :
Consider you are in the source server location (ie., Original DB location IP address 10.250.27.234)

$ pwd

/u04/app/oracle/product/10.2.0/oradata/test/

$ ls -lrt

rw-r—– 1 oracle staff 209723392 Feb 22 12:29 undotbs01.dbf
rw-r—– 1 oracle staff 429924352 Feb 22 12:29 system01.dbf

$ scp oracle@10.251.55.123:undotbs01.dbf /u03/oradata/clone/data/

Where, oracle is your Clone DB server login user id
10.251.55.123 is CLONE DB host name
undotbs01.dbf is your filename
/u03/oradata/clone/data is your destination

Script to find open cursors details

set pages 999
select sess.username
, sess.sid
, sess.serial#
, stat.value cursors
from v$sesstat stat
, v$statname sn
, v$session sess
where sess.username is not null
and sess.sid = stat.sid
and stat.statistic# = sn.statistic#
and sn.name = ‘opened cursors current’
order by value
/

Script to check sufficient space for undo generation

select sum(decode(status,’EXPIRED’,bytes,0))/sum(bytes) EXPIRED_PERCENT, sum(decode(status,’UNEXPIRED’,bytes,0))/sum(bytes) UNEXPIRED_PERCENT, sum(decode(status,’ACTIVE’,bytes,0))/sum(bytes) ACTIVE_PERCENT from dba_undo_extents;

Useful script to get the basic status of the DB

Status

Startup time

select to_char(startup_time, ‘HH24:MI DD-MON-YY’) “Startup time” from v$instance /

======================================================================
How large is the database

SELECT ROUND (SUM (used.BYTES) / 1024 / 1024 / 1024) || ‘GB’ “Database
Size”,
ROUND (SUM (used.BYTES) / 1024 / 1024 / 1024)
– ROUND (SUM (free.BYTES) / 1024 / 1024 / 1024)
|| ‘GB’ “Used Space”,
ROUND (SUM (free.BYTES) / 1024 / 1024 / 1024) || ‘GB’ “Free
Space”
FROM (SELECT BYTES
FROM v$datafile
UNION ALL
SELECT BYTES
FROM v$tempfile
UNION ALL
SELECT BYTES
FROM v$log) used,
(SELECT BYTES
FROM dba_free_space) free;

=============================================================
Which schemas are taking up all of the space

SELECT obj.owner, obj.obj_cnt, seg.seg_size
FROM (SELECT owner, COUNT (*) obj_cnt
FROM dba_objects
GROUP BY owner) obj,
(SELECT owner, CEIL (SUM (BYTES) / 1024 / 1024) seg_size
FROM dba_segments
GROUP BY owner) seg
WHERE obj.owner = seg.owner
ORDER BY seg.seg_size desc;

====================================================
Distribution of objects and data

SELECT obj.owner “Owner”, obj_cnt “Objects”,
DECODE (seg_size, NULL, 0, seg_size) “size MB”
FROM (SELECT owner, COUNT (*) obj_cnt
FROM dba_objects
GROUP BY owner) obj,
(SELECT owner, CEIL (SUM (BYTES) / 1024 / 1024) seg_size
FROM dba_segments
GROUP BY owner) seg
WHERE obj.owner = seg.owner(+)
ORDER BY 3 DESC, 2 DESC, 1

======================================================
Show the ten largest objects in the database

SELECT owner, segment_name, segment_type, mb
FROM (SELECT owner, segment_name, segment_type, BYTES / 1024 / 1024
“MB”
FROM dba_segments
ORDER BY BYTES DESC)
WHERE ROWNUM < 11;

=====================================================
Is java installed in the database?

SELECT COUNT (*)
FROM all_objects
WHERE object_type LIKE ‘%JAVA%’ AND owner = ‘SYS’;

========================================================
Display character set information

SELECT *
FROM nls_database_parameters;

===================================================
Show all used features

SELECT NAME, detected_usages
FROM dba_feature_usage_statistics
WHERE detected_usages > 0;

Script to check AWR retention period

col snap_interval format a30
col retention format a30
select snap_interval,
retention
from dba_hist_wr_control
/

Script to find the DB size

select round(sum(bytes)/1024/1024/1024) ||’ GB – DATA’ as DATA from dba_data_files;

select round(sum(bytes)/1024/1024/1024) ||’ GB – TEMP’ as TEMP from dba_temp_files;

select round(sum(bytes)/1024/1024/1024) ||’ GB – LOGS’ as LOGS from v$log;

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

To find the DB size including the Temp tablespace size:-
—————————————————–

select dbfile+logfile+tmpfile dp_space from (select sum(bytes)/(1024*1024*1024) dbfile from v$datafile),(select sum(bytes)/(1024*1024*1024) logfile from v$logfile a,v$log b where a.group#=b.group#),(select sum(bytes)/(1024*1024*1024) tmpfile from v$tempfile where status=’ONLINE’);

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

To find the DB size without the Temp tablespace size:-
—————————————————–

— How large is the database

col “Database Size” format a20
col “Free space” format a20
col “Used space” format a20
select round(sum(used.bytes) / 1024 / 1024 / 1024 ) || ‘ GB’ “Database Size”
, round(sum(used.bytes) / 1024 / 1024 / 1024 ) –
round(free.p / 1024 / 1024 / 1024) || ‘ GB’ “Used space”
, round(free.p / 1024 / 1024 / 1024) || ‘ GB’ “Free space”
from (select bytes
from v$datafile
union all
select bytes
from v$tempfile
union all
select bytes
from v$log) used
, (select sum(bytes) as p
from dba_free_space) free
group by free.p
/

Script to find how many redo log files have generated per day

SELECT A.*,Round(A.Count#*B.AVG#/1024/1024) Daily_Avg_Mb FROM (SELECT To_Char(First_Time,’YYYY-MM-DD’) DAY,Count(1) Count# FROM v$log_history GROUP BY To_Char(First_Time,’YYYY-MM-DD’) ORDER BY 1 DESC) A,(SELECT Avg(BYTES) AVG#,Count(1) Count# FROM v$log) B ;

=====================================================================
REDO LOGS Generated per day :-
____________________________

select trunc(completion_time) rundate
,count(*) logswitch
,round((sum(blocks*block_size)/1024/1024)) “REDO PER DAY (MB)”
from v$archived_log
group by trunc(completion_time)
order by 1;

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

SELECT trunc(first_time) DAY,
count(*) NB_SWITCHS,
trunc(count(*)*log_size/1024) TOTAL_SIZE_KB,
to_char(count(*)/24,’9999.9′) AVG_SWITCHS_PER_HOUR
FROM v$loghist,
(select avg(bytes) log_size from v$log) GROUP BY trunc(first_time),log_size;

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

Redo Logs generation on Hourly basis

———————————————————–

set pagesize 120;
set linesize 200;
col day for a8;
spool archivelog.lst

PROMPT Archive log distribution per hours on each day …

select
to_char(first_time,’YY-MM-DD’) day,
to_char(sum(decode(substr(to_char(first_time,’HH24′),1,2),’00’,1,0)),’999′) “00”,
to_char(sum(decode(substr(to_char(first_time,’HH24′),1,2),’01’,1,0)),’999′) “01”,
to_char(sum(decode(substr(to_char(first_time,’HH24′),1,2),’02’,1,0)),’999′) “02”,
to_char(sum(decode(substr(to_char(first_time,’HH24′),1,2),’03’,1,0)),’999′) “03”,
to_char(sum(decode(substr(to_char(first_time,’HH24′),1,2),’04’,1,0)),’999′) “04”,
to_char(sum(decode(substr(to_char(first_time,’HH24′),1,2),’05’,1,0)),’999′) “05”,
to_char(sum(decode(substr(to_char(first_time,’HH24′),1,2),’06’,1,0)),’999′) “06”,
to_char(sum(decode(substr(to_char(first_time,’HH24′),1,2),’07’,1,0)),’999′) “07”,
to_char(sum(decode(substr(to_char(first_time,’HH24′),1,2),’08’,1,0)),’999′) “08”,
to_char(sum(decode(substr(to_char(first_time,’HH24′),1,2),’09’,1,0)),’999′) “09”,
to_char(sum(decode(substr(to_char(first_time,’HH24′),1,2),’10’,1,0)),’999′) “10”,
to_char(sum(decode(substr(to_char(first_time,’HH24′),1,2),’11’,1,0)),’999′) “11”,
to_char(sum(decode(substr(to_char(first_time,’HH24′),1,2),’12’,1,0)),’999′) “12”,
to_char(sum(decode(substr(to_char(first_time,’HH24′),1,2),’13’,1,0)),’999′) “13”,
to_char(sum(decode(substr(to_char(first_time,’HH24′),1,2),’14’,1,0)),’999′) “14”,
to_char(sum(decode(substr(to_char(first_time,’HH24′),1,2),’15’,1,0)),’999′) “15”,
to_char(sum(decode(substr(to_char(first_time,’HH24′),1,2),’16’,1,0)),’999′) “16”,
to_char(sum(decode(substr(to_char(first_time,’HH24′),1,2),’17’,1,0)),’999′) “17”,
to_char(sum(decode(substr(to_char(first_time,’HH24′),1,2),’18’,1,0)),’999′) “18”,
to_char(sum(decode(substr(to_char(first_time,’HH24′),1,2),’19’,1,0)),’999′) “19”,
to_char(sum(decode(substr(to_char(first_time,’HH24′),1,2),’20’,1,0)),’999′) “20”,
to_char(sum(decode(substr(to_char(first_time,’HH24′),1,2),’21’,1,0)),’999′) “21”,
to_char(sum(decode(substr(to_char(first_time,’HH24′),1,2),’22’,1,0)),’999′) “22”,
to_char(sum(decode(substr(to_char(first_time,’HH24′),1,2),’23’,1,0)),’999′) “23”,
COUNT(*) TOT
from v$log_history
group by to_char(first_time,’YY-MM-DD’)
order by day ;