Cold (Warm) Backup of an Archivelog Mode Database
...begin backup

 

Major Cold backup steps
I. Determine where to copy the backup files and how much space is required
II. Identify the locations and names of the database files to copy
III. Shut down the database with the IMMEDIATE, TRANSACTIONAL, or NORMAL clause
IV. Copy the files (identified in step II) to the backup location (determined in step I)
V. Restart your database

Cold backup steps of an Archivelog Mode
1. Ensure that the database is in archivelog mode
2. Determine where to copy the backup files
3. Identify which files need to be backed up
4. Note the maximum sequence number of the online redo logs
5. Alter the database/tablespace into backup mode
6. Copy the data files with an OS utility to the location determined in step 2
7. Alter the database/tablespace out of backup mode
8. Archive the current online redo log, and note the maximum sequence number of the online redo logs
9. Back up the control file
10. Back up any archive redo logs generated during the backup

Prod DB - the production database from which we will copy
Copy DB - the second database to which we will copy

 

Ensure that you connected to the Prod DB

a) check server name and IP-address of the server

hostname
ifconfig

b) DB name

SQL> select name from v$database;
SQL> select instance from v$thread;

 

I. Determine where to copy the backup files and how much space is required 

1. Ensure that the database is in archivelog mode

Ensure That the Database Is in Archivelog Mode

SQL> archive log list;	Database log mode		Archive Mode
	Automatic archival		Enabled
	Archive destination		/u01/oraarch/O12C
					Win: C:\app\oradata\arch

2. Determine where to copy the backup files

SQL> select sum(bytes/1024/1024/1024) Gb from dba_data_files;

Usual Cold backup 

SQL> select name from v$datafile union select name from v$controlfile;

II. Identify the locations and names of the database files to copy

3. Identify which files need to be backed up

SQL> select name from v$datafile;

When you get to step 5, you may want to consider altering tablespaces one at a time into backup mode. If you take that approach, you need to know which data files are associated with which: tablespace: 

SQL> select tablespace_name, file_name from dba_data_files order by 1,2;

4. Note the maximum sequence number of the online redo logs

SQL> select thread#, max(sequence#) from v$log group by thread# order by thread#;

III. Shut down the Prod database with the IMMEDIATE, TRANSACTIONAL, or NORMAL clause

No need ))

5. Alter the database/tablespace into backup mode

SQL> alter database begin backup;

SQL> alter tablespace <tablespace_name> begin backup;

IV. Copy the files (identified in step 2) to the backup location (determined in step 1)

6. Copy the data files with an OS utility to the location determined in step 2

$ cp /u01/dbfile/O12C/*.dbf  /u01/hbackup/O12C
Win: cp C:\app\oradata\imkp\*.dbf G:\IMKP_FULL\hbackup\impk

7. Alter the database/tablespace out of backup mode

SQL> alter database end backup;

SQL> alter tablespace <tablespace_name> end backup;

Verify that no data files have an ACTIVE status

SQL> alter session set nls_date_format = 'DD-MON-RRRR HH24:MI:SS';
SQL> select * from v$backup where status='ACTIVE';

This is useful for determining the starting sequence number of the archivelog needed, in the event that the data file needs to be recovered

8. Archive the current online redo log, and note the maximum sequence number of the online redo logs

The following statement instructs Oracle to archive any unarchived online redo logs and to initiate a log switch. This ensures that an end-of-backup marker is written to the archive redo logs: 

SQL> alter system archive log current;

Also, note the maximum online redo log sequence number. If a failure occurs immediately after the hot backup, you need any archive redo logs generated during the hot backup to fully recover your database: 

SQL> select thread#, max(sequence#) from v$log group by thread# order by thread#;

9. Back up the control file

SQL> alter database backup controlfile to '/u01/hbackup/O12C/controlbk.ctl' reuse;
Win: SQL> alter database backup controlfile to 'G:\IMKP_FULL\hbackup\impk\controlIMKP.ctl' reuse;

10. Back up any archive redo logs generated during the backup

$ cp <archive redo logs generated during backup>  <backup directory>

V. Restart your Prod database

No need ))