[TIPS]Some Teach Topics about Oracle(页 1) - 常用数据库类{ Database Security } - 巨人脚下[ Oracle ] - 邪恶八进制信息安全团队技术讨论组努力为祖国的信息安全撑起一片蓝天

pub!1c 2006-2-11 13:48

[TIPS]Some Teach Topics about Oracle

信息来源:邪恶八进制信息安全团队
本主题记录一些Oracle数据库的使用技巧 TIPS里不准做任何形式的灌水 谢谢合作.... 欢迎大家跟帖参与完善：）

pub!1c 2006-2-11 13:50

在Linux的sqlplus中，如果使用edit对当前脚本进行编辑，则调用默认编辑器ed,如果想使用vi作为默认编辑器，则可以编辑文件$ORACLE_HOME/sqlplus/admin/glogin.sql,添加如下行

[quote]DEFINE _EDITOR = vi[/quote]
重新启动sqlplus后，运行edit命令时将会调用vi作为编辑器

在sqlplus启动时,会首先运行$ORACLE_HOME/sqlplus/admin/glogin.sql，因此我们也可以在这个文件中设置别的变量

pub!1c 2006-2-11 13:51

<div>SQL*Plus Copy Command的语法： COPY {FROM database | TO database | FROM database TO database} {APPEND|CREATE|INSERT|REPLACE} destination_table [(column, column, column, ...)] USING query</div><div></div><div>{APPEND|CREATE|INSERT|REPLACE} – 声明操作数据的方式，下面分别解释一下： Append – 向已有的目标表中追加记录，如果目标表不存在，自动创建，这种情况下和Create等效。 Create – 创建目标表并且向其中追加记录，如果目标表已经存在，则会返回错误。 Insert – 向已有的目标表中插入记录，与Append不同的是，如果目标表不存在，不自动创建而是返回错误。 Replace – 用查询出来的数据覆盖已有的目标表中的数据，如果目标表不存在，自动创建。 destination_table – 目标表的名字 [(column, column, column, ...)] – 可以指定目标表中列的名字，如果不指定，则自动使用Query中的列名。 USING query – 查询语句，交流的数据来自这儿。</div><div></div><div>例子：</div><div>copy from <a href="mailto:business/business@to111">business/business@to111</a> to <a href="mailto:business/business@to114">business/business@to114</a> create business.sys_staff using select * from business.sys_staff;</div>

pub!1c 2006-2-12 10:42

、telnet登录小型机，连接数据库，查看数据库状态。

oracle$sqlplus /nolog

SQL*Plus: Release 9.2.0.1.0 - Production on Sun Aug 28 12:58:51 2005

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

SQL> conn / as sysdba Connected. SQL> select status from v$instance;

STATUS

--------------------------------------------------------------------------------

STARTED

2、mount数据库，发现控制文件不一致。

SQL> alter database mount; alter database mount

ERROR at line 1: ORA-00214: controlfile '/oracle/oracle/app/oracle/product/9.2.0.1/dbs/hb030000/control01.ctl' version 99279 inconsistent with file '/oradata/hb030000/control02.ctl' version 99212

SQL> exit Disconnected from Oracle9i Enterprise Edition Release 9.2.0.1.0 - 64bit Producti on With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.1.0 - Production

3、rman连接catalog，恢复控制文件。

oracle$rman target / catalog rman/rman@omsora9

Recovery Manager: Release 9.2.0.1.0 - 64bit Production

Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.

connected to target database: hb030000 (not mounted) connected to recovery catalog database

RMAN>

RMAN> restore controlfile;

Starting restore at 28-AUG-05

allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=13 devtype=DISK allocated channel: ORA_DISK_2 channel ORA_DISK_2: sid=14 devtype=DISK allocated channel: ORA_DISK_3 channel ORA_DISK_3: sid=15 devtype=DISK allocated channel: ORA_DISK_4 channel ORA_DISK_4: sid=16 devtype=DISK channel ORA_DISK_1: starting datafile backupset restore channel ORA_DISK_1: restoring controlfile output filename=/oracle/oracle/app/oracle/product/9.2.0.1/dbs/hb030000/control01 .ctl channel ORA_DISK_1: restored backup piece 1 piece handle=/oradata/rmanbackup/qhdora9_ctl_c-3596129099-20050827-00.bak tag=nu ll params=NULL channel ORA_DISK_1: restore complete replicating controlfile input filename=/oracle/oracle/app/oracle/product/9.2.0.1/dbs/hb030000/control01. ctl output filename=/oradata/hb030000/control02.ctl output filename=/oradata/hb030000/control03.ctl Finished restore at 28-AUG-05

4、控制文件恢复完毕，数据库mount，打开数据库，发现system01文件不一致，需要介质恢复。 RMAN> alter database mount;

database mounted

RMAN> alter database open;

RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of alter db command at 08/28/2005 13:01:13 ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

RMAN> alter database open resetlogs;

RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of alter db command at 08/28/2005 13:01:25 ORA-01194: file 1 needs more recovery to be consistent ORA-01110: data file 1: '/oradata/hb030000/system01.dbf'

5、恢复system01数据文件，打开数据库有问题。

oracle$rman target / catalog rman/rman@omsora9

Recovery Manager: Release 9.2.0.1.0 - 64bit Production

Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.

connected to target database: HB030000 (DBID=3596129099) connected to recovery catalog database

RMAN> restore datafile 1;

Starting restore at 28-AUG-05

allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=15 devtype=DISK allocated channel: ORA_DISK_2 channel ORA_DISK_2: sid=14 devtype=DISK allocated channel: ORA_DISK_3 channel ORA_DISK_3: sid=13 devtype=DISK allocated channel: ORA_DISK_4 channel ORA_DISK_4: sid=11 devtype=DISK channel ORA_DISK_1: starting datafile backupset restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set restoring datafile 00001 to /oradata/hb030000/system01.dbf

channel ORA_DISK_1: restored backup piece 1 piece handle=/oradata/rmanbackup/df_HB030000_566351414_1298_1.bak tag=TAG2005081 4T235013 params=NULL channel ORA_DISK_1: restore complete Finished restore at 28-AUG-05

RMAN> alter database open resetlogs;

RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of alter db command at 08/28/2005 13:27:26 ORA-01152: file 1 was not restored from a sufficiently old backup ORA-01110: data file 1: '/oradata/hb030000/system01.dbf'

RMAN> recover datafile 1;

Starting recover at 28-AUG-05 using channel ORA_DISK_1 using channel ORA_DISK_2 using channel ORA_DISK_3 using channel ORA_DISK_4 channel ORA_DISK_1: starting incremental datafile backupset restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set destination for restore of datafile 00001: /oradata/hb030000/system01.dbf channel ORA_DISK_1: restored backup piece 1 piece handle=/oradata/rmanbackup/df_HB030000_567474616_1384_1.bak tag=TAG2005082 7T235015 params=NULL channel ORA_DISK_1: restore complete

RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of recover command at 08/28/2005 13:29:19 RMAN-06067: RECOVER DATABASE required with a backup or created controlfile

RMAN>

6、查看alert_hb030000.log文件，数据库是在2005/08/28/12：10分正常关闭，不完全恢复数据库。

oracle$rman target / catalog rman/rman@omsora9

Recovery Manager: Release 9.2.0.1.0 - 64bit Production

Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.

connected to target database: hb030000 (not mounted) connected to recovery catalog database

RMAN> alter database mount;

database mounted

RMAN> run { 2> set until time="to_date('2005-08-28:12:10:10','yyyy-mm-dd:hh24:mi:ss')"; 3> restore database; 4> }

executing command: SET until clause

Starting restore at 28-AUG-05

allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=13 devtype=DISK allocated channel: ORA_DISK_2 channel ORA_DISK_2: sid=14 devtype=DISK allocated channel: ORA_DISK_3 channel ORA_DISK_3: sid=15 devtype=DISK allocated channel: ORA_DISK_4 channel ORA_DISK_4: sid=16 devtype=DISK channel ORA_DISK_1: starting datafile backupset restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set restoring datafile 00001 to /oradata/hb030000/system01.dbf restoring datafile 00008 to /oradata/hb030000/tools01.dbf restoring datafile 00011 to /oradata/hb030000/GFB.DBF channel ORA_DISK_2: starting datafile backupset restore channel ORA_DISK_2: specifying datafile(s) to restore from backup set restoring datafile 00002 to /oradata/hb030000/undotbs01.dbf restoring datafile 00004 to /oradata/hb030000/drsys01.dbf restoring datafile 00010 to /oradata/hb030000/xdb01.dbf channel ORA_DISK_3: starting datafile backupset restore channel ORA_DISK_3: specifying datafile(s) to restore from backup set restoring datafile 00003 to /oradata/hb030000/cwmlite01.dbf restoring datafile 00005 to /oradata/hb030000/example01.dbf channel ORA_DISK_4: starting datafile backupset restore channel ORA_DISK_4: specifying datafile(s) to restore from backup set restoring datafile 00006 to /oradata/hb030000/indx01.dbf restoring datafile 00007 to /oradata/hb030000/odm01.dbf restoring datafile 00009 to /oradata/hb030000/users01.dbf

channel ORA_DISK_3: restored backup piece 1 piece handle=/oradata/rmanbackup/df_HB030000_566956214_1340_1.bak tag=TAG20050821T235013 params=NULL channel ORA_DISK_3: restore complete channel ORA_DISK_4: restored backup piece 1 piece handle=/oradata/rmanbackup/df_HB030000_566956214_1342_1.bak tag=TAG20050821T235013 params=NULL channel ORA_DISK_4: restore complete channel ORA_DISK_1: restored backup piece 1 piece handle=/oradata/rmanbackup/df_HB030000_566351414_1298_1.bak tag=TAG20050814T235013 params=NULL channel ORA_DISK_1: restore complete

