测试隐含参数_disable_logging带来的后果（二）

前面一篇文章，数据库处于归档模式。当设置隐含参数_disable_logging带来的后果是将redolog头损坏，当数据库处于非归档模式时，数据库会怎么样呢？用测试说明问题。由于时间关系，说明暂时省略。
SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /ora10/oracle/product/10.2.0/db_1/dbs/arch
Oldest online log sequence 1
Current log sequence 2
SQL> alter system set "_disable_logging"=true scope=spfile;

System altered.

SQL> startup force
ORACLE instance started.

Total System Global Area 327155712 bytes
Fixed Size 2083752 bytes
Variable Size 209716312 bytes
Database Buffers 109051904 bytes
Redo Buffers 6303744 bytes
Database mounted.
Database opened.
SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> conn zhou/zhou
Connected.
SQL> select sid from v$mystat where rownum<2;

SID
----------
50

SQL> select t2.name,t1.value
2 from v$sesstat t1,v$statname t2
3 where t1.statistic#=t2.statistic# and t2.name like '%redo%' and t1.sid=50;

NAME VALUE
---------------------------------------------------------------- ----------
redo synch writes 0
redo synch time 0
redo blocks read for recovery 0
redo entries 0
redo size 0
redo buffer allocation retries 0
redo wastage 0
redo writer latching time 0
redo writes 0
redo blocks written 0
redo write time 0

NAME VALUE
---------------------------------------------------------------- ----------
redo log space requests 0
redo log space wait time 0
redo log switch interrupts 0
redo ordering marks 0
redo subscn max counts 0

16 rows selected.

SQL> delete from t;

210612 rows deleted.
SQL> conn zhou/zhou
Connected.
SQL> select sid from v$mystat where rownum<2;

SID
----------
50

SQL> select t2.name,t1.value
2 from v$sesstat t1,v$statname t2
3 where t1.statistic#=t2.statistic# and t2.name like '%redo%' and t1.sid=50;

NAME VALUE
---------------------------------------------------------------- ----------
redo synch writes 0
redo synch time 0
redo blocks read for recovery 0
redo entries 0
redo size 0
redo buffer allocation retries 0
redo wastage 0
redo writer latching time 0
redo writes 0
redo blocks written 0
redo write time 0

NAME VALUE
---------------------------------------------------------------- ----------
redo log space requests 0
redo log space wait time 0
redo log switch interrupts 0
redo ordering marks 0
redo subscn max counts 0

16 rows selected.

SQL> insert into t select * from sys.obj$;

52653 rows created.

SQL> select t2.name,t1.value
2 from v$sesstat t1,v$statname t2
3 where t1.statistic#=t2.statistic# and t2.name like '%redo%' and t1.sid=50;

NAME VALUE
---------------------------------------------------------------- ----------
redo synch writes 0
redo synch time 0
redo blocks read for recovery 0
redo entries 3376
redo size 5169132
redo buffer allocation retries 0
redo wastage 0
redo writer latching time 0
redo writes 0
redo blocks written 0
redo write time 0

NAME VALUE
---------------------------------------------------------------- ----------
redo log space requests 0
redo log space wait time 0
redo log switch interrupts 0
redo ordering marks 28
redo subscn max counts 28

16 rows selected.

SQL> rollback;

Rollback complete.

SQL> select t2.name,t1.value
2 from v$sesstat t1,v$statname t2
3 where t1.statistic#=t2.statistic# and t2.name like '%redo%' and t1.sid=50;

NAME VALUE
---------------------------------------------------------------- ----------
redo synch writes 1
redo synch time 0
redo blocks read for recovery 0
redo entries 5613
redo size 5563936
redo buffer allocation retries 0
redo wastage 0
redo writer latching time 0
redo writes 0
redo blocks written 0
redo write time 0

NAME VALUE
---------------------------------------------------------------- ----------
redo log space requests 0
redo log space wait time 0
redo log switch interrupts 0
redo ordering marks 28
redo subscn max counts 28

16 rows selected.

SQL> alter system set "_disable_logging"=false;

System altered.

SQL> startup force
ORACLE instance started.

