- 浏览: 982201 次
- 性别:
- 来自: 杭州
文章分类
最新评论
-
孤星119:
好熟悉的数据库字段啊, 上家公司做的项目每天都跟这些字段打招呼 ...
Oracle exp compress参数引起的空间浪费 -
itspace:
quxiaoyong 写道遇到个问题,网上一搜,全他妈这篇文章 ...
数据库连接错误ORA-28547 -
quxiaoyong:
遇到个问题,网上一搜,全他妈这篇文章。你转来转去的有意思吗?
数据库连接错误ORA-28547 -
hctech:
关于version count过高的问题,不知博主是否看过ey ...
某客户数据库性能诊断报告 -
itspace:
invalid 写道写的不错,我根据这个来安装,有点理解错误了 ...
AIX 配置vncserver
由于昨天客户数据库数据文件头scn远远低于当前scn,我猜测是表空间做了begin backup。为了验证这一想法,特做了如下实验:
数据库版本为:
将数据库置于begin backup模式
切换几个归档,模拟业务正在进行
将数据库shutdown abort
恢复之前数据文件头处在冻结状态
根据客户现场用"recover database using backup controlfile;"将数据库进行恢复
可以看到v$backup已经置为 inactive
最后将数据打开
从以上实验可以看出,数据文件头scn远小于当前scn很大可能是ca软件将表空间begin backup所致。
+++++++++++++++++++++++++华丽的分割线+++++++++++++++++++++++++++++
其实恢复远比以上过程简单:
当数据处在mount状态时,查看数据文件头scn
查看数据库scn
在mount状态进行end backup,可以看到数据文件头进行了更新同步
随后只要将数据库打开即可
其中alert日志显示
数据库版本为:
引用
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
将数据库置于begin backup模式
引用
SQL> alter database begin backup;
Database altered.
SQL> select * from v$backup;
FILE# STATUS CHANGE# TIME
---------- ------------------ ---------- ---------
1 ACTIVE 9.7448E+12 20-MAR-10
2 ACTIVE 9.7448E+12 20-MAR-10
3 ACTIVE 9.7448E+12 20-MAR-10
4 ACTIVE 9.7448E+12 20-MAR-10
5 ACTIVE 9.7448E+12 20-MAR-10
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /opt/ora10g/archlog
Oldest online log sequence 1071
Next log sequence to archive 1073
Current log sequence 1073
Database altered.
SQL> select * from v$backup;
FILE# STATUS CHANGE# TIME
---------- ------------------ ---------- ---------
1 ACTIVE 9.7448E+12 20-MAR-10
2 ACTIVE 9.7448E+12 20-MAR-10
3 ACTIVE 9.7448E+12 20-MAR-10
4 ACTIVE 9.7448E+12 20-MAR-10
5 ACTIVE 9.7448E+12 20-MAR-10
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /opt/ora10g/archlog
Oldest online log sequence 1071
Next log sequence to archive 1073
Current log sequence 1073
切换几个归档,模拟业务正在进行
引用
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> /
System altered.
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> /
System altered.
将数据库shutdown abort
引用
SQL> shutdown abort
ORACLE instance shut down.
ORACLE instance shut down.
恢复之前数据文件头处在冻结状态
引用
SQL> select * from v$backup;
FILE# STATUS CHANGE# TIME
---------- ------------------ ---------- ---------
1 ACTIVE 9.7448E+12 20-MAR-10
2 ACTIVE 9.7448E+12 20-MAR-10
3 ACTIVE 9.7448E+12 20-MAR-10
4 ACTIVE 9.7448E+12 20-MAR-10
5 ACTIVE 9.7448E+12 20-MAR-10
FILE# STATUS CHANGE# TIME
---------- ------------------ ---------- ---------
1 ACTIVE 9.7448E+12 20-MAR-10
2 ACTIVE 9.7448E+12 20-MAR-10
3 ACTIVE 9.7448E+12 20-MAR-10
4 ACTIVE 9.7448E+12 20-MAR-10
5 ACTIVE 9.7448E+12 20-MAR-10
根据客户现场用"recover database using backup controlfile;"将数据库进行恢复
引用
SQL> recover database using backup controlfile;
ORA-00279: change 9744845765380 generated at 03/20/2010 14:42:22 needed for
thread 1
ORA-00289: suggestion : /opt/ora10g/archlog/1_1073_665850840.dbf
ORA-00280: change 9744845765380 for thread 1 is in sequence #1073
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
。。。
SQL> recover database using backup controlfile;
ORA-00279: change 9744845765485 generated at 03/20/2010 14:44:08 needed for
thread 1
ORA-00289: suggestion : /opt/ora10g/archlog/1_1077_665850840.dbf
ORA-00280: change 9744845765485 for thread 1 is in sequence #1077
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/opt/ora10g/oradata/dbra10g/redo02.log
Log applied.
Media recovery complete.
ORA-00279: change 9744845765380 generated at 03/20/2010 14:42:22 needed for
thread 1
ORA-00289: suggestion : /opt/ora10g/archlog/1_1073_665850840.dbf
ORA-00280: change 9744845765380 for thread 1 is in sequence #1073
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
。。。
SQL> recover database using backup controlfile;
ORA-00279: change 9744845765485 generated at 03/20/2010 14:44:08 needed for
thread 1
ORA-00289: suggestion : /opt/ora10g/archlog/1_1077_665850840.dbf
ORA-00280: change 9744845765485 for thread 1 is in sequence #1077
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/opt/ora10g/oradata/dbra10g/redo02.log
Log applied.
Media recovery complete.
可以看到v$backup已经置为 inactive
引用
SQL> select * from v$backup;
FILE# STATUS CHANGE# TIME
---------- ------------------ ---------- ---------
1 NOT ACTIVE 9.7448E+12 20-MAR-10
2 NOT ACTIVE 9.7448E+12 20-MAR-10
3 NOT ACTIVE 9.7448E+12 20-MAR-10
4 NOT ACTIVE 9.7448E+12 20-MAR-10
5 NOT ACTIVE 9.7448E+12 20-MAR-10
FILE# STATUS CHANGE# TIME
---------- ------------------ ---------- ---------
1 NOT ACTIVE 9.7448E+12 20-MAR-10
2 NOT ACTIVE 9.7448E+12 20-MAR-10
3 NOT ACTIVE 9.7448E+12 20-MAR-10
4 NOT ACTIVE 9.7448E+12 20-MAR-10
5 NOT ACTIVE 9.7448E+12 20-MAR-10
最后将数据打开
引用
SQL> alter database open RESETLOGS;
Database altered.
Database altered.
从以上实验可以看出,数据文件头scn远小于当前scn很大可能是ca软件将表空间begin backup所致。
+++++++++++++++++++++++++华丽的分割线+++++++++++++++++++++++++++++
其实恢复远比以上过程简单:
当数据处在mount状态时,查看数据文件头scn
引用
SQL> select * from v$backup;
FILE# STATUS CHANGE# TIME
---------- ------------------ --------------------- ---------
1 ACTIVE 9744845806881 20-MAR-10
2 ACTIVE 9744845806881 20-MAR-10
3 ACTIVE 9744845806881 20-MAR-10
4 ACTIVE 9744845806881 20-MAR-10
5 ACTIVE 9744845806881 20-MAR-10
SQL> col checkpoint_change# for 9999999999999999999
SQL> select checkpoint_change# from v$datafile_header;
CHECKPOINT_CHANGE#
--------------------
9744845806881
9744845806881
9744845806881
9744845806881
9744845806881
SQL> select checkpoint_change# from v$datafile;
CHECKPOINT_CHANGE#
--------------------
9744845806881
9744845806881
9744845806881
9744845806881
9744845806881
FILE# STATUS CHANGE# TIME
---------- ------------------ --------------------- ---------
1 ACTIVE 9744845806881 20-MAR-10
2 ACTIVE 9744845806881 20-MAR-10
3 ACTIVE 9744845806881 20-MAR-10
4 ACTIVE 9744845806881 20-MAR-10
5 ACTIVE 9744845806881 20-MAR-10
SQL> col checkpoint_change# for 9999999999999999999
SQL> select checkpoint_change# from v$datafile_header;
CHECKPOINT_CHANGE#
--------------------
9744845806881
9744845806881
9744845806881
9744845806881
9744845806881
SQL> select checkpoint_change# from v$datafile;
CHECKPOINT_CHANGE#
--------------------
9744845806881
9744845806881
9744845806881
9744845806881
9744845806881
查看数据库scn
引用
SQL> select checkpoint_change# from v$database;
CHECKPOINT_CHANGE#
--------------------
9744845806954
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
0
CHECKPOINT_CHANGE#
--------------------
9744845806954
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
0
在mount状态进行end backup,可以看到数据文件头进行了更新同步
引用
SQL> alter database end backup;
Database altered.
SQL> select checkpoint_change# from v$datafile;
CHECKPOINT_CHANGE#
--------------------
9744845806954
9744845806954
9744845806954
9744845806954
9744845806954
SQL> select checkpoint_change# from v$datafile_header;
CHECKPOINT_CHANGE#
--------------------
9744845806954
9744845806954
9744845806954
9744845806954
9744845806954
Database altered.
SQL> select checkpoint_change# from v$datafile;
CHECKPOINT_CHANGE#
--------------------
9744845806954
9744845806954
9744845806954
9744845806954
9744845806954
SQL> select checkpoint_change# from v$datafile_header;
CHECKPOINT_CHANGE#
--------------------
9744845806954
9744845806954
9744845806954
9744845806954
9744845806954
随后只要将数据库打开即可
引用
SQL> alter database open;
Database altered.
Database altered.
其中alert日志显示
引用
Sat Mar 20 15:03:28 2010
alter database end backup
Sat Mar 20 15:03:28 2010
Completed: alter database end backup
Sat Mar 20 15:03:57 2010
alter database open
Sat Mar 20 15:03:57 2010
Beginning crash recovery of 1 threads
parallel recovery started with 3 processes
Sat Mar 20 15:03:57 2010
Started redo scan
Sat Mar 20 15:03:57 2010
Completed redo scan
245 redo blocks read, 41 data blocks need recovery
Sat Mar 20 15:03:57 2010
Started redo application at
Thread 1: logseq 15, block 977
Sat Mar 20 15:03:57 2010
Recovery of Online Redo Log: Thread 1 Group 3 Seq 15 Reading mem 0
Mem# 0 errs 0: /opt/ora10g/oradata/dbra10g/redo03.log
Sat Mar 20 15:03:57 2010
Completed redo application
Sat Mar 20 15:03:57 2010
Completed crash recovery at
Thread 1: logseq 15, block 1222, scn 9744845826986
41 data blocks read, 41 data blocks written, 245 redo blocks read
Sat Mar 20 15:03:57 2010
LGWR: STARTING ARCH PROCESSES
ARC0 started with pid=19, OS id=8939
Sat Mar 20 15:03:57 2010
ARC0: Archival started
ARC1: Archival started
LGWR: STARTING ARCH PROCESSES COMPLETE
ARC1 started with pid=20, OS id=8941
Sat Mar 20 15:03:57 2010
Thread 1 advanced to log sequence 16
Thread 1 opened at log sequence 16
Current log# 2 seq# 16 mem# 0: /opt/ora10g/oradata/dbra10g/redo02.log
Successful open of redo thread 1
Sat Mar 20 15:03:57 2010
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Sat Mar 20 15:03:57 2010
ARC1: STARTING ARCH PROCESSES
Sat Mar 20 15:03:57 2010
SMON: enabling cache recovery
Sat Mar 20 15:03:57 2010
ARC0: Becoming the 'no FAL' ARCH
ARC0: Becoming the 'no SRL' ARCH
Sat Mar 20 15:03:57 2010
ARC2: Archival started
ARC1: STARTING ARCH PROCESSES COMPLETE
ARC1: Becoming the heartbeat ARCH
ARC2 started with pid=21, OS id=8951
Sat Mar 20 15:03:57 2010
Successfully onlined Undo Tablespace 1.
Sat Mar 20 15:03:57 2010
SMON: enabling tx recovery
Sat Mar 20 15:03:57 2010
Database Characterset is ZHS16GBK
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
QMNC started with pid=22, OS id=8960
Sat Mar 20 15:03:58 2010
Completed: alter database open
alter database end backup
Sat Mar 20 15:03:28 2010
Completed: alter database end backup
Sat Mar 20 15:03:57 2010
alter database open
Sat Mar 20 15:03:57 2010
Beginning crash recovery of 1 threads
parallel recovery started with 3 processes
Sat Mar 20 15:03:57 2010
Started redo scan
Sat Mar 20 15:03:57 2010
Completed redo scan
245 redo blocks read, 41 data blocks need recovery
Sat Mar 20 15:03:57 2010
Started redo application at
Thread 1: logseq 15, block 977
Sat Mar 20 15:03:57 2010
Recovery of Online Redo Log: Thread 1 Group 3 Seq 15 Reading mem 0
Mem# 0 errs 0: /opt/ora10g/oradata/dbra10g/redo03.log
Sat Mar 20 15:03:57 2010
Completed redo application
Sat Mar 20 15:03:57 2010
Completed crash recovery at
Thread 1: logseq 15, block 1222, scn 9744845826986
41 data blocks read, 41 data blocks written, 245 redo blocks read
Sat Mar 20 15:03:57 2010
LGWR: STARTING ARCH PROCESSES
ARC0 started with pid=19, OS id=8939
Sat Mar 20 15:03:57 2010
ARC0: Archival started
ARC1: Archival started
LGWR: STARTING ARCH PROCESSES COMPLETE
ARC1 started with pid=20, OS id=8941
Sat Mar 20 15:03:57 2010
Thread 1 advanced to log sequence 16
Thread 1 opened at log sequence 16
Current log# 2 seq# 16 mem# 0: /opt/ora10g/oradata/dbra10g/redo02.log
Successful open of redo thread 1
Sat Mar 20 15:03:57 2010
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Sat Mar 20 15:03:57 2010
ARC1: STARTING ARCH PROCESSES
Sat Mar 20 15:03:57 2010
SMON: enabling cache recovery
Sat Mar 20 15:03:57 2010
ARC0: Becoming the 'no FAL' ARCH
ARC0: Becoming the 'no SRL' ARCH
Sat Mar 20 15:03:57 2010
ARC2: Archival started
ARC1: STARTING ARCH PROCESSES COMPLETE
ARC1: Becoming the heartbeat ARCH
ARC2 started with pid=21, OS id=8951
Sat Mar 20 15:03:57 2010
Successfully onlined Undo Tablespace 1.
Sat Mar 20 15:03:57 2010
SMON: enabling tx recovery
Sat Mar 20 15:03:57 2010
Database Characterset is ZHS16GBK
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
QMNC started with pid=22, OS id=8960
Sat Mar 20 15:03:58 2010
Completed: alter database open
发表评论
-
buffer cache 的内部结构
2020-03-18 14:21 584BUFFER CACHE作为数据块的 ... -
Oracle OMC介绍
2020-03-18 13:19 495Oracle管理云服务(OMC)的大数据平台,自动收集的企业 ... -
参加Oracle勒索病毒防范专题培训会议
2019-09-27 17:15 5332019年7月22日,受邀参加Oracle勒索病毒防范专题培训 ... -
记一次内存换IO的Oracle优化
2019-09-27 16:50 835某客户数据库从P595物理 ... -
如何定位Oracle SQL执行计划变化的原因
2019-07-03 14:49 1471性能优化最难的是能够 ... -
如何定位Oracle SQL执行计划变化的原因
2018-10-30 09:24 1185性能优化最难的是能够 ... -
数据库性能优化目标
2018-10-08 10:59 528从数据库性能优化的场 ... -
数据库无法打开的原因及解决办法
2018-10-05 20:45 2146数据库的启动是一个相当复杂的过程。比如,Oracle在启动之前 ... -
怎么样彻底删除数据库?
2018-09-18 11:10 608Oracle提供了drop database命令用来删除数据库 ... -
Oracle减少日志量的方法
2018-09-10 10:17 871LGWR进程将LOG BUFFER中的 ... -
如何快速关闭数据库
2018-09-09 13:14 1240“一朝被蛇咬,十年怕井绳”。在没被“蛇”咬之前,很多DBA喜欢 ... -
关于《如何落地智能化运维》PPT
2018-05-17 10:19 1137在DTCC 2018发表《如何落地智能化运维》演讲,主要内容如 ... -
记录在redhat5.8平台安装oracle11.2容易忽视的几个问题
2018-05-11 19:58 587问题一:ping不通问题 在虚拟机上安装好linux系统后, ... -
《Oracle DBA实战攻略》第一章
2018-05-11 10:42 958即日起,不定期更新《OracleDBA实战攻略》一书电子版,请 ... -
Oracle 12c新特性
2018-05-11 10:33 907查询所有pdb [oracle@gj4 ~]$ sqlplu ... -
关于修改memory_target的值后数据库无法启动的问题
2017-02-28 12:24 3990操作系统:RHEL6.5 数据库版本:11.2.0.4 ... -
10g rac安装error while loading shared libraries libpthread.so.0 问题
2017-02-28 12:22 70311g rac安装在二节点跑脚本一般会报此错误: 解决这个问 ... -
记一次Oracle会话共享模式故障处理过程
2017-02-27 19:16 807故障简述 XXX第八人民医院HIS数据库7月13日11点左右从 ... -
RESMGR:cpu quantum等待事件处理过程
2017-02-27 18:23 2658由于数据库上线过程中出现大量的RESMGR:cpu quant ... -
谈谈log file sync
2014-03-19 14:18 1778数据库中的log file sync等待事件指的是,当user ...
相关推荐
ORACLE常用命令 一、ORACLE的启动和关闭 1、在单机环境下 要想启动或关闭ORACLE系统必须首先切换到ORACLE用户,如下 su - oracle a、启动ORACLE系统 oracle>svrmgrl SVRMGR>connect internal SVRMGR>startup ...
在Oracle数据库管理中,数据的导入与导出是常见的操作之一,主要用于数据迁移、备份或恢复等场景。本文将深入解析Oracle的导入导出语句,包括其基本语法、应用场景及高级用法,帮助读者更好地理解和掌握Oracle的导出...
2. 使用`ALTER TABLESPACE xxx BEGIN BACKUP`命令将表空间设置为备份状态。 3. 实际复制数据文件到备份位置,这可以通过操作系统命令如`COPY`完成。 4. 完成备份后,使用`ALTER TABLESPACE xxx END BACKUP`解除备份...
Oracle提供了多种备份方式,包括冷备份(Cold Backup)、热备份(Hot Backup)、RMAN(Restore and Recovery Manager)备份以及IMP(Import)等。 - **冷备份**:在数据库关闭的情况下进行的备份,适用于快速恢复场景,但...
对于每一个表空间,需要在 Primary Database 上执行 alter tablespace begin backup 语句,然后在 Standby 数据库上复制对应的数据文件。接着,在 Primary Database 上执行 alter tablespace end backup 语句,直到...
通过执行`ALTER DATABASE BEGIN BACKUP`命令开始备份,接着拷贝所有的数据文件到指定的备份目录,再用`ALTER DATABASE END BACKUP`结束备份。这种全局的热备份方法适用于整个数据库的备份。 三、基于表空间的热备份...
在IT行业中,数据库管理是确保数据安全与高效利用的关键环节,而Oracle作为全球领先的数据库管理系统之一,其功能强大且灵活性高。在企业级应用中,为了保障数据的安全性与连续性,定期的数据备份是一项必不可少的...
public class OracleBackup { public static void main(String[] args) { String url = "jdbc:oracle:thin:@//your-oracle-host:port/service-name"; String user = "your_username"; String password = "your_...
BEGIN BACKUP`等命令进行在线备份。 3. **备份重做日志文件**:在数据库运行过程中备份重做日志文件。 **适用场景:** - 大型数据库,业务不允许长时间中断的情况。 - 需要在最短时间内完成备份并恢复数据的一致性...
使用`begin backup`和`end backup`命令进行表空间备份,并使用`switch logfile`切换日志文件,最后使用`recovery database`进行恢复操作,直至指定时间点。 这个实验全面覆盖了Oracle数据库的备份与恢复策略,对于...
通过`ALTER TABLESPACE XXX BEGIN BACKUP`命令,Oracle会锁定数据文件头,这样即使在备份过程中发生了检查点,文件头的checkpoint SCN也不会被更新,而是仅增加checkpoint计数。这样做可以确保在恢复时从文件头的SCN...
Oracle数据库系统是企业级广泛应用的关系型数据库管理系统,其在数据管理、事务处理和高可用性方面表现出色。本文将深入探讨Oracle中的定时执行计划任务,这是数据库管理员(DBA)进行自动化运维的重要工具。 首先...
根据提供的文档内容,我们可以归纳和扩展出以下几个关键的Oracle知识点: ### 1. PL/SQL 数据类型引用 在PL/SQL中,如果需要引用表中字段的数据类型,正确的做法是使用 `%type`。例如,如果你有一个表 `employees` ...
BEGIN TRUNCATE TABLE table_name; END; / LOAD DATA INFILE 'data.csv' INTO TABLE table_name FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS ( column1, column2, ... ); COMMIT; EXIT...
BEGIN BACKUP),拷贝文件,然后关闭备份模式(ALTER TABLESPACE ... END BACKUP)。 - 备份归档日志文件。在归档模式下,应确保归档路径可写,否则数据库可能挂起。可以停止归档进程,备份日志,然后重新启动归档...
Guerrilla Oracle® gives you the foundation you need to build a functioning system and to begin exploring more complex technical topics. Along with a deeper understanding of the Oracle DBMS, you will ...
sys.dbms_backup_restore.restoreBackupPiece(done=>done,handle=>'/opt/oracle/flash_recovery_area/ORCL/backupset/2008_07_18/o1_mf_ncsnf_TAG20080718T203240_482fls2h_.bkp',params=>null); sys.dbms_backup_...
BEGIN BACKUP`和`END BACKUP`命令。 - **联机热备的恢复** - **完全恢复**: 使用所有可用的归档日志恢复到某个时间点。 - **不完全恢复**: 使用可用的归档日志恢复到某个时间点之前。 **3.4 分类案例** - **...
热备份则涉及使用特定的SQL命令(如`ALTER TABLESPACE BEGIN/END BACKUP`)来锁定数据文件,并在不影响数据库运行的同时进行备份。热备份需要额外的存储空间来保存归档日志,以便在恢复时追踪事务。 在数据库备份...
- **表空间**: SQL > `ALTER TABLESPACE users BEGIN BACKUP;` - **结束热备模式**: 对整个数据库或特定表空间结束热备模式。 - **数据库**: SQL > `ALTER DATABASE END BACKUP;` - **表空间**: SQL > `ALTER ...