?
Solved

how to make a clone database usind coldbackup

Posted on 2009-12-21
8
Medium Priority
?
511 Views
Last Modified: 2013-12-18
Dear,
 I have a database server which include a PROD DB and I have other server for testing
I created DB by using DBCA by creating a templete from PROD DB and then I used DMP file and I import it to the Test server.

the prevoius was Hot back

but I want to do the cold back which I think is more easy

I will do some steps and I need to correct me and guide me please

This the step which I think I need it:

in the PROD DB server:
CMD
Set oracle_sid=PROD
Sqlplus/nolog
Conn sys /as sysdba
The datafiles:
Select name from v$datafile

E:\ORADATA\PROD\SYSTEM01.DBF
E:\ORADATA\PROD\UNDOTBS01.DBF
E:\ORADATA\PROD\SYSAUX01.DBF
E:\ORADATA\PROD\USERS01.DBF
E:\ORADATA\PROD\IFSAPP_DATA.DBF
E:\ORADATA\PROD\IFSAPP_INDEX.DBF
E:\ORADATA\PROD\IFSAPP_LOB.DBF
E:\ORADATA\PROD\IFSAPP_REPORT_DATA.DBF
E:\ORADATA\PROD\IFSAPP_REPORT_INDEX.DBF
E:\ORADATA\PROD\IFSAPP_ARCHIVE_DATA.DBF
E:\ORADATA\PROD\IFSAPP_ARCHIVE_INDEX.DBF


The control files:
select name from v$controlfile
E:\ORADATA\PROD\CONTROL01.CTL
E:\ORADATA\PROD\CONTROL02.CTL
E:\ORADATA\PROD\CONTROL03.CTL

SHUTDOWN the database normaly

then copy this file to the TEST DB

am I right ? need you help plz

 
 
0
Comment
Question by:albusaidi
7 Comments
 

Author Comment

by:albusaidi
ID: 26094687
I tried also to remove Business Management Software zone but I don't know how ?
0
 
LVL 15

Expert Comment

by:Franck Pachot
ID: 26095400
Yes that's right, if you have same file path on the destination.
Regards,
Franck.
0
 
LVL 48

Accepted Solution

by:
schwertner earned 252 total points
ID: 26096028
OFFLINE COLD BACKUP

1.      Preparation :
Obtain a list of the relevant files by querying V$DATAFILE, V$LOGFILE, V$CONTROLFILE and investigate all available redo log files. The connection should be made with sysdba privilege.

Connected to:
Oracle9i Enterprise Edition Release 9.0.1.1.1 - Production
With the Partitioning option
JServer Release 9.0.1.1.1 - Production

SQL> select name from v$datafile;  

NAME
--------------------------------------------------------------------------------
C:\ORACLE\ORADATA\MAIN\SYSTEM01.DBF
C:\ORACLE\ORADATA\MAIN\UNDOTBS01.DBF
C:\ORACLE\LSDCONTACT00.DBF
C:\ORACLE\ORADATA\MAIN\TOOLS01.DBF
C:\ORACLE\ORADATA\MAIN\USERS01.DBF
C:\ORACLE\LSICONTACT00.DBF
C:\ORACLE\LSDDOCUMENT00.DBF
C:\ORACLE\LSIDOCUMENT00.DBF
C:\ORACLE\LSDIDMAP00.DBF
C:\ORACLE\LSIIDMAP00.DBF
C:\ORACLE\LSDMEDICAL00.DBF
C:\ORACLE\LSIMEDICAL00.DBF
C:\ORACLE\LSDSYSTEM00.DBF
C:\ORACLE\LSISYSTEM00.DBF

14 rows selected.

SQL> select member from v$logfile;  

MEMBER
--------------------------------------------------------------------------------
C:\ORACLE\ORADATA\MAIN\REDO01.LOG
C:\ORACLE\ORADATA\MAIN\REDO02.LOG
C:\ORACLE\ORADATA\MAIN\REDO03.LOG

