分类目录归档：数据库

使用dbms_backup_restore包恢复数据库

发表于 2011 年 12 月 19 日 由 惜分飞

Oracle提供了一个包:DBMS_BACKUP_RESTORE包是由dbmsbkrs.sql 和 prvtbkrs.plb 这两个脚本创建的.catproc.sql 脚本运行后会调用这两个包.所以是每个数据库都有的这个包是Oracle服务器和操作系统之间IO操作的接口.由恢复管理器直接调用。而且据说这两个脚本的功能是内建到Oracle的一些库文件中的.
由此可见，我们可以在数据库 nomount 情况下调用这些package ,来达到我们的恢复目的
1、启动数据库到nomount状态

[oracle@node1 ora11g]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Mon Dec 19 13:34:22 2011

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area 2137886720 bytes
Fixed Size                  2230072 bytes
Variable Size            1493174472 bytes
Database Buffers          637534208 bytes
Redo Buffers                4947968 bytes

2、恢复controlfile

SQL> DECLARE
  2  devtype varchar2(256);
  3  done boolean;
  4  BEGIN
  5  devtype:=sys.dbms_backup_restore.deviceAllocate (type=>'',ident=>'t1');
  6  sys.dbms_backup_restore.restoreSetDatafile;
  7  sys.dbms_backup_restore.restoreControlfileTo(cfname=>'/opt/oracle/oradata/ora11g/control01.ctl');
  8  sys.dbms_backup_restore.restoreBackupPiece(done=>done,handle=>'/tmp/ora11g_02mu1avd_1_1.rman', params=>null);
  9  sys.dbms_backup_restore.deviceDeallocate;
 10  END;
 11  /

PL/SQL procedure successfully completed.

3、恢复数据文件

SQL> DECLARE
  2  devtype varchar2(256);
  3  done boolean;
  4  BEGIN
  5  devtype:=sys.dbms_backup_restore.deviceAllocate (type=>'',ident=>'t1');
  6  sys.dbms_backup_restore.restoreSetDatafile;
  7  sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>1,toname=>'/opt/oracle/oradata/ora11g/system01.dbf');
sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>2,toname=>'/opt/oracle/oradata/ora11g/sysaux01.dbf');
sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>3,toname=>'/opt/oracle/oradata/ora11g/undotbs01.dbf');
  8    9   10  sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>4,toname=>'/opt/oracle/oradata/ora11g/users01.dbf');
 11  sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>5,toname=>'/opt/oracle/oradata/ora11g/example01.dbf');
 12  sys.dbms_backup_restore.restoreBackupPiece(done=>done,handle=>'/tmp/ora11g_01mu1aqq_1_1.rman', params=>null);
 13  sys.dbms_backup_restore.deviceDeallocate;
 14  END;
 15  /

PL/SQL procedure successfully completed.

4、启动数据库

SQL> alter database mount;
SQL> alter database recover until cancel using backup controlfile;
SQL> alter database open resetlogs;

5、特别说明
在oracle 10g及其以后版本中，因为rman中有catalog with start命令，可以实现rman备份的加载，所以不需要使用dbms_backup_restore包处理，在oracle 9i及其以前版本中，可能因为没有catalog库，控制文件中又没有了备份集信息，需要采用这种方法处理数据文件还原，然后根据实际情况，使用ALTER DATABASE REGISTER LOGFILE 添加日志，进行恢复

发表在 rman备份/恢复 | 2 条评论

Memory Notification: Library Cache Object loaded into SGA

发表于 2011 年 12 月 18 日 由 惜分飞

0.环境

--系统环境
[oracle@bas bdump]$ more /etc/redhat-release 
Red Hat Enterprise Linux AS release 4 (Nahant Update 7)

--数据库版本
SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

1.alert日志信息

Sun Dec 18 02:03:38 2011
Memory Notification: Library Cache Object loaded into SGA
Heap size 7607K exceeds notification threshold (2048K)
Details in trace file /opt/app/oracle/admin/BAS/udump/bas_ora_29900.trc

2.bas_ora_29900.trc文件信息

[oracle@bas bdump]$ more  /opt/app/oracle/admin/BAS/udump/bas_ora_29900.trc
/opt/app/oracle/admin/BAS/udump/bas_ora_29900.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
ORACLE_HOME = /opt/app/oracle/product/10.2.0/db_1
System name:    Linux
Node name:      bas
Release:        2.6.9-78.ELsmp
Version:        #1 SMP Wed Jul 9 15:46:26 EDT 2008
Machine:        x86_64
Instance name: BAS
Redo thread mounted by this instance: 1
Oracle process number: 34
Unix process pid: 29900, image: oracle@bas (TNS V1-V3)

*** 2011-12-18 02:03:35.244
*** SERVICE NAME:(SYS$USERS) 2011-12-18 02:03:35.229
*** SESSION ID:(5465.1) 2011-12-18 02:03:35.229
Memory Notification: Library Cache Object loaded into SGA
Heap size 5249K exceeds notification threshold (2048K)
LIBRARY OBJECT HANDLE: handle=25d38a9d8 mutex=0x25d38ab08(0)

3.警告原因

These are warning messages that should not cause the program responsible for these errors to fail.  They appear as a result of new event messaging mechanism and memory manager in 10g Release 2.
The meaning is that the process is just spending a lot of time in finding free memory extents during an allocate as the memory may be heavily fragmented.  Fragmentation in memory is impossible to eliminate completely, however, continued messages of large allocations in memory indicate there are tuning opportunities on the application.  
The messages do not imply that an ORA-4031 is about to happen.

