oracle dba 學習日誌3（backup)

--a list of datafilesSELECT * FROM V$DATAFILE;--a listing of datafiles along with their associated tablespaces:SELECT t.NAME "Tablespace", f.NAME "Datafile"  FROM V$TABLESPACE t, V$DATAFILE f  WHERE t.TS# = f.TS#  ORDER BY t.NAME;select * from v$tablespace;SELECT MEMBER FROM V$LOGFILE;SELECT NAME FROM V$CONTROLFILE;/*Because the current database structure may not match the database structure at the time a given control file backup was created, saving a list of files recorded in the backup control file can aid the recovery procedure.*/ ALTER DATABASE BACKUP CONTROLFILE TO 'filename' /*To check whether a datafile is part of a current online tablespace backup, query the V$BACKUP view.  This view is useful only for user-managed online tablespace backups,  not offline tablespace backups or RMAN backups.  For example, the following query displays  which datafiles are currently included in a tablespace  that has been placed in backup mode:  In the STATUS column, NOT ACTIVE indicates that the file is not currently in backup mode  (that is, ALTER TABLESPACE ... BEGIN BACKUP),  whereas ACTIVE indicates that the file is currently in backup mode.*/ select * from v$backup; SELECT t.name AS "TB_NAME", d.file# as "DF#", d.name AS "DF_NAME", b.statusFROM V$DATAFILE d, V$TABLESPACE t, V$BACKUP bWHERE d.TS#=t.TS#AND b.FILE#=d.FILE#AND b.STATUS='ACTIVE';/*Making User-Managed Backups of the Whole Database*/consistent(files)(shutdown immediate,normal,transactional)在noarchivelog模式下可以進行THE WHOLE DATABASE BACKUP.並且可以恢複。(restore)在archivelog模式下可以進行THE WHOLE DATABASE BACKUP.(restore),同時還可以進行recovery.在inconsistent(files)(shutdown abort)只有在archivelog模式下可以完全恢複。--For example, you can back up the datafiles and control files in the /disk1/oracle/dbs directory to /disk2/backup as follows:% cp /disk1/oracle/dbs/*.dbf /disk2/backup% cp /disk1/oracle/dbs/*.cf /disk2/backup% cp /disk1/oracle/network/admin/*.ora /disk2/backup% cp /disk1/oracle/rdbms/admin/*.ora /disk2/backup、*Making User-Managed Backups of Offline Tablespaces and Datafiles*/Note the following guidelines when backing up offline tablespaces:You cannot offline the SYSTEM tablespace or a tablespace with active rollback segments. The following procedure cannot be used for such tablespaces.Assume that a table is in tablespace Primary and its index is in tablespace Index. Taking tablespace Index offline while leaving tablespace Primary online can cause errors when DML is issued against the indexed tables located in Primary. The problem only manifests when the access method chosen by the optimizer needs to access the indexes in the Index tablespace.SELECT TABLESPACE_NAME, FILE_NAME  FROM SYS.DBA_DATA_FILES  WHERE TABLESPACE_NAME = 'USERS';  Take the tablespace offline using normal priority if possible. ALTER TABLESPACE USERS OFFLINE NORMAL;ALTER TABLESPACE USERS ONLINE;Back up the offline datafiles. For example, a UNIX user might enter the following to back up the datafile users.f:% cp /disk1/oracle/dbs/users.f /disk2/backup/users.backupBack up the offline datafiles. For example, a UNIX user might enter the following to back up the datafile users.f:% cp /disk1/oracle/dbs/users.f /disk2/backup/users.backupAfter you bring a tablespace online, it is open and available for use.Archive the unarchived redo logs so that the redo required to recover the tablespace backup is archived. For example, enter:ALTER SYSTEM ARCHIVE LOG CURRENT;/*Making User-Managed Backups of Online Tablespaces and Datafiles*/--Making User-Managed Backups of Online Read/Write TablespacesYou must put a read/write tablespace in backup mode to make user-managed datafile backups when the tablespace is online and the database is open. The ALTER TABLESPACE BEGIN BACKUP statement places a tablespace in backup mode.ALTER TABLESPACE users BEGIN BACKUP;(ONLINE,OFFLINE 離線)ALTER TABLESPACE USERS END BACKUP;Oracle stops recording checkpoints to the datafiles in the tablespace when a tablespace is in backup mode.  Because a block can be partially updated at the very moment that  the operating system backup utility is copying it,  Oracle copies whole changed data blocks into the redo stream while in backup mode.   After you take the tablespace out of backup mode with the ALTER TABLESPACE ... END BACKUP   or ALTER DATABASE END BACKUP statement,   Oracle advances the datafile header to the current database checkpoint.   When you restore a datafile backed up in this way, the datafile header has a record of the most recent datafile checkpoint that occurred before the online tablespace backup, not any that occurred during it. As a result, Oracle asks for the appropriate set of redo log files to apply should recovery be needed. The redo logs contain all changes required to recover the datafiles and make them consistent.--back upTo back up online read/write tablespaces in an open database:Before beginning a backup of a tablespace, identify all of the datafiles in the tablespace with the DBA_DATA_FILES data dictionary view. For example, assume that you want to back up the users tablespace. Enter the following:SELECT TABLESPACE_NAME, FILE_NAMEFROM SYS.DBA_DATA_FILESWHERE TABLESPACE_NAME = 'USERS';SELECT * FROM DBA_DATA_FILES;Mark the beginning of the online tablespace backup. For example, the following statement marks the start of an online backup for the tablespace users:SQL> ALTER TABLESPACE users BEGIN BACKUP;Back up the online datafiles of the online tablespace with operating system commands. For example, UNIX users might enter:% cp /oracle/dbs/tbs_21.f /oracle/backup/tbs_21.backup% cp /oracle/dbs/tbs_22.f /oracle/backup/tbs_22.backupAfter backing up the datafiles of the online tablespace, indicate the end of the online backup by using the SQL statement ALTER TABLESPACE with the END BACKUP option. For example, the following statement ends the online backup of the tablespace users:SQL> ALTER TABLESPACE users END BACKUP;Archive the unarchived redo logs so that the redo required to recover the tablespace backup is archived. For example, enter:SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;/*Ending a Backup After an Instance Failure or SHUTDOWN ABORT*/About Instance Failures When Tablespaces are in Backup ModeThe following situations can cause a tablespace backup to fail and be incomplete:The backup completed, but you did not indicate the end of the online tablespace backup operation with the ALTER TABLESPACE ... END BACKUP statement.An instance failure or SHUTDOWN ABORT interrupted the backup before you could complete it.Whenever crash recovery is required (not instance recovery, because in this case the datafiles are open already), if a datafile is in backup mode when an attempt is made to open it, then the system assumes that the file is a restored backup. Oracle will not open the database until either a recovery command is issued, or the datafile is taken out of backup mode.備份的時候，open database 會提示錯誤。In high availability situations, and in situations when no DBA is monitoring the database (for example, in the early morning hours), the requirement for user intervention is intolerable. Hence, you can write a crash recovery script that does the following:Mounts the databaseRuns the ALTER DATABASE END BACKUP statementRuns ALTER DATABASE OPEN, allowing the system to come up automaticallyAn automated crash recovery script containing ALTER DATABASE END BACKUP is especially useful in the following situations:--Ending Backup Mode with the ALTER DATABASE END BACKUP StatementSQL> STARTUP MOUNT;SQL>  SELECT * FROM V$BACKUP WHERE STATUS = 'ACTIVE';SQL> ALTER DATABASE END BACKUP;You can use this statement only when the database is mounted but not open. If the database is open, use ALTER TABLESPACE ... END BACKUP or ALTER DATABASE DATAFILE ... END BACKUP for each affected tablespace or datafile.--Ending Backup Mode with the RECOVER CommandThe ALTER DATABASE END BACKUP statement is not the only way to respond to a failed online backup: you can also run the RECOVER command. This method is useful when you are not sure whether someone has restored a backup, because if someone has indeed restored a backup, then the RECOVER command brings the backup up to date. Only run the ALTER DATABASE END BACKUP or ALTER TABLESPACE ... END BACKUP statement if you are sure that the files are current.To take tablespaces out of backup mode with the RECOVER command:Mount the database. For example, enter:SQL> STARTUP MOUNTRecover the database as normal. For example, enter:SQL> RECOVER DATABASEUse the V$BACKUP view to confirm that there are no active datafiles:SQL>  SELECT * FROM V$BACKUP WHERE STATUS = 'ACTIVE';FILE#      STATUS             CHANGE#    TIME     ---------- ------------------ ---------- ---------0 rows selected.--To back up online read-only tablespaces in an open database:Query the DBA_TABLESPACES view to determine which tablespaces are read-only. For example, run this query:SELECT TABLESPACE_NAME, STATUS FROM DBA_TABLESPACESWHERE STATUS = 'READ ONLY';Before beginning a backup of a read-only tablespace, identify all of the tablespace's datafiles by querying the DBA_DATA_FILES data dictionary view. For example, assume that you want to back up the history tablespace. Enter the following:SELECT TABLESPACE_NAME, FILE_NAMEFROM SYS.DBA_DATA_FILESWHERE TABLESPACE_NAME = 'HISTORY'; TABLESPACE_NAME                   FILE_NAME-------------------------------   --------------------HISTORY                           /oracle/dbs/tbs_hist1.fHISTORY                           /oracle/dbs/tbs_hist2.fIn this example, /oracle/dbs/tbs_hist1.f and /oracle/dbs/tbs_hist2.f are fully specified filenames corresponding to the datafiles of the history tablespace.Back up the online datafiles of the read-only tablespace with operating system commands. You do not have to take the tablespace offline or put the tablespace in backup mode because users are automatically prevented from making changes to the read-only tablespace. For example, UNIX users can enter:% cp /oracle/dbs/tbs_hist*.f /backupNote:When restoring a backup of a read-only tablespace, take the tablespace offline, restore the datafiles, then bring the tablespace online. A backup of a read-only tablespace is still usable if the read-only tablespace is made read/write after the backup, but the restored backup will require recovery.Optionally, export the metadata in the read-only tablespace. By using the transportable tablespace feature, you can quickly restore the datafiles and import the metadata in case of media failure or user error. For example, export the metadata for tablespace history as follows:% exp TRANSPORT_TABLESPACE=y TABLESPACES=(history) FILE=/oracle/backup/tbs_hist.dmp