Note_Tech

All technological notes.

Project maintained by simonangel-fong Hosted on GitHub Pages — Theme by mattgraham

DBA - Backup: Full Backup and Incremental Backup

Back

Full Backup

Incremental Backups

-- To perform an incremental backup at level 0
BACKUP INCREMENTAL LEVEL 0 DATABASE;

-- To perform a differential incremental backup
BACKUP INCREMENTAL LEVEL 1 DATABASE;

-- To perform a cumulative incremental backup
BACKUP INCREMENTAL LEVEL 1 CUMULATIVE DATABASE;

-- to apply incremental backups to data file image copies.
BACKUP AS COPY INCREMENTAL LEVEL 0 DATABASE;

Incrementally Updated Backups

incrementally_updated_backups

Example

recover copy of database with tag 'daily inc';
backup incremental level 1 for recover of copy with tag 'daily inc' database;
  RECOVER BACKUP
Day 1 Nothing Create image copies
Day 2 Nothing Create incremental level 1
Day 3 and onward Recover copies based on incremental Create incremental level 1

Lab: Applying the incremental backup to the level 0 image copies

Setup

#!/bin/sh

sqlplus /nolog <<EOF
connect / as sysdba

ALTER SESSION set container=pdb1;

DROP TABLESPACE INVENTORY INCLUDING CONTENTS and DATAFILES CASCADE CONSTRAINTS;

CREATE SMALLFILE TABLESPACE "INVENTORY"
 DATAFILE '/u01/app/oracle/oradata/ORCL/pdb1/inventory01.dbf' SIZE 30M,
          '/u01/app/oracle/oradata/ORCL/pdb1/inventory02.dbf' size 40M REUSE
 AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED LOGGING
 EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

drop user inventory cascade;

create user inventory identified by pass4INV
default tablespace inventory;

grant connect, resource to inventory;
ALTER USER inventory QUOTA UNLIMITED ON INVENTORY;

exit;
EOF


sqlplus / as sysdba <<EOF

ALTER SESSION set container=pdb1;

CREATE TABLE INVENTORY.PRODUCT_MASTER
( PRODUCT_ID NUMBER(7)
, PRODUCT_NAME VARCHAR2(50) NOT NULL
, CODE VARCHAR2(10) NOT NULL
, REORDER_THRESHOLD NUMBER(5)
, COST NUMBER(5, 2)
, PRICE NUMBER(5, 2)
, CONSTRAINT PK_INV PRIMARY KEY (PRODUCT_ID) VALIDATE ,
CONSTRAINT CHK_GT_0 CHECK (reorder_threshold > 0) VALIDATE ) TABLESPACE INVENTORY;

CREATE TABLE INVENTORY.PRODUCT_ON_HAND
(
WAREHOUSE_ID NUMBER(3),
PRODUCT_ID NUMBER(7),
QUANTITY NUMBER(5),
WAREHOUSE_CITY VARCHAR2(30),
LAST_UPDATE DATE,
  CONSTRAINT PK_POH
    PRIMARY KEY (WAREHOUSE_ID, PRODUCT_ID) VALIDATE,
  CONSTRAINT FK_PROD_ON_HAND_PROD_ID
    FOREIGN KEY (PRODUCT_ID) REFERENCES
      INVENTORY.PRODUCT_MASTER (PRODUCT_ID) VALIDATE
);

CREATE TABLE INVENTORY.OBSOLETE_PRODUCTS
AS select product_id, product_name, code, cost, price
from inventory.product_master;

CREATE INDEX INVENTORY.OBS_CODE ON INVENTORY.OBSOLETE_PRODUCTS (CODE);

create index inventory.prod_name_code
on inventory.product_master(product_name,code);

create index inventory.poh_prod_id_qty on
inventory.product_on_hand(product_id, quantity);


set echo on
alter table inventory.product_master nologging
/
insert /*+ append */ into INVENTORY.PRODUCT_MASTER
select
100000 + rownum,
'NAME' || dbms_random.string('a',mod(abs(dbms_random.random),30) + 16),
dbms_random.string('a',10),
mod(abs(dbms_random.random),1000) + 100,
25.25,
50.88
from dba_objects
/
commit
/
insert /*+ append */ into inventory.product_master
select
200000 + rownum,
'NAME' || dbms_random.string('a',mod(abs(dbms_random.random),30) + 16),
dbms_random.string('a',10),
mod(abs(dbms_random.random),1000) + 100,
25.25,
50.88
from dba_objects
/
commit
/
insert /*+ append */ into inventory.product_master
select
300000 + rownum,
'NAME' || dbms_random.string('a',mod(abs(dbms_random.random),30) + 16),
dbms_random.string('a',10),
mod(abs(dbms_random.random),1000) + 100,
25.25,
50.88
from dba_objects
/
commit
/
alter table inventory.product_master logging
/

create index inventory.code_func on inventory.product_master (upper(substr(code,5,2)));

alter table inventory.product_master add (primary_source varchar2(50), secondary_source varchar2(50));

alter table inventory.product_master add (obsoleted date);

exit
EOF

mkdir -p /home/oracle/backup/orcl

Perform Level 0 incremental Backup

run {
-- set channel's name, device type as DISK, set path to store backup
  ALLOCATE CHANNEL "chl" DEVICE TYPE DISK FORMAT "/home/oracle/backup/orcl/%U";

-- implement incremental level 0 bacup db as image copies, add tag,
  BACKUP AS COPY TAG 'BASE01' INCREMENTAL LEVEL 0 DATABASE;
}

The script performs an incrimental level 0 backup to a specific directory.

lab_incremental_level0

lab_incremental_level0

Perform Level 1 Incremental Backup

#!/bin/sh

sqlplus / as sysdba<<EOF
ALTER SESSION set container=pdb1;
CONNECT inventory/pass4INV@pdb1
/* retrieve a random integer between 1 an 100 use that as a mod function */
/* to change the obsolete date for product ids in the table */


update product_master set obsoleted = SYSDATE
where mod(product_id,100) IN (
  select (round(dbms_random.value(1,100))) from dual)
/
commit;
exit
EOF


run {
-- set channel's name, device type as DISK, set path to store backup
  ALLOCATE CHANNEL "chl" DEVICE TYPE DISK FORMAT "/home/oracle/backup/orcl/%U";

-- implement incremental level 1 backup, add tag,
  BACKUP TAG 'incr_update' INCREMENTAL LEVEL 1 DATABASE;
}

lab_incremental_leve1

Recovery from Level 0 Backup

run {
ALLOCATE CHANNEL "chl" DEVICE TYPE DISK FORMAT "/home/oracle/backup/orcl/%U";
RECOVER COPY OF DATABASE WITH TAG 'BASE01';
}

lab_incremental_leve1

Clear up

#!/bin/sh

rman target "'/ as sysdba'"<<EOF
delete NOPROMPT backup of tablespace pdb1:INVENTORY;
exit;
EOF

sqlplus /nolog >> /tmp/cleanup.log 2>&1 <<EOF
connect / as sysdba
alter session set container=pdb1;
DROP USER inventory cascade;
DROP TABLESPACE INVENTORY INCLUDING CONTENTS and DATAFILES;
exit
EOF

TOP