channel ORA_DISK_2: restored backup piece 1 piece handle=/oradata/rmanbackup/df_HB030000_566956214_1341_1.bak tag=TAG20050821T235013 params=NULL channel ORA_DISK_2: restore complete Finished restore at 28-AUG-05

RMAN>

RMAN> recover database;

Starting recover at 28-AUG-05 using channel ORA_DISK_1 using channel ORA_DISK_2 using channel ORA_DISK_3 using channel ORA_DISK_4 channel ORA_DISK_4: starting incremental datafile backupset restore channel ORA_DISK_4: specifying datafile(s) to restore from backup set destination for restore of datafile 00002: /oradata/hb030000/undotbs01.dbf destination for restore of datafile 00004: /oradata/hb030000/drsys01.dbf destination for restore of datafile 00010: /oradata/hb030000/xdb01.dbf channel ORA_DISK_1: starting incremental datafile backupset restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set destination for restore of datafile 00003: /oradata/hb030000/cwmlite01.dbf destination for restore of datafile 00005: /oradata/hb030000/example01.dbf channel ORA_DISK_2: starting incremental datafile backupset restore channel ORA_DISK_2: specifying datafile(s) to restore from backup set destination for restore of datafile 00006: /oradata/hb030000/indx01.dbf destination for restore of datafile 00007: /oradata/hb030000/odm01.dbf destination for restore of datafile 00009: /oradata/hb030000/users01.dbf channel ORA_DISK_3: starting incremental datafile backupset restore channel ORA_DISK_3: specifying datafile(s) to restore from backup set destination for restore of datafile 00001: /oradata/hb030000/system01.dbf destination for restore of datafile 00008: /oradata/hb030000/tools01.dbf destination for restore of datafile 00011: /oradata/hb030000/GFB.DBF channel ORA_DISK_2: restored backup piece 1 piece handle=/oradata/rmanbackup/df_HB030000_567474616_1383_1.bak tag=TAG20050827T235015 params=NULL channel ORA_DISK_2: restore complete channel ORA_DISK_1: restored backup piece 1 piece handle=/oradata/rmanbackup/df_HB030000_567474616_1381_1.bak tag=TAG20050827T235015 params=NULL channel ORA_DISK_1: restore complete channel ORA_DISK_3: restored backup piece 1 piece handle=/oradata/rmanbackup/df_HB030000_567474616_1384_1.bak tag=TAG20050827T235015 params=NULL channel ORA_DISK_3: restore complete channel ORA_DISK_4: restored backup piece 1 piece handle=/oradata/rmanbackup/df_HB030000_567474616_1382_1.bak tag=TAG20050827T235015 params=NULL channel ORA_DISK_4: restore complete

starting media recovery

archive log thread 1 sequence 1107 is already on disk as file /oradata/hb030000/redo02.log archive log thread 1 sequence 1108 is already on disk as file /oracle/oracle/app/oracle/product/9.2.0.1/dbs/hb030000/redo03.rdo archive log filename=/oradata/hb030000/redo02.log thread=1 sequence=1107 archive log filename=/oracle/oracle/app/oracle/product/9.2.0.1/dbs/hb030000/redo03.rdo thread=1 sequence=1108 media recovery complete Finished recover at 28-AUG-05

RMAN> RMAN> alter database open resetlogs;

database opened new incarnation of database registered in recovery catalog starting full resync of recovery catalog full resync complete

RMAN>

pub!1c 2006-2-12 10:44

问题是，由于DBA的错误，导致了控制文件损坏。一般来说，oracle缺省会有3个备份，除非你自己舍弃这样的配置。

总之，会看到oracle提示，当前的控制文件太老，需要作恢复。

svrmgrl>startup mount

svrmgrl>alter database backup controlfile to trace;

然后执行恢复控制文件的命令

svrmgrl>CREATE CONTROLFILE DATABASE "LT" NORESETLOGS NOARCHIVELOG
[quote]
  MAXLOGFILES 32
  MAXLOGMEMBERS 2
  MAXDATAFILES 254
  MAXINSTANCES 8
  MAXLOGHISTORY 907[/quote]
LOGFILE
[quote]
GROUP 1 '/export/home/oracle/oradata/LT/redo01.log'  SIZE 500K,
GROUP 2 '/export/home/oracle/oradata/LT/redo02.log'  SIZE 500K,
GROUP 3 '/export/home/oracle/oradata/LT/redo03.log'  SIZE 500K[/quote]
DATAFILE
[quote]
'/export/home/oracle/oradata/oradata/DB/System.dbf'[/quote]
.................
..........
然后shutdown,再startup

pub!1c 2006-2-12 10:45

