Several backup summary and backup Summary

Several backup summary and backup Summary
[Oracle @ localhost ~] $ Cat/etc/redhat-release
Red Hat Enterprise Linux Server release 5.5 (Tikanga)
SQL> select * from v $ version where rownum = 1; BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-Production

The following section describes cold backup (full offline backup), partial offline backup, and partial online backup. The sections after RMAN and logical backup (exp, imp | expdp, impdp) will be covered.
Full offline backup: (archive and non-archive are applicable for full database backup)
This is not much to mention. Here is a clear description of http://blog.itpub.net/29876893/viewspace-1607155/
However, this backup has many disadvantages:
1. The production database is absolutely not allowed after shutdown.
2. You need to copy the file through the operating system command. This affects the database performance for a large file, and sometimes it is not a general slowness!
3. If the storage of the database adopts ASM, it cannot be copied directly. We cannot see the partition of the file system.
Partial offline backup: (for tablespace)
We can change the offline and online table space (archive required)
SQL> select username, default_tablespace from dba_users where username = 'hr ';

USERNAME DEFAULT_TABLESPACE
------------------------------------------------------------
HR USERS

SQL> alter tablespace users offline;

The tablespace has been changed.
Copy the data file under users.
SQL> select file_id, tablespace_name, online_status from dba_data_files where tablespace_name = 'users ';


FILE_ID TABLESPACE_NAME ONLINE _
-----------------------------------------------
4 USERS OFFLINE


In this case, set users online:

SQL> alter tablespace users online;
The tablespace has been changed.
Of course, you can also directly put the data file offline:
SQL> alter database datafile 4 offline;
The database has been changed.
SQL> truncate table tt; the table is truncated.

SQL> insert into tt values (1, dbms_flashback.get_system_change_number );
One row has been created.
SQL> commit;
Submitted.
SQL> alter system switch logfile;
The system has been changed.


SQL> insert into tt values (2, dbms_flashback.get_system_change_number );
One row has been created.
SQL> commit;
Submitted.
SQL> alter system switch logfile;
The system has been changed.


SQL> insert into tt values (3, dbms_flashback.get_system_change_number );
One row has been created.
SQL> commit;
Submitted.
SQL> alter system switch logfile;
The system has been changed.

SQL> select * from v $ log;


GROUP # THREAD # SEQUENCE # bytes blocksize members arc status FIRST_CHANGE # FIRST_TIME NEXT_CHANGE # NEXT_TIME
------------------------------------------------------------------------------------------------------------------------------------
1 1 439 52428800 512 1 yes active 7312260 month-15 7312279 month-15
2 1 440 52428800 512 1 no current 7312279 month-15 2.8147E + 14
3 1 438 52428800 512 1 yes active 7312248 month-15 7312260 month-15


In this case, we delete the data files in the tablespace user.
In this case, only mount can be started.
Detailed recovery reference http://blog.itpub.net/29876893/viewspace-1607155/
You can do not restore the table space first. You can take the tablespace offline, open the database, and then restore it.

This type of backup is better because we do not need to shutdown the backup tablespace, but we cannot use the offline system, undo, redo, temp tablespace. It is a big pity:

SQL> alter tablespace system offline;
Alter tablespace system offline
*
Row 3 has an error:
ORA-01541: System tablespace cannot be offline; close if necessary

Note that the current undo tablespace cannot be offline!
SQL> alter tablespace UNDOTBS1 offline;
Alter tablespace UNDOTBS1 offline
*
Row 3 has an error:
ORA-30042: cannot take restored tablespace offline

If it is not the currently used undo tablespace:
SQL> alter tablespace UNDO_W online;
The tablespace has been changed.

Disadvantages of this method include:
2. You need to copy the file through the operating system command. This affects the database performance for a large file, and sometimes it is not a general slowness!
3. If the storage of the database adopts ASM, it cannot be copied directly. We cannot see the partition of the file system.
Partial online backup: This backup method applies to the archive mode.
Oracle has made great improvements to back up data in the online status:
For example:
SQL> select * from v $ backup;


FILE # status change # TIME
----------------------------------------------------
1 not active 0
2 not active 0
3 not active 0
5 not active 0
6 not active 0
7 not active 0
8 not active 0
9 not active 0
11 not active 0
12 not active 0
10 rows have been selected.

These files are not active at this time.

SQL> alter tablespace CHAO begin backup;
The tablespace has been changed.
SQL> alter tablespace SYSTEM begin backup;
The tablespace has been changed.

SQL> select * from v $ backup;


FILE # status change # TIME
----------------------------------------------------
1 ACTIVE 7420181 29-4 month-15
2 not active 0
3 not active 0
5 not active 0
6 not active 0
7 ACTIVE 7420021 29-4 month-15
8 not active 0
9 not active 0
11 not active 0
12 not active 0
10 rows have been selected.

At this time, the files No. 1 and No. 7 are active and the SCN is recorded as the starting point for the next recovery!
Backup means copying the backup data file directly.
Disable the activity status of data file 7:
SQL> alter tablespace CHAO end backup;
The tablespace has been changed.

SQL> select * from v $ backup where file # = 7;


FILE # status change # TIME
----------------------------------------------------
7 not active 7420021 29-4 month-15

If you want to completely back up data, will this Operation Drive crazy people!

SQL> alter database begin backup;
Alter database begin backup
*
Row 3 has an error:
ORA-01146: unable to start online backup-file 1 is in backup ORA-01110:
Data File 1: '/u01/app/oracle/oradata/orcl3939/system01.dbf'
SQL> alter tablespace system end backup;
The tablespace has been changed.
This place is not so nice! You need to disable the previous hot state!
SQL> alter database begin backup;
The database has been changed.



SQL> select * from v $ backup;


FILE # status change # TIME
----------------------------------------------------
1 ACTIVE 7420464 29-4 month-15
2 ACTIVE 7420464 29-4 month-15
3 ACTIVE 7420464 29-4 month-15
5 ACTIVE 7420464 29-4 month-15
6 ACTIVE 7420464 29-4 month-15
7 ACTIVE 7420464 29-4 month-15
8 ACTIVE 7420464 29-4 month-15
9 ACTIVE 7420464 29-4 month-15
11 ACTIVE 7420464 29-4 month-15
12 ACTIVE 7420464 29-4 month-15
10 rows have been selected.
 
Alter database end backup directly if the backup is terminated;
Disadvantages of this backup:
1. A large number of redo logs will be generated. Why?
SQL> show parameter db_block_size
Db_block_size integer 8192

[Oracle @ localhost ~] $ Dumpe2fs/dev/sda1
Bash: dumpe2fs: command not found
[Oracle @ localhost ~] $ Su-root
Password:
[Root @ localhost ~] # Dumpe2fs/dev/sda1
Block size: 1024

SQL> select 8*1024/1024 from dual;

8*1024/1024
-----------
8
The eight operating system blocks constitute a data block:

At this time, use the OS command to copy the block. The read is performed according to the OS block, but an oracle transaction is modifying the block. In the end, oracle cannot identify this block when we recover!
To solve this problem, as long as the block is in the hot backup state, the operation of this data block will be recorded in the log mode, so the operation of a block will increase the log volume by 8 KB.

Briefly describe the simulation process:
Select * from v $ statname where name like '% redo %'; collects a lot of redo information, including redo size. SQL> select * from v $ sesstatwhere sid = 159;
SQL> select * from v $ sesstat where statistic # = 169 and sid = 159;

You can insert a piece of data and then view the generated logs (probably estimated ).
Then execute the tablespace Hot Backup:
Check the log volume
Insert the same data to view the log volume.
If you are interested, you can simulate it yourself.