Total System Global Area 327155712 bytes
Fixed Size 2083752 bytes
Variable Size 209716312 bytes
Database Buffers 109051904 bytes
Redo Buffers 6303744 bytes
Database mounted.
Database opened.

Fri Jan 7 16:45:08 2011
ALTER DATABASE OPEN
Fri Jan 7 16:45:09 2011
Beginning crash recovery of 1 threads
parallel recovery started with 3 processes
Fri Jan 7 16:45:10 2011
Started redo scan
Fri Jan 7 16:45:12 2011
Completed redo scan
0 redo blocks read, 0 data blocks need recovery
Fri Jan 7 16:45:13 2011
Started redo application at
Thread 1: logseq 9, block 5506
Fri Jan 7 16:45:13 2011
Recovery of Online Redo Log: Thread 1 Group 3 Seq 9 Reading mem 0
Mem# 0: /oradata/ldbra/redo03.log
Fri Jan 7 16:45:13 2011
Completed redo application
Fri Jan 7 16:45:13 2011
Completed crash recovery at
Thread 1: logseq 9, block 5506, scn 10783758926749
0 data blocks read, 0 data blocks written, 0 redo blocks read
Fri Jan 7 16:45:17 2011
Thread 1 advanced to log sequence 10 (thread open)
Thread 1 opened at log sequence 10
Current log# 1 seq# 10 mem# 0: /oradata/ldbra/redo01.log
Successful open of redo thread 1
Fri Jan 7 16:45:18 2011
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Fri Jan 7 16:45:18 2011
SMON: enabling cache recovery
Fri Jan 7 16:45:30 2011
Successfully onlined Undo Tablespace 1.
Fri Jan 7 16:45:30 2011
SMON: enabling tx recovery
Fri Jan 7 16:45:31 2011
Database Characterset is ZHS16GBK
Opening with internal Resource Manager plan
where NUMA PG = 1, CPUs = 4
Fri Jan 7 16:45:38 2011
Errors in file /ora10/admin/ldbra/udump/ldbra_ora_12026.trc:
ORA-00354: corrupt redo log block header
ORA-00353: log corruption near block 2 change 10783758906748 time 01/07/2011 16:41:51
ORA-00334: archived log: '/oradata/ldbra/redo03.log'
Fri Jan 7 16:45:38 2011
Errors in file /ora10/admin/ldbra/udump/ldbra_ora_12026.trc:
ORA-00600: internal error code, arguments: [4194], [9], [12], [], [], [], [], []
Fri Jan 7 16:45:51 2011
Errors in file /ora10/admin/ldbra/udump/ldbra_ora_12026.trc:
ORA-00354: corrupt redo log block header
ORA-00353: log corruption near block 2 change 10783758906748 time 01/07/2011 16:41:51
ORA-00334: archived log: '/oradata/ldbra/redo03.log'
ORA-00600: internal error code, arguments: [4194], [9], [12], [], [], [], [], []
Fri Jan 7 16:45:54 2011
Errors in file /ora10/admin/ldbra/udump/ldbra_ora_12026.trc:
ORA-00354: corrupt redo log block header
ORA-00353: log corruption near block 2 change 10783758906748 time 01/07/2011 16:41:51
ORA-00334: archived log: '/oradata/ldbra/redo03.log'
ORA-00600: internal error code, arguments: [4194], [9], [12], [], [], [], [], []
Fri Jan 7 16:45:54 2011
Doing block recovery for file 2 block 4408
Block recovery from logseq 10, block 57 to scn 10783758926947
Fri Jan 7 16:45:54 2011
Recovery of Online Redo Log: Thread 1 Group 1 Seq 10 Reading mem 0
Mem# 0: /oradata/ldbra/redo01.log
Block recovery stopped at EOT rba 10.59.16
Block recovery completed at rba 10.59.16, scn 2510.3391013981
Doing block recovery for file 2 block 137
Block recovery from logseq 10, block 57 to scn 10783758926940
Fri Jan 7 16:45:54 2011
Recovery of Online Redo Log: Thread 1 Group 1 Seq 10 Reading mem 0
Mem# 0: /oradata/ldbra/redo01.log
Block recovery completed at rba 10.59.16, scn 2510.3391013981
replication_dependency_tracking turned off (no async multimaster replication found)
Fri Jan 7 16:46:15 2011
Starting background process QMNC
QMNC started with pid=16, OS id=12650

