ORACLE_基础二十二(User-Managed Backup)

Trminology

1.Whole database backup

   a.Targt database may be open or closed

   b. Backup of all datafiles and the control file

2.Partial database backups

   a.Tablespace

   b.Data file

   c. Control file

3.Consisitent backups

4.Inconsistent backups

Get DB File Information

1.V$DATAFILE

2.V$CONTROFILE

3.V$LOGFILE

4.DBA_DATA_FILS

select name , status from v$datafile;select name from v$controlfile;slect member from v$logfile;select t.name tablespace, f.name datafile from v$tablespac t, v$datafile f where t.ts# =f.t.tst# order by t.name;

Consistent Whole DB Backup

Open Database Backup

1.Maintains high database availability

2.Can be done at a tablespace or datafile level

3.Supports nonstop business operations

热备需要在归档模式下

Ending Online TS Baskup

What does Backup Mode do?

The tablespace is checkpointed, the checkpoint SCN marker in the datafile headers case to increment with checkpoints , and full images of changed DB blocks are writeen to the redo logs.

Backup Status Information

select * from v$backup;

Read-Only TS Baskup

1.Only one backup is needed after altering the tablespace to read-only

2. resume a normal backup schedule for that tablespace after making it read-wirte

3.The control file must correctly indetify the tablespace in read-only mode; otherwise you must recover it.

Logging and Nologging

                       LOGGING                                           NOLOGGING

ALL changes recorded to redo                                     Minimal redo recorded

Fully recoverable from last backup                            Not recoverable from last backup

No additional backup                                                 May require additional backup

Manual Control File Backups

1.Creating a binary image

    ALTER DATABASE BACKUP CONTROLFILE TO 'control1.bkp'

2.Creating a text trac file

   ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS '/tmp/con/cxxx.sql'

Backup the Init Param File

CREATE PFILE FROM SPFILE;

CREATE PFILE='/backup/init.ora' from SPFILE;

Using DBVERIFY

DBV 检查表空间

Recovery in NOARCHIVELOG

1.In NOARCHIVELOG mode, you must restor the following database files:

  a.All datafiles

  b.Control files

2.You can also restore the following files

 a.Redo log files

 b.Password file

 c.Parameter file

3.Advatanges

  a.Easy to perform, with low risk of error

  b.Recovery time is the time is takes to restore all files

4.Disadvantages

   a.Data is lost and must be reaplied manually

   b.The entire database is restored to the point of the last whole closed backup

[oracle@localhost king]$ cp *.* ./backup/[oracle@localhost king]$ cp /u01/app/oracle/product/10.2.0/db_1/dbs/orapwking ./backup/[oracle@localhost king]$ cp /u01/app/oracle/product/10.2.0/db_1/dbs/spfileking.ora ./backup/SQL> create pfile from spfile [oracle@localhost dbs]$ pwd/u01/app/oracle/product/10.2.0/db_1/dbs[oracle@localhost dbs]$ vi initking.ora *.control_files='/u01/app/oracle/oradata/king/backup/control01.ctl','/u01/app/oracle/oradata/king/backup/control02.ctl','/u01/app/oracle/oradata/king/backup/control03.ctl'SQL> create spfile from pfile;  SQL> statup mount; SQL> col name format a50;                                                                                                    SQL> select file#,name from v$datafile;                                                                                           FILE# NAME---------- --------------------------------------------------         1 /u01/app/oracle/oradata/king/system01.dbf         2 /u01/app/oracle/oradata/king/undotbs01.dbf         3 /u01/app/oracle/oradata/king/backup/sysaux01.dbf         4 /u01/app/oracle/oradata/king/users01.dbfalter database rename file '/u01/app/oracle/oradata/king/system01.dbf' to  '/u01/app/oracle/oradata/king/backup/system01.dbf';alter database rename file '/u01/app/oracle/oradata/king/undotbs01.dbf' to  '/u01/app/oracle/oradata/king/backup/undotbs01.dbf';alter database rename file '/u01/app/oracle/oradata/king/sysaux01.dbf' to  '/u01/app/oracle/oradata/king/backup/sysaux01.dbf';alter database rename file '/u01/app/oracle/oradata/king/users01.dbf' to  '/u01/app/oracle/oradata/king/backup/users01.dbf';SQL> select file#,name from v$tempfile;                                              FILE# NAME---------- --------------------------------------------------         1 /u01/app/oracle/oradata/king/temp01.dbfalter database rename file '/u01/app/oracle/oradata/king/temp01.dbf' to  '/u01/app/oracle/oradata/king/backup/temp01.dbf';

SQL> col member format a40;                                                     SQL> select group#,member from v$logfile;                                           GROUP# MEMBER---------- ----------------------------------------         3 /u01/app/oracle/oradata/king/redo03.log         2 /u01/app/oracle/oradata/king/redo02.log         1 /u01/app/oracle/oradata/king/redo01.logalter database rename file '/u01/app/oracle/oradata/king/redo03.log' to  '/u01/app/oracle/oradata/king/backup/redo03.log';alter database rename file '/u01/app/oracle/oradata/king/redo02.log' to  '/u01/app/oracle/oradata/king/backup/redo02.log';alter database rename file '/u01/app/oracle/oradata/king/redo01.log' to  '/u01/app/oracle/oradata/king/backup/redo01.log';SQL> alter database open;

Without Redo Log File Backups

1.shut down the instance

2.Restore the datafiles and the control file from the most recent whole database backup

3.Perform cancel-based recovery

4.Open the database with the RESETLOGS option.

在mount下执行:

recover database until using backup controlfile;

alter database  open resetlogs;

Complete Recovery

1.Make sure that datafiles for restore are offline.

2.Restore only lost or damaged datafiles.

3.Do not restore the control files ,redo log files, password files or parameter file.

4.Recover the datafiles

Datermine Files Need Recovery

1.View V$RECOVER_FILE to datermine which datafiles ndd recovery.

2.View V$ARCHIVED_LOG for a list of all archived redo logfiles for the database.

3.View V$RECOVERY_LOG for a list of all archived redo log files required for recovery

About V$RECOVERY_LOG

About v$recover_file

RECOVER Command

Recover a mounted database:

1.RECOVER DATABASE

2.RECOVER DATAFILE  '/xxx/xxx/1.dbf'

Recover an open database:

1.RECOVER TABLESPACE users

2.RECOVER DATAFILE  '/123/123/1.dbf'

Using Archived Redo Log Files

1.To change archive location, use the

  ALTER SYSTEM ARCHIVE LOG...command

2.To apply redo log files automatically

  a.Issue the SET AUTORECOVERY ON command before starting media recovery.

  b.Enter auto when prompted for an archived log file

  c.Use the Recover AUTOMATIC ... command

Restore Files to a New Location

1.Use operating system commands to restore the datafile to the new location

2.Use the ALTER DATABAS RENAME FILE command to record the change in the control file.

Complete Recovery Methods

1.Closed database recovery for :

     a.System datafiles

     b. undo segment datafiles

     c. Whole database

2.Open database recovery,with database initailly opend (for file loss)

3.Open database recovery with database initailly closed(for hardware failure)

4.Data file recovery wit no datafile backup