Category Archives: Oracle

通过sql查询rman备份信息

Posted on by

查看所有备份集

    SELECT A.RECID "BACKUP SET",
         A.SET_STAMP,
         DECODE (B.INCREMENTAL_LEVEL,
                 '', DECODE (BACKUP_TYPE, 'L', 'Archivelog', 'Full'),
                 1, 'Incr-1级',
                 0, 'Incr-0级',
                 B.INCREMENTAL_LEVEL)
            "Type LV",
         B.CONTROLFILE_INCLUDED "包含CTL",
         DECODE (A.STATUS,
                 'A', 'AVAILABLE',
                 'D', 'DELETED',
                 'X', 'EXPIRED',
                 'ERROR')
            "STATUS",
         A.DEVICE_TYPE "Device Type",
         A.START_TIME "Start Time",
         A.COMPLETION_TIME "Completion Time",
         A.ELAPSED_SECONDS "Elapsed Seconds",
         --a.BYTES/1024/1024/1024 "大小(G)",
         --a.COMPRESSED,
         A.TAG "Tag",
         A.HANDLE "Path"
    FROM GV$BACKUP_PIECE A, GV$BACKUP_SET B
   WHERE A.SET_STAMP = B.SET_STAMP AND A.DELETED = 'NO'
ORDER BY A.COMPLETION_TIME DESC;

查找某个备份集中包含数据文件

SELECT distinct c.file#,A.SET_STAMP, D.NAME, C.CHECKPOINT_CHANGE#, C.CHECKPOINT_TIME
  FROM V$BACKUP_DATAFILE C, V$BACKUP_PIECE A, V$DATAFILE D
 WHERE A.SET_STAMP = C.SET_STAMP
AND D.FILE# = C.FILE#
AND A.DELETED='NO'
AND c.set_stamp=&set_stamp
ORDER BY C.FILE#;

查询某个备份集中控制文件

SELECT DISTINCT A.SET_STAMP,
                D.NAME,
                C.CHECKPOINT_CHANGE#,
                C.CHECKPOINT_TIME
  FROM V$BACKUP_DATAFILE C, V$BACKUP_PIECE A, V$CONTROLFILE D
 WHERE A.SET_STAMP = C.SET_STAMP
   AND C.FILE# = 0
   AND A.DELETED = 'NO'
   AND C.SET_STAMP = &SET_STAMP;

查看某个备份集中归档日志

SELECT DISTINCT B.SET_STAMP,
                B.THREAD#,
                B.SEQUENCE#,
                B.FIRST_TIME,
                B.FIRST_CHANGE#,
                B.NEXT_TIME,
                B.NEXT_CHANGE#
  FROM V$BACKUP_REDOLOG B, V$BACKUP_PIECE A
 WHERE A.SET_STAMP = B.SET_STAMP
   AND A.DELETED = 'NO'
   AND B.SET_STAMP = &SET_STAMP
 ORDER BY THREAD#, SEQUENCE#;

查看某个备份集SPFILE

SELECT DISTINCT A.SET_STAMP, B.COMPLETION_TIME, HANDLE
  FROM V$BACKUP_SPFILE B, V$BACKUP_PIECE A
 WHERE A.SET_STAMP = B.SET_STAMP
   AND A.DELETED = 'NO'
   AND B.SET_STAMP = &SET_STAMP;

rman配置信息

SELECT NAME,VALUE FROM V$RMAN_CONFIGURATION;

远程访问ASM

Posted on by

一、动态监听
1.监听文件

[oracle@node1 admin]$ more /u01/oracle/network/admin/listener.ora
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /u01/oracle)
      (PROGRAM = extproc)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = node1)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
    )
  )

2.监听状态

[oracle@node1 admin]$ lsnrctl status

LSNRCTL for Linux: Version 10.2.0.4.0 - Production on 18-JAN-2012 13:38:42

