2018年8月 – 第2页

PostgreSQL逻辑导出导入

pg_dump命令说明

C:\Users\Administrator>pg_dump --help
pg_dump 把一个数据库转储为纯文本文件或者是其它格式.
用法:
  pg_dump [选项]... [数据库名字]
一般选项:
  -f, --file=FILENAME          输出文件或目录名
  -F, --format=c|d|t|p         输出文件格式 (定制, 目录, tar)
                               明文 (默认值))
  -j, --jobs=NUM               执行多个并行任务进行备份转储工作
  -v, --verbose                详细模式
  -V, --version                输出版本信息，然后退出
  -Z, --compress=0-9           被压缩格式的压缩级别
  --lock-wait-timeout=TIMEOUT  在等待表锁超时后操作失败
  --no-sync                    do not wait for changes to be written safely to disk
  -?, --help                   显示此帮助, 然后退出
控制输出内容选项:
  -a, --data-only              只转储数据,不包括模式
  -b, --blobs                  在转储中包括大对象
  -B, --no-blobs               exclude large objects in dump
  -c, --clean                  在重新创建之前，先清除（删除）数据库对象
  -C, --create                 在转储中包括命令,以便创建数据库
  -E, --encoding=ENCODING      转储以ENCODING形式编码的数据
  -n, --schema=SCHEMA          只转储指定名称的模式
  -N, --exclude-schema=SCHEMA  不转储已命名的模式
  -o, --oids                   在转储中包括 OID
  -O, --no-owner               在明文格式中, 忽略恢复对象所属者
  -s, --schema-only            只转储模式, 不包括数据
  -S, --superuser=NAME         在明文格式中使用指定的超级用户名
  -t, --table=TABLE            只转储指定名称的表
  -T, --exclude-table=TABLE    不转储指定名称的表
  -x, --no-privileges          不要转储权限 (grant/revoke)
  --binary-upgrade             只能由升级工具使用
  --column-inserts             以带有列名的INSERT命令形式转储数据
  --disable-dollar-quoting     取消美元 (符号) 引号, 使用 SQL 标准引号
  --disable-triggers           在只恢复数据的过程中禁用触发器
  --enable-row-security        启用行安全性（只转储用户能够访问的内容）
  --exclude-table-data=TABLE   不转储指定名称的表中的数据
  --if-exists              当删除对象时使用IF EXISTS
  --inserts                    以INSERT命令，而不是COPY命令的形式转储数据
  --no-publications            do not dump publications
  --no-security-labels         不转储安全标签的分配
  --no-subscriptions           do not dump subscriptions
  --no-synchronized-snapshots  在并行工作集中不使用同步快照
  --no-tablespaces             不转储表空间分配信息
  --no-unlogged-table-data     不转储没有日志的表数据
  --quote-all-identifiers      所有标识符加引号，即使不是关键字
  --section=SECTION            备份命名的节 (数据前, 数据, 及 数据后)
  --serializable-deferrable   等到备份可以无异常运行
  --snapshot=SNAPSHOT          为转储使用给定的快照
  --strict-names               要求每个表和/或schema包括模式以匹配至少一个实体
  --use-set-session-authorization
                               使用 SESSION AUTHORIZATION 命令代替
                ALTER OWNER 命令来设置所有权
联接选项:
  -d, --dbname=DBNAME       对数据库 DBNAME备份
  -h, --host=主机名        数据库服务器的主机名或套接字目录
  -p, --port=端口号        数据库服务器的端口号
  -U, --username=名字      以指定的数据库用户联接
  -w, --no-password        永远不提示输入口令
  -W, --password           强制口令提示 (自动)
  --role=ROLENAME          在转储前运行SET ROLE
如果没有提供数据库名字, 那么使用 PGDATABASE 环境变量
的数值.
报告错误至 <pgsql-bugs@postgresql.org>.
C:\Users\Administrator>

导出环境准备

C:\Users\Administrator>psql -U u_xifenfei -d db_xifenfei
用户 u_xifenfei 的口令：
psql (10.4)
输入 "help" 来获取帮助信息.
db_xifenfei=# create table t_dump as select * from pg_tables;
SELECT 69
db_xifenfei=# insert into t_dump select * from t_dump;
INSERT 0 69
…………
db_xifenfei=# insert into t_dump select * from t_dump;
INSERT 0 565248
db_xifenfei=# create table public.t_dump_pub as select * from t_dump;
SELECT 1130496
db_xifenfei=# select count(*) from t_dump;
  count
---------
 1130496
