Database structed both Logical and Physical
Logical structure of the database: tablespaces, schema objects (tables, views,
indexs, procedures, functions....)
Physical structure: datafiles, redologfiles, controlfiles, archivelogfiles,
parameter files, passwords files.
Oracle Database backup and Recovery:
Types of Backup:
Logical Backup
Physical Backup
Logical backup:
export and Import
till 9i: exp and imp (buffer=100m)
from 10g onwards: expdp and impdp
Full database backup: recommended database user to perform the full backup is
system.
edwprod:
create a directory with full location tbs1
GRANT READ, WRITE ON DIRECTORY TO USER;
orenv
eg: expdp full=u direcotry= dumpfile= logfile=
user: / as sysdba
user : system
password: password
importing of the full database expdp backup:
edwdev:
empty databbase
create the database structure: tablespaces tbs01 with same sizes (if filesystsem
structure is same , then no need of creating the databas structure)
impdp remap_tablespace=tab1:tab01
Schema level:
remap_schema=user1:dxc
schema refresh activity:
impdp command--->
not recommended to drop the schema
drop the objects of the schema ( tables and non-tables objects)
set pagesize 0
set feedback off
set heading off
set trimspool on
set verify off
spool drop_objects.sql
select 'DROP ' || object_type || ' ' || owner || '.' || object_name ||
decode(object_type, 'TABLE',' cascade constraints;',';')
from dba_objects
where owner = '&schema'
/
spool off
set feedback on
set heading on
table/objects backup:
imp:
drop table ---both data and structure
truncate table --drops only data
table_exists_action=truncate (append/replace/skip)
parallel=<[Link] CUPS>
dba_datapump_jobs ---name, opeartion
expdp user/passw attach=<name>
expdp>stop_job/start_job/status
**************************************************************
Physical Backup:
At the entire database level, tablespaces level
Types:
Online/Hot Backup --- ARCHIVELOG MODE
offline/Cold Backup --NOARCHIVELOG MODE
USER managed Backup: HOT backup
oracle Managed backup: RMAN
Complete recovery: only if we have present controlfile + current redologfiles
Incomplete recovery: any of present controlfile or current redolog file is
lost----open database with resetlogs
MTTR_TARGET=10MINS
datafiles, redologfiles, controlfiles, archivelogfiles, parameter files, passwords
files
1) If we lost pfile/spfile and we don't have backup:
startup: nomount
it reads the pfile/spfile and puts the database in nomount statE (Oracle
Instance: SGA Memory componnets allocated + BG procees)
2) if we lost controlfile
mount: it will read the controlfile and corresponding database datafile with name
and path
Standard practice is : Controlfiles are multiplexed; more than 1 on different
location
if controfiles are multiplexed ( 2), then identify which controfile is lost(1) (NOT
able to ready)
copy the good control file 2 to 1 controlfile and rename the control01
ALTER DATBAASE MOUNT:
CTRL2
CTRL3
CONTROL_FILES=/CTRL1/[Link],/CTRL2/[Link], /CTRL3/[Link]
rman> restore controlfile from autobackup;
rman> restore controlfile from <BACKUPPIECE>;
rman> SHOW ALL;
SNAPSHOT controlfile backup locaTION <snapxxx>
$ORACLE_HOME/dbs
<snapxxx>
CP $ORACLE_HOME/dbs/<snapxxx> /CTRL1/[Link]
CP $ORACLE_HOME/dbs/<snapxxx> /CTRL2/[Link]
CP $ORACLE_HOME/dbs/<snapxxx> /CTRL3/[Link]
3) passwordfile: it store the password of sys
sys
/ as sysdba (password file + remote_login_passwordfile=exclusive)
orpwd utilizty
orpwd file=<path>orpw<sid_name>.ora password=sys_passwd entries=4
4) loss of redologfile/s
REDO GROUPS recommended 3
group1 ---recommended min 2 members(mirrored) (redologfiles) -INACTIVE
group2 ---recommended min 2 member(mirrored) (redologfiles)
group3 ---recommended min 2 member(mirrored) (redologfiles)
if one member of group 1 is lost:
No issues to the database availabiiTY
Only a erro/warning message writtne as 2nd member of group 1 is not accessible in
[Link]
DROP THE REDOLOG GROUP 1 MEMBER <2ND memberpath>;
crate redolog group 1 member <2ND memberpath>;
CURRENT/ACTIVE/INACTIVE/UNUSED
If status of a group is INACITVE/USRED
--INACTIVE
No issues to the database availabiiTY
drop and recreate the redolog group 1
alter database clear archived redolog group 1;
--CURRENT --ALL MEMBERS OF GROUP LOST ( group2)
even we have datafiles, controfiles, archivelog files
alter database clear archived redolog group 1;
IF the above command fails, then we have only below option
restore datafiles, controfiles from backup and perform point in time
recovery(Before the current redolog group lost) by applying archivelogs
ACTIVE
even we have datafiles, controfiles, archivelog files
alter database clear archived redolog group 1;
IF the above command fails, then we have only below option
restore datafiles, controfiles from backup and perform point in time
recovery(Before the current redolog group lost) by applying archivelogs
IF THE DATABASE IS IN NOARCHIVELOG MODE, DOES THE RECOVERY IS POSSIBLE?
STARTED THE DATABASE
GROUP1--ACTIVE
GROUP2--CURRENT
GROUP3---INACTIVE
5) Full database restore and recovery
6) LOSS OF NON-SYSTEM DATAFILE
7)LOSS OF SYSTEM DATAFILE
8)TABLE POINT IN TIME RECOVERY( Application team want the table as it is on 3pm
IST) ---- 19c have table recovery
9) loss of undo tablespace datafile
10) DATABASE CRASH DURING HOT BACKUP MODE ( DB UP ---> ALTER DATABASE BEGIN BACKUP
---> SHUTODOWN ABORT)
11) LOSS OF A DATAFILE CREATED 2 HOURS AGO WHICH DOES NOT HAVE ANY BACKUP ( DB IS
ARCHIVELOG MODE , bd full backup at 10pm every day)
AT 2PM
alter tablespace tbs1 add datafile '/u11/oradata/test/[Link]' size 500m;
at 4pm /u11 mount point crashed DUE TO STORAGE ISSUE
No impact to AVAILABILITY archivelog files
ALTER DATABASE CREATE DATAFILE AS '/u11/oradata/test/[Link]';
ALTER DATABASE DATAFILE '/u11/oradata/test/[Link]' OFFLINE;
RECOVER DATAFILE '/u11/oradata/test/[Link]';
ALTER DATABASE DATAFILE '/u11/oradata/test/[Link]' ONLINE;