Subject: How to Extract Controlfiles, Datafiles, and Archived Logs from RMAN Backupsets 
Type: REFERENCE 
Status: PUBLISHED 
Content Type: TEXT/X-HTML 
Creation Date: 08-APR-1998 
Last Revision Date: 21-FEB-2003 
 How to extract controlfiles, datafiles, and archived logs from SMR backupsets without using RMAN 
Introduction: <PRE> When using RMAN to restore objects (datafiles, controlfiles, or archivelogs)
from backupsets, the object restore can be driven from the recovery catalog
or the target database controlfile. This note explains how to extract
objects from backupsets when the recovery catalog and controlfiles have been
lost. In this scenario, you effectively perform the RMAN functions through
PL/SQL procedure calls
</PRE>
 Contents: <PRE> 1. Prerequisites
2. Extracting the controlfile from a backupset
3. Extracting datafiles from a backupset
4. Applying incrementals
5. Extracting archivelogs from a backupset
6. A typical scenario
7. Errors
8. Things to be done
</PRE>
 1. Prerequisites <PRE> The customer must have a knowledge of the contents of backupsets i.e. what
they contain, when the backups were created, and the type of backups. Ideally
they should have logs of the RMAN backup sessions that produced the
backupsets.
</PRE><PRE> Note that the following anonymous PL/SQL blocks are run on the instance of
the database being recovered (the 'target'). The instance must be at least
started (once the controlfile has been restored the database can also be
mounted). Anonymous blocks can be executed in this manner as long as they
call only 'fixed' packages. The DBMS_BACKUP_RESTORE packages are fixed.
</PRE><PRE> IMPORTANT: All the anonymous blocks must be executed by SYS or a user
 who has execute privilege on SYS.DBMS_BACKUP_RESTORE
</PRE>
 2. Extracting the controlfile from a backupset <PRE> The first stage is to extract the controlfile from a backupset. This is
achieved by making use of the following SYS.DBMS_BACKUP_RESTORE packaged
functions & procedures:
</PRE><PRE> FUNCTION deviceAllocate - allocates a device for sequential I/O
PROCEDURE restoreSetDataFile - begins a restore conversation
PROCEDURE restoreControlfileTo - specifies the controlfile destination
PROCEDURE restoreBackupPiece - performs the restore
PROCEDURE deviceDeallocate - deallocates the I/O device
</PRE><PRE> The following anonymous block can be created and executed to restore a
controlfile from a backupset. Before executing it, you MUST edit the block
as follows:
</PRE><PRE> a. The filetable PL/SQL table entries must reflect the backuppieces
 comprising the backupset
b. The v_maxPieces variable must reflect the number of backuppieces
 comprising the backupset
c. The call to restoreControlfileTo must specify the correct controlfile
 path & filename
</PRE><PRE> IMPORTANT: The latest backup of the controlfile should be restored. Because
 recovery (using backup controlfile) will be performed manually,
 the recovering session will need to start applying redo from
 the current log sequence AT THE TIME OF THE CONTROLFILE BACKUP.
 Thus, to take advantage of incremental backups, restore a
 controlfile taken along with the incremental backups, thus
 reducing the amount of redo required during recovery.
</PRE>
 DECLARE <PRE> v_dev varchar2(50); -- device type allocated for restore
v_done boolean; -- has the controlfile been fully extracted yet
type t_fileTable is table of varchar2(255)
index by binary_integer;
v_fileTable t_fileTable; -- Stores the backuppiece names
v_maxPieces number:=1; -- Number of backuppieces in backupset
</PRE>
BEGIN 
-- Initialise the filetable & number of backup pieces in the backupset -- This section of code MUST be edited to reflect the customer's available -- backupset before the procedure is compiled and run. In this example, the -- backupset consists of 4 pieces: <PRE> v_fileTable(1):='fulldb_s15_p1';
v_fileTable(2):='fulldb_s15_p2';
v_fileTable(3):='fulldb_s15_p3';
v_fileTable(4):='fulldb_s15_p4';
v_maxPieces:=4;

</PRE>
-- Allocate a device. In this example, I have specified 'sbt_tape' as I am -- reading backuppieces from the media manager. If the backuppiece is on disk, -- specify type=>null <PRE> v_dev:=sys.dbms_backup_restore.deviceAllocate(type=>'sbt_tape',
 ident=>'t1');

</PRE>
-- Begin the restore conversation <PRE> sys.dbms_backup_restore.restoreSetDatafile;

</PRE>
-- Specify where the controlfile is to be recreated <PRE> sys.dbms_backup_restore.restoreControlfileTo(cfname=>'/support2/OFA_V804/u1/oradata/dbs/ctrl1V804.ctl');
</PRE>
-- Restore the controlfile <PRE> FOR i IN 1..v_maxPieces LOOP
sys.dbms_backup_restore.restoreBackupPiece(done=>v_done,
</PRE>
handle=>v_fileTable(i), params=>null); <PRE> IF v_done THEN
 GOTO all_done;