SQL> show parameter control_files;  

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_files                        string      C:\oracle\oradata\main\control
                                                            01.ctl, C:\oracle\oradata\main
                                                             \control02.ctl, C:\oracle\orad
                                                               ata\main\control03.ctl


or

 SQL> SELECT value FROM v$parameter
WHERE name = 'control_files';


Run
ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS <Path and Controlfilename>;

ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS /u03/ctl_file;

This will create a file with a copy of the physical structure of the database in case it is needed to recreate it at any time.

The command ALTER DATABASE BACKUP CONTROLFILE TO
TRACE provides a script to create the control file.
 In addition, the individual control files should also be backed-up by using the
command ALTER DATABASE BACKUP CONTROLFILE to <filename>.
This provides a binary copy of the control file at that time.


 
Locate the relevant parameter files :
init<sid>.ora file;        
config<sid>.ora file;
password file if used.
Also locate and back up the initialization parameter file and other Oracle product initialization files. To find them, do a search for *.ora starting in your Oracle home directory and recursively search all of its subdirectories.

Investigate where are all available redo log files:
SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u03/oradata/oralin1/arch
Oldest online log sequence     30
Next log sequence to archive   32
Current log sequence           32


Investigate the location of Archive Log files  and ensure they are transferred in a save location on another computer:

log_archive_dest
log_archive_dest_1
log_archive_dest_10
log_archive_dest_2
log_archive_dest_3
log_archive_dest_4
log_archive_dest_5
log_archive_dest_6

log_archive_dest_7
log_archive_dest_8
log_archive_dest_9
Note: If you are forced to perform a restore operation, you must restore the control files to all locations specified in the parameter file. Consequently, it is better to make copies of each multiplexed control fileeven if the control files are identicalto avoid problems at restore time.  

Locate the initialization parameter file and other Oracle product initialization files. To find them, do a search for *.ora starting in your Oracle home directory and recursively search all of its subdirectories:
root@oralin1:/ > locate *.ora

/u01/app/oracle/admin/oralin1/pfile/initorcf.ora
/u01/app/oracle/product/8.1.7/dbs/init.ora
/u01/app/oracle/product/8.1.7/dbs/initdw.ora
/u01/app/oracle/product/8.1.7/dbs/initorcf.ora
/u01/app/oracle/product/8.1.7/hs/admin/inithsodbc.ora
/u01/app/oracle/product/8.1.7/hs/admin/listener.ora
/u01/app/oracle/product/8.1.7/hs/admin/tnsnames.ora
/u01/app/oracle/product/8.1.7/network/admin/listener.ora
/u01/app/oracle/product/8.1.7/network/admin/samples/cman.ora
/u01/app/oracle/product/8.1.7/network/admin/samples/listener.ora
/u01/app/oracle/product/8.1.7/network/admin/samples/names.ora
/u01/app/oracle/product/8.1.7/network/admin/samples/sqlnet.ora
/u01/app/oracle/product/8.1.7/network/admin/samples/tnsnames.ora
/u01/app/oracle/product/8.1.7/network/admin/sqlnet.ora
/u01/app/oracle/product/8.1.7/network/admin/tnsnames.ora

oracle@oralin1:~ > locate orapw
/u01/app/oracle/product/8.1.7/bin/orapwd
/u01/app/oracle/product/8.1.7/bin/orapwdO
/u01/app/oracle/product/8.1.7/dbs/orapworcf

To do this prepare a shell script like:
#!/bin/sh

