Oracle physical backup and logical backup

Oracle backup is divided into two types:

Logical backup: expdp/impdp is only used to store the exported data. Therefore, only the data saved during Backup can be recovered,

Logical backup is suitable for backing up tables with few changes.

Disadvantage: long recovery time

Advantage: Logical backup is not related to the platform, so it is an important means of data migration.

Physical backup: backs up a database by copying physical backup files.

Physical backup: There are two types:

Cold backup: You need to stop the database consistently, and then copy the data files and log files. before stopping the control file,

You must use v $ datafile, v $ logfile, and v $ controlfile to determine the file path.

Non-open databases

Advantage: simple execution and reliability dependency cp

Disadvantage: Stopping the database and backing up the entire database

Hot Backup: Prerequisites: the database must be running in active and archive mode.

User Management backup and oracle management backup

User Management backup: refers to the hot backup mode of the tablespace by placing it in the hot backup mode, and then copying it using the operating system tool.

Before 10 Gb, you can only backup the table space one by one. alter tablespace name begin backup;

Alter tablespace name end backup;
In 10 Gb, the database can be placed in the Hot Standby state alter database begin backup
Alter database end backup

1. Separate storage of tablespaces

2. Freeze other parts of the file header. At this time, only the file header is a good part.

3. Changed log Behavior

1. Verify that the tablespace is stored separately

SYS @ ORCL> col name for a50
SYS @ ORCL> select name, checkpoint_change # from v $ datafile;


NAME CHECKPOINT_CHANGE #
--------------------------------------------------------------------
/U01/app/oracle/oradata/ORCL/system01.dbf 626426
/U01/app/oracle/oradata/ORCL/undotbs01.dbf 626426
/U01/app/oracle/oradata/ORCL/sysaux01.dbf 626426
/U01/app/oracle/oradata/ORCL/users01.dbf 626426
/U01/app/oracle/oradata/ORCL/example01.dbf 626426
/U01/app/oracle/oradata/ORCL/block. dbf 626426


6 rows selected.

2. view the current system SCN number

SYS @ ORCL> select dbms_flashback.get_system_change_number from dual;


GET_SYSTEM_CHANGE_NUMBER
------------------------
626513

3. Modify the users tablespace to the Hot Standby state.

SYS @ ORCL> alter tablespace users begin backup;


Tablespace altered.

4. view the SCN Number of the current tablespace. The SCN number in the users tablespace header has been frozen.

SYS @ ORCL> select name, checkpoint_change # from v $ datafile;


NAME CHECKPOINT_CHANGE #
--------------------------------------------------------------------
/U01/app/oracle/oradata/ORCL/system01.dbf 626426
/U01/app/oracle/oradata/ORCL/undotbs01.dbf 626426
/U01/app/oracle/oradata/ORCL/sysaux01.dbf 626426
/U01/app/oracle/oradata/ORCL/users01.dbf 626522
/U01/app/oracle/oradata/ORCL/example01.dbf 626426
/U01/app/oracle/oradata/ORCL/block. dbf 626426


6 rows selected.

5. Generate a system checkpoint to verify that the users tablespace File Header SCN is frozen

SYS @ ORCL> alter system checkpoint;


System altered.


SYS @ ORCL> select name, checkpoint_change # from v $ datafile;


NAME CHECKPOINT_CHANGE #
--------------------------------------------------------------------
/U01/app/oracle/oradata/ORCL/system01.dbf 626543
/U01/app/oracle/oradata/ORCL/undotbs01.dbf 626543
/U01/app/oracle/oradata/ORCL/sysaux01.dbf 626543
/U01/app/oracle/oradata/ORCL/users01.dbf 626522
/U01/app/oracle/oradata/ORCL/example01.dbf 626543
/U01/app/oracle/oradata/ORCL/block. dbf 626543


6 rows selected.

6. Check the file status of the current backup and find that file #4 is the file activity status corresponding to the users tablespace. This indicates that media recovery is required.
 

SYS @ ORCL> select * from v $ backup;


FILE # status change # TIME
-----------------------------------------------
1 not active 0
2 not active 0
3 not active 0
4. ACTIVE 626522 06-MAR-14
5 not active 0
6 not active 0


6 rows selected.


SYS @ ORCL> shutdown immediate // an error is returned when the database is shut down in Immediate mode.
ORA-01149: cannot shutdown-file 4 has online backup set
ORA-01110: data file 4: '/u01/app/oracle/oradata/ORCL/users01.dbf'
SYS @ ORCL> startup force
ORACLE instance started.


Total System Global Area 285212672 bytes
Fixed Size 1218992 bytes
Variable Size 79693392 bytes
Database Buffers 201326592 bytes
Redo Buffers 2973696 bytes
Database mounted.
ORA-01113: file 4 needs media recovery
ORA-01110: data file 4: '/u01/app/oracle/oradata/ORCL/users01.dbf'


7. End the hot standby status of the users tablespace.

SYS @ ORCL> alter tablespace users end backup;


Tablespace altered.

8. Check the file status of the current backup. In this case, file #4 means that the file corresponding to the users tablespace is no longer active.

SYS @ ORCL> select * from v $ backup;


FILE # status change # TIME
-----------------------------------------------
1 not active 0
2 not active 0
3 not active 0
4-not ACTIVE 626522 06-MAR-14
5 not active 0
6 not active 0


6 rows selected.


SYS @ ORCL> select name, checkpoint_change # from v $ datafile;


NAME CHECKPOINT_CHANGE #
--------------------------------------------------------------------
/U01/app/oracle/oradata/ORCL/system01.dbf 626543
/U01/app/oracle/oradata/ORCL/undotbs01.dbf 626543
/U01/app/oracle/oradata/ORCL/sysaux01.dbf 626543
/U01/app/oracle/oradata/ORCL/users01.dbf 626543
/U01/app/oracle/oradata/ORCL/example01.dbf 626543
/U01/app/oracle/oradata/ORCL/block. dbf 626543


6 rows selected.

9. Open the database

SYS @ ORCL> alter database open;


Database altered.


SYS @ ORCL> select * from v $ backup;


FILE # status change # TIME
-----------------------------------------------
1 not active 0
2 not active 0
3 not active 0
4-not ACTIVE 626522 06-MAR-14
5 not active 0
6 not active 0


6 rows selected.