SQL> shutdown immediate
ORA-00600: internal error code, arguments: [4194], [29], [28], [], [], [], [],
[]

SQL> shutdown abort
ORACLE instance shut down.
SQL> exit
SQL> startup
ORACLE instance started.

Total System Global Area 327155712 bytes
Fixed Size 2083752 bytes
Variable Size 209716312 bytes
Database Buffers 109051904 bytes
Redo Buffers 6303744 bytes
Database mounted.
Database opened.

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 /ora10/oracle/product/10.2.0/db_1/dbs/arch
Autotune of undo retention is turned on.
IMODE=BR
ILAT =10
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 = 50
sga_max_size = 327155712
__shared_pool_size = 180355072
shared_pool_size = 33554432
__large_pool_size = 4194304
__java_pool_size = 4194304
__streams_pool_size = 8388608
sga_target = 314572800
control_files = /oradata/ldbra/control01.ctl
__db_cache_size = 109051904
compatible = 10.2.0.3.0
log_checkpoint_interval = 10000
_disable_logging = FALSE
db_files = 80
db_file_multiblock_read_count= 2
_allow_resetlogs_corruption= TRUE
undo_management = AUTO
global_names = TRUE
parallel_max_servers = 5
max_dump_file_size = UNLIMITED
sort_area_size = 49152
db_name = ldbra
pga_aggregate_target = 10485760
workarea_size_policy = MANUAL
_ash_enable = TRUE
PMON started with pid=2, OS id=19779
PSP0 started with pid=3, OS id=19781
MMAN started with pid=4, OS id=19783
DBW0 started with pid=5, OS id=19785
LGWR started with pid=6, OS id=19787
CKPT started with pid=7, OS id=19789
SMON started with pid=8, OS id=19791
RECO started with pid=9, OS id=19793
MMON started with pid=10, OS id=19797
MMNL started with pid=11, OS id=19799
Fri Jan 7 16:56:23 2011
ALTER DATABASE MOUNT
Fri Jan 7 16:56:28 2011
Setting recovery target incarnation to 3
Fri Jan 7 16:56:28 2011
Successful mount of redo thread 1, with mount id 1525474551
Fri Jan 7 16:56:28 2011
Database mounted in Exclusive Mode
Completed: ALTER DATABASE MOUNT
Fri Jan 7 16:56:28 2011
ALTER DATABASE OPEN
Fri Jan 7 16:56:29 2011
Beginning crash recovery of 1 threads
parallel recovery started with 3 processes
Fri Jan 7 16:56:30 2011
Started redo scan
Fri Jan 7 16:56:30 2011
Completed redo scan
14 redo blocks read, 13 data blocks need recovery
Fri Jan 7 16:56:30 2011
Started redo application at
Thread 1: logseq 10, block 136
Fri Jan 7 16:56:30 2011
Recovery of Online Redo Log: Thread 1 Group 1 Seq 10 Reading mem 0
Mem# 0: /oradata/ldbra/redo01.log
Fri Jan 7 16:56:30 2011
Completed redo application
Fri Jan 7 16:56:31 2011
Completed crash recovery at
Thread 1: logseq 10, block 150, scn 10783758947090
13 data blocks read, 13 data blocks written, 14 redo blocks read
Fri Jan 7 16:56:31 2011
Thread 1 advanced to log sequence 11 (thread open)
Thread 1 opened at log sequence 11
Current log# 2 seq# 11 mem# 0: /oradata/ldbra/redo02.log
Successful open of redo thread 1
Fri Jan 7 16:56:31 2011
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Fri Jan 7 16:56:31 2011
SMON: enabling cache recovery
Fri Jan 7 16:56:34 2011
Successfully onlined Undo Tablespace 1.
Fri Jan 7 16:56:34 2011
SMON: enabling tx recovery
Fri Jan 7 16:56:34 2011
Database Characterset is ZHS16GBK
Opening with internal Resource Manager plan
where NUMA PG = 1, CPUs = 4
replication_dependency_tracking turned off (no async multimaster replication found)
Fri Jan 7 16:56:39 2011
Errors in file /ora10/admin/ldbra/udump/ldbra_ora_19842.trc:
ORA-00354: corrupt redo log block header
ORA-00353: log corruption near block 2 change 10783758906748 time 01/07/2011 16:41:51
ORA-00334: archived log: '/oradata/ldbra/redo03.log'
Fri Jan 7 16:56:39 2011
Errors in file /ora10/admin/ldbra/udump/ldbra_ora_19842.trc:
ORA-00600: internal error code, arguments: [4194], [29], [28], [], [], [], [], []
Fri Jan 7 16:56:42 2011
Errors in file /ora10/admin/ldbra/udump/ldbra_ora_19842.trc:
ORA-00354: corrupt redo log block header
ORA-00353: log corruption near block 2 change 10783758906748 time 01/07/2011 16:41:51
ORA-00334: archived log: '/oradata/ldbra/redo03.log'
ORA-00600: internal error code, arguments: [4194], [29], [28], [], [], [], [], []
Fri Jan 7 16:56:43 2011
Errors in file /ora10/admin/ldbra/udump/ldbra_ora_19842.trc:
ORA-00354: corrupt redo log block header
ORA-00353: log corruption near block 2 change 10783758906748 time 01/07/2011 16:41:51
ORA-00334: archived log: '/oradata/ldbra/redo03.log'
ORA-00600: internal error code, arguments: [4194], [29], [28], [], [], [], [], []
Doing block recovery for file 2 block 4974
Block recovery from logseq 11, block 70 to scn 10783758947919
Fri Jan 7 16:56:43 2011
Recovery of Online Redo Log: Thread 1 Group 2 Seq 11 Reading mem 0
Mem# 0: /oradata/ldbra/redo02.log
Block recovery stopped at EOT rba 11.72.16
Block recovery completed at rba 11.72.16, scn 2510.3391034957
Doing block recovery for file 2 block 57
Block recovery from logseq 11, block 62 to scn 10783758947916
Fri Jan 7 16:56:43 2011
Recovery of Online Redo Log: Thread 1 Group 2 Seq 11 Reading mem 0
Mem# 0: /oradata/ldbra/redo02.log
Block recovery completed at rba 11.72.16, scn 2510.3391034957
Error 600 occured during Bufq Startup Notifier
Fri Jan 7 16:56:43 2011
Errors in file /ora10/admin/ldbra/udump/ldbra_ora_19842.trc:
ORA-00600: internal error code, arguments: [4194], [29], [28], [], [], [], [], []
Starting background process QMNC
QMNC started with pid=16, OS id=20081
Fri Jan 7 16:57:06 2011
Errors in file /ora10/admin/ldbra/bdump/ldbra_q001_20216.trc:
ORA-00600: internal error code, arguments: [kwqbgqc: bad state], [1], [1], [], [], [], [], []
Fri Jan 7 16:57:09 2011
Errors in file /ora10/admin/ldbra/bdump/ldbra_q001_20216.trc:
ORA-00600: internal error code, arguments: [kwqbgqc: bad state], [1], [1], [], [], [], [], []
Fri Jan 7 16:57:09 2011
Errors in file /ora10/admin/ldbra/bdump/ldbra_q001_20216.trc:
ORA-00600: internal error code, arguments: [kwqbgqc: bad state], [1], [1], [], [], [], [], []
Fri Jan 7 16:57:10 2011
Completed: ALTER DATABASE OPEN
Fri Jan 7 16:57:11 2011
Errors in file /ora10/admin/ldbra/bdump/ldbra_q001_20216.trc:
ORA-00600: internal error code, arguments: [kwqbgqc: bad state], [1], [1], [], [], [], [], []
Fri Jan 7 16:57:11 2011
Errors in file /ora10/admin/ldbra/bdump/ldbra_q001_20216.trc:
ORA-00600: internal error code, arguments: [kwqbgqc: bad state], [1], [1], [], [], [], [], []
Fri Jan 7 16:57:11 2011
Errors in file /ora10/admin/ldbra/bdump/ldbra_mmon_19797.trc:
ORA-00354: corrupt redo log block header
ORA-00353: log corruption near block 2 change 10783758906748 time 01/07/2011 16:41:51
ORA-00334: archived log: '/oradata/ldbra/redo03.log'
Fri Jan 7 16:57:11 2011
Errors in file /ora10/admin/ldbra/bdump/ldbra_mmon_19797.trc:
ORA-00600: internal error code, arguments: [4194], [9], [12], [], [], [], [], []
Fri Jan 7 16:57:12 2011
Errors in file /ora10/admin/ldbra/bdump/ldbra_q001_20216.trc:
ORA-00600: internal error code, arguments: [kwqbgqc: bad state], [1], [1], [], [], [], [], []
Fri Jan 7 16:57:15 2011
Errors in file /ora10/admin/ldbra/bdump/ldbra_mmon_19797.trc:
ORA-00354: corrupt redo log block header
ORA-00353: log corruption near block 2 change 10783758906748 time 01/07/2011 16:41:51
ORA-00334: archived log: '/oradata/ldbra/redo03.log'
ORA-00600: internal error code, arguments: [4194], [9], [12], [], [], [], [], []
Fri Jan 7 16:57:15 2011
Doing block recovery for file 2 block 4408
Block recovery from logseq 11, block 80 to scn 10783758947939
Fri Jan 7 16:57:15 2011
Recovery of Online Redo Log: Thread 1 Group 2 Seq 11 Reading mem 0
Mem# 0: /oradata/ldbra/redo02.log
Block recovery stopped at EOT rba 11.82.16
Block recovery completed at rba 11.82.16, scn 2510.3391034978
Doing block recovery for file 2 block 137
Block recovery from logseq 11, block 62 to scn 10783758947937
Fri Jan 7 16:57:15 2011
Recovery of Online Redo Log: Thread 1 Group 2 Seq 11 Reading mem 0
Mem# 0: /oradata/ldbra/redo02.log
Block recovery completed at rba 11.82.16, scn 2510.3391034978
ORA-600 encountered when generating server alert SMG-4120
Fri Jan 7 16:57:15 2011
Errors in file /ora10/admin/ldbra/bdump/ldbra_mmon_19797.trc:
ORA-00600: internal error code, arguments: [ktcpoptx_0], [0x072B975D8], [0x072BB1D18], [0], [], [], [], []
Fri Jan 7 16:57:17 2011
Errors in file /ora10/admin/ldbra/bdump/ldbra_mmon_19797.trc:
ORA-00354: corrupt redo log block header
ORA-00353: log corruption near block 2 change 10783758906748 time 01/07/2011 16:41:51
ORA-00334: archived log: '/oradata/ldbra/redo03.log'
Fri Jan 7 16:57:17 2011
Errors in file /ora10/admin/ldbra/bdump/ldbra_mmon_19797.trc:
ORA-00600: internal error code, arguments: [4194], [29], [28], [], [], [], [], []
Fri Jan 7 16:57:17 2011
Errors in file /ora10/admin/ldbra/bdump/ldbra_q001_20216.trc:
ORA-00600: internal error code, arguments: [kwqbgqc: bad state], [1], [1], [], [], [], [], []
Fri Jan 7 16:57:19 2011
Errors in file /ora10/admin/ldbra/bdump/ldbra_q001_20216.trc:
ORA-00600: internal error code, arguments: [kwqbgqc: bad state], [1], [1], [], [], [], [], []
Fri Jan 7 16:57:19 2011
Errors in file /ora10/admin/ldbra/bdump/ldbra_q001_20216.trc:
ORA-00600: internal error code, arguments: [kwqbgqc: bad state], [1], [1], [], [], [], [], []
Fri Jan 7 16:57:20 2011
Errors in file /ora10/admin/ldbra/bdump/ldbra_mmon_19797.trc:
ORA-00354: corrupt redo log block header
ORA-00353: log corruption near block 2 change 10783758906748 time 01/07/2011 16:41:51
ORA-00334: archived log: '/oradata/ldbra/redo03.log'
ORA-00600: internal error code, arguments: [4194], [29], [28], [], [], [], [], []
Doing block recovery for file 2 block 4974
Block recovery from logseq 11, block 70 to scn 10783758947919
Fri Jan 7 16:57:20 2011
Recovery of Online Redo Log: Thread 1 Group 2 Seq 11 Reading mem 0
Mem# 0: /oradata/ldbra/redo02.log
Block recovery completed at rba 11.72.16, scn 2510.3391034960
Doing block recovery for file 2 block 57
Block recovery from logseq 11, block 62 to scn 10783758947940
Fri Jan 7 16:57:20 2011
Recovery of Online Redo Log: Thread 1 Group 2 Seq 11 Reading mem 0
Mem# 0: /oradata/ldbra/redo02.log
Block recovery completed at rba 11.84.16, scn 2510.3391034981
Fri Jan 7 16:57:21 2011
Errors in file /ora10/admin/ldbra/bdump/ldbra_q001_20216.trc:
ORA-00600: internal error code, arguments: [kwqbgqc: bad state], [1], [1], [], [], [], [], []
Fri Jan 7 16:57:21 2011
Errors in file /ora10/admin/ldbra/bdump/ldbra_q001_20216.trc:
ORA-00600: internal error code, arguments: [kwqbgqc: bad state], [1], [1], [], [], [], [], []
Fri Jan 7 16:57:23 2011
Errors in file /ora10/admin/ldbra/bdump/ldbra_q001_20216.trc:
ORA-00600: internal error code, arguments: [kwqbgqc: bad state], [1], [1], [], [], [], [], []
Fri Jan 7 16:57:26 2011
Starting background process CJQ0
CJQ0 started with pid=20, OS id=20709
Fri Jan 7 16:57:28 2011
Errors in file /ora10/admin/ldbra/bdump/ldbra_q001_20216.trc:
ORA-00600: internal error code, arguments: [kwqbgqc: bad state], [1], [1], [], [], [], [], []
Fri Jan 7 16:57:29 2011
Errors in file /ora10/admin/ldbra/bdump/ldbra_q001_20216.trc:
ORA-00600: internal error code, arguments: [kwqbgqc: bad state], [1], [1], [], [], [], [], []
Fri Jan 7 16:57:29 2011
Errors in file /ora10/admin/ldbra/bdump/ldbra_q001_20216.trc:
ORA-00600: internal error code, arguments: [kwqbgqc: bad state], [1], [1], [], [], [], [], []
Fri Jan 7 16:57:31 2011
Errors in file /ora10/admin/ldbra/bdump/ldbra_q001_20216.trc:
ORA-00600: internal error code, arguments: [kwqbgqc: bad state], [1], [1], [], [], [], [], []
Fri Jan 7 16:57:31 2011
Errors in file /ora10/admin/ldbra/bdump/ldbra_q001_20216.trc:
ORA-00600: internal error code, arguments: [kwqbgqc: bad state], [1], [1], [], [], [], [], []

