Backup and Recover basic guide 笔记

数据本身才是最重要的, 不管一个数据库系统run地有多顺畅, 数据丢失了, 其他的什么东西都免谈. 于是最近也花了很多时间来学习和了解备份和恢复的基本概念和方法. 在看完Backup and Recover basic guide的文档, 做了一些实验后, 对Oracle大概的备份和恢复有了一个基本的认识. 下面贴上在学习过程记录的一些基本语句.

[@more@]

------------------------------------------ RMAN quick start guide -----------------------------------------------
-- --- Backup individual file
RMAN> BACKUP ARCHIVELOG COMPLETION TIME BETWEEN 'SYSDATE-31' AND 'SYSDATE-7';

RMAN> BACKUP TABLESPACE system, users, tools;

RMAN> BACKUP AS BACKUPSET DATAFILE
'ORACLE_HOME/oradata/trgt/users01.dbf',
'ORACLE_HOME/oradata/trgt/tools01.dbf';

RMAN> BACKUP DATAFILE 1,3,5;

RMAN> BACKUP CURRENT CONTROLFILE TO '/backup/curr_cf.copy';

RMAN> BACKUP SPFILE;

RMAN> BACKUP BACKUPSET ALL;

RMAN> BACKUP FORMAT='AL_%d/%t/%s/%p' ARCHIVELOG LIKE '%arc_dest%';

RMAN> BACKUP TAG 'weekly_full_db_bkup' DATABASE MAXSETSIZE 10M;

RMAN> BACKUP COPIES 2 DEVICE TYPE sbt BACKUPSET ALL;

--- List Backup
RMAN> LIST BACKUP OF ARCHIVELOG FROM SEQUENCE 1437;

--- Monitor RMAN
SELECT OPERATION, STATUS, MBYTES_PROCESSED, START_TIME, END_TIME from V$RMAN_STATUS;

--- Calculate the progress of Rman
SELECT SID, SERIAL#, CONTEXT, SOFAR, TOTALWORK,
ROUND(SOFAR/TOTALWORK*100,2) "% COMPLETE"
FROM V$SESSION_LONGOPS
WHERE OPNAME LIKE 'RMAN%' AND OPNAME NOT LIKE '%aggregate%'
AND TOTALWORK != 0 AND SOFAR <> TOTALWORK;

---- Uncatalog backup file in RMAN
RMAN> change backuppiece 43 uncatalog;

------------------------------------------ RMAN quick start guide -----------------------------------------------

------------------------------------------- Backup and Recover Basics -------------------------------------------------------------

------------------ Chapeter 2: Backup and Recovery Strategies

--- create missed logfile
alter database clear logfile group 4;

---- Backup retention policy
RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 3 DAYS;
RMAN> CONFIGURE RETENTION POLICY TO REDUNDANCY 3;

RMAN> restore spfile from autobackup db_recovery_file_dest='/storage/ora_recover/harry' db_name='HARRY';

------------------ Chapeter 2: Backup and Recovery Strategies

------------------ Chapeter3: Setting Up and Configuring Backup and Recovery

