Oracle 小升级 opatch apply使用

这里记录的是Oracle 升级到10.2.0.4后,出现EM不能正常启动情况下,Oracle提示需要打上Patch 8350262,下面是记录打补丁过程

[oracle@ocp ~]$ unzip ~/p8350262_10204_Generic.zip
Archive:  /home/oracle/p8350262_10204_Generic.zip
   creating: 8350262/
  inflating: 8350262/killDBConsole
   creating: 8350262/files/
   creating: 8350262/files/sysman/
   creating: 8350262/files/sysman/jlib/
   creating: 8350262/files/sysman/jlib/emCORE.jar/
   creating: 8350262/files/sysman/jlib/emCORE.jar/oracle/
   creating: 8350262/files/sysman/jlib/emCORE.jar/oracle/sysman/
   creating: 8350262/files/sysman/jlib/emCORE.jar/oracle/sysman/eml/
   creating: 8350262/files/sysman/jlib/emCORE.jar/oracle/sysman/eml/sec/
   creating: 8350262/files/sysman/jlib/emCORE.jar/oracle/sysman/eml/sec/fsc/
  inflating: 8350262/files/sysman/jlib/emCORE.jar/oracle/sysman/eml/sec/fsc/FSWalletUtil.class
   creating: 8350262/files/sysman/jlib/emCORE.jar/oracle/sysman/eml/sec/util/
  inflating: 8350262/files/sysman/jlib/emCORE.jar/oracle/sysman/eml/sec/util/RootCert.class
  inflating: 8350262/files/sysman/jlib/emCORE.jar/oracle/sysman/eml/sec/util/SecConstants.class
   creating: 8350262/files/sysman/jlib/emCORE.jar/oracle/sysman/eml/sec/rep/
  inflating: 8350262/files/sysman/jlib/emCORE.jar/oracle/sysman/eml/sec/rep/RepWalletUtil.class
   creating: 8350262/files/sysman/jlib/emd_java.jar/
   creating: 8350262/files/sysman/jlib/emd_java.jar/oracle/
   creating: 8350262/files/sysman/jlib/emd_java.jar/oracle/sysman/
   creating: 8350262/files/sysman/jlib/emd_java.jar/oracle/sysman/eml/
   creating: 8350262/files/sysman/jlib/emd_java.jar/oracle/sysman/eml/sec/
   creating: 8350262/files/sysman/jlib/emd_java.jar/oracle/sysman/eml/sec/fsc/
  inflating: 8350262/files/sysman/jlib/emd_java.jar/oracle/sysman/eml/sec/fsc/FSWalletUtil.class
   creating: 8350262/files/sysman/jlib/emd_java.jar/oracle/sysman/eml/sec/util/
  inflating: 8350262/files/sysman/jlib/emd_java.jar/oracle/sysman/eml/sec/util/RootCert.class
  inflating: 8350262/files/sysman/jlib/emd_java.jar/oracle/sysman/eml/sec/util/SecConstants.class
   creating: 8350262/files/sysman/jlib/emd_java.jar/oracle/sysman/eml/sec/rep/
  inflating: 8350262/files/sysman/jlib/emd_java.jar/oracle/sysman/eml/sec/rep/RepWalletUtil.class
  inflating: 8350262/killDBConsole.pl
  inflating: 8350262/README.txt
   creating: 8350262/etc/
   creating: 8350262/etc/xml/
  inflating: 8350262/etc/xml/ShiphomeDirectoryStructure.xml
  inflating: 8350262/etc/xml/GenericActions.xml
   creating: 8350262/etc/config/
  inflating: 8350262/etc/config/inventory
  inflating: 8350262/etc/config/actions
