DM8物理备份与恢复
目录
-
物理备份:直接扫描数据库文件,找出那些已经分配、使用的数据页,拷贝并保存到备份集中。物理备份过程中,不关心数据页的具体内容是什么,也不关心数据页属于哪一张表,只是简单的根据数据库文件系统的描述,来挑选有效的数据页。
-
物理备份根据数据库服务状态分为:联机备份(热备),脱机备份(冷备)。
-
联机备份:数据库服务运行的情况下进行备份。
-
脱机备份:数据库服务关闭的情况下进行备份。
个人总结:无论是联机备份还是脱机备份,只要是库级的物理备份,恢复的时候都需要脱机进行;
完全备份只需一条语句,恢复则要分三部曲,restore-->recover-->recover(三步在下面的实验中有演示);
1、联机备份与恢复
步骤:开启归档-->联机备份-->删除数据-->关闭数据库服务-->执行三个恢复语句进行数据恢复-->启动数据库服务验证数据是否正确
1、开启归档模式:
1)查看是否数据库是否处于归档状态(N为非归档状态,Y为归档状态)
SQL> select arch_mode from v$database;
LINEID ARCH_MODE
---------- ---------
1 N
used time: 10.513(ms). Execute id is 52.
2)修改数据库为配置状态
SQL> alter database mount;
executed successfully
used time: 00:00:01.769. Execute id is 0.
3)添加归档类型及目录并开启归档
SQL> alter database add archivelog 'DEST=/dm8/arch,type=local';
executed successfully
used time: 36.225(ms). Execute id is 0.
SQL> alter database archivelog;
executed successfully
used time: 5.734(ms). Execute id is 0.
4)修改数据库为open状态
SQL> alter database open;
executed successfully
used time: 00:00:01.799. Execute id is 0.
SQL> select arch_mode from v$database;
LINEID ARCH_MODE
---------- ---------
1 Y
used time: 1.047(ms). Execute id is 53.
2、开始备份
1)初始数据:
SQL> select * from test1;
LINEID ID
---------- -----------
1 1
2 2
3 3
4 4
5 5
6 6
7 7
8 8
9 9
10 10
10 rows got
used time: 2.101(ms). Execute id is 54.
2)开始联机备份:指定备份集路径为:/dm8/data/full_bak
SQL> BACKUP DATABASE BACKUPSET '/dm8/data/full_bak';
executed successfully
used time: 00:00:02.112. Execute id is 55.
3)备份成功,删除表:
SQL> drop table test1;
executed successfully
used time: 128.207(ms). Execute id is 56.
SQL> commit;
executed successfully
used time: 0.490(ms). Execute id is 57.
4)关闭数据库服务恢复数据(无论联机备份还是脱机备份,在恢复的时候都需要脱机恢复)
[dmdba@localhost bin]$ ./DmServiceDMSERVER stop
Stopping DmServiceDMSERVER: [ OK ]
5)开始恢复数据:
还原数据库:数据库的还原包括数据库配置文件还原和数据文件还原,通过 RESTORE 命令还原后的数据库不可用,需进一步执行 RECOVER 命令,将数据库恢复到备份结束时的状态。
[dmdba@localhost bin]$ ./dmrman
dmrman V8
RMAN> restore database '/dm8/data/DAMENG/dm.ini' from backupset '/dm8/data/full_bak/';
restore database '/dm8/data/DAMENG/dm.ini' from backupset '/dm8/data/full_bak/';
file dm.key not found, use default license!
RESTORE DATABASE CHECK......
RESTORE DATABASE,data collect......
RESTORE DATABASE,database refresh ......
RESTORE BACKUPSET [/dm8/data/full_bak] START......
total 5 packages processed...
total 6 packages processed...
RESTORE DATABASE,UPDATE ctl file......
RESTORE DATABASE,REBUILD key file......
RESTORE DATABASE,CHECK db info......
RESTORE DATABASE,UPDATE db info......
total 6 packages processed...
total 6 packages processed!
CMD END.CODE:[0]
restore successfully.
time used: 00:00:01.195
6)恢复数据库
RMAN> recover database '/dm8/data/DAMENG/dm.ini' from backupset '/dm8/data/full_bak/';
recover database '/dm8/data/DAMENG/dm.ini' from backupset '/dm8/data/full_bak/';
Database mode = 0, oguid = 0
EP[0]'s cur_lsn[43462]
RESTORE RLOG CHECK......
RESTORE RLOG, gen tmp file......
RESTORE RLOG FROM BACKUPSET [/dm8/data/full_bak] START......
total 2 packages processed...
total 2 packages processed...
total 2 packages processed!
CMD END.CODE:[0]
EP:0 total 1 pkgs applied, percent: 50%
EP:0 total 2 pkgs applied, percent: 100%
Recover from archive log finished, time used:0.017s.
recover successfully!
time used: 275.419(ms)
7)更新数据库
数据库更新是指更新数据库的 DB_MAGIC,并将数据库调整为可正常工作状态。数据库更新发生在重做 REDO 日志恢复数据库后,或者目标库不需要执行重做日志已经处于一致状态的情况。
RMAN> recover database '/dm8/data/DAMENG/dm.ini' UPDATE DB_MAGIC;
recover database '/dm8/data/DAMENG/dm.ini' UPDATE DB_MAGIC;
Database mode = 0, oguid = 0
EP[0]'s cur_lsn[43466]
EP[0]'s apply_lsn[43466] >= end_lsn[43466]
recover successfully!
time used: 00:00:01.009
8)恢复过程中没有报错,启动数据库服务,查询数据并对比没有错误。
[dmdba@localhost bin]$ ./DmServiceDMSERVER start
Starting DmServiceDMSERVER: [ OK ]
[dmdba@localhost bin]$ ./disql
disql V8
username:
password:
Server[LOCALHOST:5236]:mode is normal, state is open
login used time: 7.544(ms)
SQL> select * from test1;
LINEID ID
---------- -----------
1 1
2 2
3 3
4 4
5 5
6 6
7 7
8 8
9 9
10 10
10 rows got
used time: 12.037(ms). Execute id is 4.
2、脱机备份与恢复
关闭数据库服务的情况下使用rman工具(rman工具为数据库自带工具)进行备份:
步骤:关闭数据库服务-->脱机备份-->启动数据库服务并删除数据(也可以直接在一个新初始化的实例中做恢复)-->关闭数据库服务-->执行三个恢复语句-->启动服务并验证是否恢复成功:
和前面的联机备份恢复一样,先备份,再删除一些数据,然后进行恢复,看是否成功:
1)停掉数据库服务并使用数据库安装目录下的bin目录下的rman工具进行完全备份:
[dmdba@localhost bin]$ ./DmServiceDMSERVER stop
Stopping DmServiceDMSERVER: [ OK ]
[dmdba@localhost bin]$ ./dmrman CTLSTMT="BACKUP DATABASE '/dm8/data/DAMENG/dm.ini' FULL TO BACKUP_FILE1 BACKUPSET '/dm8/data/BACKUP_FILE_01'"
dmrman V8
BACKUP DATABASE '/dm8/data/DAMENG/dm.ini' FULL TO BACKUP_FILE1 BACKUPSET '/dm8/data/BACKUP_FILE_01'
file dm.key not found, use default license!
Database mode = 0, oguid = 0
EP[0]'s cur_lsn[46019]
BACKUP DATABASE [DAMENG],execute......
CMD CHECK LSN......
BACKUP DATABASE [DAMENG],collect dbf......
CMD CHECK ......
DBF BACKUP SUBS......
total 1 packages processed...
total 2 packages processed...
total 3 packages processed...
total 4 packages processed...
total 5 packages processed...
DBF BACKUP MAIN......
BACKUPSET [/dm8/data/BACKUP_FILE_01] END, CODE [0]......
META GENERATING......
total 6 packages processed...
total 6 packages processed...
total 6 packages processed!
CMD END.CODE:[0]
backup successfully!
time used: 00:00:01.311
2)删除数据,和上面的联机备份操作一样此处省略;
3)还原数据库restore(和上面联机备份一样)
[dmdba@localhost bin]$ ./dmrman CTLSTMT="RESTORE DATABASE '/dm8/data/DAMENG/dm.ini' FROM BACKUPSET '/dm8/data/BACKUP_FILE_01'"
dmrman V8
RESTORE DATABASE '/dm8/data/DAMENG/dm.ini' FROM BACKUPSET '/dm8/data/BACKUP_FILE_01'
file dm.key not found, use default license!
RESTORE DATABASE CHECK......
RESTORE DATABASE,data collect......
RESTORE DATABASE,database refresh ......
RESTORE BACKUPSET [/dm8/data/BACKUP_FILE_01] START......
total 5 packages processed...
total 6 packages processed...
RESTORE DATABASE,UPDATE ctl file......
RESTORE DATABASE,REBUILD key file......
RESTORE DATABASE,CHECK db info......
RESTORE DATABASE,UPDATE db info......
total 6 packages processed...
total 6 packages processed!
CMD END.CODE:[0]
restore successfully.
time used: 971.740(ms)
4)恢复数据库(recover)
[dmdba@localhost bin]$ ./dmrman CTLSTMT="RECOVER DATABASE '/dm8/data/DAMENG/dm.ini' FROM BACKUPSET '/dm8/data/BACKUP_FILE_01'"
dmrman V8
RECOVER DATABASE '/dm8/data/DAMENG/dm.ini' FROM BACKUPSET '/dm8/data/BACKUP_FILE_01'
file dm.key not found, use default license!
Database mode = 0, oguid = 0
EP[0]'s cur_lsn[46019]
RESTORE RLOG CHECK......
CMD END.CODE:[603],DESC:[no log generates while the backupset [/dm8/data/BACKUP_FILE_01] created]
no log generates while the backupset [/dm8/data/BACKUP_FILE_01] created
recover successfully!
time used: 250.328(ms)
5)更新数据库(UPDATE DB_MAGIC)
[dmdba@localhost bin]$ ./dmrman CTLSTMT="RECOVER DATABASE '/dm8/data/DAMENG/dm.ini' UPDATE DB_MAGIC"
dmrman V8
RECOVER DATABASE '/dm8/data/DAMENG/dm.ini' UPDATE DB_MAGIC
file dm.key not found, use default license!
Database mode = 0, oguid = 0
EP[0]'s cur_lsn[46019]
EP[0]'s apply_lsn[46019] >= end_lsn[46019]
recover successfully!
time used: 00:00:01.009