Copyright (c) 1991, 2007, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=node1)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 10.2.0.4.0 - Production
Start Date                18-JAN-2012 13:32:49
Uptime                    0 days 0 hr. 5 min. 53 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/oracle/network/admin/listener.ora
Listener Log File         /u01/oracle/network/log/listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=node1)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0)))
Services Summary...
Service "+ASM" has 1 instance(s).
  Instance "+ASM", status BLOCKED, has 1 handler(s) for this service...
Service "+ASM_XPT" has 1 instance(s).
  Instance "+ASM", status BLOCKED, has 1 handler(s) for this service...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "chf" has 1 instance(s).
  Instance "chf", status READY, has 1 handler(s) for this service...
Service "chfXDB" has 1 instance(s).
  Instance "chf", status READY, has 1 handler(s) for this service...
Service "chf_XPT" has 1 instance(s).
  Instance "chf", status READY, has 1 handler(s) for this service...
The command completed successfully

3.客户端tns文件[有部分测试加上了(UR=A)]

vm_asm =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.30)(PORT = 1521))
    (CONNECT_DATA =
      (SERVICE_NAME = +ASM)
      (INSTANCE_NAME = +ASM)
      (UR=A)  #分存在和不存在测试
    )
  )

vm_chf =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.30)(PORT = 1521))
    (CONNECT_DATA =
      (SERVICE_NAME = chf)
      (INSTANCE_NAME = chf)
    )
  )

4.无测试(UR=A)

C:\Users\XIFENFEI>tnsping vm_asm

TNS Ping Utility for 32-bit Windows: Version 11.2.0.1.0 - Production on 23-2月 -
2012 22:15:10

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

已使用的参数文件:
e:\oracle\11_2_0\network\admin\sqlnet.ora


已使用 TNSNAMES 适配器来解析别名
尝试连接 (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.30)(PORT =
1521)) (CONNECT_DATA = (SERVICE_NAME = +ASM) (INSTANCE_NAME = +ASM)))
OK (10 毫秒)

C:\Users\XIFENFEI>SQLPLUS sys/xifenfei@vm_asm as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on 星期四 2月 23 22:15:14 2012

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

ERROR:
ORA-12514: TNS: 监听程序当前无法识别连接描述符中请求的服务


请输入用户名:
ERROR:
ORA-12560: TNS: 协议适配器错误


请输入用户名:
ERROR:
ORA-12560: TNS: 协议适配器错误


SP2-0157: 在 3 次尝试之后无法连接到 ORACLE, 退出 SQL*Plus

--通过下面的数据库实例测试，证明动态监听是正常工作的，可以访问数据库
C:\Users\XIFENFEI>SQLPLUS sys/xifenfei@vm_chf as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on 星期四 2月 23 21:55:03 2012

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


连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
chf

5.含(UR=A)测试

C:\Users\XIFENFEI>tnsping vm_asm

TNS Ping Utility for 32-bit Windows: Version 11.2.0.1.0 - Production on 23-2月 -
2012 22:16:49

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

已使用的参数文件:
e:\oracle\11_2_0\network\admin\sqlnet.ora


已使用 TNSNAMES 适配器来解析别名
尝试连接 (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.30)(PORT =
1521)) (CONNECT_DATA = (SERVICE_NAME = +ASM) (INSTANCE_NAME = +ASM) (UR=A)))
OK (20 毫秒)

C:\Users\XIFENFEI>SQLPLUS sys/xifenfei@vm_asm as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on 星期四 2月 23 22:16:52 2012

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


连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select instance_name from v$instance;

INSTANCE_NAME
--------------------------------
+ASM

二、静态监听
1.监听文件

[oracle@node1 admin]$ more listener.ora
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =  
     (GLOBAL_DBNAME = chf)
     (ORACLE_HOME = /u01/oracle)
     (SID_NAME = chf)
    )
    (SID_DESC =
     (GLOBAL_DBNAME = +ASM)
     (ORACLE_HOME = /u01/oracle)
     (SID_NAME = +ASM)
     )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.30)(PORT = 1521))
    )
  )

