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 manually archive, if it is closed, set this parameter to pay attention: If it is an OPS / RAC environment Need to comment out first, then execute the following steps, finally restart 1, turn on the archive a. Close the database shutdown immediateb. Startup Mountc. Alter Database ArchiveLogd. Alter Database Opne2, disable archive a. Turn off Database Shutdown ... immediateb startup mountc alter database noarchivelogd alter database open archived information can view SQL> archive log listDatabase log mode archive ModeAutomatic archival EnabledArchive destination E by the following words: / 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 more, to ensure that the unit of archive does not exceed n seconds set Archive_lag_target = n unit: second range: 0 ~ 7200
[Q] How to export / import in different versions [A] Export use low version, imported with the current version If the version is too large, you need to use the intermediate version of the transition
[Q] How to pass the different character sets [a] a. Precate condition is to ensure that the export / import meets other character set criteria, such as the customer environment 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_ID according to the character set ID Name Name Name
[Q] How to back up control file [A] Back up backup to a binary file ALTER DATABASE BACKUP ControlFile to '$ backup_dept / controlfile.000' [Reuse]; Backup to text file method Alter Database Backup ControlFile to TRACE [RESETLOGS | NoresetLogs];
[Q] Control file damage How to restore [A] 1. If you are damaged a single control file, you only need to close the database, copy a good data file to overwrite the extracted data file or modify the related part of the init.ora file, If you lose all control files, you need to create a control file or a script that creates a control file from the backup recovery can be obtained via the 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] Lost a data file, no backup but how to recover the archive since the data file [A] guarantees the following conditions a. Can't be a system data file B. You cannot lose control files If you meet the above conditions, then SQL> Startup Mountsql > ALTER DATABASE CREATE DATAFILE '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 damage [A] 1, if it is a non-current log and archive, you can use the ALTER DATABASE CLOGFILE GROUP N to create a new log file. If the log is not archive, you need to use ALTER DATABASE CLEAR UNATIVED LOGFILE Group N2, if it is the current log damage, it is generally not CLEAR, it may mean that if there is a backup, you can use a backup to perform incomplete recovery If there is no backup, you can only use _allow_resetlogs_corruption = true to force to recover, but, Such a 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 from backup card (restore) Control file and data file [A] You can use the following method to restore the backup of the backup film RESTORE controlfile from backuppiecefile; if it is 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-IIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIMMMDD-QQ, The iiiiiiiiii is DBID to recover data files, and Oracle 816 starts with a package DBMS_BACKUP_RESTORE to execute in the Nomount state, you can read 815 or even previous backups, read files for recovery 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.RESTOREDATAFIETO (1,' E: /oAdata/penny/system01.dbf '); 9 dbms_backup_restore.restoreDataFileto (2,' E: / Oracle / oradata / penny / UNDOTBS01.DBF '); 10 dbms_backup_restore.restoreDataFileto (3,' E: /ORACLE/ORADATA/PENNY/USERS01.DBF '); 11 dbms_backup_restore.restorebackuppiece (' D: /orabak/BACKUP_1_4_04F4IAJT.PENNY',done => DO Ne); 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'), prompt to subscript superior, what to do [A] Complete error message is as follows, SQL> EXEC DBMS_LOGMNR_D.BUILD ('logminer.ora', 'file directory') Begin dbms_logmnr_d.build ('logminer.ora'); end Directory '); end; * Error is located on the 1st line: ORA-06532: The subscript beyond the limit ORA-06512: "sys.dbms_logmnr_d", Line 793 ORA-06512: In line 1 solution is: 1. Editing 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; 2. save the file and run the script changed after SQLPLUS> Connect internal SQLPLUS> @ $ ORACLE_HOME / rdbms / admin / dbmslmd.sql 3. recompile the package SQLPLUS> alter package DBMS_LOGMNR_D compile body; [ Q] execute execute dbms_logmnr.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-YYYY'), DictFileName In Varchar2 Default '', OPTIONS IN B INARY_INTEGER DEFAULT 0; you can know if to_date ('01-jan-1988 ',' DD-MON-YYYY ') fails, will result in the above error, so the solution can be 1, alter session set nls_Language = American2, with similar below Method Execution Execute DBMS_Logmnr.Start_Logmnr (DictFileName => F: /TEMP2/testdict.ora ', starttime => to_date ('01 -01-1988', 'DD-MM-YYYY'), endtime => to_date (' 01-01-2988 ',' DD-MM-YYYY ');