(1 行记录)
db_xifenfei=# select count(*) from public.t_dump_pub;
  count
---------
 1130496
(1 行记录)

导出命令

pg_dump  -U u_xifenfei -f e:/u_xifenfei.sql db_xifenfei
pg_dump  -U u_xifenfei -f e:/u_xifenfei2.sql --inserts db_xifenfei

psql导入说明

C:\Users\Administrator>psql -?
psql是PostgreSQL 的交互式客户端工具。
使用方法:
  psql [选项]... [数据库名称 [用户名称]]
通用选项:
  -c,--command=命令        执行单一命令(SQL或内部指令)然后结束
 -d, --dbname=数据库名称   指定要连接的数据库 (默认："Administrator")
  -f, --file=文件名      从文件中执行命令然后退出
  -l, --list             列出所有可用的数据库,然后退出
  -v, --set=, --variable=NAME=VALUE
                           设置psql变量NAME为VALUE
                           (例如，-v ON_ERROR_STOP=1)
  -V, --version            输出版本信息, 然后退出
  -X, --no-psqlrc         不读取启动文档(~/.psqlrc)
  -1 ("one"), --single-transaction
                          作为一个单一事务来执行命令文件(如果是非交互型的)
  -?, --help[=options]     显示此帮助，然后退出
      --help=commands      列出反斜线命令，然后退出
      --help=variables     列出特殊变量，然后退出
输入和输出选项：
  -a, --echo-all          显示所有来自于脚本的输入
  -b, --echo-errors        回显失败的命令
  -e, --echo-queries      显示发送给服务器的命令
 -E, --echo-hidden        显示内部命令产生的查询
  -L, --log-file=文件名  将会话日志写入文件
  -n, --no-readline       禁用增强命令行编辑功能(readline)
  -o, --output=FILENAME 将查询结果写入文件(或 |管道)
  -q, --quiet             以沉默模式运行(不显示消息，只有查询结果)
  -s, --single-step       单步模式 (确认每个查询)
  -S, --single-line        单行模式 (一行就是一条 SQL 命令)
输出格式选项 :
 -A, --no-align           使用非对齐表格输出模式
  -F, --field-separator=STRING
             为字段设置分隔符,用于不整齐的输出(默认："|")
  -H, --html             HTML 表格输出模式
  -P, --pset=变量[=参数]    设置将变量打印到参数的选项(查阅 \pset 命令)
  -R, --record-separator=STRING
             为不整齐的输出设置字录的分隔符(默认：换行符号)
  -t, --tuples-only      只打印记录i
  -T, --table-attr=文本   设定 HTML 表格标记属性（例如,宽度,边界)
  -x, --expanded           打开扩展表格输出
  -z, --field-separator-zero
                           为不整齐的输出设置字段分隔符为字节0
  -0, --record-separator-zero
                           为不整齐的输出设置记录分隔符为字节0
联接选项:
  -h, --host=主机名        数据库服务器主机或socket目录(默认："本地接口")
  -p, --port=端口        数据库服务器的端口(默认："5432")
  -U, --username=用户名    指定数据库用户名(默认："Administrator")
  -w, --no-password       永远不提示输入口令
  -W, --password           强制口令提示 (自动)
更多信息，请在psql中输入"\?"(用于内部指令)或者 "\help"(用于SQL命令)，
或者参考PostgreSQL文档中的psql章节.
臭虫报告至 <pgsql-bugs@postgresql.org>.

导入操作

C:\Users\Administrator>psql -U u_xifenfei -d db_xifenfei
用户 u_xifenfei 的口令：
psql (10.4)
输入 "help" 来获取帮助信息.
db_xifenfei=# create database test_pump;
CREATE DATABASE
C:\Users\Administrator>psql -d test_pump -U u_xifenfei -f e:/u_xifenfei.sql
用户 u_xifenfei 的口令：
SET
SET
SET
SET
SET
 set_config
------------
(1 行记录)
SET
SET
SET
CREATE SCHEMA
ALTER SCHEMA
CREATE EXTENSION
COMMENT
SET
SET
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
COPY 1130496
COPY 1130496

导出/导入数据核对

C:\Users\Administrator>psql -d test_pump -U u_xifenfei
用户 u_xifenfei 的口令：
psql (10.4)
输入 "help" 来获取帮助信息.
test_pump=# \d
                   关联列表
  架构模式  |    名称    |  类型  |   拥有者
------------+------------+--------+------------
 public     | t_dump_pub | 数据表 | u_xifenfei
 u_xifenfei | t_dump     | 数据表 | u_xifenfei