2.监听状态

[oracle@node1 admin]$ lsnrctl status

LSNRCTL for Linux: Version 10.2.0.4.0 - Production on 18-JAN-2012 13:53:52

Copyright (c) 1991, 2007, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.30)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 10.2.0.4.0 - Production
Start Date                18-JAN-2012 13:51:48
Uptime                    0 days 0 hr. 2 min. 4 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/oracle/network/admin/listener.ora
Listener Log File         /u01/oracle/network/log/listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.30)(PORT=1521)))
Services Summary...
Service "+ASM" has 2 instance(s).
  Instance "+ASM", status UNKNOWN, has 1 handler(s) for this service...
  Instance "+ASM", status BLOCKED, has 1 handler(s) for this service...
Service "+ASM_XPT" has 1 instance(s).
  Instance "+ASM", status BLOCKED, has 1 handler(s) for this service...
Service "chf" has 2 instance(s).
  Instance "chf", status UNKNOWN, has 1 handler(s) for this service...
  Instance "chf", status READY, has 1 handler(s) for this service...
Service "chfXDB" has 1 instance(s).
  Instance "chf", status READY, has 1 handler(s) for this service...
Service "chf_XPT" has 1 instance(s).
  Instance "chf", status READY, has 1 handler(s) for this service...
The command completed successfully

3.无(UR=A)测试

C:\Users\XIFENFEI>tnsping vm_asm

TNS Ping Utility for 32-bit Windows: Version 11.2.0.1.0 - Production on 23-2月 -
2012 22:11:34

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

已使用的参数文件:
e:\oracle\11_2_0\network\admin\sqlnet.ora


已使用 TNSNAMES 适配器来解析别名
尝试连接 (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.30)(PORT =
1521)) (CONNECT_DATA = (SERVICE_NAME = +ASM) (INSTANCE_NAME = +ASM)))
OK (20 毫秒)

C:\Users\XIFENFEI>SQLPLUS sys/xifenfei@vm_asm as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on 星期四 2月 23 22:11:06 2012

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


连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select instance_name from v$instance;

INSTANCE_NAME
--------------------------------
+ASM

4.含(UR=A)测试

C:\Users\XIFENFEI>tnsping vm_asm

TNS Ping Utility for 32-bit Windows: Version 11.2.0.1.0 - Production on 23-2月 -
2012 22:12:49

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

已使用的参数文件:
e:\oracle\11_2_0\network\admin\sqlnet.ora


已使用 TNSNAMES 适配器来解析别名
尝试连接 (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.30)(PORT =
1521)) (CONNECT_DATA = (SERVICE_NAME = +ASM) (INSTANCE_NAME = +ASM) (UR=A)))
OK (10 毫秒)

C:\Users\XIFENFEI>SQLPLUS sys/xifenfei@vm_asm as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on 星期四 2月 23 22:12:53 2012

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


连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select instance_name from v$instance;

INSTANCE_NAME
--------------------------------
+ASM

5.easy connect访问asm实例

C:\Users\XIFENFEI>SQLPLUS sys/xifenfei@192.168.1.30/+asm as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on 星期四 2月 23 22:27:42 2012

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


连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select instance_name from v$instance;

INSTANCE_NAME
--------------------------------
+ASM

三、总结
1.在动态监听中，只有设置了(UR=A)才能够远程访问ASM实例；
2.在静态监听中，无论是否设置(UR=A)均可远程访问ASM实例，甚至可以使用easy connect方法方法ASM实例。

statspack报告中逻辑读为负值

Posted on by

最近遇到两次在sp报告中，显示逻辑读为负数。进行分析情况如下：
一台是运营商的crm库（aix 5.3+9.2.0.8+rac）