END IF;
END LOOP;
</PRE>
<<all_done>> -- Deallocate the device <PRE> sys.dbms_backup_restore.deviceDeallocate;
</PRE>
END; / 
 3. Extracting datafiles from a backupset <PRE> The second stage is to extract the datafiles from a backupset. This is
achieved by making use of the following SYS.DBMS_BACKUP_RESTORE packaged
functions & procedures:
</PRE><PRE> FUNCTION deviceAllocate - allocates a device for sequential I/O
PROCEDURE restoreSetDataFile - begins a restore conversation
PROCEDURE restoreDataFileTo - datafile number & destination
PROCEDURE restoreBackupPiece - performs the restore
PROCEDURE deviceDeallocate - deallocates the I/O device
</PRE><PRE> The following anonymous block can be created and executed to restore a
datafile from a backupset. Before executing it, you MUST edit the block
as follows:
</PRE><PRE> a. The filetable PL/SQL table entries must reflect the backuppieces
 comprising the backupset
b. The v_maxPieces variable must reflect the number of backuppieces
 comprising the backupset
c. The call to restoreDataFileTo must specify the correct datafile number,
 and datafile path & filename
</PRE>
 DECLARE <PRE> v_dev varchar2(50); -- device type allocated for restore
v_done boolean:=false; -- has the datafile been fully extracted yet
type t_fileTable is table of varchar2(255)
index by binary_integer;
v_fileTable t_fileTable; -- Stores the backuppiece names
v_maxPieces number:=1; -- Number of backuppieces in backupset
</PRE>
BEGIN 
-- Initialise the filetable & number of backup pieces in the backupset -- This section of code MUST be edited to reflect the customer's available -- backupset before the procedure is compiled and run. In this example, the -- backupset consists of 4 pieces: <PRE> v_fileTable(1):='fulldb_s15_p1';
v_fileTable(2):='fulldb_s15_p2';
v_fileTable(3):='fulldb_s15_p3';
v_fileTable(4):='fulldb_s15_p4';
v_maxPieces:=4;

</PRE>
-- Allocate a device. In this example, I have specified 'sbt_tape' as I am -- reading backuppieces from the media manager. If the backuppiece is on disk, -- specify type=>null <PRE> v_dev:=sys.dbms_backup_restore.deviceAllocate(type=>'sbt_tape',
</PRE>
ident=>'t1'); 
-- Begin the restore conversation <PRE> sys.dbms_backup_restore.restoreSetDatafile;

</PRE>
-- Specify where the datafile is to be recreated <PRE> sys.dbms_backup_restore.restoreDataFileTo(dfnumber=>1,
</PRE>
toname=>'/support2/OFA_V804/u1/oradata/dbs/sysV804.dbf'); 
-- Restore the datafile <PRE> FOR i IN 1..v_maxPieces LOOP
sys.dbms_backup_restore.restoreBackupPiece(done=>v_done,
</PRE>
handle=>v_fileTable(i), params=>null); <PRE> IF v_done THEN
 GOTO all_done;
END IF;
END LOOP;
</PRE>
<<all_done>> -- Deallocate the device <PRE> sys.dbms_backup_restore.deviceDeallocate;
</PRE>
END; / 
 4. Applying incrementals <PRE> If incrementals are to be applied, you must execute this anonymous block
for each incremental datafile backup. The following SYS.DBMS_BACKUP_RESTORE
packaged functions & procedures are called:
</PRE><PRE> FUNCTION deviceAllocate - allocates a device for sequential I/O
PROCEDURE applySetDataFile - begins a restore conversation
PROCEDURE applyDataFileTo - datafile number & destination
PROCEDURE applyBackupPiece - performs the restore
PROCEDURE deviceDeallocate - deallocates the I/O device
</PRE><PRE> The following anonymous block can be created and executed to restore a
datafile from a backupset. Before executing it, you MUST edit the block
as follows:
</PRE><PRE> a. The filetable PL/SQL table entries must reflect the backuppieces
 comprising the backupset
b. The v_maxPieces variable must reflect the number of backuppieces
 comprising the backupset
c. The call to applyDataFileTo must specify the correct datafile number,
 and datafile path & filename
</PRE>
 DECLARE <PRE> v_dev varchar2(50); -- device type allocated for restore
v_done boolean:=false; -- has the datafile been fully extracted yet
type t_fileTable is table of varchar2(255)
index by binary_integer;
v_fileTable t_fileTable; -- Stores the backuppiece name
v_maxPieces number:=1; -- Number of backuppieces in backupset
</PRE>
BEGIN 
-- Initialise the filetable & number of backup pieces in the backupset -- This section of code MUST be edited to reflect the customer's available -- backupset before the procedure is compiled and run. In this example, the -- backupset consists of 1 piece, a level 2 backupset: <PRE> v_fileTable(1):='fulldb_level2_s18_p1';
v_maxPieces:=1;
</PRE>
-- Allocate a device. In this example, I have specified 'sbt_tape' as I am -- reading backuppieces from the media manager. If the backuppiece is on disk, -- specify type=>null <PRE> v_dev:=sys.dbms_backup_restore.deviceAllocate(type=>'sbt_tape',
 ident=>'t1');
