DATABASE BACKUPS


Physical backup is a copy of physical file at particular point in time.
There are two types of physical backups

a) Cold backup or offline backup or closed backup
b) Hot backup or online backup or open backup

Every database has two phases

     a) Physical phase nothing but physical files (data files)
        OS level files stored in Harddisk.
        *.dbf,*.ctl, *.log
          Hard disk is media. If it is corrupted we cannot recover.
         So we will keep all physical files in different disk.
 For media recovery the database must be in archive log mode.
To recover the database the database must be in archive log mode.
COLD BACKUP

 a) Shutdown the database properly.
  b) Take a backup of control files, database files,
    redo log files.
  c) If the database is running in archivelog mode redo log files     is optional.

 
HOTBACKUP
  a) Hot backup is a backup of database files and control files
       when the database is up and running.
   b) The database must be in archive log mode.
  c) Recovery is possible only when the database is in archivelog
     mode.
  d) The procedure is different for hotbackup and cold backup.
       But recovey procedure is 100% same.
       whether it is cold or hot backup.
 In real time the database must be up and running.
  sql> select status from v$instance;

COLDBACKUP PROCEDURE

sql>shutdown immediate;
I would like to know where the c/r/d files are located at
To know the locations use the following command
$cd prd
$cp *.dbf *.ctl *.log /location
*.log are manadatory when the database is cold backup
*.log are optional when the database is hot backup.


$. .bash_profile
$export $ORACLE_HOME=prd
$sqlplus '/as sysdba'
sql>select name from v$controlfile;
sql>select name from v$datafile;
sql>select member from v$logfile;
sql>exit
$cd $ORACLE_HOME/dbs
$ls
dbs]vi initprd.ora
go to insert mode
log_archive_dest=/home/orcl/arch
save and exit pfile
$pwd
/home/oracle
$mkdir arch
log in as root user and give the permissions
#chmod -R 777 /home/oracle/arch
$echo $ORACLE_SID
 prd
if it is not prd then
$export $ORACLE_SID = prd
$echo $ORACLE_SID
 prd

Check whether the database is running in archive log or noarchive log mode.
sql>archive log list;
Right now the database in no archive log mode.
       or
We can find by using the following statement
sql>select instance_name, status from v$instance;
sql>shut immediate;
sql>startup mount;
sql>alter database archivelog;
sql>archive log list;
       archive log mode
sql>alter database open;
we are not doing anything so arch folder contains empty.
We can force log switch using this command.
How we can switch the log?
sql>alter system switch logfile;
    
Now check the arch folder some archive information is generated.
here we didn't do any transaction so these are empty.
In archive log files contains



How to take a cold backup? (Practical Demo)
sql>select name from v$database;
prd
sql>select name from v$datafile;
sql>select name from v$controlfile;
sql>select member from v$logfile;
Shutdown the database properly
sql>shut immediate;
sql>exit
go to /home/oracle/prd folder.
]$cd /home/oracle/prd
 prd]$mkdir cold
 prd]$cp *.dbf *.log *.ctl cold/ ( or u can specify the path)
go to home directory
$sqlplus '/as sysdba'
sql>startup
sql>
           ----------End of Cold Backups--------------






How to take hot backup?

Algorithm
-----------

Before taking hot backup check whether the database is in archivelogmode or not.
#xhost +
#su - oracle
go to /home/oracle/prd folder
 prd]$mkdir hotbkp
prd]$cp  *.dbf /home/oracle/prd/hotbkp
when the database is in open stage we take a backup is called hotbackup.
In hotbackup we take data files and control files.
 because log files is blank. Because log files are written onto   archive log files.

To take hot backup

a) First we keep the database is in begin backup mode.
b) Go to OS level and take a backup of data files.
sql>show user
     SYS
sql>
sql>select * from v$backup;
That means the database is hotbackup mode or not.

sql>select * from v$backup;
here stataus is NOT ACTIVE
sql>alter database begin backup'
sql>select * from v$backup;
      now ACTIVE
sql>host
$cd prd
prd]$ls
if hotbkp folder is not there create it
  prd]$mkdir hotbkp
prd]$cp *.dbf hotbkp/
 $exit
sql>select * from v$backup;
active
sql> alter database end backup;
(this is used for come out of the hot backup mode)
The above one is data files backup.






Control files backup
 In hot backup mode we take backup of control files at database level
 sql>alter database backup controlfile to
  '/home/oracle/prd/hotbkp/bkpcnt.ctl';

  Database altered
sql>host
$cd prd
prd]$cd hotbkp/
 hotbkp]$ls
     First we have to take datafiles and then control files
  bcz control files contains datafiles information.
#sql>startup force;
# $rm -rf cold/
# $rm -rf hotbkp/


Senario :
When Control files, Redolog fiels and Data files are lost
----------------------------------
Assume that we have last night cold backup.
sql>archive log list;
archive log mode
sql>shut immediate;
sql>host
cd prd
prd]$cp * coldbkp/
prd]$exit
sql>startup
sql>startup force;
sql>select * from all_users;
 10 rows selected

sql>grant connect,resource to naveen identified by naveen;
 one user created
sql>connect naveen/naveen;
sql>create table demo (a number)
sql>insert into demo values(&a)
 insert some data.
sql>connect /as sysdba;
sql>select * from all_users;


Here i would like to generate archive log file forcebly;
sql>alter system switch logfile;
sql>/
sql>/ (safe side)
open another termianl
login as oracle user
go to prd directory
 prd]$rm *
prd]$
sql>select * from dba_users;
 It is throwing error
 bcz all files are deleted.
  here system.dbf is file missing go and check whethe it
 is there or not.
if developer complains then
as a dba i will do the following things.
sql>shut abort;
sql>host
$cd prd
prd]$cd coldbkp/
coldbkp]$ls
coldbkp]$cp * /home/oracle/prd/
 (This is known as restoring)


 go to home directory
$sqlplus '/as sysdba'
connected to an idle instance
sql>startup mount;
(Recovey operations are possible in mount stage only)
sql>recover database using backup controlfile until cancel;
                   It is showing archive log file number
specify log: {<RET>=suggested |filename |AUTO |CANCEL)
here press enter it will apply
again it will ask another
enter again
enter
again suggesting another is required check whether the number is
available it or not
It is asking 73 it is not there.
so enter CANCEL
media recovery cancelled.
sql>
open anothe terminal go to arch folder
check whether it is there or not
 arch]$ls -lrt

sql>alter database open;
must user RESETLOGS or NORESETLOGs option for database open;
sql>alter database open resetlogs;
Database altered.
sql>select * from all_users;
here i got 11 users;
sql>connect naveen/naveen;
sql>select * from tab;
sql>select count(*) from demo;
Right now log sequence number is 72.
sql>alter system switch logfile;
go to arch folder and check it .
 It will change the number. Instead of 73 we will get another number.
In two cases we are using resetlog option while opening the database.
a) when we loose control file
Use backup controlfile to recover the database
b) When we loose redo logfiles
      after performing recovery we are using the reset log option with
        alter database open statement to recreate the log files.

No comments:

ORA-00059: Maximum Number Of DB_FILES Exceeded in 19C database

When I am adding datafile to my 19C database facing the below error. SQL> alter tablespace DATA  add datafile '/u01/data/data15.dbf&#...