(2 行记录)

PostgreSQL模式理解

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：PostgreSQL模式理解

在PostgreSQL中schema和oracle中不一样,在oracle中schema基本上可以理解为user,但是在PostgreSQL中user和schema没有严格意义上的对应关系.
创建模式
这里可以看到使用u_xifenfei创建的schema就是属于该用户,默认的public schema属于postgres用户.

db_xifenfei=# \c
您现在已经连接到数据库 "db_xifenfei",用户 "u_xifenfei".
db_xifenfei=# create schema u_xifenfei;
CREATE SCHEMA
db_xifenfei=# \dn
      架构模式列表
    名称    |   拥有者
------------+------------
 public     | postgres
 u_xifenfei | u_xifenfei
(2 行记录)

数据库检索schema的顺序
默认情况下优先检索和用户名一致的schema,然后是public schema

db_xifenfei=# show search_path;
   search_path
-----------------
 "$user", public
(1 行记录)

测试schema的检索顺序
这里比较明显优先使用/找到的是u_xifenfei schema（和用户名匹配的schema),然后再是默认的public schema

db_xifenfei=# create table t_xifenfei as select * from pg_database;
SELECT 4
db_xifenfei=# select pg_relation_filepath('t_xifenfei');
            pg_relation_filepath
---------------------------------------------
 pg_tblspc/16406/PG_10_201707211/16407/16421
(1 行记录)
db_xifenfei=# select count(*) from t_xifenfei;
 count
-------
     4
(1 行记录)
db_xifenfei=# select schemaname,tablename,tableowner,tablespace
db_xifenfei-# from pg_tables where tableowner='u_xifenfei';
 schemaname |  tablename  | tableowner | tablespace
------------+-------------+------------+------------
 public     | t_xifenfei  | u_xifenfei |
 u_xifenfei | t_xifenfei  | u_xifenfei |
(3 行记录)
--public.t_xifenfei和public.t_xifenfei2昨天创建
db_xifenfei=# select count(*) from public.t_xifenfei;
  count
---------
 1310720
(1 行记录)
db_xifenfei=# drop table t_xifenfei;
DROP TABLE
db_xifenfei=# select count(*) from t_xifenfei;
  count
---------
 1310720
(1 行记录)

PostgreSQL的表空间、数据库、用户之间的关系

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：PostgreSQL的表空间、数据库、用户之间的关系

玩多了Oracle,习惯了使用Oracle的体系架构去对比别的数据库,今天看PostgreSQL发现两者明显不一样:
1. 在数据库/表空间/schema三者关系上的区别

表空间是物理结构，同一表空间下可以有多个数据库
数据库是逻辑结构，是表／索引／视图／存储过程的集合，一个数据库下可以有多个schema
模式是逻辑结构，是对数据库的逻辑划分
2. 在oracle中用户和schema基本上可以画上等同关系,但是pg中两者没有这样严格的对应关系

相关测试实验
创建用户
在pg中role比user少login,其他基本上相同(也就是说如果给role授权login,等同user)

postgres=# CREATE USER u_xifenfei WITH
postgres-# LOGIN
postgres-# SUPERUSER
postgres-# CREATEDB
postgres-# CREATEROLE
postgres-# INHERIT
postgres-# REPLICATION
postgres-# CONNECTION LIMIT -1
postgres-# PASSWORD 'xifenfei';
CREATE ROLE

创建表空间

postgres=# CREATE TABLESPACE tbs_xifenfei
postgres-#   OWNER u_xifenfei
postgres-#   LOCATION 'D:\Program Files\PostgreSQL\tbs_xifenfei';
CREATE TABLESPACE

创建数据库

postgres=# CREATE DATABASE db_xifenfei
postgres-#     WITH
postgres-#     OWNER = u_xifenfei
postgres-#     ENCODING = 'UTF8'
postgres-#     TABLESPACE = tbs_xifenfei
postgres-#     CONNECTION LIMIT = -1;
CREATE DATABASE

查询数据库和表空间信息

postgres=# select oid, datname, datlastsysoid, dattablespace
postgres-#  from pg_catalog.pg_database order by 1,2;
  oid  |   datname   | datlastsysoid | dattablespace
-------+-------------+---------------+---------------
     1 | template1   |         12937 |          1663
 12937 | template0   |         12937 |          1663
 12938 | postgres    |         12937 |          1663
 16407 | db_xifenfei |         12937 |         16406