</PRE>
-- Begin the restore conversation <PRE> sys.dbms_backup_restore.applySetDataFile;
</PRE>
-- Specify where the datafile is to be recreated <PRE> sys.dbms_backup_restore.applyDataFileTo(dfnumber=>1,
 toname=>'/support2/OFA_V804/u1/oradata/dbs/sysV804.dbf');
</PRE>
-- Restore the datafile <PRE> FOR i IN 1..v_maxPieces LOOP
sys.dbms_backup_restore.applyBackupPiece(done=>v_done,
 handle=>v_fileTable(i),
 params=>null);
IF v_done THEN
 GOTO all_done;
END IF;
END LOOP;
</PRE>
<<all_done>> -- Deallocate the device <PRE> sys.dbms_backup_restore.deviceDeallocate;
</PRE>
END; / 
 5. Extracting archivelogs from a backupset <PRE> The last restore stage is to extract the archivelogs from a backupset. This is
achieved by making use of the following SYS.DBMS_BACKUP_RESTORE packaged
functions & procedures:
</PRE><PRE> FUNCTION deviceAllocate - allocates a device for sequential I/O
PROCEDURE restoreSetArchivedLog - begins a restore conversation
PROCEDURE restoreArchivedLog - archivelog sequence & thread numbers
PROCEDURE restoreBackupPiece - performs the restore
PROCEDURE deviceDeallocate - deallocates the I/O device
</PRE><PRE> The following anonymous block can be created and executed to restore
an archivelog from a backupset. Before executing it, you MUST edit the block
as follows:
</PRE><PRE> a. The filetable PL/SQL table entries must reflect the backuppieces
 comprising the backupset
b. The v_maxPieces variable must reflect the number of backuppieces
 comprising the backupset
c. The call to restoreSetArchivedLog must specify the destination
 where the archivelog is to be restored. Ideally the destination string
 should be the same as init.ora:log_archive_dest
d. The call to restoreArchivedLog must specify the log sequence number
 and thread number of the archivelog
</PRE>
 DECLARE <PRE> v_dev varchar2(50); -- device type allocated for restore
v_done boolean:=false; -- has the log been fully extracted yet
type t_fileTable is table of varchar2(255)
index by binary_integer;
v_fileTable t_fileTable; -- Stores the backuppiece names
v_maxPieces number:=1; -- Number of backuppieces in backupset
</PRE>
BEGIN 
-- Initialise the filetable & number of backup pieces in the backupset -- This section of code MUST be edited to reflect the customer's available -- backupset before the procedure is compiled and run. In this example, the -- archivelog backupset consists of 2 pieces: <PRE> v_fileTable(1):='al_s20_p1';
 v_fileTable(2):='al_s20_p2';
 v_maxPieces:=2;

</PRE>
-- Allocate a device. In this example, I have specified 'sbt_tape' as I am -- reading backuppieces from the media manager. If the backuppiece is on disk, -- specify type=>null <PRE> v_dev:=sys.dbms_backup_restore.deviceAllocate(type=>'sbt_tape',
</PRE>
ident=>'t1'); 
-- Begin the restore conversation <PRE> sys.dbms_backup_restore.restoreSetArchivedLog(destination=>'/support2/OFA_V804/app/oracle/admin/arch/arch_');

</PRE>
-- Specify where the archivelog is to be recreated <PRE> sys.dbms_backup_restore.restoreArchivedLog(thread=>1,
</PRE>
sequence=>100); 
-- Restore the archivelog <PRE> FOR i IN 1..v_maxPieces LOOP
sys.dbms_backup_restore.restoreBackupPiece(done=>v_done,
</PRE>
handle=>v_fileTable(i), params=>null); <PRE> IF v_done THEN
 GOTO all_done;
END IF;
END LOOP;
</PRE>
<<all_done>> -- Deallocate the device <PRE> sys.dbms_backup_restore.deviceDeallocate;
</PRE>
END; / 
 <PRE> For restoring multiple archives from a backupset, add a loop
around sys.dbms_backup_restore.restoreArchivedLog()
</PRE><PRE> for seq in <min seq#>..<max seq#> loop
sys.dbms_backup_restore.restoreArchivedLog(thread=>1,
 sequence=>seq);
end loop
</PRE>
6. A typical scenario <PRE> A customer has backupsets consisting of:
o. an incremental level 0 database backup
o. an incremental level 2 database backup
o. archivelogs from the time of the level 2 backup to the current time
The target database and recovery catalog have been irretrievably lost.
</PRE><PRE> In this situation, the following steps should be followed (using the
above anonymous blocks):
1. Start the target instance (nomount)
2. Restore the latest controlfile, ideally from the same backupset as
 the last incremental to be restored (make further copies if necessary
 as per the init.ora)