--系统版本
[zwq_crm2:/home/crm_oraeye]oslevel -s 
5300-08-07-0920

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

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
PL/SQL Release 9.2.0.8.0 - Production
CORE    9.2.0.8.0       Production
TNS for IBM/AIX RISC System/6000: Version 9.2.0.8.0 - Production
NLSRTL Version 9.2.0.8.0 - Production

--数据库启动时间
SQL> SELECT A.INST_ID, TO_CHAR (startup_time, 'yyyy-mm-dd hh24:mi:ss')
  2   FROM gv$instance a;

   INST_ID TO_CHAR(STARTUP_TIM
---------- -------------------
         2 2011-02-19 03:33:49
         1 2011-02-19 03:56:17

--异常的逻辑读统计数据
SQL>   SELECT b.snap_id,
  2           TO_CHAR (B.SNAP_TIME, 'yyyy-mm-dd hh24:mi:ss'),
  3           TO_CHAR (A.VALUE, '999,999,999,999,999')
  4      FROM stats$sysstat a, stats$snapshot b
  5     WHERE     a.NAME = 'session logical reads'
  6           AND a.instance_number = 2
  7           AND a.snap_id = b.snap_id
  8           AND A.SNAP_ID >=47913
  9           AND A.SNAP_ID <=47920
 10  ORDER BY a.snap_id;

   SNAP_ID TO_CHAR(B.SNAP_TIME TO_CHAR(A.VALUE,'999
---------- ------------------- --------------------
   47913	2012-02-10 20:00	4,764,705,272,783
   47914	2012-02-10 21:00	4,761,539,910,574
   47915	2012-02-10 22:00	4,749,529,436,021
   47916	2012-02-10 23:00	4,745,952,040,146 
   47917	2012-02-11 00:00	4,738,052,256,634 
   47918	2012-02-11 01:00	4,738,894,245,521 
   47919	2012-02-11 02:00	4,739,587,095,184 
   47920	2012-02-11 03:00	4,740,409,262,259

另一台是运营商的开停机库（aix 5.3+9.2.0.8+rac）

--系统版本
[zwq_offon2:/home/oraeye]oslevel -s 
5300-08-07-0920

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

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
PL/SQL Release 9.2.0.8.0 - Production
CORE    9.2.0.8.0       Production
TNS for IBM/AIX RISC System/6000: Version 9.2.0.8.0 - Production
NLSRTL Version 9.2.0.8.0 - Production

--数据库启动时间
SQL> SELECT A.INST_ID, TO_CHAR (startup_time, 'yyyy-mm-dd hh24:mi:ss')
  2    FROM gv$instance a;

   INST_ID TO_CHAR(STARTUP_TIM
---------- -------------------
         2 2010-01-23 19:16:46
         1 2010-01-23 19:15:09

--异常的逻辑读统计数据
SQL>   SELECT b.snap_id,
  2           TO_CHAR (B.SNAP_TIME, 'yyyy-mm-dd hh24:mi:ss'),
  3           TO_CHAR (A.VALUE, '999,999,999,999,999')
  4      FROM stats$sysstat a, stats$snapshot b
  5     WHERE     a.NAME = 'session logical reads'
  6           AND a.instance_number = 2
  7           AND a.snap_id = b.snap_id
  8           AND A.SNAP_ID IN ('38271', '38272', '38339', '38340')
  9  ORDER BY a.snap_id;

   SNAP_ID TO_CHAR(B.SNAP_TIME TO_CHAR(A.VALUE,'999
---------- ------------------- --------------------
     38271 2012-01-31 19:00:05    4,269,858,122,434
     38272 2012-01-31 20:00:02    4,266,001,522,867
     38339 2012-02-02 09:00:02    4,275,651,080,526
     38340 2012-02-02 10:00:02    4,250,263,107,466

这两个数据库中都出现了在数据库没有重启的情况下stats$sysstat.value的值出现波动情况，而且都是在40万亿以上的时候。不知道是特点的版本巧合(特定的数据库版本，特定的操作系统版本)，还是Oracle未公布bug。出现这样的情况，目前只能通过重启实例来使得statspack捕获到的逻辑读值变小，从而避免这样的波动，从而解决sp中出现逻辑读为负数的情况。

找出11g undo 回滚段名称

Posted on by

当数据库启动的时报undo相关异常，很多情况下我们不得不使用隐含参数来处理(_offline_rollback_segments和_corrupted_rollback_segments)，而这个就需要明确异常的undo回滚段名称。在11g中，undo回滚段的名称发生了改变，在11g之前的版本中，回滚段名称是”_SYSTEMn$”之类，而到了11g回滚段的名称变为了”_SYSTEMn_时间戳$”，因为时间戳我们不知道，所以我们不能通过n的值，来确定回滚段的名称，从而也就不能很明确的使用_offline_rollback_segments和_corrupted_rollback_segments来标明异常回滚段。既然回滚段的名称我们可以通过dba_rollback_segs视图来查询，那么我们可以根据这个视图找到对应的回滚段是存储在哪张基表中，下面为dba_rollback_segs视图对应的sql语句

select un.name, decode(un.user#,1,'PUBLIC','SYS'),
       ts.name, un.us#, f.file#, un.block#,
       s.iniexts * ts.blocksize,
       decode(bitand(ts.flags, 3), 1, to_number(NULL),
                                      s.extsize * ts.blocksize),
       s.minexts, s.maxexts,
       decode(bitand(ts.flags, 3), 1, to_number(NULL),
                                      s.extpct),
       decode(un.status$, 2, 'OFFLINE', 3, 'ONLINE',
                          4, 'UNDEFINED', 5, 'NEEDS RECOVERY',
                          6, 'PARTLY AVAILABLE', 'UNDEFINED'),
       decode(un.inst#, 0, NULL, un.inst#), un.file#
from sys.undo$ un, sys.seg$ s, sys.ts$ ts, sys.file$ f
where un.status$ != 1
  and un.ts# = s.ts#
  and un.file# = s.file#
  and un.block# = s.block#
  and s.type# in (1, 10)
  and s.ts# = ts.ts#
  and un.ts# = f.ts#
  and un.file# = f.relfile#

通过观察，我们知道回滚段信息是存储在sys.undo$中(name字段表示回滚段名称，status$字段表示回滚段状态[1:DELETE,2:OFFLINE,3:ONLINE,4:UNDEFINED,5:NEEDS RECOVERY,6:PARTLY AVAILABLE,其他表示:UNDEFINED]),通过这个视图我们需要找的到status$为5所对应name。
找这些值较简易的方法就是通过odu抽取sys.undo$表中数据，然后在其他库上还原，然后通过sql语句查询
1.设置odu参数(config.txt)
output_format dmp,其他参数根据你的实际情况设置

2.填写system表空间对应的数据文件(control.txt)

[oracle@localhost odu]$ more control.txt 
#ts #fno   #rfno     filename                                          block_size
1     1      1       /u01/oradata/first/system01.dbf

3.odu导出数据

ODU> unload table sys.undo$

Unloading table: UNDO$,object ID: 15
Unloading segment,storage(Obj#=15 DataObj#=15 TS#=0 File#=1 Block#=224 Cluster=0)
21 rows unloaded

4.导入至新库

imp chf/xifenfei file=C:\Users\XIFENFEI\Downloads\SYS_UNDO$.dmp FROMUSER=SYS TOUSER=CHF

5.查询需要处理的回滚段

--数据库版本10g及其以上版本
set pagesize 0 feedback off verify off heading off echo off linesize 1000
select WMSYS.WM_CONCAT(name) from UNDO$ where status$=5;

--数据库版本9i及其以下版本(自己拼接)
set pagesize 0 feedback off verify off heading off echo off 
select name from UNDO$ where status$=5;

现在已经找出来了需要处理的回滚段，其他数据库恢复步骤与以前数据库相同。