[oracle@ocp ~]cd 8350262
[oracle@ocp 8350262]$ $ORACLE_HOME/OPatch/opatch apply     --Note:$ORACLE_HOME/OPatch/opatch路径
Invoking OPatch 10.2.0.4.2
Oracle Interim Patch Installer version 10.2.0.4.2
Copyright (c) 2007, Oracle Corporation.  All rights reserved.
Oracle Home       : /opt/oracle/app/10.2.0/db_1
Central Inventory : /opt/oracle/oraInventory
   from           : /etc/oraInst.loc
OPatch version    : 10.2.0.4.2
OUI version       : 10.2.0.4.0
OUI location      : /opt/oracle/app/10.2.0/db_1/oui
Log file location : /opt/oracle/app/10.2.0/db_1/cfgtoollogs/opatch/opatch2011-10-17_18-11-40PM.log
ApplySession applying interim patch '8350262' to OH '/opt/oracle/app/10.2.0/db_1'
Running prerequisite checks...
OPatch detected non-cluster Oracle Home from the inventory and will patch the local system only.
Backing up files and inventory (not for auto-rollback) for the Oracle Home
Backing up files affected by the patch '8350262' for restore. This might take a while...
Backing up files affected by the patch '8350262' for rollback. This might take a while...
Patching component oracle.sysman.agent.core, 10.2.0.4.0a...
Updating jar file "/opt/oracle/app/10.2.0/db_1/sysman/jlib/emCORE.jar" with "/sysman/jlib/emCORE.jar/oracle/sysman/eml/sec/fsc/FSWalletUtil.class"
Updating jar file "/opt/oracle/app/10.2.0/db_1/sysman/jlib/emCORE.jar" with "/sysman/jlib/emCORE.jar/oracle/sysman/eml/sec/rep/RepWalletUtil.class"
Updating jar file "/opt/oracle/app/10.2.0/db_1/sysman/jlib/emCORE.jar" with "/sysman/jlib/emCORE.jar/oracle/sysman/eml/sec/util/RootCert.class"
Updating jar file "/opt/oracle/app/10.2.0/db_1/sysman/jlib/emCORE.jar" with "/sysman/jlib/emCORE.jar/oracle/sysman/eml/sec/util/SecConstants.class"
Updating jar file "/opt/oracle/app/10.2.0/db_1/sysman/jlib/emd_java.jar" with "/sysman/jlib/emd_java.jar/oracle/sysman/eml/sec/fsc/FSWalletUtil.class"
Updating jar file "/opt/oracle/app/10.2.0/db_1/sysman/jlib/emd_java.jar" with "/sysman/jlib/emd_java.jar/oracle/sysman/eml/sec/rep/RepWalletUtil.class"
Updating jar file "/opt/oracle/app/10.2.0/db_1/sysman/jlib/emd_java.jar" with "/sysman/jlib/emd_java.jar/oracle/sysman/eml/sec/util/RootCert.class"
Updating jar file "/opt/oracle/app/10.2.0/db_1/sysman/jlib/emd_java.jar" with "/sysman/jlib/emd_java.jar/oracle/sysman/eml/sec/util/SecConstants.class"
ApplySession adding interim patch '8350262' to inventory
Verifying the update...
Inventory check OK: Patch ID 8350262 is registered in Oracle Home inventory with proper meta-data.
Files check OK: Files from Patch ID 8350262 are present in Oracle Home.
OPatch succeeded.
[oracle@ocp 8350262]$ ../opatch lsinventory  --Note检测是否安装成功
Invoking OPatch 10.2.0.4.2
Oracle Interim Patch Installer version 10.2.0.4.2
Copyright (c) 2007, Oracle Corporation.  All rights reserved.
Oracle Home       : /opt/oracle/app/10.2.0/db_1
Central Inventory : /opt/oracle/oraInventory
   from           : /etc/oraInst.loc