SQL> alter system set UNDO_MANAGEMENT=Manual scope=spfile;

System altered.

SQL> shutdown abort
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 327155712 bytes
Fixed Size 2083752 bytes
Variable Size 209716312 bytes
Database Buffers 109051904 bytes
Redo Buffers 6303744 bytes
Database mounted.
Database opened.
SQL> Create rollback segment r01 ;

Rollback segment created.

SQL> Alter rollback segment r01 online ;

Rollback segment altered.

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/oradata/ldbra/system01.dbf
/oradata/ldbra/undotbs01.dbf
/oradata/ldbra/sysaux01.dbf
/oradata/ldbra/users01.dbf
/oradata/ldbra/example01.dbf
/oradata/ldbra/streams01.dbf
/oradata/ldbra/company01.dbf
/oradata/ldbra/STRM_TBS.DBF
/oradata/ldbra/streams_01.dbf
/oradata/ldbra/users02.dbf

10 rows selected.

SQL> Create undo tablespace undotbs_new datafile '/oradata/ldbra/undonew01.dbf' size 50m;

Tablespace created.

SQL> Drop tablespace undotbs including contents and datafiles;
Drop tablespace undotbs including contents and datafiles
*
ERROR at line 1:
ORA-00959: tablespace 'UNDOTBS' does not exist