从这里可以看出来，这个只是分配大的内存块(超过_kgl_large_heap_warning_threshold参数值)的一个警告信息，不会对系统的性能以及ORA-4031产生什么影响，如果不是很在意这个警告，可以忽略

4.解决方法

In 10g we have a new undocumented parameter that sets the KGL heap size warning threshold.   This parameter was not present in 10gR1.  Warnings are written if heap size exceeds this threshold.
    
Set  _kgl_large_heap_warning_threshold  to a reasonable high value or zero to prevent these warning messages. Value needs to be set in bytes. 

If you want to set this to 8192 (8192 * 1024) and are using an spfile: 

(logged in as "/ as sysdba")

SQL> alter system set "_kgl_large_heap_warning_threshold"=8388608 scope=spfile ; 

SQL> shutdown immediate 
SQL> startup 

If using an "old-style" init parameter, 

Edit the init parameter file and add 

_kgl_large_heap_warning_threshold=8388608

NOTE:  
1)The default threshold in 10.2.0.1 is 2M.   So these messages could show up frequently in some application environments.
2)In 10.2.0.2,  the threshold was increased to 50MB after regression tests, so this should be a reasonable and recommended value.

参考MOS:330239.1

发表在 Oracle | 一条评论

INSTEAD OF触发器实现视图DML操作

发表于 2011 年 12 月 18 日 由 惜分飞

有网友询问了，一个多表关联视图，怎么实现dml操作，其实这个可以使用INSTEAD OF触发器实现
1、准备实验环境
创建两个表和一个视图(两表union关联)，并各自插入一条模拟数据

C:\Users\XIFENFEI>sqlplus chf/xifenfei

SQL*Plus: Release 11.2.0.1.0 Production on 星期日 12月 18 10:57:05 2011

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options

SQL> CREATE TABLE XFF_T1 (ID NUMBER PRIMARY KEY, NAME VARCHAR2(30));

表已创建。

SQL> CREATE TABLE XFF_T2 (ID NUMBER PRIMARY KEY, NAME VARCHAR2(30));

表已创建。

SQL> CREATE VIEW V_XFF_T AS SELECT * FROM XFF_T1 UNION ALL SELECT * FROM XFF_T2;


视图已创建。

SQL> INSERT INTO XFF_T1 VALUES (1, 'XFF_T1');

已创建 1 行。

SQL> INSERT INTO XFF_T2 VALUES (2, 'XFF_T2');

已创建 1 行。

SQL> COMMIT;

提交完成。

SQL> SELECT * FROM V_XFF_T;

        ID NAME                           
---------- ------------------------------ 
         1 XFF_T1                         
         2 XFF_T2

2、尝试dml操作视图
插入、删除、更新dml操作全部失败

SQL> INSERT INTO V_XFF_T VALUES (3, 'V_XFF_T', );
INSERT INTO V_XFF_T VALUES (3, 'V_XFF_T')
            *
第 1 行出现错误:
ORA-01732: 此视图的数据操纵操作非法


SQL> DELETE FROM V_XFF_T WHERE ID=2;
DELETE FROM V_XFF_T WHERE ID=2
            *
第 1 行出现错误:
ORA-01732: 此视图的数据操纵操作非法


SQL> UPDATE V_XFF_T SET NAME='XIFENFEI' WHERE ID=3;
UPDATE V_XFF_T SET NAME='XIFENFEI' WHERE ID=2
       *
第 1 行出现错误:
ORA-01732: 此视图的数据操纵操作非法

3、创建INSTEAD OF触发器

SQL> CREATE OR REPLACE TRIGGER INSTEADOF_T
  2  INSTEAD OF INSERT OR UPDATE OR DELETE ON V_XFF_T
  3  FOR EACH ROW
  4  BEGIN
  5  IF INSERTING THEN
  6  INSERT INTO XFF_T2 VALUES (:NEW.ID, :NEW.NAME);
  7  ELSIF UPDATING THEN
  8  UPDATE XFF_T2 SET ID = :NEW.ID, NAME = :NEW.NAME
  9  WHERE ID = :OLD.ID;
 10  ELSIF DELETING THEN
 11  DELETE XFF_T2 WHERE ID = :OLD.ID;
 12  END IF;
 13  END;
 14  /

触发器已创建

4、重试dml操作
使用基于INSTEAD OF触发器实现了对复杂视图的dml操作

SQL> INSERT INTO V_XFF_T VALUES (3, 'V_XFF_T');

已创建 1 行。

SQL> SELECT * FROM V_XFF_T;

        ID NAME                           
---------- ------------------------------ 
         1 XFF_T1                         
         2 XFF_T2                         
         3 V_XFF_T                        

SQL> DELETE FROM V_XFF_T WHERE ID=2;

已删除 1 行。

SQL> SELECT * FROM V_XFF_T;

        ID NAME                           
---------- ------------------------------ 
         1 XFF_T1                         
         3 V_XFF_T                        

SQL> UPDATE V_XFF_T SET NAME='XIFENFEI' WHERE ID=3;

已更新 1 行。

SQL> SELECT * FROM V_XFF_T;

        ID NAME                           
---------- ----------------------------- 
         1 XFF_T1                         
         3 XIFENFEI    

SQL> COMMIT;

提交完成。

5、查询基表情况
因为编写的INSTEAD OF触发器是对XFF_T2表作用，所以所有关于该视图的操作，都映射到XFF_T2表中

SQL> SELECT * FROM XFF_T1;

        ID NAME                           
---------- ------------------------------ 
         1 XFF_T1                         

SQL> SELECT * FROM XFF_T2;

        ID NAME                           
---------- ------------------------------ 
         3 XIFENFEI
发表在 Oracle 开发 | 评论关闭