Part III, Backup and Recovery [Q] How to turn on / Off Archive [A] If the archive is turned on, ensure that log_archive_start = true enables automatic archiving, otherwise it can only be manually archived. If it is closed, set this parameter to pay attention to false If it is an OPS / RAC environment, you need to comment on Parallel_server = true, then execute the following steps, and finally restart 1 with this parameter 1, turn on archive a. Close the database Shutdown Immediateb. Startup Mountc. Alter Database ArchiveLogd. Alter Database Opne2, prohibit filing a close the database shutdown immediateb startup mountc alter database noarchivelogd alter database open archived information can be viewed through the following statement SQL> archive log listDatabase log mode archive ModeAutomatic archival EnabledArchive destination E:.... / oracle / ora92 / database / archiveOldest online log Sequence 131Next Log Sequence To Archive 133Current Log Sequence 133 [q] How to set up timing archiving [A] 9i or later, to ensure that the unit of archive does not exceed n seconds set Archive_Lag_target = n unit: second range: 0 ~ 7200 [q] Different versions How to export / import [A] Export with low versions, imported with the current version If the version is too large, you need to use the middle version of the transition [Q] how to pass the different character sets [A] a. Pre-condition is guaranteed to export / Importing consistent with other character sets, such as the customer environment is consistent with the database character set. b. Modify the 2,3 bytes of the DMP file to the character set of the target database, pay attention to replace it with hexadecimal. Reference function (ID in the following functions): NLS_CHARSET_NAME Name NLS_CHARSET_NAME Name NLS_CHARSET_ID Name NLS_CHARSET_ID to get the character set ID [q] Backup control file [A] Back up the control file to a binary file ALTER DATABASE BACKUP ControlFile To '$ backup_dept / controlfile.000' [reuse]; Backup is a text file method Alter Database backup controlfile to turn [resetLogs | NoreSetLogs]; [q] Control file corruption how to recover [a] 1, if it is damage A single control file only needs to turn off the database, copy a good data file override or modify the related part of the init.ora file. If it is a loss of all control files, you need to create a control file or recover from backup. Creating a control file can be obtained by ALTER DATABASE Backup ControlFile To TRACE. [Q] How to back up a table space [A] ALTER TABLESPACE Name Begin Backup; Host CP This tablespace Data file destination; ALTER TABLESPACE Name End Backup; if you are backup multiple tablespace or the entire database, you only need one The operation of a table space is OK.
[Q] How to quickly get hot standby scripts for the entire database [A] can write a similar script SQL> SET ServerOutput OnBegindBMS_output.enable (10000); for BK_TS in (Select Distinct T.TS #, T.Name from V $ TABLESPACE T, V $ datafile d where t.ts # = d.ts #) loopdbms_output.put_line ('-' || bk_ts.name); dbms_output.put_line ('alter tablespace' || bk_ts.name || 'Begin Backup ; '); for bk_file in (SELECT FILE #, Name from V $ DataFile WHERE TS # = BK_TS.TS #) loopdbms_output.put_line (' Host CP '|| BK_FILE.NAME ||' $ BACKUP_DEPT / '); End Loop DBMS_OUTPUT.PUT_LINE ('ALTER TABLESPACE' || BK_TS.NAME || 'End Backup;'); end loop; end; / [q] Loss a data file, but no backup, how to open the database [A] If not Backup can only be deleted this data file, which will result in the corresponding data loss. SQL> startup mount - ARCHIVELOG mode command SQL> Alter database datafile 'file name' offline; - NOARCHIVELOG mode command SQL> Alter database datafile 'file name' offline drop; SQLl> Alter database open; Note: This data file can not be System data file [q] Loss a data file, no backup but how to recover the files since the data file creation [A] guarantees the following conditions a. Can't be system data file B. You can't lose control file If you meet the above conditions, SQL > Startup Mountsql> ALTER DATABASE CREATE DATAFILE 'File Name' As 'File Name' Size ... Reuse; SQL> Recover DataFile N; - File Number or SQL> Recover DataFile 'File Name'; or SQL> Recover Database; SQL> ALTER DATABASE OPEN; [Q] How to restore [A] 1 online log, if it is a non-current log and archive, you can use ALTER DATABASE CLOGFILE Group N to create a new log file. If the log has not filed, you need to use Alter Database Clear UnarchiveD logfile group N2, if it is current log damage, it is generally not clear, it may mean that lost data If there is a backup, you can use a backup for incomplete recovery If there is no backup, you may only use _allow_resetlogs_corruption = true to enforce recovery However, this method is not recommended, preferably under the guidance of Oracle Support.
[Q] how to create RMAN recovery catalog [A] First, create a database user, usually RMAN, and give recovery_catalog_owner role permissions sqlplus sysSQL> create user rman identified by rman; SQL> alter user rman default tablespace tools temporary tablespace temp ; SQL> alter user rman quota unlimited on tools; SQL> grant connect, resource, recovery_catalog_owner to rman; SQL> exit; then, with the user login, create a recovery catalog rman catalog rman / rmanRMAN> create catalog tablespace tools; RMAN> exit Finally, you can return the directory registration target database RMAN Catalog RMAN / RMAN Target Backdba / Backdbarman> Register Database; [Q] How to move data files when recovering, restore to another location [a] to a RMAN Example Run {set untric Time 'JUL 01 1999 00: 05: 00'; Allocate Channel D1 Type Disk; Set NewName for DataFile '/u04/oracle/prod/sys1prod.dbf'to' / U02 / Oracle / Prod / Sys1Prod. DBF '; set newname for datafile' /u04/oracle/prod/usr1prod.dbf'to '/u02/oracle/prod/usr1prod.dbf';set newname for datafile' /u04/oracle/Prod/tmp1prod.dbf'to '/u02/oracle/prod/tmp1prod.dbf';Restore ControlFile To' /u02/oracle/prod/ctl1prod.ora';Replicate ControlFile from '/u02/oracle/Prod/ctl1Prod.ra';Restore Database ASE; SQL "Alter Database Mount"; Switch DataFile All; Recover Database; SQL "ALTER DATABASE OPEN RESETLOGS"; Release Channel D1;} [q] How to recover (restore) Control files and data files from backup tablets (RESTORE) A] You can use the following method to restore the backup of the backup film in the RMAN. RESTORE controlfile from backuppiecefile; if you are 9i automatic backup, you can use the following method RESTORE controlFile from autobackup; however, if the control file is all lost, you need to specify DBID, Such as set dbid =? The default format of the automatic backup control file is% f, this format is c-iiiiiiiiiiii-yyyymmdd-qq, where IIIIIIII is DBID to recover data file, Oracle 816 starts with a package dbms_backup_restore in Nomount Can be executed in the state, you can read 815 or even the previous backup sheet,
The read file is used to recover, you can run in SQLPLUS, as follows SQL> Startup Nomount SQL> Declare 2 DevType Varchar2 (256); 3 Done Boolean; 4 Begin 5 DevType: = dbms_backup_restore.deviceAllocate ('', params => '' ); 6 dbms_backup_restore.restoresetdatafile; 7 dbms_backup_restore.restorecontrolfileto ( 'E: / Oracle / oradata / penny / control01.ctl'); 8 dbms_backup_restore.restoreDataFileto (1, 'E: / Oracle / oradata / p enny / system01.dbf' ); 9 dBMS_BACKUP_RESTORE.RESTOREDATAFILETO (2, 'E: / ORADATA / P EnNy / undotbs01.dbf'); 10 dbms_backup_restore.restoreDataFileTo (3, 'E: / Oracle / ORADATA / P EnNY / users01.dbf'); 11 dBMS_BACKUP_RESTORE.RESTOREBACKUPPIECE ('D: / ORABAK / BACKUP_1_4 _04F
4IAJT.PENNY ', DONE => DONE); 12 END; 13 / PL / SQL process has been successfully completed.
SQL> ALTER DATABASE MOUNT; [Q] What is the meaning of% s in the Format format in RMAN, what is the meaning of what is mean [A] can refer to the following% C backup card copy number% D database name% D at the first few days in the month (DD)% M is located in the month (mm)% F a DBID-based name, this format is C-IIIIIIIII-YYYYMMDDD-QQ, where IIIIIIII is the DBID, YYYMMDD of the database, QQ is a 1-256 sequence% n database name that fills to the right to the maximum of eight characters% U A number of eight characters represents the backup set and the creation time% p. The backup slice in the backup set, from 1st to the creation File% U A unique file name, representing% u_% p_% C% S backup set number% T backup set timestamp% T-year monthly day format (YYYYMMDD) [q] execute EXEC DBMS_LOGMNR_D.BUILD ('logminer . ORA ',' File Directory ' Logminer.ora ',' file Directory '); END; * Error is located in Chapter 1: ORA-06532: Latestage beyond the limit ORA-06512: in "sys.dbms_logmnr_d", line 793 ora-06512: LINE 1 solution For: 1. Editing the file "dbmslmd.sql" in the "$ oracle_home / rdbms / admin" directory Change line: Type col_Desc_array is varray (513) of col_description; Type Col_Desc_Array Is Varray (700) of col_description; and save the file 2. Run the changed script SQLPLUS> Connect Internal Sqlplus> @ $ oracle_home / rdbms / admin / dbmslmd.sql 3. Recompass the package SQLUS> ALTER PACKAGE DBMS_LOGMNR_D Compile Body; [q] Execute Execute DBMS_L ogmnr.start_logmnr (DictFileName => 'DictFileName') prompted ORA-01843: Invalid month, this is what causes [A] We analyzed start_logmnr package PROCEDURE start_logmnr (startScn IN NUMBER default 0, endScn IN NUMBER default 0, startTime IN DATE default TO_DATE ('01-Jan-1988 ',' DD-MON-YYYY '), EndTime In Date Default To_Date ('01-Jan-2988', 'DD-MON-YYY'), DictFileName In Varchar2 Default ', Options In binary_integer default 0); You can know if to_date ('01-marn-1988 ',' DD-MON-YYYY ') failed, will result in the above error, so the solution can be 1, ALTER session set nls_language =
American2, perform Execute dbms_logmnr.start_logmnr (DictFileName => 'f: /temp2/testdict.ora', starttime => to_date ('01 -01-1988) with the following method
',' DD-mm-yyyy '), endtime => to_date ('01 -01-2988', 'DD-MM-YYYY');
