FAQs about Oracle-backup and recovery

Part 3 backup and recovery[Q] How to enable/disable Archiving[A] If archiving is enabled, ensure log_archive_start = true to enable automatic archiving. Otherwise, only manual archiving is supported. If archiving is disabled, set this parameter to false. Note: in the OPS/RAC environment, You Need To comment out parallel_server = true first, then perform the following steps, and then use this parameter to restart 1. Enable archive. shut down the database shutdown immediateb. startup mountc. alter database archivelogd. alter database opne2. Archive disabled. shut down the database shutdown immediateb. startup mountc. alter database noarchivelogd. you can use the following statement to view the alter database open archive information: SQL> Archive log listDatabase log mode archive ModeAutomatic archival EnabledArchive destination E: \ oracle \ ora92 \ database \ archiveOldest online log sequence 131 Next log sequence to archive 133 Current log sequence 133[Q] how to set timed Archiving[A] 9i and later versions, ensure that the minimum interval of archiving is not more than n seconds. Set Archive_lag_target = n unit: seconds range: 0 ~ 7200[Q] How to export/import different versions[A] use A lower version for export. Use an intermediate version for import if the current version is too large.[Q] How to import data before different character sets[A] a. The prerequisite is to ensure that the export/import meets other character set standards. For example, the customer environment is consistent with the database character set. B. Modify the 2 and 3 bytes of the dmp file to the character set of the target database. replace them with hexadecimal. Reference Function (the ID in the following function is in decimal format): nls_charset_name obtains the character set name nls_charset_id Based on the Character Set ID to obtain the character set ID based on the Character Set Name[Q] how to back up control files[A] backing up A record as A binary file alter database backup controlfile to '$ BACKUP_DEPT/controlfile.000' [reuse]; backup as a text file: alter database backup controlfile to trace [resetlogs | noresetlogs];[Q] how to restore control File Corruption[A] 1. If A single control file is damaged, you only need to close the database, copy A good data file to overwrite the bad data file, or modify init. related part of the ora file 2. If the control file is lost, you can use alter database backup controlfile to trace to create a control file.[Q] How to hot back up a tablespace[A] Alter tablespace name begin backup; host cp is the destination of the data file in the tablespace; Alter tablespace name end backup; if multiple tablespaces or the entire database are backed up, you only need to perform a tablespace operation.[Q] How to quickly obtain the Hot Standby script of the entire database[A] You 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 | 'beginbackup;'); 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] How do I open a database when I lose a data file but there is no backup?[A] If there is no backup, this data file can only be deleted, which may lead to 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: the data file cannot be a system data file.[Q] How can I restore an archive after a data file is lost without being backed up?[A] ensure that the following conditions. it cannot be a system data file B. the control file cannot be lost. If the preceding conditions are met, 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 recover from online log corruption[A] 1. If A non-current log is archived, you can use Alter database clear logfile group n to create A new log file. If the log is not archived, you need to use Alter database clear unarchived logfile group n2. If the current log is corrupted and cannot be clear, it may mean that if data is lost and backed up, you can use backup for Incomplete recovery. If there is no backup, you may only use _ allow_resetlogs_resume uption = true for forced recovery. However, this method is not recommended, it is best to perform this operation under the guidance of Oracle support.[Q] how to create an RMAN recovery directory[A] First, create A database user, generally RMAN, and grant the recovery_catalog_owner role permission 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, log on with this user and create the recovery directory rman catalog rman/rmanRMAN> create catalog tablespace tools; RMAN> exit; finally, you can register the target database in the recovery directory with rman catalog rman/rman target backdba/backdbaRMAN> register database;[Q] how to move the data file during restoration and restore it to another location?[A] run {set until time 'Jul 01 1999 00:05:00 'for An RMAN example; 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. ora '; restore database; SQL "alter database mount"; switch datafile all; recover database; SQL "alter database open resetlogs"; release channel d1 ;}[Q] how to restore (restore) control files and data files from backuppiece[A] You can use the following method to restore the backup part's control file restore controlfile from backuppiecefile in RMAN. If it is 9i automatic backup, you can use the following method to restore controlfile from autobackup; however, if all the control files are lost, you must specify the DBID, such as set dbid =? The default format of the automatic backup control file is % F, in the form of a c-IIIIIIIIII-YYYYMMDD-QQ, where IIIIIIIIII is the DBID as to the recovery (restore) data file, oracle 816 starts to have a package dbms_backup_restore which can be executed in the nomount state. It can read 815 or even the previous backup slices. The read files can be used to restore and run in SQLPLUS, the following SQL> startup nomountSQL> DECLARE2 devtype varchar2 (256); 3 done boolean; 4 BEGIN5 devtype: = require ('', params =>''); 6 require; 7 dbms_backup_restore.restorecontrolfi Leto ('e: \ Oracle \ oradata \ penny \ control01.ctl '); 8 bytes (1, 'e: \ Oracle \ oradata \ 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 bytes ('d: \ orabak \ backup_00004_04f4iajt.penny ', done => done); 12 END; 13/PL/S The QL process has been completed successfully. SQL> alter database mount;[Q] What is the meaning of % s in Rman format?[A] For details, refer to the following % c copies % d database name % D days in the month (DD) % M days in the year (MM) % F a unique name based on DBID, in the form of c-IIIIIIIIII-YYYYMMDD-QQ, where IIIIIIIIII is the database's DBID, YYYYMMDD is the date, QQ is a series of 1-database names, fill to the right to a maximum of eight characters % u an eight character name represents the backup set and creation time % p the backup set in the backup set number, the number of files created from 1 to % U is a unique file name, % u _ % p _ % c % s backup Set ID % t backup set timestamp % T year month day format (YYYYMMDD)[Q] execute exec dbms_logmnr_d.build ('logminer. ora ', 'file directory') and prompt that the subscript is out of bounds. What should I do?[A] The complete error message is as follows: SQL> exec dbms_logmnr_d.build ('logminer. ora ', 'file directory') BEGIN dbms_logmnr_d.build ('logminer. ora ', 'file directory'); END; * ERROR is located in row 1st: ORA-06532: subscript exceeds limit ORA-06512: In SYS. DBMS_LOGMNR_D ", line 793ORA-06512: solution in line 1: 1. edit the file "dbmslmd" in the "$ ORACLE_HOME/rdbms/admin" directory. SQL "change row: TYPE col_desc_array IS VARRAY (513) OF col_description; TYPE col_desc_array IS VARRAY (700) OF col_description; and save file 2. run the changed script SQLPLUS> Connect internalSQLPLUS >@$ ORACLE_HOME/rdbms/admin/dbmslmd. sql3. re-compile the package SQLPLUS> alter package DBMS_LOGMNR_D compile body;[Q] execute dbms_logmnr.start_logmnr (DictFileName => 'dictfilename ') to prompt ORA-01843: Invalid month, why?[A] analyze the 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 BINARY_INTEGER default 0); you can know that if TO_DATE ('01-jan-1988 ', 'dd-MON-YYYY') fails, the preceding error occurs. Therefore, the solution can be 1. Alter session set NLS_LANGUAGE = American2. execute dbms_logmnr.start_logmnr (DictFileName => 'f: \ temp2 \ TESTDICT in a similar way. ora ', starttime => TO_DATE ('01-01-1988', 'dd-MM-YYYY '), endTime => TO_DATE ('01-01-2988', 'dd-MM-YYYY '));