OPatch version    : 10.2.0.4.2
OUI version       : 10.2.0.4.0
OUI location      : /opt/oracle/app/10.2.0/db_1/oui
Log file location : /opt/oracle/app/10.2.0/db_1/cfgtoollogs/opatch/opatch2011-10-17_18-18-34PM.log
Lsinventory Output file location : /opt/oracle/app/10.2.0/db_1/cfgtoollogs/opatch/lsinv/lsinventory2011-10-17_18-18-34PM.txt
--------------------------------------------------------------------------------
Installed Top-level Products (2):
Oracle Database 10g                                                  10.2.0.1.0
Oracle Database 10g Release 2 Patch Set 3                            10.2.0.4.0
There are 2 products installed in this Oracle Home.
Interim patches (1) :
Patch  8350262      : applied on Mon Oct 17 18:17:28 CST 2011
   Created on 14 Sep 2010, 04:59:44 hrs PST8PDT
   Bugs fixed:
     8350262
--------------------------------------------------------------------------------
OPatch succeeded.

p8350262_10204_Generic
p8350262_10205_Generic

checking network configuration requirements

最近准备考ocp,所以决定按照教程,从头到尾系统的学习一次oracle,也算是为了对得起考证的几千大洋。
在安装oracle 10g的过程中,遇到了checking network configuration requirements is: Not executed的错误提示。

没有执行网络检测,google下,发现是/etc/hosts中没有指定静态ip地址导致,检测/etc/hosts发现果真如此(好久没有装oracle 了,竟然忘记这个基础的东西)修改如下配置
127.0.0.1 ocp localhost.localdomain localhost
192.168.1.55 ocp.xifenfei.com
重新检测,显示正常

rac中的spfile探讨

今天朋友的的rac,因为被同事做数据库升级,分别在两个节点的本地创建了spfile,然后使用这个spfile启动了数据库,因为他不是非常懂oracle,所以向我求救,我改他的建议是:
1、利用备份的原来的pfile文件创建在asm中的spfile,规则是:+ASM/SID/spfileSID
2、dbs目录下创建一个本地的initsid.ora,然后在里面加一个spfile=’+ASM pfile path’(两个节点同样操作,注意sid不同)
3、分别重启数据库
出现该问题的原因分析:
做数据库升级的朋友的同事也不懂rac的spfile的相关规则,应该是在重启数据库的时候,提示spfile不存在,然后自己手工创建利用pfile创建的spfile到dbs下面,然后朋友十一后检测数据库,发现spfile都放置在本地了。
1、通常读取参数文件顺序
我们知道,如果不指定参数文件,oracle是按照这个顺序查找文件来启动数据库的:
spfileSID.ora
spfile.ora
initSID.ora
init.ora
如果这些文件都没有找到,启动会失败。

2、RAC中关于spfile的启动探讨

[rac@cent1 dbs]$ echo $ORACLE_SID
RACDB1
[rac@cent1 dbs]$ touch spfileRACDB1.ora  <==手工创建一个空白的spfile
[rac@cent1 dbs]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 - Production on Thu Apr 29 13:45:50 2010
Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORA-27091: unable to queue I/O  <== 用sqlplus启动数据库时会报错
ORA-27069: attempt to do I/O beyond the range of the file
Additional information: 1
Additional information: 1
SQL>
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
[rac@cent1 dbs]$ crs_stat -t
Name           Type           Target    State     Host
------------------------------------------------------------
ora....B1.inst application    OFFLINE   OFFLINE
ora....B2.inst application    ONLINE    ONLINE    cent2
ora.RACDB.db   application    ONLINE    ONLINE    cent1
ora....SM1.asm application    ONLINE    ONLINE    cent1
ora....T1.lsnr application    ONLINE    ONLINE    cent1
ora.cent1.gsd  application    ONLINE    ONLINE    cent1
ora.cent1.ons  application    ONLINE    ONLINE    cent1
ora.cent1.vip  application    ONLINE    ONLINE    cent1
ora....SM2.asm application    ONLINE    ONLINE    cent2
ora....T2.lsnr application    ONLINE    ONLINE    cent2
ora.cent2.gsd  application    ONLINE    ONLINE    cent2
ora.cent2.ons  application    ONLINE    ONLINE    cent2
ora.cent2.vip  application    ONLINE    ONLINE    cent2
[rac@cent1 dbs]$ srvctl start instance -i racdb1 -d racdb  <== 用srvctl启动成功
[rac@cent1 dbs]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 - Production on Thu Apr 29 13:47:25 2010
Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
SQL> select instance_name, status from v$instance;
INSTANCE_NAME    STATUS
---------------- ------------
RACDB1           OPEN
--说明srvctl不是用那个顺序去查找参数文件

