标签云
asm 恢复 asm恢复 bbed bootstrap$ dul In Memory kcbzib_kcrsds_1 kccpb_sanity_check_2 kfed MySQL恢复 ORA-00312 ORA-00607 ORA-00704 ORA-01110 ORA-01555 ORA-01578 ORA-08103 ORA-600 2662 ORA-600 2663 ORA-600 3020 ORA-600 4000 ORA-600 4137 ORA-600 4193 ORA-600 4194 ORA-600 16703 ORA-600 kcbzib_kcrsds_1 ORA-600 KCLCHKBLK_4 ORA-15042 ORA-15196 ORACLE 12C oracle dul ORACLE PATCH Oracle Recovery Tools oracle加密恢复 oracle勒索 oracle勒索恢复 oracle异常恢复 ORACLE恢复 Oracle 恢复 ORACLE数据库恢复 oracle 比特币 OSD-04016 YOUR FILES ARE ENCRYPTED 勒索恢复 比特币加密文章分类
- Others (2)
- 中间件 (2)
- WebLogic (2)
- 操作系统 (100)
- 数据库 (1,598)
- DB2 (22)
- MySQL (70)
- Oracle (1,463)
- Data Guard (49)
- EXADATA (7)
- GoldenGate (21)
- ORA-xxxxx (158)
- ORACLE 12C (72)
- ORACLE 18C (6)
- ORACLE 19C (13)
- ORACLE 21C (3)
- Oracle ASM (65)
- Oracle Bug (7)
- Oracle RAC (47)
- Oracle 安全 (6)
- Oracle 开发 (27)
- Oracle 监听 (27)
- Oracle备份恢复 (530)
- Oracle安装升级 (84)
- Oracle性能优化 (62)
- 专题索引 (5)
- 勒索恢复 (75)
- PostgreSQL (18)
- PostgreSQL恢复 (6)
- SQL Server (27)
- SQL Server恢复 (8)
- TimesTen (7)
- 达梦数据库 (2)
- 生活娱乐 (2)
- 至理名言 (11)
- 虚拟化 (2)
- VMware (2)
- 软件开发 (36)
- Asp.Net (9)
- JavaScript (12)
- PHP (2)
- 小工具 (19)
-
最近发表
- PostgreSQL解析wal日志之—walminer
- Oracle 19c/21c最新patch信息-202404
- PostgreSQL恢复系列:pg_filedump批量处理
- PostgreSQL部分主要字典信息
- PostgreSQL恢复系列:pg_filedump恢复字典构造
- PostgreSQL 16 源码安装
- ORA-00742 ORA-00312 恢复
- 数据库open成功后报ORA-00353 ORA-00354错误引起的一系列问题(本质ntfs文件系统异常)
- ORA-600 ktsiseginfo1故障
- ORA-00600: internal error code, arguments: [16703], [1403], [4] 原因
- 最近遇到几起ORA-600 16703故障(tab$被清空),请引起重视
- ORA-600 2662快速恢复之Patch scn工具
- TNS-12518: TNS:listener could not hand off client connection
- ora.storage无法启动报ORA-12514故障处理
- 断电引起文件scn异常数据库恢复
- ORA-16188: LOG_ARCHIVE_CONFIG settings inconsistent with previously started instance
- .[hudsonL@cock.li].mkp勒索加密数据库完美恢复
- 模拟带库实现rman远程备份
- 又一例:ORA-600 kclchkblk_4和2662故障
- Oracle误删除数据文件恢复
月归档:八月 2013
ORACLE 12C Windows-Linux 部署DATAGURAD
环境描述
win 64中的ORACLE 12C(primary)与Linux 64中的ORACLE 12C(standby)搭建datagurad
primary force logging
C:\Users\XIFENFEI>sqlplus / as sysdba SQL*Plus: Release 12.1.0.1.0 Production on 星期六 8月 24 16:59:53 2013 Copyright (c) 1982, 2013, Oracle. All rights reserved. 连接到: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options CDB_CDB$ROOT@SYS> select banner from v$version; BANNER -------------------------------------------------------------------------------- Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production PL/SQL Release 12.1.0.1.0 - Production CORE 12.1.0.1.0 Production TNS for 64-bit Windows: Version 12.1.0.1.0 - Production NLSRTL Version 12.1.0.1.0 - Production CDB_CDB$ROOT@SYS> alter database force logging ; 数据库已更改。
primary rman backup
backup filesperset = 5 as compressed backupset database format 'd:/backup/rman/full_%U.rman';
primary 生成standby controlfile
CDB_CDB$ROOT@SYS> ALTER DATABASE CREATE STANDBY CONTROLFILE AS 'c:/control01.ctl'; 数据库已更改。 CDB_CDB$ROOT@SYS> create pfile='e:/pfile.txt' from spfile; 文件已创建。
standby 参数文件
DB_CREATE_FILE_DEST='+DATA' db_create_online_log_dest_1='+DATA' db_unique_name='cdb_dg' service_names='cdb' log_archive_dest_1='LOCATION=/u02/app/oracle/archivelog/ valid_for=(all_logfiles,all_roles)' log_archive_dest_2='service=primary lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=cdb' log_archive_config='dg_config=(cdb,cdb_dg)' standby_file_management=auto db_file_name_convert='E:\APP\XIFENFEI\ORADATA\','+DATA\CDB_DG\DATAFILE\' log_file_name_convert='E:\APP\XIFENFEI\ORADATA\','+DATA\CDB_DG\LOGFILE\' fal_server=primary
primary 修改参数
CDB_CDB$ROOT@SYS> alter system set log_archive_config='dg_config=(cdb,cdb_dg)'; 系统已更改。 CDB_CDB$ROOT@SYS> alter system set log_archive_dest_2=' 2 service=standby lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=cdb_dg'; 系统已更改。 CDB_CDB$ROOT@SYS> alter system set log_archive_dest_1= 2 'LOCATION=USE_DB_RECOVERY_FILE_DEST valid_for=(all_logfiles,all_roles)'; 系统已更改。 CDB_CDB$ROOT@SYS> alter system set fal_server=standby; 系统已更改。 CDB_CDB$ROOT@SYS> alter system set standby_file_management=auto; 系统已更改。 CDB_CDB$ROOT@SYS> alter system set db_file_name_convert='+DATA\CDB_DG\DATAFILE\','E:\APP\XIFENFEI\ORADATA\' scope=spfile; 系统已更改。 CDB_CDB$ROOT@SYS> alter system set log_file_name_convert='+DATA\CDB_DG\LOGFILE\','E:\APP\XIFENFEI\ORADATA\' scope=spfile; 系统已更改。
primary and standby tns
STANDBY = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.30.32 )(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = cdb) ) ) PRIMARY = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.30.1 )(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = cdb) ) )
standby restore controlfile
ASMCMD> cp /tmp/CONTROL01.CTL control01.ctl copying /tmp/CONTROL01.CTL -> +data/cdb/control01.ctl
standby password file
[oracle@xifenfei dbs]$ pwd /u01/app/oracle/product/12.1/db_1/dbs [oracle@xifenfei dbs]$ cp /tmp/rman/PWDcdb.ora orapwcdb
standby mount
SYS% cdb> create spfile from pfile='initcdb.ora'; File created. SYS% cdb> startup mount; ORACLE instance started. Total System Global Area 521936896 bytes Fixed Size 2290264 bytes Variable Size 314576296 bytes Database Buffers 197132288 bytes Redo Buffers 7938048 bytes Database mounted. SYS% cdb> select banner from v$version; BANNER -------------------------------------------------------------------------------- Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production PL/SQL Release 12.1.0.1.0 - Production CORE 12.1.0.1.0 Production TNS for Linux: Version 12.1.0.1.0 - Production NLSRTL Version 12.1.0.1.0 - Production SYS% cdb> select name from v$datafile; NAME -------------------------------------------------------------------------------- E:\APP\XIFENFEI\ORADATA\CDB\SYSTEM01.DBF E:\APP\XIFENFEI\ORADATA\CDB\PDBSEED\SYSTEM01.DBF E:\APP\XIFENFEI\ORADATA\CDB\SYSAUX01.DBF E:\APP\XIFENFEI\ORADATA\CDB\PDBSEED\SYSAUX01.DBF E:\APP\XIFENFEI\ORADATA\CDB\UNDOTBS01.DBF E:\APP\XIFENFEI\ORADATA\CDB\USERS01.DBF E:\APP\XIFENFEI\ORADATA\CDB\PDB\SYSTEM01.DBF E:\APP\XIFENFEI\ORADATA\CDB\PDB\SYSAUX01.DBF E:\APP\XIFENFEI\ORADATA\CDB\PDB\PDB_USERS01.DBF 9 rows selected.
standby rman restore
--清理控制文件中的备份集垃圾 DELETE noprompt OBSOLETE; crosscheck backup; delete noprompt expired backup; --注册新备份集 catalog start with '/tmp/rman/'; --还原数据文件 run { set newname for database to '+data'; restore database; switch datafile all; }
standby clear redo
SYS% cdb> select group# from v$log; GROUP# ---------- 4 6 5 SYS% cdb> alter database clear logfile group 4; Database altered. SYS% cdb> alter database clear logfile group 5; Database altered. SYS% cdb> alter database clear logfile group 6; Database altered.
standby add standby redolog
SYS% cdb> ALTER DATABASE ADD STANDBY LOGFILE GROUP 10 size 50M; Database altered. SYS% cdb> ALTER DATABASE ADD STANDBY LOGFILE GROUP 11 size 50M; Database altered. SYS% cdb> ALTER DATABASE ADD STANDBY LOGFILE GROUP 12 size 50M; Database altered. SYS% cdb> ALTER DATABASE ADD STANDBY LOGFILE GROUP 13 size 50M; Database altered.
primary add standby redolog
CDB_CDB$ROOT@SYS> ALTER DATABASE ADD STANDBY LOGFILE GROUP 10 'E:\APP\XIFENFEI\ORADATA\CDB\std_redo10.log' size 50M; 数据库已更改。 CDB_CDB$ROOT@SYS> ALTER DATABASE ADD STANDBY LOGFILE GROUP 11 'E:\APP\XIFENFEI\ORADATA\CDB\std_redo11.log' size 50M; 数据库已更改。 CDB_CDB$ROOT@SYS> ALTER DATABASE ADD STANDBY LOGFILE GROUP 12 'E:\APP\XIFENFEI\ORADATA\CDB\std_redo12.log' size 50M; 数据库已更改。 CDB_CDB$ROOT@SYS> ALTER DATABASE ADD STANDBY LOGFILE GROUP 13 'E:\APP\XIFENFEI\ORADATA\CDB\std_redo13.log' size 50M; 数据库已更改。
standby readonly
SYS% cdb> ALTER DATABASE OPEN READ ONLY; Database altered.
standby start mrp
SYS% cdb> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION; Database altered.
DATAGURAD 是否正常检查
--primary CDB_CDB$ROOT@SYS> archive log list; 数据库日志模式 存档模式 自动存档 启用 存档终点 USE_DB_RECOVERY_FILE_DEST 最早的联机日志序列 374 下一个存档日志序列 376 当前日志序列 376 --standby Mon Aug 12 13:56:51 2013 All non-current ORLs have been archived. Mon Aug 12 13:56:53 2013 Media Recovery Log /u02/app/oracle/archivelog/1_370_820595806.dbf Mon Aug 12 13:56:57 2013 Media Recovery Log /u02/app/oracle/archivelog/1_371_820595806.dbf Mon Aug 12 13:57:02 2013 Media Recovery Log /u02/app/oracle/archivelog/1_372_820595806.dbf Mon Aug 12 13:57:04 2013 Media Recovery Log /u02/app/oracle/archivelog/1_373_820595806.dbf Mon Aug 12 13:57:05 2013 Media Recovery Log /u02/app/oracle/archivelog/1_374_820595806.dbf Media Recovery Waiting for thread 1 sequence 375 Mon Aug 12 13:57:19 2013 Primary database is in MAXIMUM PERFORMANCE mode RFS[2]: Assigned to RFS process (PID:26114) RFS[2]: No standby redo logfiles created for thread 1 RFS[2]: Opened log for thread 1 sequence 376 dbid 1937199326 branch 820595806 Mon Aug 12 13:57:19 2013 RFS[3]: Assigned to RFS process (PID:26118) RFS[3]: Opened log for thread 1 sequence 375 dbid 1937199326 branch 820595806 Mon Aug 12 13:57:19 2013 Archived Log entry 16 added for thread 1 sequence 375 rlc 820595806 ID 0x7377d8de dest 2: Mon Aug 12 13:57:22 2013 Media Recovery Log /u02/app/oracle/archivelog/1_375_820595806.dbf Media Recovery Waiting for thread 1 sequence 376 (in transit)
ORACLE 12C Partial Global/Local Indexes for Partitioned Tables
以前我有个想法,我只想对其中的某个或者某几个分区上创建索引,其他分区不想创建,在12C之前的版本,无论是Local还是Global index,都不能实现该需求,但是从ORACLE 12C开始引进了Partial Global/Local Indexes for Partitioned Tables,解决了该问题,可以在指定的分区上创建本地索引或者全局索引,主要语法是在表或者分区,子分区级别设置[INDEXING { ON | OFF }]
创建测试表
CDB_PDB@CHF> SELECT BANNER FROM V$VERSION; BANNER -------------------------------------------------------------------------------- Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production PL/SQL Release 12.1.0.1.0 - Production CORE 12.1.0.1.0 Production TNS for 64-bit Windows: Version 12.1.0.1.0 - Production NLSRTL Version 12.1.0.1.0 - Production CDB_PDB@CHF> CREATE TABLE xifenfei_orders ( 2 order_id NUMBER(12), 3 order_address varchar2(100), 4 order_mode VARCHAR2(20)) 5 INDEXING OFF 6 PARTITION BY RANGE (order_id) 7 (PARTITION ord_p1 VALUES LESS THAN (100) INDEXING ON, 8 PARTITION ord_p2 VALUES LESS THAN (200) INDEXING OFF, 9 PARTITION ord_p3 VALUES LESS THAN (300) INDEXING ON, 10 PARTITION ord_p4 VALUES LESS THAN (400), 11 PARTITION ord_p5 VALUES LESS THAN (500)); 表已创建。
创建LOCAL INDEX
CDB_PDB@CHF> CREATE INDEX ind_lox on xifenfei_orders(order_address) LOCAL INDEXING PARTIAL; 索引已创建。 CDB_PDB@CHF> COL INDEX_NAME FOR A10 CDB_PDB@CHF> COL PARTITION_NAME FOR A15 CDB_PDB@CHF> select index_name, partition_name,STATUS 2 from user_ind_partitions 3 where index_name = 'IND_LOX'; INDEX_NAME PARTITION_NAME STATUS ---------- --------------- -------- IND_LOX ORD_P5 UNUSABLE IND_LOX ORD_P4 UNUSABLE IND_LOX ORD_P3 USABLE IND_LOX ORD_P2 UNUSABLE IND_LOX ORD_P1 USABLE --设置INDEXING OFF对应的index 状态为UNUSABLE,分区继承表 CDB_PDB@CHF> select partition_name,indexing from user_tab_partitions where table_name='XIFENFEI_ORDERS'; PARTITION_NAME INDE --------------- ---- ORD_P5 OFF ORD_P4 OFF ORD_P3 ON ORD_P2 OFF ORD_P1 ON --因为segment 延迟,无数据,所以无分区和索引记录 CDB_PDB@CHF> SELECT PARTITION_NAME,SEGMENT_NAME FROM USER_SEGMENTS WHERE SEGMENT_NAME='IND_LOX'; 未选定行 CDB_PDB@CHF> SELECT PARTITION_NAME,SEGMENT_NAME FROM USER_SEGMENTS WHERE SEGMENT_NAME='XIFENFEI_ORDERS'; 未选定行 CDB_PDB@CHF> begin 2 for i in 1 .. 449 loop 3 insert into xifenfei_orders 4 values 5 (i,'www.xifenfei.com'||i,'惜分飞'||i); 6 end loop; 7 commit; 8 end; 9 / PL/SQL 过程已成功完成。 --插入记录后,分区表有相关记录 CDB_PDB@CHF> SELECT PARTITION_NAME,SEGMENT_NAME,blocks FROM USER_SEGMENTS WHERE SEGMENT_NAME='XIFENFEI_ORDERS'; PARTITION_NAME SEGMENT_NAME BLOCKS --------------- --------------- ---------- ORD_P5 XIFENFEI_ORDERS 1024 ORD_P4 XIFENFEI_ORDERS 1024 ORD_P3 XIFENFEI_ORDERS 1024 ORD_P2 XIFENFEI_ORDERS 1024 ORD_P1 XIFENFEI_ORDERS 1024 --注意:这里只有user_tab_partitions.indexing为on的有记录,也就是说,至于这些分区的索引被创建,其他的未被创建 CDB_PDB@CHF> SELECT PARTITION_NAME,SEGMENT_NAME FROM USER_SEGMENTS WHERE SEGMENT_NAME='IND_LOX'; PARTITION_NAME SEGMENT_NAME --------------- --------------- ORD_P1 IND_LOX ORD_P3 IND_LOX
分析执行计划
CDB_PDB@CHF> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,'XIFENFEI_ORDERS',CASCADE=>TRUE); PL/SQL 过程已成功完成。 CDB_PDB@CHF> SET AUTOT TRACE CDB_PDB@CHF> SELECT ORDER_MODE FROM XIFENFEI_ORDERS WHERE ORDER_ADDRESS='www.xifenfei.com99'; 未选定行 执行计划 ---------------------------------------------------------- Plan hash value: 2800545636 ------------------------------------------------------------------------------------------------------------------------ -------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | ------------------------------------------------------------------------------------------------------------------------ -------- | 0 | SELECT STATEMENT | | 1 | 30 | 40 (0)| 00:00:01 | | | | 1 | VIEW | VW_TE_2 | 2 | 24 | 40 (0)| 00:00:01 | | | | 2 | UNION-ALL | | | | | | | | | 3 | PARTITION RANGE OR | | 1 | 34 | 1 (0)| 00:00:01 |KEY(OR)| KEY(OR)| |* 4 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| XIFENFEI_ORDERS | 1 | 34 | 1 (0)| 00:00:01 |KEY(OR)| KEY(OR)| |* 5 | INDEX RANGE SCAN | IND_LOX | 1 | | 1 (0)| 00:00:01 |KEY(OR)| KEY(OR)| | 6 | PARTITION RANGE OR | | 1 | 34 | 39 (0)| 00:00:01 |KEY(OR)| KEY(OR)| |* 7 | TABLE ACCESS FULL | XIFENFEI_ORDERS | 1 | 34 | 39 (0)| 00:00:01 |KEY(OR)| KEY(OR)| ------------------------------------------------------------------------------------------------------------------------ -------- Predicate Information (identified by operation id): --------------------------------------------------- 4 - filter("XIFENFEI_ORDERS"."ORDER_ID"<100 OR "XIFENFEI_ORDERS"."ORDER_ID">=200 AND "XIFENFEI_ORDERS"."ORDER_ID"<300) 5 - access("ORDER_ADDRESS"='www.xifenfei.com99') 7 - filter("ORDER_ADDRESS"='www.xifenfei.com99' AND ("XIFENFEI_ORDERS"."ORDER_ID">=300 AND "XIFENFEI_ORDERS"."ORDER_ID"<500 OR "XIFENFEI_ORDERS"."ORDER_ID"<200 AND "XIFENFEI_ORDERS"."ORDER_ID">=100)) --这里可以看到,当我们没有指定分区范围的时候,显示的执行计划是有分区index的部分直接走index,没有分区index的部分是扫描分区 统计信息 ---------------------------------------------------------- 34 recursive calls 0 db block gets 120 consistent gets 1 physical reads 0 redo size 347 bytes sent via SQL*Net to client 533 bytes received via SQL*Net from client 1 SQL*Net roundtrips to/from client 5 sorts (memory) 0 sorts (disk) 0 rows processed CDB_PDB@CHF> SELECT ORDER_MODE FROM XIFENFEI_ORDERS t WHERE ORDER_ADDRESS='www.xifenfei.com499' and order_id>200 and ord er_id<300; 未选定行 执行计划 ---------------------------------------------------------- Plan hash value: 3337708912 ------------------------------------------------------------------------------------------------------------------------ ------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| P stop | ------------------------------------------------------------------------------------------------------------------------ ------ | 0 | SELECT STATEMENT | | 1 | 34 | 2 (0)| 00:00:01 | | | | 1 | PARTITION RANGE SINGLE | | 1 | 34 | 2 (0)| 00:00:01 | 3 | 3 | |* 2 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| XIFENFEI_ORDERS | 1 | 34 | 2 (0)| 00:00:01 | 3 | 3 | |* 3 | INDEX RANGE SCAN | IND_LOX | 1 | | 1 (0)| 00:00:01 | 3 | 3 | ------------------------------------------------------------------------------------------------------------------------ ------ Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("ORDER_ID">200) 3 - access("ORDER_ADDRESS"='www.xifenfei.com499') --指定分区查询,可以明确的看到,该sql直接使用了分区索引 统计信息 ---------------------------------------------------------- 1 recursive calls 0 db block gets 1 consistent gets 0 physical reads 0 redo size 347 bytes sent via SQL*Net to client 533 bytes received via SQL*Net from client 1 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 0 rows processed CDB_PDB@CHF> SELECT ORDER_MODE FROM XIFENFEI_ORDERS t WHERE ORDER_ADDRESS='www.xifenfei.com499' and order_id>300 and ord er_id<400; 未选定行 执行计划 ---------------------------------------------------------- Plan hash value: 2072227240 ---------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | ---------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 34 | 14 (0)| 00:00:01 | | | | 1 | PARTITION RANGE SINGLE| | 1 | 34 | 14 (0)| 00:00:01 | 4 | 4 | |* 2 | TABLE ACCESS FULL | XIFENFEI_ORDERS | 1 | 34 | 14 (0)| 00:00:01 | 4 | 4 | ---------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("ORDER_ADDRESS"='www.xifenfei.com499' AND "ORDER_ID">300) --当指定的分区无index之时,直接判断走全表扫描 统计信息 ---------------------------------------------------------- 1 recursive calls 0 db block gets 22 consistent gets 0 physical reads 0 redo size 347 bytes sent via SQL*Net to client 533 bytes received via SQL*Net from client 1 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 0 rows processed
创建Global index
CDB_PDB@CHF> ALTER INDEX ind_lox INVISIBLE; 索引已更改。 CDB_PDB@CHF> CREATE INDEX IND_G_LOX ON XIFENFEI_ORDERS(ORDER_ADDRESS) Global INDEXING PARTIAL; 索引已创建。 CDB_PDB@CHF> select index_name,indexing from dba_indexes where index_name='IND_G_LOX'; INDEX_NAME INDEXIN ---------- ------- IND_G_LOX PARTIAL
执行计划
CDB_PDB@CHF> SELECT ORDER_MODE FROM XIFENFEI_ORDERS t WHERE ORDER_ADDRESS='www.xifenfei.com99'; 未选定行 执行计划 ---------------------------------------------------------- Plan hash value: 1912382893 ------------------------------------------------------------------------------------------------------------------------ -------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | ------------------------------------------------------------------------------------------------------------------------ -------- | 0 | SELECT STATEMENT | | 1 | 30 | 41 (0)| 00:00:01 | | | | 1 | VIEW | VW_TE_2 | 2 | 24 | 41 (0)| 00:00:01 | | | | 2 | UNION-ALL | | | | | | | | |* 3 | TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| XIFENFEI_ORDERS | 1 | 34 | 2 (0)| 00:00:01 | ROWID | ROWID | |* 4 | INDEX RANGE SCAN | IND_G_LOX | 1 | | 1 (0)| 00:00:01 | | | | 5 | PARTITION RANGE OR | | 1 | 34 | 39 (0)| 00:00:01 |KEY(OR)| KEY(OR)| |* 6 | TABLE ACCESS FULL | XIFENFEI_ORDERS | 1 | 34 | 39 (0)| 00:00:01 |KEY(OR)| KEY(OR)| ------------------------------------------------------------------------------------------------------------------------ -------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - filter("T"."ORDER_ID"<100 OR "T"."ORDER_ID">=200 AND "T"."ORDER_ID"<300) 4 - access("ORDER_ADDRESS"='www.xifenfei.com99') 6 - filter("ORDER_ADDRESS"='www.xifenfei.com99' AND ("T"."ORDER_ID">=300 AND "T"."ORDER_ID"<500 OR "T"."ORDER_ID"<200 AND "T"."ORDER_ID">=100)) --这里可以看到因为没有指定分区范围,该sql在含index的分区使用全局index,在没有index的分区直接使用全表扫描 统计信息 ---------------------------------------------------------- 1 recursive calls 0 db block gets 67 consistent gets 0 physical reads 0 redo size 347 bytes sent via SQL*Net to client 533 bytes received via SQL*Net from client 1 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 0 rows processed CDB_PDB@CHF> SELECT ORDER_MODE FROM XIFENFEI_ORDERS t WHERE ORDER_ADDRESS='www.xifenfei.com99' and order_id<100; 执行计划 ---------------------------------------------------------- Plan hash value: 3717359654 ------------------------------------------------------------------------------------------------------------------------ ------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| P stop | ------------------------------------------------------------------------------------------------------------------------ ------ | 0 | SELECT STATEMENT | | 1 | 31 | 2 (0)| 00:00:01 | | | |* 1 | TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| XIFENFEI_ORDERS | 1 | 31 | 2 (0)| 00:00:01 | 1 | 1 | |* 2 | INDEX RANGE SCAN | IND_G_LOX | 1 | | 1 (0)| 00:00:01 | | | ------------------------------------------------------------------------------------------------------------------------ ------ Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("ORDER_ID"<100) 2 - access("ORDER_ADDRESS"='www.xifenfei.com99') --指定了分区范围,而且该分区又有index,直接使用全局index 统计信息 ---------------------------------------------------------- 1 recursive calls 0 db block gets 2 consistent gets 0 physical reads 0 redo size 551 bytes sent via SQL*Net to client 544 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed CDB_PDB@CHF> SELECT ORDER_MODE FROM XIFENFEI_ORDERS t WHERE ORDER_ADDRESS='www.xifenfei.com99' and order_id>400 未选定行 执行计划 ---------------------------------------------------------- Plan hash value: 2072227240 ---------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | ---------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 34 | 14 (0)| 00:00:01 | | | | 1 | PARTITION RANGE SINGLE| | 1 | 34 | 14 (0)| 00:00:01 | 5 | 5 | |* 2 | TABLE ACCESS FULL | XIFENFEI_ORDERS | 1 | 34 | 14 (0)| 00:00:01 | 5 | 5 | ---------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("ORDER_ADDRESS"='www.xifenfei.com99' AND "ORDER_ID">400) --指定了分区范围,但是该分区无index,直接使用全表扫描 统计信息 ---------------------------------------------------------- 1 recursive calls 0 db block gets 22 consistent gets 0 physical reads 0 redo size 347 bytes sent via SQL*Net to client 533 bytes received via SQL*Net from client 1 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 0 rows processed
结论
通过测试,证明Partial Global/Local Indexes for Partitioned Tables确实能够实现对部分分区创建index。
1)如果查询条件确定的分区无index,那直接走全表扫描;
2)如果查询条件可以确定的分区范围内有index,会直接使用index(无论是Local还是GLobal);
3)如果查询条件未确定分区范围(含index和无index分区情况),那该sql会在有index分区使用index,在没有index区域走全表扫描
修改props$.NLS_CHARACTERSET导致ORA-00900异常恢复
今天一朋友和我说他的数据库不能open,open过程提示ORA-00900错误,通过分析alert日志和props$表,发现他们修改了一个无效的NLS_CHARACTERSET值,导致数据库无法正常启动(准确的说,因为数据库里面该值无效,当数据库open的过程中,检测到控制文件指定的编码和该值不一致,然后修改控制文件的编码,修改之后,数据库一到mount状态执行任何语句都报ORA-00900错误),通过一些工具修改NLS_CHARACTERSET为正确值该故障解决
重现ORA-00900故障
SQL> select * from v$version; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production PL/SQL Release 11.2.0.3.0 - Production CORE 11.2.0.3.0 Production TNS for Linux: Version 11.2.0.3.0 - Production NLSRTL Version 11.2.0.3.0 - Production SQL> select value$ from props$ where name='NLS_CHARACTERSET'; VALUE$ ------------------------------------------------------- ZHS16GBK SQL> update props$ set value$='AL16UTF16' where name='NLS_CHARACTERSET'; 1 row updated. SQL> commit; Commit complete. SQL> alter database backup controlfile to trace as '/tmp/ora11g.ctl'; Database altered. SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup ORACLE instance started. Total System Global Area 175775744 bytes Fixed Size 1343668 bytes Variable Size 117444428 bytes Database Buffers 50331648 bytes Redo Buffers 6656000 bytes Database mounted. ORA-01092: ORACLE instance terminated. Disconnection forced ORA-00604: error occurred at recursive SQL level 1 ORA-00900: invalid SQL statement Process ID: 5277 Session ID: 125 Serial number: 5 SQL> startup nomount; ORACLE instance started. Total System Global Area 175775744 bytes Fixed Size 1343668 bytes Variable Size 117444428 bytes Database Buffers 50331648 bytes Redo Buffers 6656000 bytes SQL> alter database mount; Database altered. SQL> alter database open; alter database open * ERROR at line 1: ORA-00900: invalid SQL statement SQL>select * from dual; select * from dual * ERROR at line 1: ORA-00900: invalid SQL statement SQL> shutdown abort ORACLE instance shut down.
第一次startup(open)过程报错
SMON: enabling tx recovery Updating character set in controlfile to AL16UTF16 Errors in file /u01/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_5277.trc: ORA-00604: error occurred at recursive SQL level %s ORA-00900: invalid SQL statementursive SQL level %s Errors in file /u01/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_5277.trc: ORA-00604: error occurred at recursive SQL level %s ORA-00900: invalid SQL statementursive SQL level %s Error 604 happened during db open, shutting down database USER (ospid: 5277): terminating the instance due to error 604 Errors in file /u01/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_smon_5243.trc: ORA-00604: error occurred at recursive SQL level %s ORA-00900: invalid SQL statementursive SQL level %s Instance terminated by USER, pid = 5277 ORA-1092 signalled during: ALTER DATABASE OPEN... opiodr aborting process unknown ospid (5277) as a result of ORA-1092 Sat May 18 00:44:27 2013 ORA-1092 : opitsk aborting process
这里比较明显的看到有一条(Updating character set in controlfile to AL16UTF16),正是由于这个操作,更新控制文件的编码为一个无效的编码,从而导致在后面数据库mount(加载控制文件)之后,就不能再进行其他任何操作
解决思路
使用odu找出来block位置,或者在同版本库中查询
使用dul或者bbed修改props$的NLS_CHARACTERSET值
重建控制文件(noresetlogs方式)
处理过程
SQL> shutdown abort ORACLE instance shut down. odu找出来block位置 dul或者bbed修改block值 重建控制文件(noresetlogs方式) SQL> startup ORACLE instance started. Total System Global Area 175775744 bytes Fixed Size 1343668 bytes Variable Size 117444428 bytes Database Buffers 50331648 bytes Redo Buffers 6656000 bytes Database mounted. ORA-01113: file 1 needs media recovery ORA-01110: data file 1: '/u01/oracle/oradata/ora11g/system01.dbf' SQL> recover database; Media recovery complete. SQL> alter database open; Database altered.
从oracle 9i开始,修改数据库的编码,直接使用alter database character set internal_use方式进行,而不要使用直接修改props$基表,更不能修改一个实际中不存在的编码值(本文中数据库编码和国家编码搞混淆从而出现该故障)