39.管理还原数据
1.监视和管理还原数据
--当两个事务把undo表空间使用完毕，第三个事务继续覆盖undo表空间后，通过flashback查询被覆盖的数据，就会报快照过旧。2.管理还原数据
2.1数据库没有事务
[oracle@yuanzj.com:/home/oracle]$ 10g
[oracle@yuanzj.com:/home/oracle]$
[oracle@yuanzj.com:/home/oracle]$
[oracle@yuanzj.com:/home/oracle]$
[oracle@yuanzj.com:/home/oracle]$
[oracle@yuanzj.com:/home/oracle]$ sas
SQL*Plus: Release 10.2.0.5.0 - Production on Sat Mar 4 15:46:49 2023
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining Scoring Engine
and Real Application Testing options
sys@ORCL10G 2023-03-04 15:46:49> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/oradata/orcl10g/system01.dbf
/oradata/orcl10g/undotbs01.dbf
/oradata/orcl10g/sysaux01.dbf
/oradata/orcl10g/users01.dbf
/oradata/orcl10g/example01.dbf
Elapsed: 00:00:00.01
sys@ORCL10G 2023-03-04 15:47:00> ho rm /oradata/orcl10g/undotbs01.dbf
sys@ORCL10G 2023-03-04 15:47:11> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
sys@ORCL10G 2023-03-04 15:47:24> quit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining Scoring Engine
and Real Application Testing options
[oracle@yuanzj.com:/home/oracle]$ sas
SQL*Plus: Release 10.2.0.5.0 - Production on Sat Mar 4 15:47:29 2023
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
Connected to an idle instance.
idle 2023-03-04 15:47:29> startup
ORACLE instance started.
Total System Global Area 1174405120 bytes
Fixed Size 2095800 bytes
Variable Size 301991240 bytes
Database Buffers 855638016 bytes
Redo Buffers 14680064 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 2 - see DBWR trace file
ORA-01110: data file 2: '/oradata/orcl10g/undotbs01.dbf'
idle 2023-03-04 15:47:36> show parameter undo;
NAME_COL_PLUS_SHOW_PARAM TYPE
-------------------------------------------------------------------------------- -----------
VALUE_COL_PLUS_SHOW_PARAM
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
undo_management string
AUTO
undo_retention integer
900
undo_tablespace string
UNDOTBS1
idle 2023-03-04 15:47:43> alter system set undo_management=manual scope=spfile;
System altered.
Elapsed: 00:00:00.00
idle 2023-03-04 15:48:00> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
idle 2023-03-04 15:48:10> quit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining Scoring Engine
and Real Application Testing options
[oracle@yuanzj.com:/home/oracle]$ sas
SQL*Plus: Release 10.2.0.5.0 - Production on Sat Mar 4 15:48:14 2023
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
Connected to an idle instance.
idle 2023-03-04 15:48:14> startup
ORACLE instance started.
Total System Global Area 1174405120 bytes
Fixed Size 2095800 bytes
Variable Size 301991240 bytes
Database Buffers 855638016 bytes
Redo Buffers 14680064 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 2 - see DBWR trace file
ORA-01110: data file 2: '/oradata/orcl10g/undotbs01.dbf'
idle 2023-03-04 15:48:21> alter database datafile 2 offline drop;
Database altered.
Elapsed: 00:00:00.02
idle 2023-03-04 15:48:35> alter database open;
Database altered.
Elapsed: 00:00:00.31
idle 2023-03-04 15:48:42> drop tablespace undotbs1;
Tablespace dropped.
Elapsed: 00:00:00.08
idle 2023-03-04 15:49:19>
idle 2023-03-04 15:49:20>
idle 2023-03-04 15:49:20> create undo tablespace undotbs1 datafile '/oradata/orcl10g/undotbs01.dbf' size 10m autoextend on;
Tablespace created.
Elapsed: 00:00:00.09
idle 2023-03-04 15:50:02>
idle 2023-03-04 15:50:03>
idle 2023-03-04 15:50:04> alter system set undo_management = auto scope=spfile;
System altered.
Elapsed: 00:00:00.00
idle 2023-03-04 15:50:23> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
idle 2023-03-04 15:50:35> startup
ORACLE instance started.
Total System Global Area 1174405120 bytes
Fixed Size 2095800 bytes
Variable Size 301991240 bytes
Database Buffers 855638016 bytes
Redo Buffers 14680064 bytes
Database mounted.
Database opened.
idle 2023-03-04 15:50:50> quit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining Scoring Engine
and Real Application Testing options
[oracle@yuanzj.com:/home/oracle]$ sas
SQL*Plus: Release 10.2.0.5.0 - Production on Sat Mar 4 15:50:54 2023
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining Scoring Engine
and Real Application Testing options
sys@ORCL10G 2023-03-04 15:50:54>2.2数据库有事务
--模拟事务
[oracle@yuanzj.com:/tmp]$ 10g
[oracle@yuanzj.com:/tmp]$ scott
SQL*Plus: Release 10.2.0.5.0 - Production on Sat Mar 4 16:21:05 2023
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining Scoring Engine
and Real Application Testing options
scott@ORCL10G 2023-03-04 16:21:05> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 1980-12-17 00:00:00 801 20
7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1601 300 30
7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1251 500 30
7566 JONES MANAGER 7839 1981-04-02 00:00:00 2976 20
7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1251 1400 30
7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2851 30
7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2451 10
7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3001 20
7839 KING PRESIDENT 1981-11-17 00:00:00 5001 10
7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1501 0 30
7876 ADAMS CLERK 7788 1987-05-23 00:00:00 1101 20
7900 JAMES CLERK 7698 1981-12-03 00:00:00 951 30
7902 FORD ANALYST 7566 1981-12-03 00:00:00 3001 20
7934 MILLER CLERK 7782 1982-01-23 00:00:00 1301 10
14 rows selected.
Elapsed: 00:00:00.00
scott@ORCL10G 2023-03-04 16:21:11> update emp set sal=sal+1;
14 rows updated.
Elapsed: 00:00:00.00
scott@ORCL10G 2023-03-04 16:21:30> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 1980-12-17 00:00:00 802 20
7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1602 300 30
7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1252 500 30
7566 JONES MANAGER 7839 1981-04-02 00:00:00 2977 20
7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1252 1400 30
7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2852 30
7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2452 10
7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3002 20
7839 KING PRESIDENT 1981-11-17 00:00:00 5002 10
7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1502 0 30
7876 ADAMS CLERK 7788 1987-05-23 00:00:00 1102 20
7900 JAMES CLERK 7698 1981-12-03 00:00:00 952 30
7902 FORD ANALYST 7566 1981-12-03 00:00:00 3002 20
7934 MILLER CLERK 7782 1982-01-23 00:00:00 1302 10
14 rows selected.
Elapsed: 00:00:00.01
scott@ORCL10G 2023-03-04 16:21:36>
--删除undo表空间
sys@ORCL10G 2023-03-04 16:20:38> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/oradata/orcl10g/system01.dbf
/oradata/orcl10g/undotbs01.dbf
/oradata/orcl10g/sysaux01.dbf
/oradata/orcl10g/users01.dbf
/oradata/orcl10g/example01.dbf
Elapsed: 00:00:00.00
sys@ORCL10G 2023-03-04 16:20:52> ho rm /oradata/orcl10g/undotbs01.dbf
sys@ORCL10G 2023-03-04 16:22:22> shutdown abort;
ORACLE instance shut down.
sys@ORCL10G 2023-03-04 16:22:27> quit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining Scoring Engine
and Real Application Testing options
[oracle@yuanzj.com:/home/oracle]$ sas
SQL*Plus: Release 10.2.0.5.0 - Production on Sat Mar 4 16:22:32 2023
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
Connected to an idle instance.
idle 2023-03-04 16:22:32> startup
ORACLE instance started.
Total System Global Area 1174405120 bytes
Fixed Size 2095800 bytes
Variable Size 301991240 bytes
Database Buffers 855638016 bytes
Redo Buffers 14680064 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 2 - see DBWR trace file
ORA-01110: data file 2: '/oradata/orcl10g/undotbs01.dbf'
--查看日志
vi /u01/app/ora10g/admin/orcl10g/bdump/alert_orcl10g.log
Errors in file /u01/app/ora10g/admin/orcl10g/bdump/orcl10g_dbw0_12525.trc:
ORA-01157: cannot identify/lock data file 2 - see DBWR trace file
ORA-01110: data file 2: '/oradata/orcl10g/undotbs01.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-1157 signalled during: ALTER DATABASE OPEN...
--修改undo表空间为手动模式
[oracle@yuanzj.com:/home/oracle]$ sas
SQL*Plus: Release 10.2.0.5.0 - Production on Sat Mar 4 16:24:13 2023
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining Scoring Engine
and Real Application Testing options
idle 2023-03-04 16:24:13> select status from v$instance;
STATUS
------------
MOUNTED
Elapsed: 00:00:00.00
idle 2023-03-04 16:24:37> show parameter undo;
NAME_COL_PLUS_SHOW_PARAM TYPE
-------------------------------------------------------------------------------- -----------
VALUE_COL_PLUS_SHOW_PARAM
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
undo_management string
AUTO
undo_retention integer
900
undo_tablespace string
UNDOTBS1
idle 2023-03-04 16:24:42> alter system set undo_management = manual scope=spfile;
System altered.
Elapsed: 00:00:00.01
idle 2023-03-04 16:24:57> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
--启动数据库到mount状态，新建pfile，并关闭数据库
[oracle@yuanzj.com:/home/oracle]$ sas
SQL*Plus: Release 10.2.0.5.0 - Production on Sat Mar 4 16:25:37 2023
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
Connected to an idle instance.
idle 2023-03-04 16:25:37> startup mount;
ORACLE instance started.
Total System Global Area 1174405120 bytes
Fixed Size 2095800 bytes
Variable Size 301991240 bytes
Database Buffers 855638016 bytes
Redo Buffers 14680064 bytes
Database mounted.
idle 2023-03-04 16:25:49> create pfile='/tmp/iniorcl10g.ora' from spfile;
File created.
Elapsed: 00:00:00.00
idle 2023-03-04 16:27:04> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
--在pfile中加入隐含参数
[root@yuanzj tmp]# vi iniorcl10g.ora
orcl10g.__db_cache_size=855638016
orcl10g.__java_pool_size=16777216
orcl10g.__large_pool_size=16777216
orcl10g.__shared_pool_size=268435456
orcl10g.__streams_pool_size=0
*.audit_file_dest='/u01/app/ora10g/admin/orcl10g/adump'
*.background_dump_dest='/u01/app/ora10g/admin/orcl10g/bdump'
*.compatible='10.2.0.5.0'
*.control_files='/oradata/orcl10g/control01.ctl','/oradata/orcl10g/control02.ctl','/oradata/orcl10g/control03.ctl'
*.core_dump_dest='/u01/app/ora10g/admin/orcl10g/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='orcl10g'
*.db_recovery_file_dest='/u01/app/ora10g/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orcl10gXDB)'
*.job_queue_processes=10
*.open_cursors=300
*.pga_aggregate_target=389021696
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=1167065088
*.undo_management='MANUAL'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/u01/app/ora10g/admin/orcl10g/udump'
_offline_rollback_segments=true
_corrupted_rollback_segments='_SYSSMU1$','_SYSSMU2$','_SYSSMU3$','_SYSSMU4$','_SYSSMU5$','_SYSSMU6$','_SYSSMU7$','_SYSSMU8$','_SYSSMU9$','_SYSSMU10$'
-------查看隐含参数名字--------------
select ksppinm from x$ksppi where ksppinm like '%roll%';
-------------------------------------
--使用pfile启动数据库
[oracle@yuanzj.com:/home/oracle]$ sas
SQL*Plus: Release 10.2.0.5.0 - Production on Sat Mar 4 16:31:40 2023
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
Connected to an idle instance.
idle 2023-03-04 16:31:40> startup pfile='/tmp/iniorcl10g.ora';
ORACLE instance started.
Total System Global Area 1174405120 bytes
Fixed Size 2095800 bytes
Variable Size 301991240 bytes
Database Buffers 855638016 bytes
Redo Buffers 14680064 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 2 - see DBWR trace file
ORA-01110: data file 2: '/oradata/orcl10g/undotbs01.dbf'
idle 2023-03-04 16:32:06> alter database datafile 2 offline drop;
Database altered.
Elapsed: 00:00:00.01
idle 2023-03-04 16:32:27> alter database open;
Database altered.
Elapsed: 00:00:00.32
--删除undotbs1，重建undotbs1
idle 2023-03-04 16:36:32> drop tablespace undotbs1;
Tablespace dropped.
Elapsed: 00:00:00.05
idle 2023-03-04 16:36:45>
idle 2023-03-04 16:36:47> create undo tablespace undotbs1 datafile '/oradata/orcl10g/undotbs01.dbf' size 10m autoextend on;
Tablespace created.
Elapsed: 00:00:00.09
idle 2023-03-04 16:37:12>
idle 2023-03-04 16:37:13> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
idle 2023-03-04 16:37:23> startup
ORACLE instance started.
Total System Global Area 1174405120 bytes
Fixed Size 2095800 bytes
Variable Size 301991240 bytes
Database Buffers 855638016 bytes
Redo Buffers 14680064 bytes
Database mounted.
Database opened.
idle 2023-03-04 16:37:31> alter system set undo_management=auto scope=spfile;
System altered.
Elapsed: 00:00:00.00
idle 2023-03-04 16:37:51> shu immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
idle 2023-03-04 16:38:00> startup
ORACLE instance started.
Total System Global Area 1174405120 bytes
Fixed Size 2095800 bytes
Variable Size 301991240 bytes
Database Buffers 855638016 bytes
Redo Buffers 14680064 bytes
Database mounted.
Database opened.