SQL> select name from v$tablespace;

NAME
------------------------------
SYSTEM
UNDOTBS1
SYSAUX
USERS
TEMP
EXAMPLE
STREAMS
COMPANY
STRM_TBS
TEMP11
UNDOTBS_NEW

11 rows selected.

SQL> Drop tablespace undotbs1 including contents and datafiles;

Tablespace dropped.

SQL> Shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> Startup nomount ;
ORACLE instance started.

Total System Global Area 327155712 bytes
Fixed Size 2083752 bytes
Variable Size 209716312 bytes
Database Buffers 109051904 bytes
Redo Buffers 6303744 bytes
SQL> Alter system set undo_tablespace=undotbs_new scope=spfile;

System altered.

SQL> alter system set UNDO_MANAGEMENT=auto scope=spfile;

System altered.

SQL> startup force
ORACLE instance started.

Total System Global Area 327155712 bytes
Fixed Size 2083752 bytes
Variable Size 209716312 bytes
Database Buffers 109051904 bytes
Redo Buffers 6303744 bytes
Database mounted.
Database opened.

QMNC started with pid=13, OS id=9678
Fri Jan 7 17:07:48 2011
Completed: ALTER DATABASE OPEN
Fri Jan 7 17:07:48 2011
Starting background process CJQ0
CJQ0 started with pid=14, OS id=9684