(4 行记录)
postgres=# select oid,* from pg_catalog.pg_tablespace;
  oid  |   spcname    | spcowner | spcacl | spcoptions
-------+--------------+----------+--------+------------
  1663 | pg_default   |       10 |        |
  1664 | pg_global    |       10 |        |
 16406 | tbs_xifenfei |    16405 |        |
(3 行记录)

使用u_xifenfei用户登录

C:\Users\Administrator>psql -U u_xifenfei -d db_xifenfei
用户 u_xifenfei 的口令：
psql (10.4)
输入 "help" 来获取帮助信息.
db_xifenfei=# \c
您现在已经连接到数据库 "db_xifenfei",用户 "u_xifenfei".

创建测试表

db_xifenfei=# create table t_xifenfei as select * from pg_database;
SELECT 5
db_xifenfei=# select pg_relation_filepath('t_xifenfei');
            pg_relation_filepath
---------------------------------------------
 pg_tblspc/16406/PG_10_201707211/16407/16408
(1 行记录)
db_xifenfei=# insert into t_xifenfei select * from t_xifenfei;
INSERT 0 5
…………
db_xifenfei=# insert into t_xifenfei select * from t_xifenfei;
INSERT 0 327680
db_xifenfei=# select count(*) from t_xifenfei;
  count
---------
 1310720
(1 行记录)

这里可以发现,创建表空间其实对应的是一个文件夹路径,创建数据库是在对应的表空间中创建相关目录和文件,创建表是对应的相关文件.
参考:PostgreSQL tablespace database schema

记录正常open库报ORA-600 2662

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：记录正常open库报ORA-600 2662

数据库版本10.2.0.3 32位

SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod
PL/SQL Release 10.2.0.3.0 - Production
CORE    10.2.0.3.0      Production
TNS for 32-bit Windows: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production

数据库启动alert日志报大量ORA-600和ORA-07445错误

Tue Jul 31 09:56:45 2018
Started redo application at
 Thread 1: logseq 7593, block 46691
Tue Jul 31 09:56:45 2018
Recovery of Online Redo Log: Thread 1 Group 2 Seq 7593 Reading mem 0
  Mem# 0: E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO02.LOG
Tue Jul 31 09:56:45 2018
RECOVERY OF THREAD 1 STUCK AT BLOCK 779 OF FILE 2
Tue Jul 31 09:56:45 2018
RECOVERY OF THREAD 1 STUCK AT BLOCK 4430 OF FILE 2
Tue Jul 31 09:56:45 2018
Hex dump of (file 3, block 23704) in trace file e:\oracle\product\10.2.0\admin\orcl\bdump\orcl_p002_248.trc
Corrupt block relative dba: 0x00c05c98 (file 3, block 23704)
Fractured block found during crash/instance recovery
Data in bad block:
 type: 6 format: 2 rdba: 0x00c05c98
 last change scn: 0x0000.05c3cad2 seq: 0x16 flg: 0x06
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0xe4be0601
 check value in block header: 0x5c03
 computed block checksum: 0x2e7b