3、查看srvctl读取spfile位置

[rac@cent1 dbs]$ srvctl config database -d racdb -a
cent1 RACDB1 /rac/product/10.2.0/db
cent2 RACDB2 /rac/product/10.2.0/db
DB_NAME: RACDB
ORACLE_HOME: /rac/product/10.2.0/db
SPFILE: +DATA/RACDB/spfileRACDB.ora
DOMAIN: WORLD
DB_ROLE: null
START_OPTIONS: null
POLICY:  AUTOMATIC
ENABLE FLAG: DB ENABLED

4、修改CRS中关于spfile位置

[rac@cent1 dbs]$ srvctl modify database -d racdb -p ' +DATA/RACDB/spfileRACDB1.ora'

利用rowid排序更新大表数据

现在有个案例,有一张很大的表t1,需要更新其中的一个列,然后这个列是参考另外一个表t2中的列
直接sql操作如:
update t1 ta set prov_code=(select area_code from t2 tb where ta.id=tb.id);
利用rowid,分批提交的思路写出如下sql,执行大表更新效率比较高

DECLARE
  CURSOR cur IS
    SELECT
     a.area_code, b.ROWID ROW_ID
      FROM t2 a, t1 b
     WHERE a.id = b.id
     ORDER BY b.ROWID;
  V_COUNTER NUMBER;
BEGIN
  V_COUNTER := 0;
  FOR row IN cur LOOP
    UPDATE t1
       SET prov_code = row.area_code
     WHERE ROWID = row.ROW_ID;
    V_COUNTER := V_COUNTER + 1;
    IF (V_COUNTER >= 1000) THEN
      COMMIT;
      V_COUNTER := 0;
    END IF;
  END LOOP;
  COMMIT;
END;

这里利用了rowid排序,使得update操作是一个一个数据块的进行,减少逻辑读,分批提交减小undo压力

Startup Migrate

1、为什么要使用Startup Migrate
STARTUP MIGRATE was introduced in 9.2 as a mechanism to be sure that most everything that needs to be done to run an upgrade script or a patch script is done automatically. In the past, customers were expected to adjust certain initialization parameters prior to beginning an upgrade or applying a a patch, but most of this is now done automatically by STARTUP MIGRATE. When a customer starts a database in MIGRATE mode, the following ALTER SYSTEM commands will be set automatically:
ALTER SYSTEM ENABLE RESTRICTED SESSION;
ALTER SYSTEM SET “_SYSTEM_TRIG_ENABLED”=FALSE SCOPE=MEMORY;
ALTER SYSTEM SET _undo_autotune=FALSE SCOPE=MEMORY;
ALTER SYSTEM SET undo_retention=900 SCOPE=MEMORY;
ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0 SCOPE=MEMORY;
ALTER SYSTEM SET AQ_TM_PROCESSES=0 SCOPE=MEMORY;
这些我们可以从数据库的Startup Migrate命令启动数据库的日志中可以看出来