---- Checking RMAN Syntax
$ rman checksyntax [#command file]

-- Backup setting
BACKUP DEVICE TYPE sbt DATABASE;

---- In compressed mode
RMAN> CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET;

-- Check disk space usage of falsh recover area

select * from V$RECOVERY_FILE_DEST; -- find out the current location, disk quota, space in use, space reclaimable by deleting files, and total number of files in the flash recovery area.

SELECT * FROM V$FLASH_RECOVERY_AREA_USAGE; --find out the percentage of the total disk quota used by different types of files, and how much space for each type of file can be reclaimed by deleting files that are obsolete, redundant, or already backed up to tape

-- Check backup file status
select * from v$backup_files;

-- Disable retention policy
CONFIGURE RETENTION POLICY TO NONE;

select * from DBA_OUTSTANDING_ALERTS;

-- Performing Flashback Database to a Guaranteed Restore Point
SELECT NAME, SCN, TIME, DATABASE_INCARNATION#,
GUARANTEE_FLASHBACK_DATABASE
FROM V$RESTORE_POINT
WHERE GUARANTEE_FLASHBACK_DATABASE='YES';

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
FLASHBACK DATABASE TO RESTORE POINT 'BEFORE_CHANGES';

------------------ Chapeter3: Setting Up and Configuring Backup and Recovery

------------------ Chapeter 4: Backing Up Databases Using RMAN

-- Compressed backup
BACKUP AS COMPRESSED BACKUPSET DATABASE PLUS ARCHIVELOG;

--- Backup with controlfile
BACKUP DEVICE TYPE sbt TABLESPACE users INCLUDE CURRENT CONTROLFILE;

-- Create a controlfile backup, and then back it up to tipe
BACKUP AS COPY CURRENT CONTROLFILE FORMAT '/tmp/control01.ctl';
BACKUP DEVICE TYPE sbt CONTROLFILECOPY '/tmp/control01.ctl';

-- range backup
BACKUP ARCHIVELOG FROM TIME 'SYSDATE-30' UNTIL TIME 'SYSDATE-7';

-- Delete option
BACKUP DEVICE TYPE sbt ARCHIVELOG ALL DELETE INPUT;
BACKUP DEVICE TYPE sbt ARCHIVELOG ALL DELETE ALL INPUT;

-- Differential Incremental
RMAN> BACKUP INCREMENTAL LEVEL 1 DATABASE;

-- Cumulative Incremental
RMAN> BACKUP INCREMENTAL LEVEL 1 CUMULATIVE DATABASE;

-- The number of blocks written to a backup set for each datafile with at least 50% of its blocks backed up:
SELECT FILE#, INCREMENTAL_LEVEL, COMPLETION_TIME, BLOCKS, DATAFILE_BLOCKS
FROM V$BACKUP_DATAFILE
WHERE INCREMENTAL_LEVEL > 0
AND BLOCKS / DATAFILE_BLOCKS > .5
ORDER BY COMPLETION_TIME;

-- Change tracking file
SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING;
SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE '/mydir/rman_change_track.f' REUSE;

SQL> ALTER DATABASE DISABLE BLOCK CHANGE TRACKING; -- Disable change tracking

select * from V$BLOCK_CHANGE_TRACKING -- Check cahnge tracking status

-- Move tracking file
shutdown immediate
ALTER DATABASE RENAME FILE 'ora_home/dbs/change_trk.f' TO '/new_disk/change_trk.f';
ALTER DATABASE OPEN;

-- List backup information
LIST BACKUP OF DATABASE;
LIST COPY OF DATAFILE 'ora_home/oradata/trgt/system01.dbf';
LIST BACKUPSET 213;
LIST DATAFILECOPY '/tmp/tools01.dbf';
LIST BACKUPSET TAG 'weekly_full_db_backup';
LIST COPY OF DATAFILE 'ora_home/oradata/trgt/system01.dbf' DEVICE TYPE sbt;
LIST BACKUP LIKE '/tmp/%';
LIST COPY OF DATAFILE 2 COMPLETED BETWEEN '10-DEC-2002' AND '17-DEC-2002';
LIST ARCHIVELOG ALL BACKED UP 2 TIMES TO DEVICE TYPE sbt;
list backup of datafile 1;

-- List incarnations:
RMAN> LIST INCARNATION;
RMAN> LIST INCARNATION OF DATABASE;

-- Reports of RMAN Backups
RMAN> REPORT NEED BACKUP -- Reports which database files need to be backed up to meet a configured or specified retention policy
RMAN> REPORT UNRECOVERABLE -- Reports which database files require backup because they have been affected by some NOLOGGING operation

RMAN> REPORT NEED BACKUP RECOVERY WINDOW OF 2 DAYS DATABASE SKIP TABLESPACE TBS_2;
RMAN> REPORT NEED BACKUP REDUNDANCY 2 DATAFILE 1;
RMAN> REPORT NEED BACKUP TABLESPACE TBS_3; # uses configured retention policy
RMAN> REPORT NEED BACKUP INCREMENTAL 2; # checks entire database

RMAN> REPORT NEED BACKUP RECOVERY WINDOW OF 2 DAYS DATABASE DEVICE TYPE SBT;
RMAN> REPORT NEED BACKUP DEVICE TYPE DISK;

RMAN> REPORT OBSOLETE RECOVERY WINDOW OF 3 DAYS DEVICE TYPE DISK;
RMAN> REPORT OBSOLETE REDUNDANCY 1;

-- Report database Schema
RMAN> REPORT SCHEMA;

-- If use a recovery catalog
REPORT SCHEMA AT TIME 'SYSDATE-14'; # schema 14 days ago
REPORT SCHEMA AT SCN 1000; # schema at scn 1000
REPORT SCHEMA AT SEQUENCE 100 THREAD 1; # schema at sequence 100

------------------ Chapeter 4: Backing Up Databases Using RMAN

------------------ Chapter 6: Performing Complete Restore and Recovery of Databases

-- Check status of datafile
COL FILE# FORMAT 999
COL STATUS FORMAT A7
COL ERROR FORMAT A10
COL TABLESPACE_NAME FORMAT A10
COL NAME FORMAT A30
SELECT FILE#, STATUS, ERROR, RECOVER, TABLESPACE_NAME, NAME
FROM V$DATAFILE_HEADER
WHERE RECOVER = 'YES' OR (RECOVER IS NULL AND ERROR IS NOT NULL);

-- QueryV$RECOVER_FILE to list datafiles requiring recovery
SELECT FILE#, ERROR, ONLINE_STATUS, CHANGE#, TIME FROM V$RECOVER_FILE;

-- Restore with 'NEWNAME', 'SWITCH' cmmand
RUN
{
SQL 'ALTER TABLESPACE users OFFLINE IMMEDIATE';
SQL 'ALTER TABLESPACE tools OFFLINE IMMEDIATE';
# specify the new location for each datafile
SET NEWNAME FOR DATAFILE '/olddisk/users01.dbf' TO
'/newdisk/users01.dbf';
SET NEWNAME FOR DATAFILE '/olddisk/tools01.dbf' TO
'/newdisk/tools01.dbf';
# to restore to an ASM disk group named dgroup, use:
# SET NEWNAME FOR DATAFILE '/olddisk/trgt/tools01.dbf'
# TO '+dgroup';
RESTORE TABLESPACE users, tools;
SWITCH DATAFILE ALL; # update control file with new filenames
RECOVER TABLESPACE users, tools;
}

-- Oracle Flashback Query:
SELECT * FROM EMP AS OF TIMESTAMP
TO_TIMESTAMP('2005-04-04 09:30:00', 'YYYY-MM-DD HH:MI:SS')
WHERE name = 'JOHN';

--Performing Flashback Table
FLASHBACK TABLE EMP TO TIMESTAMP TO_TIMESTAMP('2005-03-03 14:00:00', 'yyyy-mm-dd hh24:mi:ss') ENABLE TRIGGERS;

-- Recycle bin
Alter session/system set recyclebin=on/off; -- Turn on / off recyclebin

show recyclebin;

select * from user_recyclebin;
select * from dba_recyclebin;

FLASHBACK TABLE "BIN$gk3lsj/3akk5hg3j2lkl5j3d==$0" TO BEFORE DROP;
FLASHBACK TABLE HR.INT_ADMIN_EMP TO BEFORE DROP;
FLASHBACK TABLE "BIN$KSD8DB9L345KLA==$0" TO BEFORE DROP RENAME TO hr.int2_admin_emp;

-- Purge
PURGE TABLE EMP;
PURGE TABLE "BIN$KSD8DB9L345KLA==$0";
PURGE INDEX "BIN$GTE72KJ22H9==$0";

PURGE TABLESPACE hr;
PURGE TABLESPACE hr USER scott;

--- Incarnation
LIST INCARNATION;

RMAN> reset database to incarnation 4;
RMAN> run{
set until scn 973803;
restore database;
recover database;
}

------------------ Chapter 6: Performing Complete Restore and Recovery of Databases

------------------------------------------- Backup and Recover Basics -------------------------------------------------------------

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7319061/viewspace-1026625/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/7319061/viewspace-1026625/