3. Mount the database
4. Restore the datafiles from the level 0 backupset
5. Restore (apply) the datafiles from the level 2 backupset
6. Restore the archivelogs from the archivelog backupset
7. Using tradtional v7 recovery techniques, recover the database
 (until cancel using backup controlfile)
8. Open the database (resetlogs)
9. Rebuild the recovery catalog & re-register the target database
 10. Make backups of the target database and recovery catalog database
</PRE>
 8. Errors <PRE> 8.1 ORA-19615 & ORA-19613 when attempting to extract files
</PRE><PRE> Errorstack:
 ORA-19583: conversation terminated due to error
 ORA-19615: some files not found in backup set
 ORA-19613: datafile <file#> not found in backup set
 ORA-06512: at "SYS.X$DBMS_BACKUP_RESTORE", line 1043
 ORA-06512: at line 40
</PRE><PRE> The problem is that one or more backup pieces specified in the
v_fileTable table contain NO blocks for the datafile that you are
trying to extract.
</PRE><PRE> For example, I may have run an RMAN backup and allocated 2 channels to
backup the (4 datafile) database. This will create 2 backupsets.

 +- Backup piece 1a
 +- (Backupset 1) Datafiles 1,2 -+
 | +- Backup piece 2a
 Database -+
 | +- Backup piece 1b
 +- (Backupset 2) Datafiles 3,4 -+
 +- Backup piece 2b
</PRE><PRE> Although the backup pieces may contain blocks from all datafiles
associated with their backupset, they will not contain blocks from a
different backupset i.e. pieces 1a and 1b will NOT contain blocks from
datafiles 3 or 4.
</PRE><PRE> If I want to restore datafile 1, and include either backup pieces 1b or
2b in v_fileTable, I will get the errorstack above.
</PRE><PRE> This is why it is important to know what files are in what backupset.
The original RMAN backup log will help here.
</PRE>
 8. Things to be done <PRE> 8.1. Error handling
 If the procedures fail with an unhandled exception (quite likely, as
 no exception handlers have been set up), the allocated device does not
 get deallocated. This is unfriendly (the user must exit & restart the
 session) and will be addressed
</PRE>

pub!1c 2006-3-4 20:24

完全备份的SH文件：exp_comp.sh

rq=` date +"%m%d" `

su - oracle -c "exp system/manager full=y inctype=complete file=/oracle/export/db_comp$rq.dmp"

累计备份的SH文件：exp_cumu.sh

rq=` date +"%m%d" `

su - oracle -c "exp system/ manager full=y inctype=cumulative file=/oracle/export/db_cumu$rq.dmp"

增量备份的SH文件: exp_incr.sh

rq=` date +"%m%d" `

su - oracle -c "exp system/manager full=y inctype=incremental file=/oracle/export/db_incr$rq.dmp"

root用户crontab文件

/var/spool/cron/crontabs/root增加以下内容

0 2 1 * * /oracle/exp_comp.sh

30 2 * * 0-5 /oracle/exp_incr.sh

45 2 * * 6 /oracle/exp_cumu.sh

当然这个时间表可以根据不同的需求来改变的，这只是一个例子。

pub!1c 2006-4-4 12:54

Start with...Connect By子句递归查询一般用于一个表维护树形结构的应用。
创建示例表：
CREATE TABLE TBL_TEST
(
  ID NUMBER,
  NAME  VARCHAR2(100 BYTE),
  PID  NUMBER                      DEFAULT 0
);

插入测试数据：
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('1','10','0');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('2','11','1');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('3','20','0');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('4','12','1');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('5','121','2');

从Root往树末梢递归
select * from TBL_TEST
start with id=1
connect by prior id = pid

从末梢往树ROOT递归
select * from TBL_TEST
start with id=5
connect by prior pid = id

作者Blog：[url]http://blog.csdn.net/54powerman/[/url]

pub!1c 2006-5-16 22:44