Sun Oct  9 21:53:04 2011
ALTER SYSTEM enable restricted session;
Sun Oct  9 21:53:04 2011
ALTER SYSTEM SET _system_trig_enabled=FALSE SCOPE=MEMORY;
Autotune of undo retention is turned off.
Sun Oct  9 21:53:04 2011
ALTER SYSTEM SET _undo_autotune=FALSE SCOPE=MEMORY;
Sun Oct  9 21:53:04 2011
ALTER SYSTEM SET undo_retention=900 SCOPE=MEMORY;
MMNL started with pid=12, OS id=8452
Sun Oct  9 21:53:04 2011
ALTER SYSTEM SET aq_tm_processes=0 SCOPE=MEMORY;
Sun Oct  9 21:53:04 2011
Resource Manager disabled during database migration: plan '' not set
Sun Oct  9 21:53:04 2011
ALTER SYSTEM SET resource_manager_plan='' SCOPE=MEMORY;
replication_dependency_tracking turned off (no async multimaster replication found)
Completed: ALTER DATABASE OPEN MIGRATE

2、Startup Migrate主用作用
在9i,无论升级/降级 数据库都是startup migrate
10g后增加了upgrade参数,升级可直接用startup upgrade,降级仍是startup migrate
3、Startup Migrate辅助作用(解决部分ORA-00701)

SQL> alter index I_H_OBJ#_COL# rebuild;
alter index I_H_OBJ#_COL# rebuild
*
ERROR at line 1:
ORA-00701: object necessary for warmstarting database cannot be altered
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup migrate;
ORACLE instance started.
Total System Global Area 139531744 bytes
Fixed Size 452064 bytes
Variable Size 121634816 bytes
Database Buffers 16777216 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.
SQL> alter index I_H_OBJ#_COL# rebuild;
Index altered.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 139531744 bytes
Fixed Size 452064 bytes
Variable Size 121634816 bytes
Database Buffers 16777216 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.

sqlplus 使用小技巧

1、sqlplus显示语言设置
export NLS_LANG=”AMERICAN_AMERICA.ZHS16GBK”
export NLS_LANG=”SIMPLIFIED CHINESE_CHINA.ZHS16GBK”
注意数据库编码:ZHS16GBK/UTF8
2、dbms_output.put_line显示最前面空格
SQL> set serveroutput on
SQL> exec dbms_output.put_line(‘     abc’);
abc
PL/SQL procedure successfully completed.
SQL> set serveroutput on format wrapped
SQL> exec dbms_output.put_line(‘   abc’);
   abc
PL/SQL procedure successfully completed.
3、sqlplus 语句中间查询对象结构
SQL> select owner
2 # desc input
Name Null? Type
—————————————– ——– —————————-
OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(30)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(19)
CREATED DATE
LAST_DDL_TIME DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
2 ,status from input where rownum<10;
no rows selected
下一行以#开头, 就可以执行一条sql*plus命令, 执行完后, 刚才的语句可以继续输入
4、Sql*plus中sql语句中间有空行
SQL> select owner
2
SQL> ,status from input where rownum<10; SP2-0734: unknown command beginning “,status fr…” – rest of line ignored. 原因是sqlplus遇到空行就认为是语句结束了. 其实要改变这种现象, 只要使用SQLBLANKLINES参数就可以了 SQL> SET SQLBLANKLINES ON
SQL> select owner
2
3 ,status from input where rownum<10;
no rows selected

ORA-01465: invalid hex number

网友反馈blob插入一些字段报错,查询metalink,发现使用rawtohex处理即可,测试如下:

SQL> create table t_lob (t blob);
Table created.
SQL> insert into t_lob values('-------');
insert into t_lob values('-------')
                         *
ERROR at line 1:
ORA-01465: invalid hex number
SQL> insert into t_lob values(rawtohex('---------'));
1 row created.
SQL> insert into t_lob values('----------&');
insert into t_lob values('----------&')
                         *
ERROR at line 1:
ORA-01465: invalid hex number
SQL> insert into t_lob values(rawtohex('----------&'));
1 row created.
SQL> commit;
Commit complete.

网友提供java处理代码如下:

