Oracle Common FAQ V1.0 (3)

Oracle Part III, backup and recovery
[Q] How to turn on/off the archive
[A] If the archive is turned on, make sure that the log_archive_start=true is open for automatic archiving, otherwise it can only be archived manually, and set this argument to False if the archive is turned off
Note: If it is a OPS/RAC environment, you need to first comment out Parallel_server = True, and then perform the following steps, and then restart with this parameter
1, open the archive
A. Close the database shutdown immediate
B. Startup mount
C. ALTER DATABASE Archivelog
d. Alter DATABASE Opne
2. No filing
A. Close the database shutdown immediate
B. Startup mount
C. ALTER DATABASE Noarchivelog
d. ALTER DATABASE open
Archive information can be viewed through the following statements
Sql> Archive Log List
Database Log Mode Archive mode
Automatic Archival Enabled
Archive Destination E:\oracle\ora92\database\archive
Oldest online log sequence 131
Next log sequence to archive 133
Current log sequence 133
[Q] How to set up a timed archive
[a]9i above, ensure that the minimum interval for archiving is no more than n seconds
Set archive_lag_target = n
Unit: Seconds Range: 0~7200
[Q] How to export/import different versions
[A] Export with a low version, import with current version
If the version spans are too large, you need to use intermediate version transitions
[Q] How to guide data before different character sets
[A]a. The previous condition is to ensure that export/import conforms to other character set criteria, such as customer environment and database character set.
B. Modify the DMP file's 2, 3 bytes as the target database's character set, and note that you want to replace it with 16.
Reference function (the ID in the following function is decimal):
Nls_charset_name gets the character set name based on the character set ID
NLS_CHARSET_ID the character set ID based on the character set name
[Q] How to back up control files
[A] re-line backup to a binary file
ALTER DATABASE backup Controlfile to ' $BACKUP _dept/controlfile.000 ' [reuse];
Backup as text file mode
ALTER DATABASE backup Controlfile to trace [resetlogs|noresetlogs];
[Q] Control how file corruption is recovered
[A]1, if a single control file is corrupted
Just close the database, copy a good data file and overwrite the bad data file.
or modify the relevant part of the Init.ora file
2, if the loss of all control files, you need to create a control file or restore from the backup
Scripts for creating control files can be obtained through ALTER DATABASE backup Controlfile to trace.
[Q] How to warm up a table space
A Alter tablespace name begin backup;
Host CP is the data file destination for this tablespace;
Alter tablespace name end backup;
If you are backing up multiple table spaces or the entire database, you need only one table space to operate.
[Q] How to quickly get the entire database hot standby script
[A] can write a similar script
Sql>set Serveroutput on
Begin
Dbms_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#) loop
Dbms_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#) loop
Dbms_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] lost a data file, but no backup, how to open the database
[A] If no backup can only be deleted this data file, will cause 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 cannot be a system data file
[Q] Loses a data file, does not have a backup but has the archive since the creation of the data file how to recover
[A] ensure the following conditions
A. Cannot be a system data file
B. Cannot lose control file
If the above conditions are met, the
Sql>startup Mount
Sql>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] Online log corruption recovery
[A]1, if not current log and archive, you can use
Alter Database Clear LogFile Group N to create a new log file
If the log is not yet archived, you will need to use the
Alter Database Clear unarchived logfile Group N
2, if the current log is corrupted, generally can not clear, it may mean that data loss
If there is a backup, you can use backup for incomplete recovery
If there is no backup, you may only use _allow_resetlogs_corruption=true for forced recovery, but such a method is not recommended, preferably under the guidance of Oracle support.
[Q] How to create an Rman recovery directory
[A] First, create a database user, typically Rman, and give Recovery_catalog_owner role permissions
Sqlplus SYS
Sql> 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 in with this user to create the recovery directory
Rman Catalog Rman/rman
rman> Create catalog Tablespace tools;
Rman> exit;
Finally, you can register the target database in the recovery directory
Rman Catalog Rman/rman Target BACKDBA/BACKDBA
rman> Register database;
[Q] How to move the data file when recovering to another location
[A] give an example of an Rman
Run {
Set until time ' June 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.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 backup slices (backuppiece)
[A] You can use the following method to recover the control file of the backup slice in Rman
Restore Controlfile from Backuppiecefile;
If it is an automatic backup of 9i, you can use the following method
Restore Controlfile from Autobackup;
However, if the control files are all lost, you need to specify DBID, such as set dbid=?
The default format for automatic backup control files is%f, which is in the form of
C-IIIIIIIIII-YYYYMMDD-QQ, where IIIIIIIIII is dbid
As for the restore data file, Oracle 816 begins with a package Dbms_backup_restore
In the Nomount state can be performed, can read 815 or even before the backup slice, read out the file for recovery
Can be 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\penny ystem01.dbf ');
9 Dbms_backup_restore.restoredatafileto (2, ' E:\Oracle\oradata\penny\UNDOTBS01. DBF ');
Dbms_backup_restore.restoredatafileto (3, ' E:\ORACLE\ORADATA\PENNY\USERS01. DBF ');
One dbms_backup_restore.restorebackuppiece (' D:\orabak\BACKUP_1_4_04F4IAJT. PENNY ', done=>done);
End;
13/
The PL/SQL process has completed successfully.
Sql> ALTER DATABASE Mount;
[Q] %s in the format of Rman is something similar to what it means
[A] can refer to the following
Number of copies of%c backup slice
%d database name
%d The day ordinal of the month (DD)
%m in the month ordinal of the year (MM)
%F a unique name based on dbid, this format is in the form of C-IIIIIIIIII-YYYYMMDD-QQ, where IIIIIIIIII for the database DBID,YYYYMMDD for the date, QQ is a 1-256 sequence
%n database name, fill to the maximum eight characters to the right
%u a eight-character name represents the backup set and the creation time
%p number of backup slices in this backup set, starting from 1 to the number of files created
%u a unique file name that represents%u_%p_%c
Number of the%s backup set
%t Backup set Timestamp
%T Month-day format (YYYYMMDD)
[Q] Executive exec dbms_logmnr_d.build (' Logminer.ora ', ' file directory '), indicating the subscript hyper-bounds, how to 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 on line 1th:
ORA-06532: Subscript exceeds limit
ORA-06512: In the SYS. Dbms_logmnr_d ", line 793
ORA-06512: On line 1
The solution is:
1. Edit the file "Dbmslmd.sql" located in the "$ORACLE _home/rdbms/admin" directory
To change a line:
TYPE Col_desc_array is Varray (513) of col_description;
For
TYPE Col_desc_array is Varray (m) of col_description;
and save the file
2. Run the changed script
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 ') hint ORA-01843: Invalid month, what's the reason
[A] We 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);
It can be known that if To_date (' 01-jan-1988 ', ' dd-mon-yyyy ') fails, the above error will result
So the solution can be
1. Alter Session Set Nls_language=american
2, in a similar manner as follows
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 '));