Tue Jul 31 09:56:45 2018
Completed redo application
Tue Jul 31 09:56:46 2018
Reread of rdba: 0x00c05c98 (file 3, block 23704) found same corrupted data
RECOVERY OF THREAD 1 STUCK AT BLOCK 40841 OF FILE 8
Tue Jul 31 09:56:46 2018
RECOVERY OF THREAD 1 STUCK AT BLOCK 297 OF FILE 2
Tue Jul 31 09:56:46 2018
Errors in file e:\oracle\product\10.2.0\admin\orcl\bdump\orcl_p002_248.trc:
ORA-00600: internal error code, arguments: [kssadpm1], [], [], [], [], [], [], []
Tue Jul 31 09:56:46 2018
Errors in file e:\oracle\product\10.2.0\admin\orcl\bdump\orcl_p001_2320.trc:
ORA-00600: internal error code, arguments: [545], [0xCA15AE84], [3], [16], [], [], [], []
ORA-00600: internal error code, arguments: [545], [0xCA15AE84], [3], [16], [], [], [], []
ORA-00600: internal error code, arguments: [545], [0xCA15AE84], [3], [16], [], [], [], []
ORA-00600: internal error code, arguments: [545], [0xCA15AE84], [3], [8], [], [], [], []
Tue Jul 31 09:56:46 2018
Errors in file e:\oracle\product\10.2.0\admin\orcl\bdump\orcl_p004_2116.trc:
ORA-00600: internal error code, arguments: [kssdmc: null so], [], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [kssdmc: null so], [], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [kssdmc: null so], [], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [kssdmc: null so], [], [], [], [], [], [], []
ORA-07445: exception encountered: core dump [ACCESS_VIOLATION] [unable_to_trans_pc] [PC:0x7C3429C1] [ADDR:0xCA800000] [UNABLE_TO_READ] []
Tue Jul 31 09:56:46 2018
Errors in file e:\oracle\product\10.2.0\admin\orcl\bdump\orcl_p000_220.trc:
ORA-00600: internal error code, arguments: [ksfdchkfobrerr1], [0xBB9852DC], [0xA7EC530C], [], [], [], [], []
Tue Jul 31 09:56:46 2018
Errors in file e:\oracle\product\10.2.0\admin\orcl\bdump\orcl_p003_252.trc:
ORA-00600: internal error code, arguments: [kssrc_test_cleanup:popall], [0xCA000304], [], [], [], [], [], []
Tue Jul 31 09:56:46 2018
Errors in file e:\oracle\product\10.2.0\admin\orcl\bdump\orcl_p001_228.trc:
ORA-07445: exception encountered: core dump [ACCESS_VIOLATION] [_kghalf+895] [PC:0x603BEBF3] [ADDR:0xFFFFFFF8] [UNABLE_TO_READ] []
Tue Jul 31 09:56:46 2018
Errors in file e:\oracle\product\10.2.0\admin\orcl\bdump\orcl_lgwr_1848.trc:
ORA-07445: exception encountered: core dump [ACCESS_VIOLATION] [_ksarcv+243] [PC:0x5B2223] [ADDR:0x206C000C] [UNABLE_TO_READ] []
Tue Jul 31 09:56:47 2018
Errors in file e:\oracle\product\10.2.0\admin\orcl\bdump\orcl_p000_220.trc:
ORA-07445: exception encountered: core dump [ACCESS_VIOLATION] [_kslwlmod+166] [PC:0x46946A] [ADDR:0x54F8] [UNABLE_TO_WRITE] []
ORA-00081: address range [0x75C80047, 0x75C8004B) is not readable
ORA-00600: internal error code, arguments: [ksfdchkfobrerr1], [0xBB9852DC], [0xA7EC530C], [], [], [], [], []
Tue Jul 31 09:56:47 2018
Errors in file e:\oracle\product\10.2.0\admin\orcl\bdump\orcl_ckpt_1852.trc:
ORA-07445: exception encountered: core dump [ACCESS_VIOLATION] [_kcbs_dump_adv_state+471] [PC:0x59B403] [ADDR:0xCC60CBAD] [UNABLE_TO_READ] []
ORA-00600: internal error code, arguments: [kssrc_test_cleanup:popall], [0xCA000304], [], [], [], [], [], []
Tue Jul 31 09:56:47 2018
Errors in file e:\oracle\product\10.2.0\admin\orcl\bdump\orcl_pmon_1828.trc:
ORA-07445: exception encountered: core dump [ACCESS_VIOLATION] [_kews_idle_wait+378] [PC:0x60BE2E] [ADDR:0x1820D468] [UNABLE_TO_WRITE] []
Tue Jul 31 09:56:47 2018
Errors in file e:\oracle\product\10.2.0\admin\orcl\bdump\orcl_p004_236.trc:
ORA-00081: address range [0x75C80041, 0x75C80045) is not readable
ORA-07445: exception encountered: core dump [ACCESS_VIOLATION] [unable_to_trans_pc] [PC:0x7C3429C1] [ADDR:0xCA800000] [UNABLE_TO_READ] []

检查主要坏块
主要影响数据库恢复的坏块,system不言而喻,block 2也比较敏感

E:\>dbv file=E:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\TTTS.DAT
DBVERIFY: Release 10.2.0.3.0 - Production on 星期三 8月 1 00:24:52 2018
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
DBVERIFY - 开始验证: FILE = E:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\TTTS.DAT
页 2 流入 - 很可能是介质损坏
Corrupt block relative dba: 0x02000002 (file 8, block 2)
Fractured block found during dbv:
Data in bad block:
 type: 29 format: 2 rdba: 0x02000002
 last change scn: 0x0000.05c45a54 seq: 0x2 flg: 0x04
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0x9e601d02
 check value in block header: 0xea86
 computed block checksum: 0xc434
