Oracle Physical Backup

Physical backups copy database files (e.g., control files, datafiles, redo logs) to a different secure location or backup server.

There are two types:

1. cold backup
2. hot backup

Cold Backup:

1. Database State is Offline mode(shutdown the database), no user access.
2. Database is consistent by default.
3. Always synchronized (no changes during backup).

Hot Backup:

1. Database State is Online, users can modify data.
2. Required to Database in ARCHIVELOG mode.
3. Changes logged during the backup are applied for consistency.
4. data file header freezed during this backup. so, generating the More Redo log during this backup.
5. media recovery is need.

Cold Backup:

check tablespace and datafile lists:

col TABLESPACE_NAME format a20
col FILE_NAME  format a50
set linesize 250
Select tablespace_name, file_name, bytes/1024/1024 as bytes_in_MB from dba_data_files;

check control file location and pfile location:

select name from v$controlfile;
show parameter control_file
show parameter pfile

check redo log file location:
select member from v$logfile;

process:

1. shutdown the database
2. copy all file to backup location
3. startup the database

Hot Backup all files(data,redo,control and parameter file):

step 1:

shut immediate
startup mount
alter database archivelog;
alter system set log_archive_dest_1='location=/u01/app/oracle/oradata/PEARL/arch/' scope=both;
alter database open;

select group#,sequence#,status from v$log

alter database begin backup;

select file#, status from v$backup;

alter database end backup;

select file#, status from v$backup;

control_file backup:

alter database backup controlfile to '/u02/backup/hot/control.bkp';

copy the particular log file only

Hot Backup only particular tablespace:

ALTER TABLESPACE tablespace_name BEGIN BACKUP;

cp /u01/app/oracle/oradata/PEARL/users01.dbf /u02/backup/hot/

ALTER TABLESPACE tablespace_name END BACKUP;

Recover the control files: (Need to Down the databse)

remove the control files

Step 1:

shut abort

copy the backup control file to control file location

Step 2:

startup mount
recover database using backup controlfile until cancel;
/u01/app/oracle/oradata/PEARL/redo01.log --give one by one redo log file
alter database open resetlogs;

select instance_name,status from v$instance;

recover the system data file:(Need to Down the databse)

Step 1:

shut abort
copy the backup system data file to current system files

Step 2:

startup mount
recover tablespace system;
AUTO
alter database open;

Recover the non-system data file: (no need to shutdown)

select file_name from dba_data_file where tablespace_name='USERS';

delete the users data file

copy backup user data file to current data loation

alter tablespace users offline;

recover tablespace users;
AUTO

alter tablespace users online;

Clone using hot backup

create target database data directory:

cd /u02
mkdir clone
chown -R oracle. clone
su - oracle
cd /u02/clone
mkdir admin oradata
cd  admin
mkdir pfile diag adump reco
cd ../oradata
mkdir data log control arch

create target database bash_profile:(change dbname and data location)
su - oracle
cp -rf .bash_profile .env_clone

create pfile for target:
create pfile='/u02/clone/admin/pfile/initclone.ora' from spfile;

start hot backup
copy the all data and log files to clone location
stop hot backup

controlfile backup:

alter database backup controlfile to trace as '/u02/clone/oradata/control/control.bkp';

edit pfile, and create controlfile use backup and replace the resuse to set

run the environment file (.env_clone)
[oracle@localhost ~]$ . .env_clone
[oracle@localhost ~]$
[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Nov 23 10:36:20 2024
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Connected to an idle instance.

startup pfile='/u02/clone/admin/pfile/initclone.ora' nomount;

@/u02/clone/oradata/control/control.bkp

select * from v$logfile

recover database using backup controlfile until cancel;

alter database open reset logs;

clone use cold backup:

create target database data directory:

cd /u03
mkdir clone
chown -R oracle. clone
su - oracle
cd /u03/clone
mkdir admin oradata
cd  admin
mkdir pfile diag adump reco
cd ../oradata
mkdir data log control arch

create target database bash_profile:(change dbname and data location)
su - oracle
cp -rf .bash_profile .env_clone

create pfile for target:
create pfile='/u03/clone/admin/pfile/initclone.ora' from spfile;

shutdown the database
copy the all data and log files to clone location
start the databsae

control file backup:

alter database backup controlfile to trace as '/u03/clone/oradata/control/control.bkp';

edit pfile, and create controlfile use backup and replace the resuse to set

run the environment file (.env_clone)
[oracle@localhost ~]$ . .env_clone
[oracle@localhost ~]$
[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Nov 23 10:36:20 2024
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Connected to an idle instance.

startup pfile='/u03/clone/admin/pfile/initclone.ora' nomount;

@/u03/clone/oradata/control/control.bkp

select * from v$logfile

alter database open;