实验:基于cancel的不完全恢复 实验环境查看 lsnrctl status select open_mode from v$database; --监听与数据库状态 show parameter recovery; select flashback_on from v$database; archive log list; --闪回与归档的配置 1)准备环境:RMAN全库备份 RMAN
实验:基于cancel的不完全恢复
实验环境查看
lsnrctl status
select open_mode from v$database;
--监听与数据库状态
show parameter recovery;
select flashback_on from v$database;
archive log list;
--闪回与归档的配置
1)准备环境:RMAN全库备份
RMAN> backup as compressed backupset full database;
--压缩备份
--backup full database ;备份集备份
Starting backup at 20-MAR-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=45 device type=DISK
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/ORA11GR2/system01.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/ORA11GR2/sysaux01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/ORA11GR2/undotbs01.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/ORA11GR2/example01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/ORA11GR2/users01.dbf
channel ORA_DISK_1: starting piece 1 at 20-MAR-13
channel ORA_DISK_1: finished piece 1 at 20-MAR-13
piece handle=/u01/app/FRA/ORA11GR2/backupset/2013_03_20/o1_mf_nnndf_TAG20130320T151949_8nlrrrfm_.bkp tag=TAG20130320T151949 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:02:17
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 20-MAR-13
channel ORA_DISK_1: finished piece 1 at 20-MAR-13
piece handle=/u01/app/FRA/ORA11GR2/backupset/2013_03_20/o1_mf_ncsnf_TAG20130320T151949_8nlrx2qs_.bkp tag=TAG20130320T151949 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 20-MAR-13
2)创建测试数据
SYS@ORA11GR2>create table scott.t1 as select 1 as id from dual;
Table created.
SYS@ORA11GR2>alter system archive log current;
System altered.
SYS@ORA11GR2>create table scott.t2 as select 2 as id from dual;
Table created.
SYS@ORA11GR2>alter system archive log current;
System altered.
SYS@ORA11GR2>create table scott.t3 as select 3 as id from dual;
Table created.
SYS@ORA11GR2>alter system archive log current;
System altered.
SYS@ORA11GR2>
SYS@ORA11GR2>select table_name,tablespace_name from dba_tables where owner='SCOTT' AND table_name LIKE 'T_';
TABLE_NAME TABLESPACE_NAME
------------------------- ------------------------------
T3 USERS
T2 USERS
T1 USERS
完成测试数据构造后,查看生成的归档日志
ls /u01/app/oracle/fast_recovery_area/PROD/archivelog/2014_10_08
ls -lrt
3)删除所有数据文件和在线数据文件
在sqlplus里面删除:
SYS@PROD>select name from v$datafile;
NAME
--------------------
/u01/app/oracle/oradata/PROD/datafile/o1_mf_system_b2251bs1_.dbf
/u01/app/oracle/oradata/PROD/datafile/o1_mf_sysaux_b2251bvo_.dbf
/u01/app/oracle/oradata/PROD/datafile/o1_mf_undotbs1_b2251bw5_.dbf
/u01/app/oracle/oradata/PROD/datafile/o1_mf_users_b2251byw_.dbf
/u01/app/oracle/oradata/PROD/datafile/o1_mf_example_b2257d0c_.dbf
/u01/app/oracle/oradata/PROD/datafile/tbs_move_01.dbf
SYS@PROD>select member from v$logfile;
MEMBER
----------------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/oradata/PROD/onlinelog/o1_mf_3_b22567o2_.log
/u01/app/oracle/fast_recovery_area/PROD/onlinelog/o1_mf_3_b2256827_.log
/u01/app/oracle/oradata/PROD/onlinelog/o1_mf_2_b2255zsg_.log
/u01/app/oracle/fast_recovery_area/PROD/onlinelog/o1_mf_2_b22560gb_.log
/u01/app/oracle/oradata/PROD/onlinelog/o1_mf_1_b2255npg_.log
/u01/app/oracle/fast_recovery_area/PROD/onlinelog/o1_mf_1_b2255nxl_.log
SYS@PROD>!rm /u01/app/oracle/oradata/PROD/datafile/*.dbf;
SYS@PROD>!rm /u01/app/oracle/oradata/PROD/onlinelog/*.log;
SYS@PROD>!rm /u01/app/oracle/fast_recovery_area/PROD/onlinelog/*.log;
在OS里面删除:
[oracle@ocmu ORA11GR2]$ pwd
/u01/app/oracle/oradata/ORA11GR2
[oracle@ocmu ORA11GR2]$ ls
control01.ctl example01.dbf redo02.log sysaux01.dbf temp01.dbf users01.dbf
control02.ctl redo01.log redo03.log system01.dbf undotbs01.dbf
[oracle@ocmu ORA11GR2]$ rm *.log
[oracle@ocmu ORA11GR2]$ ls
control01.ctl example01.dbf system01.dbf undotbs01.dbf
control02.ctl sysaux01.dbf temp01.dbf users01.dbf
[oracle@ocmu ORA11GR2]$
4)数据库启动到mount模式
SYS@ORA11GR2>shutdown abort;
ORACLE instance shut down.
SYS@ORA11GR2>startup mount;
ORACLE instance started.
Total System Global Area 841162752 bytes
Fixed Size 1339768 bytes
Variable Size 532680328 bytes
Database Buffers 301989888 bytes
Redo Buffers 5152768 bytes
Database mounted.
SYS@ORA11GR2>
5)RMAN还原数据库
RMAN> restore database;
Starting restore at 20-MAR-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=18 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/ORA11GR2/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/ORA11GR2/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/ORA11GR2/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/ORA11GR2/users01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/ORA11GR2/example01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/FRA/ORA11GR2/backupset/2013_03_20/o1_mf_nnndf_TAG20130320T151949_8nlrrrfm_.bkp
channel ORA_DISK_1: piece handle=/u01/app/FRA/ORA11GR2/backupset/2013_03_20/o1_mf_nnndf_TAG20130320T151949_8nlrrrfm_.bkp tag=TAG20130320T151949
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:03:26
Finished restore at 20-MAR-13
RMAN>
6)基于cancel恢复
SYS@ORA11GR2>recover database until cancel;
--在sqlplus中完成
ORA-00279: change 883460 generated at 03/20/2013 15:19:50 needed for thread 1
ORA-00289: suggestion :
/u01/app/FRA/ORA11GR2/archivelog/2013_03_20/o1_mf_1_8_8nlrzy8w_.arc
ORA-00280: change 883460 for thread 1 is in sequence #8
Specify log: {
--应用sequence #8为8,名字为o1_mf_1_8_8nlrzy8w_.arc的归档日志文件,直接按回车键
ORA-00279: change 884069 generated at 03/20/2013 15:23:42 needed for thread 1
ORA-00289: suggestion :
/u01/app/FRA/ORA11GR2/archivelog/2013_03_20/o1_mf_1_9_8nls091w_.arc
ORA-00280: change 884069 for thread 1 is in sequence #9
ORA-00278: log file
'/u01/app/FRA/ORA11GR2/archivelog/2013_03_20/o1_mf_1_8_8nlrzy8w_.arc' no longer
needed for this recovery
Specify log: {
--应用sequence #9为9的这个归档日志文件,直接按回车键
ORA-00279: change 884101 generated at 03/20/2013 15:23:53 needed for thread 1
ORA-00289: suggestion :
/u01/app/FRA/ORA11GR2/archivelog/2013_03_20/o1_mf_1_10_8nls0os6_.arc
ORA-00280: change 884101 for thread 1 is in sequence #10
ORA-00278: log file
'/u01/app/FRA/ORA11GR2/archivelog/2013_03_20/o1_mf_1_9_8nls091w_.arc' no longer
needed for this recovery
Specify log: {
cancel
--手动输入cancel结束恢复,不管后面有没有可恢复文件,都会结束;
Media recovery cancelled.
SYS@ORA11GR2>
SYS@ORA11GR2>alter database open resetlogs;
Database altered.
SYS@ORA11GR2>
7)检查
SYS@ORA11GR2>select table_name,tablespace_name from dba_tables where owner='SCOTT' AND table_name LIKE 'T_';
TABLE_NAME TABLESPACE_NAME
------------------------- ------------------------------
T2 USERS
T1 USERS
SYS@ORA11GR2>
注 应用了两个归档日志,第三个归档日志cancel了,从结果中也可以看到,恢复以后的数据库中,只
存在T1,T2两张表,它们俩的重做日志就在前两个归档中,T3表的所有重做条目都在第三个归档中,
由于采取了cancel,所以,T3表并未恢复

MySQL数据库升级的步骤包括:1.备份数据库,2.停止当前MySQL服务,3.安装新版本MySQL,4.启动新版本MySQL服务,5.恢复数据库。升级过程需注意兼容性问题,并可使用高级工具如PerconaToolkit进行测试和优化。

MySQL备份策略包括逻辑备份、物理备份、增量备份、基于复制的备份和云备份。1.逻辑备份使用mysqldump导出数据库结构和数据,适合小型数据库和版本迁移。2.物理备份通过复制数据文件,速度快且全面,但需数据库一致性。3.增量备份利用二进制日志记录变化,适用于大型数据库。4.基于复制的备份通过从服务器备份,减少对生产系统的影响。5.云备份如AmazonRDS提供自动化解决方案,但成本和控制需考虑。选择策略时应考虑数据库大小、停机容忍度、恢复时间和恢复点目标。

MySQLclusteringenhancesdatabaserobustnessandscalabilitybydistributingdataacrossmultiplenodes.ItusestheNDBenginefordatareplicationandfaulttolerance,ensuringhighavailability.Setupinvolvesconfiguringmanagement,data,andSQLnodes,withcarefulmonitoringandpe

在MySQL中优化数据库模式设计可通过以下步骤提升性能:1.索引优化:在常用查询列上创建索引,平衡查询和插入更新的开销。2.表结构优化:通过规范化或反规范化减少数据冗余,提高访问效率。3.数据类型选择:使用合适的数据类型,如INT替代VARCHAR,减少存储空间。4.分区和分表:对于大数据量,使用分区和分表分散数据,提升查询和维护效率。

tooptimizemysqlperformance,lofterTheSeSteps:1)inasemproperIndexingTospeedUpqueries,2)使用ExplaintplaintoAnalyzeandoptimizequeryPerformance,3)ActiveServerConfigurationStersLikeTlikeTlikeTlikeIkeLikeIkeIkeLikeIkeLikeIkeLikeIkeLikeNodb_buffer_pool_sizizeandmax_connections,4)

MySQL函数可用于数据处理和计算。1.基本用法包括字符串处理、日期计算和数学运算。2.高级用法涉及结合多个函数实现复杂操作。3.性能优化需避免在WHERE子句中使用函数,并使用GROUPBY和临时表。

MySQL批量插入数据的高效方法包括:1.使用INSERTINTO...VALUES语法,2.利用LOADDATAINFILE命令,3.使用事务处理,4.调整批量大小,5.禁用索引,6.使用INSERTIGNORE或INSERT...ONDUPLICATEKEYUPDATE,这些方法能显着提升数据库操作效率。

在MySQL中,添加字段使用ALTERTABLEtable_nameADDCOLUMNnew_columnVARCHAR(255)AFTERexisting_column,删除字段使用ALTERTABLEtable_nameDROPCOLUMNcolumn_to_drop。添加字段时,需指定位置以优化查询性能和数据结构;删除字段前需确认操作不可逆;使用在线DDL、备份数据、测试环境和低负载时间段修改表结构是性能优化和最佳实践。


热AI工具

Undresser.AI Undress
人工智能驱动的应用程序,用于创建逼真的裸体照片

AI Clothes Remover
用于从照片中去除衣服的在线人工智能工具。

Undress AI Tool
免费脱衣服图片

Clothoff.io
AI脱衣机

Video Face Swap
使用我们完全免费的人工智能换脸工具轻松在任何视频中换脸!

热门文章

热工具

螳螂BT
Mantis是一个易于部署的基于Web的缺陷跟踪工具,用于帮助产品缺陷跟踪。它需要PHP、MySQL和一个Web服务器。请查看我们的演示和托管服务。

MinGW - 适用于 Windows 的极简 GNU
这个项目正在迁移到osdn.net/projects/mingw的过程中,你可以继续在那里关注我们。MinGW:GNU编译器集合(GCC)的本地Windows移植版本,可自由分发的导入库和用于构建本地Windows应用程序的头文件;包括对MSVC运行时的扩展,以支持C99功能。MinGW的所有软件都可以在64位Windows平台上运行。

SublimeText3 英文版
推荐:为Win版本,支持代码提示!

PhpStorm Mac 版本
最新(2018.2.1 )专业的PHP集成开发工具

EditPlus 中文破解版
体积小,语法高亮,不支持代码提示功能