DBVERIFY - 验证完成
检查的页总数: 121600
处理的页总数 (数据): 58606
失败的页总数 (数据): 0
处理的页总数 (索引): 45192
失败的页总数 (索引): 0
处理的页总数 (其它): 4453
处理的总页数 (段)  : 0
失败的总页数 (段)  : 0
空的页总数: 13348
标记为损坏的总页数: 1
流入的页总数: 1
最高块 SCN            : 96716847 (0.96716847)
E:\>dbv FILE = E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF
DBVERIFY: Release 10.2.0.3.0 - Production on 星期三 8月 1 00:28:28 2018
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
DBVERIFY - 开始验证: FILE = E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF
页 68377 流入 - 很可能是介质损坏
Corrupt block relative dba: 0x00410b19 (file 1, block 68377)
Fractured block found during dbv:
Data in bad block:
 type: 6 format: 2 rdba: 0x00410b19
 last change scn: 0x0000.05c45bdf seq: 0x1 flg: 0x06
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0x4f3b0601
 check value in block header: 0x465b
 computed block checksum: 0x11c7
DBV-00200: 块 dba 4262777 已标记为损坏
DBVERIFY - 验证完成
检查的页总数: 74240
处理的页总数 (数据): 46209
失败的页总数 (数据): 0
处理的页总数 (索引): 9729
失败的页总数 (索引): 0
处理的页总数 (其它): 1923
处理的总页数 (段)  : 0
失败的总页数 (段)  : 0
空的页总数: 16378
标记为损坏的总页数: 2
流入的页总数: 1
最高块 SCN            : 96757289 (0.96757289)

尝试recover数据库,open数据库

E:\>sqlplus / as sysdba
SQL*Plus: Release 10.2.0.3.0 - Production on 星期二 7月 31 15:40:05 2018
Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.
连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> recover database;
完成介质恢复。
SQL> alter database open;
数据库已更改。

数据库报ORA-600 2662错误
该数据库没有增加隐含隐含参数(屏蔽一致性,屏蔽事务),数据库直接启动之后报ORA-600 2662

Tue Jul 31 15:40:15 2018
SMON: enabling cache recovery
Tue Jul 31 15:40:16 2018
Successfully onlined Undo Tablespace 1.
Tue Jul 31 15:40:16 2018
SMON: enabling tx recovery
Tue Jul 31 15:40:16 2018
Database Characterset is ZHS16GBK
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
QMNC started with pid=32, OS id=1152
Tue Jul 31 15:40:17 2018
Errors in file e:\oracle\product\10.2.0\admin\orcl\bdump\orcl_smon_1288.trc:
ORA-00600: internal error code, arguments: [2662], [0], [96736891], [0], [96752794], [4264138], [], []
Tue Jul 31 15:40:18 2018
Non-fatal internal error happenned while SMON was doing logging scn->time mapping.
SMON encountered 1 out of maximum 100 non-fatal internal errors.
Tue Jul 31 15:40:18 2018
Errors in file e:\oracle\product\10.2.0\admin\orcl\bdump\orcl_smon_1288.trc:
ORA-00600: internal error code, arguments: [2662], [0], [96736892], [0], [96752794], [4264138], [], []
Non-fatal internal error happenned while SMON was doing extent coalescing.
SMON encountered 3 out of maximum 100 non-fatal internal errors.
Tue Jul 31 15:40:19 2018
db_recovery_file_dest_size of 2048 MB is 0.00% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
Hex dump of (file 8, block 2) in trace file e:\oracle\product\10.2.0\admin\orcl\bdump\orcl_mmon_1740.trc
Corrupt block relative dba: 0x02000002 (file 8, block 2)
Fractured block found during buffer read
Data in bad block:
 type: 29 format: 2 rdba: 0x02000002
 last change scn: 0x0000.05c45a54 seq: 0x2 flg: 0x04
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0x9e601d02
 check value in block header: 0xea86
 computed block checksum: 0xc434
