ORACLE数据库管理-启动取消TX-recover

最新推荐文章于 2023-05-26 13:44:14 发布

执笔画情ora

最新推荐文章于 2023-05-26 13:44:14 发布

阅读量276

点赞数

分类专栏： ORACLE数据库UNDO管理与故障处理 ORACLE数据库启动与关闭

本文链接：https://blog.csdn.net/oradbm/article/details/103995026

版权

ORACLE数据库UNDO管理与故障处理同时被 2 个专栏收录

10 篇文章 0 订阅

订阅专栏

ORACLE数据库启动与关闭

5 篇文章 0 订阅

订阅专栏

实验目的，设置隐藏参数，全部offline undo segments，对宕机时未提交事务得影响。

1 存在事务未提交得情况下，shutdown abort。

2 修改参数文件undo_management='MANUAL' ROLLBACK_SEGMENTS='SYSTEM'.

1 创建普通用户test
CREATE USER TEST IDENTIFIED BY "123";
GRANT CONNECT,RESOURCE ,UNLIMITED TABLESPACE TO TEST;
CREATE TABLESPACE TEST DATAFILE '/u01/test01.dbf' SIZE 200m;
2 创建测试表test01
SQL> connect test/123
Connected.
SQL>
SQL>
SQL> show user
USER is "TEST"
SQL>
create table test01 tablespace TEST as select OBJECT_ID,OBJECT_NAME FROM DBA_OBJECTS WHERE ROWNUM<1;
3 产生大量的事务，不提交。
SQL> insert into TEST01 VALUES(1,'123');
insert into TEST01 VALUES(1,'123');
insert into TEST01 VALUES(1,'123');
insert into TEST01 VALUES(1,'123');
insert into TEST01 VALUES(1,'123');
insert into TEST01 VALUES(1,'123');
insert into TEST01 VALUES(1,'123');
insert into TEST01 VALUES(1,'123');
insert into TEST01 VALUES(1,'123');
insert into TEST01 VALUES(1,'123');
insert into TEST01 VALUES(1,'123');
insert into TEST01 VALUES(1,'123');
insert into TEST01 VALUES(1,'123');
insert into TEST01 VALUES(1,'123');
insert into TEST01 VALUES(1,'123');
insert into TEST01 VALUES(1,'123');
insert into TEST01 VALUES(1,'123');
insert into TEST01 VALUES(1,'123');
insert into TEST01 VALUES(1,'123');
insert into TEST01 VALUES(1,'123');
insert into TEST01 VALUES(1,'123');
insert into TEST01 VALUES(1,'123');
insert into TEST01 VALUES(1,'123');
insert into TEST01 VALUES(1,'123');
insert into TEST01 VALUES(1,'123');
1 row created.

SQL>
1 row created.

SQL>
4 查询到online的 rollback segments。
SQL> SET LINES 120
SQL> COL SEGMENT_NAME FOR A30
SQL> SELECT SEGMENT_NAME,STATUS FROM DBA_ROLLBACK_SEGS;

SEGMENT_NAME STATUS
------------------------------ ----------------
SYSTEM ONLINE
_SYSSMU1_762089623$ ONLINE
_SYSSMU2_3062791661$ ONLINE
_SYSSMU3_1499641855$ ONLINE
_SYSSMU4_3564003469$ ONLINE
_SYSSMU5_1728379857$ ONLINE
_SYSSMU6_965511687$ ONLINE
_SYSSMU7_2247632671$ ONLINE
_SYSSMU8_437891266$ ONLINE
_SYSSMU9_3215744559$ ONLINE
_SYSSMU10_2925533193$ ONLINE

11 rows selected.

5 模拟异常宕机
SQL> shutdown abort
ORACLE instance shut down.
SQL>
6 编写参数文件，添加参数
undo_management='MANUAL'
rolback_segments='SYSTEM'
7 启动数据库
[oracle@vm01 dbs]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Wed Jan 15 03:10:57 2020

Connected to an idle instance.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 1207959552 bytes
Fixed Size 8620080 bytes
Variable Size 838862800 bytes
Database Buffers 352321536 bytes
Redo Buffers 8155136 bytes
SQL> alter database mount;

Database altered.

SQL> alter database open;

Database altered.

Beginning crash recovery of 1 threads
2020-01-15T03:11:37.536856-08:00
Started redo scan
2020-01-15T03:11:37.620105-08:00
Completed redo scan
read 338 KB redo, 37 data blocks need recovery
2020-01-15T03:11:37.621080-08:00
Started redo application at
Thread 1: logseq 35, block 55666, offset 0
2020-01-15T03:11:37.621355-08:00
Recovery of Online Redo Log: Thread 1 Group 2 Seq 35 Reading mem 0
Mem# 0: /u01/oradata/prodpri/redo02.log
2020-01-15T03:11:37.623343-08:00
Completed redo application of 0.02MB
2020-01-15T03:11:37.724668-08:00
Completed crash recovery at
Thread 1: RBA 35.56343.16, nab 56343, scn 0x00000000004c5c90
37 data blocks read, 37 data blocks written, 338 redo k-bytes read
数据库未出现TX-RECOVER字眼。

