使用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 条评论

Linux中文件大小限制

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

在日常的维护中，偶尔总担心某个文件会不会因为太大(超过系统限制大小)，导致工作不正常。查找了一些资料，这里对文件大小限制的情况做个记录，以便以后做到心中有底
一、文件大小限制列表

二、查看数据块大小
1、直接查看操作磁盘(Block size)

[root@bas ~]# tune2fs -l /dev/sda2
tune2fs 1.35 (28-Feb-2004)
Filesystem volume name:   /home
…………
Block size:               4096
Fragment size:            4096
…………
Journal backup:           inode blocks

2、查看某个文件(IO Block)

[oracle@bas backup]$ stat full_back.dmp 
  File: `full_back.dmp'
  Size: 429687808000    Blocks: 840054384  IO Block: 4096   regular file
Device: 812h/2066d      Inode: 13          Links: 1
Access: (0644/-rw-r--r--)  Uid: (  501/  oracle)   Gid: (  501/oinstall)
Access: 2011-12-18 02:00:25.000000000 +0800
Modify: 2011-12-18 20:49:31.000000000 +0800
Change: 2011-12-18 20:49:31.000000000 +0800
发表在 Linux | 一条评论

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 | 一条评论