Reread of rdba: 0x02000002 (file 8, block 2) found same corrupted data
Tue Jul 31 15:40:20 2018
Errors in file e:\oracle\product\10.2.0\admin\orcl\bdump\orcl_smon_1288.trc:
ORA-00600: internal error code, arguments: [2662], [0], [96736902], [0], [96752794], [4264138], [], []
Tue Jul 31 15:40:20 2018
Completed: alter database open
Tue Jul 31 15:40:20 2018
Non-fatal internal error happenned while SMON was doing extent coalescing.
SMON encountered 4 out of maximum 100 non-fatal internal errors.
Tue Jul 31 15:40:21 2018
Errors in file e:\oracle\product\10.2.0\admin\orcl\bdump\orcl_smon_1288.trc:
ORA-00600: internal error code, arguments: [2662], [0], [96736922], [0], [96752794], [4264138], [], []
Non-fatal internal error happenned while SMON was doing extent coalescing.
SMON encountered 5 out of maximum 100 non-fatal internal errors.
Tue Jul 31 15:40:22 2018
Errors in file e:\oracle\product\10.2.0\admin\orcl\bdump\orcl_j000_1076.trc:
ORA-00600: 内部错误代码, 参数: [2662], [0], [96736954], [0], [96754130], [12615382], [], []
Tue Jul 31 15:40:22 2018
DEBUG: Replaying xcb 0xbba2d2e4, pmd 0xab1920fc for failed op 8
Reconstructing  Uhdr 0x800019 for xcb 0xbba2d2e4, pmd 0xab1920fc
Doing block recovery for file 2 block 25
Block recovery from logseq 7594, block 63 to scn 96736910
Tue Jul 31 15:40:22 2018
Recovery of Online Redo Log: Thread 1 Group 3 Seq 7594 Reading mem 0
  Mem# 0: E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO03.LOG
Block recovery completed at rba 7594.82.16, scn 0.96736911
DEBUG: Restoring block headers for xcb 0xbba2d2e4, pmd 0xab1920fc
DEBUG: Finished replay for xcb 0xbba2d2e4, pmd 0xab1920fc for failed op 8
Tue Jul 31 15:40:22 2018
Errors in file e:\oracle\product\10.2.0\admin\orcl\bdump\orcl_j000_1076.trc:
ORA-00603: ORACLE 服务器会话因致命错误而终止
ORA-00600: 内部错误代码, 参数: [2662], [0], [96736955], [0], [96754130], [12615382], [], []
ORA-00600: 内部错误代码, 参数: [2662], [0], [96736954], [0], [96754130], [12615382], [], []
Tue Jul 31 15:40:31 2018
Errors in file e:\oracle\product\10.2.0\admin\orcl\bdump\orcl_smon_1288.trc:
ORA-00600: internal error code, arguments: [2662], [0], [96736963], [0], [96752794], [4264138], [], []
Tue Jul 31 15:40:31 2018
Non-fatal internal error happenned while SMON was doing extent coalescing.
SMON encountered 6 out of maximum 100 non-fatal internal errors.
Tue Jul 31 15:40:41 2018
Errors in file e:\oracle\product\10.2.0\admin\orcl\bdump\orcl_smon_1288.trc:
ORA-00600: internal error code, arguments: [2662], [0], [96736967], [0], [96752794], [4264138], [], []
Tue Jul 31 15:41:03 2018
Errors in file e:\oracle\product\10.2.0\admin\orcl\bdump\orcl_pmon_1448.trc:
ORA-00474: SMON process terminated with error
Tue Jul 31 15:41:03 2018
PMON: terminating instance due to error 474
Tue Jul 31 15:41:03 2018
Errors in file e:\oracle\product\10.2.0\admin\orcl\bdump\orcl_j004_1344.trc:
ORA-00474: SMON process terminated with error
Tue Jul 31 15:41:03 2018
Errors in file e:\oracle\product\10.2.0\admin\orcl\bdump\orcl_j003_2104.trc:
ORA-00474: SMON process terminated with error
Tue Jul 31 15:41:03 2018
Errors in file e:\oracle\product\10.2.0\admin\orcl\bdump\orcl_j002_932.trc:
ORA-00474: SMON process terminated with error
Tue Jul 31 15:41:04 2018
Errors in file e:\oracle\product\10.2.0\admin\orcl\bdump\orcl_j001_1680.trc:
ORA-00474: SMON process terminated with error
Tue Jul 31 15:41:04 2018
Errors in file e:\oracle\product\10.2.0\admin\orcl\bdump\orcl_q000_2336.trc:
ORA-00474: SMON process terminated with error
Tue Jul 31 15:41:05 2018
Errors in file e:\oracle\product\10.2.0\admin\orcl\bdump\orcl_lgwr_552.trc:
ORA-00474: SMON process terminated with error
Tue Jul 31 15:41:05 2018
Errors in file e:\oracle\product\10.2.0\admin\orcl\bdump\orcl_dbw0_1420.trc:
ORA-00474: SMON process terminated with error
Tue Jul 31 15:41:05 2018
Errors in file e:\oracle\product\10.2.0\admin\orcl\bdump\orcl_dbw1_2556.trc:
ORA-00474: SMON process terminated with error
Tue Jul 31 15:41:05 2018
Errors in file e:\oracle\product\10.2.0\admin\orcl\bdump\orcl_psp0_1812.trc:
ORA-00474: SMON process terminated with error
Tue Jul 31 15:41:05 2018
Errors in file e:\oracle\product\10.2.0\admin\orcl\bdump\orcl_mman_1924.trc:
ORA-00474: SMON process terminated with error
Tue Jul 31 15:41:05 2018
Errors in file e:\oracle\product\10.2.0\admin\orcl\bdump\orcl_ckpt_2940.trc:
ORA-00474: SMON process terminated with error
Tue Jul 31 15:41:10 2018
Errors in file e:\oracle\product\10.2.0\admin\orcl\bdump\orcl_reco_660.trc:
ORA-00474: SMON process terminated with error
Instance terminated by PMON, pid = 1448
Tue Jul 31 15:41:57 2018