8 查看表test数据。
SQL> select count(1) from test01;

COUNT(1)
----------
0

上述同样操作使用update语句
SQL> show parameter undo

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
temp_undo_enabled boolean FALSE
undo_management string MANUAL
undo_retention integer 900
undo_tablespace string
SQL> SELECT SEGMENT_NAME,STATUS FROM DBA_ROLLBACK_SEGS;

SEGMENT_NAME STATUS
------------------------------ ----------------
SYSTEM ONLINE
_SYSSMU1_762089623$ OFFLINE
_SYSSMU2_3062791661$ OFFLINE
_SYSSMU3_1499641855$ OFFLINE
_SYSSMU4_3564003469$ OFFLINE
_SYSSMU5_1728379857$ OFFLINE
_SYSSMU6_965511687$ OFFLINE
_SYSSMU7_2247632671$ OFFLINE
_SYSSMU8_437891266$ OFFLINE
_SYSSMU9_3215744559$ OFFLINE
_SYSSMU10_2925533193$ OFFLINE

SEGMENT_NAME STATUS
------------------------------ ----------------
_SYSSMU21_567861045$ OFFLINE
_SYSSMU22_2686851961$ OFFLINE
_SYSSMU23_94587328$ OFFLINE
_SYSSMU24_2584993644$ OFFLINE
_SYSSMU25_2795507501$ OFFLINE
_SYSSMU26_762234276$ OFFLINE
_SYSSMU27_123628667$ OFFLINE
_SYSSMU28_3444433565$ OFFLINE
_SYSSMU29_3470449870$ OFFLINE
_SYSSMU30_51700156$ OFFLINE

21 rows selected.

[oracle@vm01 dbs]$ sqlplus test/123

SQL*Plus: Release 12.2.0.1.0 Production on Wed Jan 15 03:22:25 2020

Last Successful login time: Wed Jan 15 2020 03:21:47 -08:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> update test01 set object_id=10;

24 rows updated.

SQL> update test01 set object_id=10;
update test01 set object_id=10
*
ERROR at line 1:
ORA-01552: cannot use system rollback segment for non-system tablespace 'TEST';

SQL> select object_id from test01;

OBJECT_ID
----------
1
1
1
1
1
1
1
1
1
1
1

OBJECT_ID
----------
1
1

SQL> create undo tablespace undotbs3 datafile '/u01/undo3.dbf' size 20M;

Tablespace created.
[oracle@vm01 trace]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Wed Jan 15 03:27:04 2020

Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>

编辑参数文件
[oracle@vm01 dbs]$ vi initprodpri.ora

prodpri.__data_transfer_cache_size=0
prodpri.__db_cache_size=419430400
prodpri.__inmemory_ext_roarea=0
prodpri.__inmemory_ext_rwarea=0
prodpri.__java_pool_size=16777216
prodpri.__large_pool_size=33554432
prodpri.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
prodpri.__pga_aggregate_target=486539264
prodpri.__sga_target=721420288
prodpri.__shared_io_pool_size=33554432
prodpri.__shared_pool_size=201326592
prodpri.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/prodpri/adump'
*.audit_trail='db'
*.compatible='12.2.0'
*.control_files='/u01/oradata/prodpri/control01.ctl','/u01/oradata/prodpri/control02.ctl'
*.db_block_size=8192
*.db_file_name_convert='/u01/oradata/prodpri','/u01/oradata'
*.db_name='prodpri'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=prodpriXDB)'
*.local_listener='LISTENER_PRODPRI'
*.log_archive_dest_1='LOCATION=/u01/arch/prodpri'
*.log_archive_format='%t_%s_%r.dbf'
*.memory_target=1147m
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.service_names='prodpri,boss,app'
*.undo_tablespace='UNDOTBS3'
#*.undo_management='manual'
#*.rollback_segments='system'
~
~SQL> create spfile from pfile;

File created.

SQL> startup
ORACLE instance started.

Total System Global Area 1207959552 bytes
Fixed Size 8620080 bytes
Variable Size 838862800 bytes
Database Buffers 352321536 bytes
Redo Buffers 8155136 bytes
Database mounted.
Database opened.
SQL> show parameter undo

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
temp_undo_enabled boolean FALSE
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS3
SQL>
SQL> select object_id from test.test01;

OBJECT_ID
----------
1
1
1
1
1
1
1
1
1
1
1

OBJECT_ID
----------
1
1

24 rows selected.

SQL>
SQL> SELECT SEGMENT_NAME,STATUS FROM DBA_ROLLBACK_SEGS;

SEGMENT_NAME STATUS
------------------------------ ----------------
_SYSSMU32_2716343464$ ONLINE
_SYSSMU33_3120567609$ ONLINE
_SYSSMU34_964871371$ ONLINE
_SYSSMU35_628768198$ ONLINE
_SYSSMU36_2298171139$ ONLINE
_SYSSMU37_1733129179$ ONLINE
_SYSSMU38_981763433$ ONLINE
_SYSSMU39_3475643199$ ONLINE
_SYSSMU40_3809481662$ ONLINE

31 rows selected.

SQL>