1、第一种方式，直接resize tempfile , 虽然简单，但是很容易无效 <div style="SCROLLBAR-HIGHLIGHT-COLOR: buttonhighlight; OVERFLOW: auto; WIDTH: 500px"><pre style="BORDER-RIGHT: black 1px solid; PADDING-RIGHT: 4px; BORDER-TOP: black 1px solid; PADDING-LEFT: 4px; PADDING-BOTTOM: 4px; BORDER-LEFT: black 1px solid; PADDING-TOP: 4px; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ededed"><div id="3"><div><!--Code highlighting produced by Actipro CodeHighlighter (freeware)[url]http://www.CodeHighlighter.com/--[/url]>sys@mescp>select tablespace_name,file_name ,2file_id,bytes/1024/1024as "size(M)"3from dba_temp_files4/TABLESPACE_NAME FILE_NAMEFILE_ID size(M)-------------------- ---------------------------------- ---------- ----------TEMP/orabin/oradata/mescp/temp01.dbf 11024TEMP/orabin/oradata/mescp/tmp1_01.dbf 240962 rows selected.sys@mescp>alterdatabase tempfile 1 resize 16M;alterdatabase tempfile 1 resize 16M*ERROR at line 1:ORA-03297: filecontains used data beyond requested RESIZE value-- 失败了</div></div></pre></div><div id="4"> 　　2、第二种，建立另一个临时表空间，并把它设为default temporary tablespace,再删除原来的temporary tablespace以及tempfile。 　　需要切换，复杂了点…… 　　3、我提供的方式 </div><div style="SCROLLBAR-HIGHLIGHT-COLOR: buttonhighlight; OVERFLOW: auto; WIDTH: 500px"><pre style="BORDER-RIGHT: black 1px solid; PADDING-RIGHT: 4px; BORDER-TOP: black 1px solid; PADDING-LEFT: 4px; PADDING-BOTTOM: 4px; BORDER-LEFT: black 1px solid; PADDING-TOP: 4px; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ededed"><div id="5"><div><!--Code highlighting produced by Actipro CodeHighlighter (freeware)[url]http://www.CodeHighlighter.com/--[/url]>sys@mescp>alterdatabase tempfile 1drop ;Database altered.sys@mescp>alter tablespace tempadd tempfile2'/orabin/oradata/mescp/temp01.dbf'3 size 16M reuse autoextend onnext 16M maxsize 4096M;Tablespace altered.sys@mescp>alterdatabase tempfile 2drop ;Database altered.sys@mescp>alter tablespace tempadd tempfile2'/orabin/oradata/mescp/tmp1_01.dbf'3sys@mescp>3 size 16M reuse autoextend onnext 16M maxsize 4096M;sys@mescp>/Tablespace altered.sys@mescp>select tablespace_name,file_name ,2file_id,bytes/1024/1024as "size(M)"3from dba_temp_files4/TABLESPACE_NAME FILE_NAMEFILE_ID size(M)-------------------- ---------------------------------- ---------- ----------TEMP/orabin/oradata/mescp/temp01.dbf 116TEMP/orabin/oradata/mescp/tmp1_01.dbf 2162 rows selected.</div></div></pre></div><div id="6"> 　　无需重新建立temporary tablespace,无需修改database的default temporary tablespace</div>

pub!1c 2006-5-23 23:38

论坛上经常有人问,如何给导入文件加上时间戳标记.在这里把我曾经给过的两种方法总结一下.

　　1.可以在DOS下从系统获得时间戳

　　利用Dos命令取得时间戳:

　　C:\>echo %date%

　　2006-04-24 星期一

　　C:\>echo %date:~0,10%

　　2006-04-24

　　接下来使用导出(exp)工具引用该时间戳就很容易了:

　　exp userid=eygle/eygle file=d:\eygle%date:~0,10%.dmp log=d:\eygle%date:~0,10%.log

　　2.使用sql脚本从数据库获得时间戳

　　有朋友反应在NT下该命令执行结果与Windows2000和WindowsXp上不同,没有NT的环境可以试验(据网友说用""把date引用起来就可以了,如: "%date%",我没有测试过).

　　我们也可以使用如下的SQL方式获得时间戳.

　　首先编写一个doexp.sql脚本:

　　column today new_val dt

　　select to_char( sysdate, 'ddmmyyyy' ) today from dual;

　　host exp userid=eygle/eygle tables=emp file=exp_prod_&dt..exp log=exp_prod_&dt..log

　　exit

　　再编写一个start.bat批处理脚本调用doexp.sql脚本:

　　echo off

　　set oracle_sid=eygle

　　sqlplus eygle/eygle @doexp

　　执行结果如下:

　　E:\Itpub\ItpubTrain\Exp2>dir

　　驱动器 E 中的卷是 SWAPDK

　　卷的序列号是 F8BA-FCAC

　　E:\Itpub\ItpubTrain\Exp2 的目录

　　2003-10-07 15:14

.

　　2003-10-07 15:14

..

　　2003-08-26 15:18 170 doexp.sql

　　2003-10-07 15:12 4,096 exp_prod_07102003.exp

　　2003-10-07 15:12 335 exp_prod_07102003.log

　　2003-08-26 15:18 4,096 exp_prod_26082003.exp

　　2003-08-26 15:18 312 exp_prod_26082003.log

　　2003-10-07 15:42 4,096 exp_prod_30082003.exp

　　2003-08-30 17:09 335 exp_prod_30082003.log

　　2003-10-07 15:14 1,660 sqlnet.log

　　2003-10-07 15:12 60 start.bat

　　9 个文件 15,160 字节

　　2 个目录 674,652,160 可用字节

页: [1]

邪恶八进制信息安全团队技术讨论组's Archiver

[TIPS]Some Teach Topics about Oracle