这是一个不常见的错误,没有使用隐含参数强制拉库,数据库正常open成功,但是由于system坏块,导致数据库启动之后报ORA-600 2662错误(再次证明2662不一定只有强制拉库发生,正常open的库也有可能,主要取决block scn和datafile scn,如果不是open过程必须要访问的block,那可能在open之后由于访问需要再报出来该错误).这个问题比较简单,因为open成功之后再crash,而且该坏块引起smon报错但是并没有ora-600 4xxx相关错误,因此根据经验,直接在数据库open之后,处理掉system异常报错对象和坏块对象即可.另外block 2 比较特殊,需要对其上面的对象进行处理

Tue Jul 31 15:55:54 2018
Hex dump of (file 8, block 2) in trace file e:\oracle\product\10.2.0\admin\orcl\udump\orcl_ora_3664.trc
Corrupt block relative dba: 0x02000002 (file 8, block 2)
Completely zero block found during reading space header
Reread of blocknum=2, file=E:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\TTTS.DAT. found same corrupt data
Reread of blocknum=2, file=E:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\TTTS.DAT. found same corrupt data
Reread of blocknum=2, file=E:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\TTTS.DAT. found same corrupt data
Reread of blocknum=2, file=E:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\TTTS.DAT. found same corrupt data
Reread of blocknum=2, file=E:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\TTTS.DAT. found same corrupt data
Tue Jul 31 15:55:54 2018
Errors in file e:\oracle\product\10.2.0\admin\orcl\udump\orcl_ora_3664.trc:
ORA-07445: 出现异常错误: 核心转储 [ACCESS_VIOLATION] [_krbodmpcx+243] [PC:0x2317857] [ADDR:0x8] [UNABLE_TO_READ] []
ORA-19880: 数据文件 E:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\TTTS.DAT 的空间标头已损坏, 块 2 备份已中止

补充ora-600 2662 block记录

          ----------------------------------------
          SO: BB97A97C, type: 24, owner: BB882B10, flag: INIT/-/-/0x00
          (buffer) (CR) PR: BB8029D8 FLG: 0x100000
          class bit: 00000000
          kcbbfbp: [BH: B9FCF268, LINK: BB97A9A0]
          where: kdswh06: kdscgr, why: 0
          BH (B9FCF268) file#: 1 rdba: 0x004110ca (1/69834) class: 1 ba: B9832000
            set: 9 blksize: 8192 bsi: 0 set-flg: 2 pwbcnt: 0
            dbwrid: 0 obj: 573 objn: 575 tsn: 0 afn: 1
            hash: [bb9151ec,bb9151ec] lru: [b9fcf378,b9fcf200]
            ckptq: [NULL] fileq: [NULL] objq: [b9fcf3d0,aa776524]
            use: [bb97a9a0,bb97a9a0] wait: [NULL]
            st: XCURRENT md: SHR tch: 0
            flags:
            LRBA: [0x0.0.0] HSCN: [0xffff.ffffffff] HSUB: [65535]
            Using State Objects
              ----------------------------------------
              SO: BB97A97C, type: 24, owner: BB882B10, flag: INIT/-/-/0x00
              (buffer) (CR) PR: BB8029D8 FLG: 0x100000
              class bit: 00000000
              kcbbfbp: [BH: B9FCF268, LINK: BB97A9A0]
              where: kdswh06: kdscgr, why: 0
            buffer tsn: 0 rdba: 0x004110ca (1/69834)
            scn: 0x0000.05c4549a seq: 0x01 flg: 0x06 tail: 0x549a0601
            frmt: 0x02 chkval: 0xc60a type: 0x06=trans data

月度归档：8月 2018