PostgreSQL VS Oracle Crash Recovery - 2

digoal

接第一部分:

Oracle测试:

字符集介绍 :UTF-8 Encoding

The UTF-8 character codes in Table B-2 show that the following conditions are true:

    *

      ASCII characters use 1 byte
    *

      European (except ASCII), Arabic, and Hebrew characters require 2 bytes
    *

      Indic, Thai, Chinese, Japanese, and Korean characters as well as certain symbols such as the euro symbol require 3 bytes
    *

      Characters in the Private Use Area #1 require 3 bytes
    *

      Supplementary characters require 4 bytes
    *

      Characters in the Private Use Area #2 require 4 bytes

Oracle's AL32UTF8 character set supports 1-byte, 2-byte, 3-byte, and 4-byte values. Oracle's UTF8 character set supports 1-byte, 2-byte, and 3-byte values, but not 4-byte values.




SQL> create user test default tablespace tbs_test identified by "test";

User created.

SQL> grant connect,resource to test;

Grant succeeded.

SQL> conn test/test
Connected.

SQL> set timing on

SQL> create table test.tbl_test (id int,firstname varchar2(32),lastname varchar2(32),corp varchar2(32),age int,create_time date ,info varchar2(12) tablespace tbs_test;

Table created.

Elapsed: 00:00:00.08

SQL> insert into test.tbl_test (id,firstname,lastname,corp,age,create_time,info)
  2  select rownum as id,'zhou','digoal','sky-mobi',27,sysdate,'abcdefghijklmnopqrstuvwxyz09876543211234567890poiuytrewqasdfghmjuiklopff'
  3  from dual connect by level <= 10000000;

10000000 rows created.

Elapsed: 00:01:14.19

SQL> select bytes/1024/1024 from user_segments where segment_name='TBL_TEST';

BYTES/1024/1024
---------------
           1280

alter system checkpoint; (等待锁,做不下去)

SQL> shutdown abort


Thu Jan 13 12:51:06 2011
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Picked latch-free SCN scheme 3
Using LOG_ARCHIVE_DEST_1 parameter default value as /app/oracle/product/10.2.0/db_1/dbs/arch
Autotune of undo retention is turned on.
IMODE=BR
ILAT =182
LICENSE_MAX_USERS = 0
SYS auditing is disabled
ksdpec: called for event 13740 prior to event group initialization
Starting up ORACLE RDBMS Version: 10.2.0.4.0.
System parameters with non-default values:
  processes                = 1500
  sessions                 = 1655
  __shared_pool_size       = 1325400064
  __large_pool_size        = 16777216
  __java_pool_size         = 16777216
  __streams_pool_size      = 0
  sga_target               = 8589934592
  control_files            = /home/oracle/oradata/skyoss/control01.ctl, /home/oracle/oradata/skyoss/control02.ctl, /home/oracle/orad
ata/skyoss/control03.ctl
  db_block_size            = 8192
  __db_cache_size          = 7214202880
  compatible               = 10.2.0.3.0
  db_file_multiblock_read_count= 16
  undo_management          = AUTO
  undo_tablespace          = UNDOTBS1
  remote_login_passwordfile= EXCLUSIVE
  db_domain                =
  dispatchers              =
  local_listener           = LISTENER_SKYOSS
  job_queue_processes      = 10
  background_dump_dest     = /app/oracle/admin/skyoss/bdump
  user_dump_dest           = /app/oracle/admin/skyoss/udump
  core_dump_dest           = /app/oracle/admin/skyoss/cdump
  audit_file_dest          = /app/oracle/admin/skyoss/adump
  db_name                  = skyoss
  open_cursors             = 300
  pga_aggregate_target     = 1672478720

PMON started with pid=2, OS id=883
PSP0 started with pid=3, OS id=885
MMAN started with pid=4, OS id=887
DBW0 started with pid=5, OS id=889
LGWR started with pid=6, OS id=891
CKPT started with pid=7, OS id=893
SMON started with pid=8, OS id=895
RECO started with pid=9, OS id=897
CJQ0 started with pid=10, OS id=899
MMON started with pid=11, OS id=901
MMNL started with pid=12, OS id=903
Thu Jan 13 12:51:07 2011
ALTER DATABASE   MOUNT
Thu Jan 13 12:51:11 2011
Setting recovery target incarnation to 2
Thu Jan 13 12:51:11 2011
Successful mount of redo thread 1, with mount id 2573386875
Thu Jan 13 12:51:11 2011
Database mounted in Exclusive Mode
Completed: ALTER DATABASE   MOUNT
Thu Jan 13 12:51:11 2011
ALTER DATABASE OPEN
Thu Jan 13 12:51:11 2011
Beginning crash recovery of 1 threads
parallel recovery started with 7 processes
Thu Jan 13 12:51:11 2011
Started redo scan
Thu Jan 13 12:51:13 2011
Completed redo scan
1884260 redo blocks read, 73566 data blocks need recovery
Thu Jan 13 12:51:14 2011
Started redo application at
Thread 1: logseq 1, block 907745
Thu Jan 13 12:51:14 2011
Recovery of Online Redo Log: Thread 1 Group 1 Seq 1 Reading mem 0
  Mem# 0: /home/oracle/oradata/skyoss/redo01.log
Thu Jan 13 12:51:15 2011
Recovery of Online Redo Log: Thread 1 Group 2 Seq 2 Reading mem 0
  Mem# 0: /home/oracle/oradata/skyoss/redo02.log
Thu Jan 13 12:51:17 2011
Recovery of Online Redo Log: Thread 1 Group 3 Seq 3 Reading mem 0
  Mem# 0: /home/oracle/oradata/skyoss/redo03.log
Thu Jan 13 12:51:19 2011
Completed redo application
Thu Jan 13 12:54:48 2011
Completed crash recovery at
Thread 1: logseq 3, block 813235, scn 652699
73566 data blocks read, 69888 data blocks written, 1884260 redo blocks read
Thu Jan 13 12:54:49 2011
Thread 1 advanced to log sequence 4 (thread open)
Thread 1 opened at log sequence 4
  Current log# 1 seq# 4 mem# 0: /home/oracle/oradata/skyoss/redo01.log
Successful open of redo thread 1
Thu Jan 13 12:54:49 2011
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Thu Jan 13 12:54:49 2011
SMON: enabling cache recovery
Thu Jan 13 12:54:49 2011
Successfully onlined Undo Tablespace 1.
Thu Jan 13 12:54:49 2011
SMON: enabling tx recovery
Thu Jan 13 12:54:49 2011
Database Characterset is UTF8
Opening with internal Resource Manager plan
where NUMA PG = 1, CPUs = 8
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
QMNC started with pid=30, OS id=1008
Thu Jan 13 12:54:50 2011
Completed: ALTER DATABASE OPEN
Thu Jan 13 12:54:58 2011
SMON: Parallel transaction recovery tried

恢复时间 232秒.

SQL> select bytes/1024/1024 from user_segments where segment_name='TBL_TEST';

BYTES/1024/1024
---------------
           1280

Elapsed: 00:00:00.04
SQL> alter table tbl_test enable row movement;

Table altered.

Elapsed: 00:00:00.04
SQL> alter table tbl_test shrink space cascade;

Table altered.

Elapsed: 00:01:38.58
SQL> select bytes/1024/1024 from user_segments where segment_name='TBL_TEST';

BYTES/1024/1024
---------------
          .0625

Elapsed: 00:00:00.01


回收空间时间约 99秒


SQL> delete from test.tbl_test;

10000000 rows deleted.

Elapsed: 00:04:23.98

SQL> shutdown abort;
ORACLE instance shut down.


Thu Jan 13 13:17:21 2011
Starting ORACLE instance (normal)
***********
  pga_aggregate_target     = 1672478720
PMON started with pid=2, OS id=1626
PSP0 started with pid=3, OS id=1628
MMAN started with pid=4, OS id=1630
DBW0 started with pid=5, OS id=1632
LGWR started with pid=6, OS id=1634
CKPT started with pid=7, OS id=1636
SMON started with pid=8, OS id=1638
RECO started with pid=9, OS id=1641
CJQ0 started with pid=10, OS id=1643
MMON started with pid=11, OS id=1645
MMNL started with pid=12, OS id=1647
Thu Jan 13 13:17:21 2011
ALTER DATABASE   MOUNT
Thu Jan 13 13:17:26 2011
Setting recovery target incarnation to 2
Thu Jan 13 13:17:26 2011
Successful mount of redo thread 1, with mount id 2573383073
Thu Jan 13 13:17:26 2011
Database mounted in Exclusive Mode
Completed: ALTER DATABASE   MOUNT
Thu Jan 13 13:17:26 2011
ALTER DATABASE OPEN
Thu Jan 13 13:17:26 2011
Beginning crash recovery of 1 threads
parallel recovery started with 7 processes
Thu Jan 13 13:17:26 2011
Started redo scan
Thu Jan 13 13:17:29 2011
Completed redo scan
1881731 redo blocks read, 111978 data blocks need recovery
Thu Jan 13 13:17:39 2011
Started redo application at
Thread 1: logseq 15, block 551986
Thu Jan 13 13:17:39 2011
Recovery of Online Redo Log: Thread 1 Group 3 Seq 15 Reading mem 0
  Mem# 0: /home/oracle/oradata/skyoss/redo03.log
Thu Jan 13 13:17:41 2011
Recovery of Online Redo Log: Thread 1 Group 1 Seq 16 Reading mem 0
  Mem# 0: /home/oracle/oradata/skyoss/redo01.log
Thu Jan 13 13:17:46 2011
Recovery of Online Redo Log: Thread 1 Group 2 Seq 17 Reading mem 0
  Mem# 0: /home/oracle/oradata/skyoss/redo02.log
Thu Jan 13 13:17:48 2011
Completed redo application
Thu Jan 13 13:18:49 2011
Completed crash recovery at
Thread 1: logseq 17, block 458205, scn 1178716
111978 data blocks read, 111978 data blocks written, 1881731 redo blocks read
Thu Jan 13 13:18:50 2011
Thread 1 advanced to log sequence 18 (thread open)
Thread 1 opened at log sequence 18
  Current log# 3 seq# 18 mem# 0: /home/oracle/oradata/skyoss/redo03.log
Successful open of redo thread 1
Thu Jan 13 13:18:50 2011
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Thu Jan 13 13:18:50 2011
SMON: enabling cache recovery
Thu Jan 13 13:18:51 2011
Successfully onlined Undo Tablespace 1.
Thu Jan 13 13:18:51 2011
SMON: enabling tx recovery
Thu Jan 13 13:18:51 2011
Database Characterset is UTF8
Opening with internal Resource Manager plan
where NUMA PG = 1, CPUs = 8
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
QMNC started with pid=30, OS id=1704
Thu Jan 13 13:18:51 2011
Completed: ALTER DATABASE OPEN


恢复时间约 90秒

SQL> select bytes/1024/1024 from user_segments where segment_name='TBL_TEST';

BYTES/1024/1024
---------------
           1280

Elapsed: 00:00:00.05


update测试:

SQL> update tbl_test  set info='fefeabcdefghijklmnopqrstuvwxyz09876543211234567890poiuytrewqasdfghmjuiklopff' ;

10000000 rows updated.

Elapsed: 00:08:30.52
SQL> select bytes/1024/1024 from user_segments where segment_name='TBL_TEST';

BYTES/1024/1024
---------------
           1280

Elapsed: 00:00:00.21

SQL> shutdown abort
ORACLE instance shut down.

SQL> startup
ORACLE instance started.

Total System Global Area 8589934592 bytes
Fixed Size                  2097656 bytes
Variable Size            1358958088 bytes
Database Buffers         7214202880 bytes
Redo Buffers               14675968 bytes
Database mounted.
Database opened.


Thu Jan 13 13:31:31 2011
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Picked latch-free SCN scheme 3
Using LOG_ARCHIVE_DEST_1 parameter default value as /app/oracle/product/10.2.0/db_1/dbs/arch
Autotune of undo retention is turned on.
IMODE=BR
ILAT =182
LICENSE_MAX_USERS = 0
SYS auditing is disabled
ksdpec: called for event 13740 prior to event group initialization
Starting up ORACLE RDBMS Version: 10.2.0.4.0.
System parameters with non-default values:
  processes                = 1500
  sessions                 = 1655
  __shared_pool_size       = 1325400064
  __large_pool_size        = 16777216
  __java_pool_size         = 16777216
  __streams_pool_size      = 0
  sga_target               = 8589934592
  control_files            = /home/oracle/oradata/skyoss/control01.ctl, /home/oracle/oradata/skyoss/control02.ctl, /home/oracle/orad
ata/skyoss/control03.ctl
  db_block_size            = 8192
  __db_cache_size          = 7214202880
  compatible               = 10.2.0.3.0
  db_file_multiblock_read_count= 16
  undo_management          = AUTO
  undo_tablespace          = UNDOTBS1
  remote_login_passwordfile= EXCLUSIVE
  db_domain                =
  dispatchers              =
  local_listener           = LISTENER_SKYOSS
  job_queue_processes      = 10
  background_dump_dest     = /app/oracle/admin/skyoss/bdump
  user_dump_dest           = /app/oracle/admin/skyoss/udump
  core_dump_dest           = /app/oracle/admin/skyoss/cdump
  audit_file_dest          = /app/oracle/admin/skyoss/adump
  db_name                  = skyoss
  open_cursors             = 300
  pga_aggregate_target     = 1672478720
PMON started with pid=2, OS id=2011
PSP0 started with pid=3, OS id=2013
MMAN started with pid=4, OS id=2015
DBW0 started with pid=5, OS id=2017
LGWR started with pid=6, OS id=2019
CKPT started with pid=7, OS id=2021
SMON started with pid=8, OS id=2023
RECO started with pid=9, OS id=2025
CJQ0 started with pid=10, OS id=2027
MMON started with pid=11, OS id=2029
MMNL started with pid=12, OS id=2031
Thu Jan 13 13:31:31 2011
ALTER DATABASE   MOUNT
Thu Jan 13 13:31:35 2011
Setting recovery target incarnation to 2
Thu Jan 13 13:31:35 2011
Successful mount of redo thread 1, with mount id 2573383155
Thu Jan 13 13:31:35 2011
Database mounted in Exclusive Mode
Completed: ALTER DATABASE   MOUNT
Thu Jan 13 13:31:36 2011
ALTER DATABASE OPEN
Thu Jan 13 13:31:36 2011
Beginning crash recovery of 1 threads
parallel recovery started with 7 processes
Thu Jan 13 13:31:36 2011
Started redo scan
Thu Jan 13 13:31:38 2011
Completed redo scan
1887354 redo blocks read, 84480 data blocks need recovery
Thu Jan 13 13:31:40 2011
Started redo application at
Thread 1: logseq 34, block 924605
Thu Jan 13 13:31:40 2011
Recovery of Online Redo Log: Thread 1 Group 1 Seq 34 Reading mem 0
  Mem# 0: /home/oracle/oradata/skyoss/redo01.log
Thu Jan 13 13:31:40 2011
Recovery of Online Redo Log: Thread 1 Group 2 Seq 35 Reading mem 0
  Mem# 0: /home/oracle/oradata/skyoss/redo02.log
Thu Jan 13 13:31:45 2011
Recovery of Online Redo Log: Thread 1 Group 3 Seq 36 Reading mem 0
  Mem# 0: /home/oracle/oradata/skyoss/redo03.log
Thu Jan 13 13:31:49 2011
Completed redo application
Thu Jan 13 13:32:12 2011
Completed crash recovery at
Thread 1: logseq 36, block 835407, scn 1733905
84480 data blocks read, 84480 data blocks written, 1887354 redo blocks read
Thu Jan 13 13:32:13 2011
Thread 1 advanced to log sequence 37 (thread open)
Thread 1 opened at log sequence 37
  Current log# 1 seq# 37 mem# 0: /home/oracle/oradata/skyoss/redo01.log
Successful open of redo thread 1
Thu Jan 13 13:32:13 2011
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Thu Jan 13 13:32:13 2011
SMON: enabling cache recovery
Thu Jan 13 13:32:13 2011
Successfully onlined Undo Tablespace 1.
Thu Jan 13 13:32:13 2011
SMON: enabling tx recovery
Thu Jan 13 13:32:13 2011
Database Characterset is UTF8
Opening with internal Resource Manager plan
where NUMA PG = 1, CPUs = 8
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
QMNC started with pid=30, OS id=2079
Thu Jan 13 13:32:14 2011
Completed: ALTER DATABASE OPEN

恢复时间约43秒,后续影响(磁盘IO吃光)约370秒

Thu Jan 13 13:32:53 2011
Thread 1 advanced to log sequence 38 (LGWR switch)
  Current log# 2 seq# 38 mem# 0: /home/oracle/oradata/skyoss/redo02.log
Thu Jan 13 13:33:33 2011
Thread 1 advanced to log sequence 39 (LGWR switch)
  Current log# 3 seq# 39 mem# 0: /home/oracle/oradata/skyoss/redo03.log
Thu Jan 13 13:34:13 2011
Thread 1 advanced to log sequence 40 (LGWR switch)
  Current log# 1 seq# 40 mem# 0: /home/oracle/oradata/skyoss/redo01.log
Thu Jan 13 13:34:58 2011
Thread 1 advanced to log sequence 41 (LGWR switch)
  Current log# 2 seq# 41 mem# 0: /home/oracle/oradata/skyoss/redo02.log
Thu Jan 13 13:35:38 2011
Thread 1 advanced to log sequence 42 (LGWR switch)
  Current log# 3 seq# 42 mem# 0: /home/oracle/oradata/skyoss/redo03.log
Thu Jan 13 13:36:21 2011
Thread 1 advanced to log sequence 43 (LGWR switch)
  Current log# 1 seq# 43 mem# 0: /home/oracle/oradata/skyoss/redo01.log
Thu Jan 13 13:37:04 2011
Thread 1 advanced to log sequence 44 (LGWR switch)
  Current log# 2 seq# 44 mem# 0: /home/oracle/oradata/skyoss/redo02.log
Thu Jan 13 13:38:00 2011
Thread 1 advanced to log sequence 45 (LGWR switch)
  Current log# 3 seq# 45 mem# 0: /home/oracle/oradata/skyoss/redo03.log

SQL> select bytes/1024/1024 from user_segments where segment_name='TBL_TEST';

BYTES/1024/1024
---------------
           1280

Elapsed: 00:00:00.21

持续IO影响约370秒

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
          16.62    0.00    2.75   11.25    0.00   69.38

Device:         rrqm/s   wrqm/s   r/s   w/s   rsec/s   wsec/s avgrq-sz avgqu-sz   await  svctm  %util
cciss/c0d0        0.00  5326.00  0.00 290.00     0.00 45608.00   157.27     5.98   20.67   3.38  97.90
dm-0              0.00     0.00  0.00  0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
dm-1              0.00     0.00  0.00  0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
dm-2              0.00     0.00  0.00 5601.00     0.00 44808.00     8.00   121.68   21.83   0.17  97.90
dm-3              0.00     0.00  0.00  0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
oracle@db5-> date
Thu Jan 13 13:38:25 CST 2011


truncate 测试:
oracle不支持ddl回滚(fra特性除外,需要另外配置,但是也很麻烦)
SQL> truncate table tbl_test;

Table truncated.

Elapsed: 00:00:15.34

SQL> select bytes/1024/1024 from user_segments where segment_name='TBL_TEST';

BYTES/1024/1024
---------------
          .0625

Elapsed: 00:00:00.01


Thu Jan 13 13:45:18 2011
Starting ORACLE instance (normal)
...............................................
Thu Jan 13 13:45:23 2011
ALTER DATABASE OPEN
Thu Jan 13 13:45:23 2011
Beginning crash recovery of 1 threads
parallel recovery started with 7 processes
Thu Jan 13 13:45:24 2011
Started redo scan
Thu Jan 13 13:45:24 2011
Completed redo scan
1996 redo blocks read, 70 data blocks need recovery
Thu Jan 13 13:45:24 2011
Started redo application at
Thread 1: logseq 45, block 272442
Thu Jan 13 13:45:24 2011
Recovery of Online Redo Log: Thread 1 Group 3 Seq 45 Reading mem 0
  Mem# 0: /home/oracle/oradata/skyoss/redo03.log
Thu Jan 13 13:45:24 2011
Completed redo application
Thu Jan 13 13:45:24 2011
Completed crash recovery at
Thread 1: logseq 45, block 274438, scn 1759847
70 data blocks read, 70 data blocks written, 1996 redo blocks read
Thu Jan 13 13:45:24 2011
Thread 1 advanced to log sequence 46 (thread open)
Thread 1 opened at log sequence 46
  Current log# 1 seq# 46 mem# 0: /home/oracle/oradata/skyoss/redo01.log
Successful open of redo thread 1
Thu Jan 13 13:45:25 2011
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Thu Jan 13 13:45:25 2011
SMON: enabling cache recovery
Thu Jan 13 13:45:25 2011
Successfully onlined Undo Tablespace 1.
Thu Jan 13 13:45:25 2011
SMON: enabling tx recovery
Thu Jan 13 13:45:25 2011
Database Characterset is UTF8
Opening with internal Resource Manager plan
where NUMA PG = 1, CPUs = 8
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
QMNC started with pid=21, OS id=2401
Thu Jan 13 13:45:26 2011
Completed: ALTER DATABASE OPEN

恢复时间约8秒