SQL> conn zhou/zhou
Connected.
SQL> select count(*) from t;

COUNT(*)
----------
657

SQL> conn zhou/zhou
Connected.
SQL> select sid from v$mystat where rownum<2;

SID
----------
50

SQL> select t2.name,t1.value
2 from v$sesstat t1,v$statname t2
3 where t1.statistic#=t2.statistic# and t2.name like '%redo%' and t1.sid=50;

NAME VALUE
---------------------------------------------------------------- ----------
redo synch writes 0
redo synch time 0
redo blocks read for recovery 0
redo entries 0
redo size 0
redo buffer allocation retries 0
redo wastage 0
redo writer latching time 0
redo writes 0
redo blocks written 0
redo write time 0

NAME VALUE
---------------------------------------------------------------- ----------
redo log space requests 0
redo log space wait time 0
redo log switch interrupts 0
redo ordering marks 0
redo subscn max counts 0

16 rows selected.

SQL> insert into t select * from sys.obj$;

52653 rows created.

SQL> select t2.name,t1.value
2 from v$sesstat t1,v$statname t2
3 where t1.statistic#=t2.statistic# and t2.name like '%redo%' and t1.sid=50;

NAME VALUE
---------------------------------------------------------------- ----------
redo synch writes 0
redo synch time 0
redo blocks read for recovery 0
redo entries 2853
redo size 5135160
redo buffer allocation retries 2
redo wastage 0
redo writer latching time 0
redo writes 0
redo blocks written 0
redo write time 0

NAME VALUE
---------------------------------------------------------------- ----------
redo log space requests 0
redo log space wait time 0
redo log switch interrupts 0
redo ordering marks 28
redo subscn max counts 163

16 rows selected.

SQL> rollback
2 ;

Rollback complete.

SQL> select t2.name,t1.value
2 from v$sesstat t1,v$statname t2
3 where t1.statistic#=t2.statistic# and t2.name like '%redo%' and t1.sid=50;

NAME VALUE
---------------------------------------------------------------- ----------
redo synch writes 1
redo synch time 1
redo blocks read for recovery 0
redo entries 5050
redo size 5526868
redo buffer allocation retries 2
redo wastage 0
redo writer latching time 0
redo writes 0
redo blocks written 0
redo write time 0

NAME VALUE
---------------------------------------------------------------- ----------
redo log space requests 0
redo log space wait time 0
redo log switch interrupts 0
redo ordering marks 28
redo subscn max counts 176

16 rows selected.