///插入
public void test()
	{
		conn=DBUtil.getActiveConnection();
		String sqlStr="Set define off";
		String ss="————";
		String sql="insert into test1(names,btestname) values('12',rawtohex('"+ss+"'))";
		System.out.println(sqlStr);
		System.out.println(sql);
		try {
			pstmt=conn.prepareStatement(sqlStr);
			pstmt.addBatch();
			pstmt=conn.prepareStatement(sql);
			pstmt.addBatch();
			pstmt.executeBatch();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally
		{
			DBUtil.closeStatement(pstmt);
			DBUtil.closeConnection(conn);
		}
	}
////查询语句
public void getTest()
	{
		conn=DBUtil.getActiveConnection();
		String sql="select * from test1 where names='12'";
		//oracle.sql.BLOB blob = null;
		InputStream inStream=null;
		try {
			pstmt=conn.prepareStatement(sql);
			set=pstmt.executeQuery();
			if(set!=null && set.next())
			{
				//接收blob类型
				java.sql.Blob blob = (oracle.sql.BLOB)set.getBlob("btestname");
				//注意
				inStream = blob.getBinaryStream();
		        if(blob!=null) System.out.println("有值============");
				try {
					byte[] data = new byte[200];
					 int length=0;//每次读取的实际字节长度
						//is.read()方法:从buff缓中区的第0个位开始读取字节,每次最多读取200,
						//方法会返回一个实际读取的长度,用length接收,当值为-1时,表示所有的字节全部读取完毕
					 while((length=inStream.read(data,0,200))!=-1)
					{
						//把字节以平台的默认编码方式转为字符,从buff的第0个位开始转换,实际要转换的长度是length
						String str=new String(data,0,length);
						System.out.println("最终结果====  "+str+"  ====");
					}
					//关闭流
					inStream.close();
				} catch (IOException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally
		{
			DBUtil.closeResultSet(set);
			DBUtil.closeStatement(pstmt);
			DBUtil.closeConnection(conn);
		}
	}

SCN与Oracle数据库恢复的关系–补充

一、为什么需要System checkpoint SCN号与Datafile Checkpoint SCN号
1.对只读表空间,其数据文件的Datafile Checkpoint SCN、Start SCN和END SCN号均相同。这三个SCN在表空间处于只读期间都将被冻结。
2.如果控制文件不是当前的控制文件,则System checkpoint会小于Start SCN或END SCN号。记录这些SCN号,可以区分控制文件是否是当前的控制文件。
Recovery database using backup controlfile
当有一个Start SCN号超过了System Checkpoit SCN号时,则说明控制文件不是当前的控制文件,因此在做recovery时需要采用using backup controlfile。这是为什么需要记录SystemCheckpoint SCN的原因之一。
二、重建控制文件,重建方式分两种(resetlogs和noresetlogs)
1.使用resetlogs选项时,System Checkpoint SCN为被归为0,而其中记录的各个数据文件的Datafile Checkpoint SCN则来自于Start SCN(也就是说可能会从冷备份的数据文件的数据文件头中获取)。根据上述的描述,此时需要采用using backup controlfile做recovery.因此情况是System Checkpoint SCN=0 < Start SCN = Datafile Checkpoint SCN。 2.使用noresetlogs选项时,有一个前提就是:一定要有online redo log的存在。否则就要使用resetlogs选项。这个时候控制文件重建好时,其system checkpoint SCN=Datafile Checkpoint SCN=Lastest Checkpoint SCN in online redo log,我们可以看到Datafile Checkpoint SCN并没有从Start SCN中读取。而是读取了最新的日志文件中的SCN作为自己的数据。此时重建的控制文件在恢复中的作用跟最新的控制文件类似,System Checkpoint SCN(已经读取最新的redo log的checkpoint SCN信息)可能会>Start SCN(因为数据文件可能会从冷备份中恢复),恢复时就不需要加using backup controlfile子句了。
3.关于backup controlfile的补充:backup controlfile只有备份时刻的archive log信息,并没有DB crash时刻的archive log信息,所以并不会自动应用online redo log,而是提示找不到序号为Lastest Archive log sequence + 1的archive log,尽管你可以手动指定online redo log来实现完全恢复,但因为一旦使用了using backup controlfile子句,Oracle就视为不完全恢复,必须open resetlogs!实际上,假如你有旧的控制文件又不想resetlogs,那很简单,使用旧的控制文件mount然后backup to trace,然后手工创建控制文件,使用reuse database … noresetlogs .这样就可以recover database自动恢复并open database而不用resetlogs了(记住:必须有所有的online redo logs才可以这样!)。
三、Low SCN与Next SCN
1.在一个事务提交后,会在redo log中存在一条redo记录,同时,系统为其提供一个最新的SCN(通过函数dbms_flashback.get_system_change_number可以知道当前的最新SCN),记录在该条记录中。如果该条记录是在redo log被清空(日志满做切换时或发生checkpoint时,所有变化日志已经被写入数据文件中)前,则其SCN被记录为redo log的low SCN。以后在日志再次被清空前写入的redo记录中SCN则成为Next SCN。
2.当日志切换或发生checkpoint时,从Low SCN到Next SCN之间的所有redo记录的数据就被DBWn进程写入数据文件中,而CKPT进程则将所有数据文件(无论redo log中的数据是否影响到该数据文件)的文件头上记录的Start SCN(通过视图v$datafile_header的字段checkpoint_change#可以查询)更新为Next SCN,同时将控制文件中的System Checkpoint SCN(通过视图v$database的字段checkpoint_change#可以查询)、每个数据文件对应的Datafile Checkpoint(通过视图v$datafile的字段checkpoint_change#可以查询)也更新为Next SCN。但是,如果该数据文件所在的表空间被设置为read-only时,数据文件的Start SCN和控制文件中Datafile Checkpoint SCN都不会被更新。
其他请见:SCN与Oracle数据库恢复的关系

rman中关于archivelog操作

RMAN> list backup of archivelog all;
列出所有archive log 备份
RMAN> list backup of archivelog from logseq 100 until logseq 120;
列出archive log 从100到120
RMAN> list backup of archivelog sequence between 100 and 110;
列出archive log 从100到120
–说明:between……and只能使用sequence,而不能使用logseq
RMAN> list backup of archivelog from logseq 100;
列出seq大于等于100的archive log
RMAN> list backup of archivelog low logseq 120;
列出seq大于等于120的archive log
RMAN> list backup of archivelog sequence 100;
列出seq为100的archive log
–说明:在对于rman中关于archivelog的操作中logseq与sequence作用相同,但是建议尽量使用sequence
RMAN> list backup of archivelog logseq 85;
列出seq为85的archive log
RMAN> list backup of archivelog until logseq 85;
列出seq小于等于85的archive log
RMAN> list backup of archivelog high logseq 40;
列出seq小于等于40的archive log
RMAN> list backup of archivelog from time ‘sysdate-7’;
列出7天以前的archive log
RMAN> run {
2> set archivelog destination to ‘/opt/oracle/oradata/test/newlog’;
3> restore archivelog low logseq 40;
4> }
从seq为40开始,恢复到/opt/oracle/oradata/test/newlog中
–说明:list backup of archivelog中限定日志的位置也适合restore archivelog
RMAN> backup archivelog sequence between 100 and 110 format ‘/tmp/text_test.rman’ delete input;
备份seq为100至110的archive log
–说明:list backup of archivelog中限定日志的位置也适合backup archivelog
RMAN>DELETE ARCHIVELOG ALL COMPLETED BEFORE ‘SYSDATE-7’;
删除7天前archive log
RMAN>DELETE ARCHIVELOG low logseq 40;
删除seq大于等于40的archive log
RMAN> crosscheck archivelog all;
RMAN> delete expired archivelog all;
删除无效archive log
–说明:DELETE ARCHIVELOG中限定日志的位置也适合restore archivelog