# Data files
cp /u01/app/oracle/oradata/oralin1/*.dbf   /u03/oradata/oralin1/cold_bku/

# Online log files
cp /u01/app/oracle/oradata/oralin1/*.log   /u03/oradata/oralin1/cold_bku/

#Control files
cp /u01/app/oracle/oradata/oralin1/*.ctl   /u03/oradata/oralin1/cold_bku/

#Archived log files
cp /u03/oradata/oralin1/arch/*.arc         /u03/oradata/oralin1/cold_bku/

# init<sid>.ora file
cp /u01/app/oracle/admin/oralin1/pfile/initorcf.ora   /u03/oradata/oralin1/cold_bku/

# NET ora files
cp /u01/app/oracle/product/8.1.7/network/admin/*.ora  /u03/oradata/oralin1/cold_bku/

# password file
cp /u01/app/oracle/product/8.1.7/dbs/orapworcf  /u03/oradata/oralin1/cold_bku/

Pay attention to the SPFILE placed in the location pointed by the parameter

SQL> show parameter SPFILE

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      C:\ORACLE11G\PRODUCT\11.1.0\DB
                                                 _1\DATABASE\SPFILEORCL.ORA
SQL>



Archive log investigation:


SQL> archive log list
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     37
Current log sequence           39



Execution :  

Use operating system commands or a backup utility to make backups of all
datafiles and all control files specified by the CONTROL_FILES parameter of
the initialization parameter file. Also back up the initialization parameter file
and other Oracle product initialization files. To find them, do a search for
*.ora starting in your Oracle home directory and recursively search all of its
subdirectories.

The database must be shut down cleanly (ie NORMAL, or IMMEDIATE).
Do not make a whole database backup when the instance is aborted or stopped because of a failure. Reopen the database and shut it down cleanly first.  
Take OS copies of the database files, redo log files, controlfiles and parameter files as specified above.  
Consider taking a copy of any relevant archive logs.  
After this is complete you may open the database again.  

A backup strategy is useless if the database being backed up is corrupt to begin with. Oracle contains online utilities to verify the correctness of a database; these tools should be used prior to starting the database backup operation. No database backup operation should be allowed to proceed if the database has not been recently verified as consistent in all respects.
It is recognized that it may not always be feasible to verify the integrity of the database before every database backup. However, an attempt should be made to verify the database integrity as often as operationally possible. For some details read section 2.6  of Database Backup and Recovery Strategy.
 





Avoiding the Backup of Online Redo Logs
Although it may seem that you should back up online redo logs along with the
datafiles and control file, this technique is dangerous. You should not back up
online redo logs for the following reasons:
"      The best method for protecting the online logs against media failure is by multiplexing them, that is, having multiple log members per group, on different disks and disk controllers.
"      If your database is in ARCHIVELOG mode, then ARCn is already archiving the redo logs.
"      If your database is in NOARCHIVELOG mode, then the only type of backups that you should perform are closed, consistent, whole database backups. The files in this type of backup are all consistent and do not need recovery, so the online logs are not needed.
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
LVL 1

Assisted Solution

by:shalowfast
shalowfast earned 248 total points
ID: 26146670
All you need to is backup the files given by this query;

     select name from v$datafile
     union
     select name from v$tempfile
     union
     select name from v$controlfile
     union
     select member name from v$logfile;

and the parameter file

     select value from v$parameter where name in ('spfile', 'ifile')and value is not null;

when i do could bakups i also get the sql to re-create the controlfile. This is useful if you whant to change the locations of the datafiles when yo clone the db in another server or if you whant to rename the database. You get the sql of the controlfile with this command:

     alter database backup controlfile to trace as '/some/arbitrary/path';

after you got the list of files yo need to backup you should shutdown the instance, i usually put it in read only mode in case i need to query something

     shutdown immediate
     startup mount
     alter database open read only;

this doesnt change the SCN and your backup would be consistent

hope this answer helps

regards
0
 
LVL 48

Expert Comment

by:schwertner
ID: 26165085
You have to pay attention to the SPFILE and the PASSWORD file.
Without them it is a hard task to recreate the DB.
0
 

Author Comment

by:albusaidi
ID: 26276803
i need time to try but it seems to be very diffecult
0
 
LVL 48

Expert Comment

by:schwertner
ID: 26278346
No, it isn't difficult.

You have to be acurate.

First investigate the location of ALL needed files - the current files!
Before copying the files shutdown the instance.
These are the two often mistakes ....
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Steps to fix “Unable to mount database. (hr=0x80004005, ec=1108)”.
Web hosting control panels were first developed to make it faster and easier for most users to set up and operate websites. The graphical user interface (GUI) allows users to perform tasks by pointing and clicking rather than typing highly specific…
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question