联享品牌网站建设,互联网营销公司经营范围,seo网站诊断,南宁网站关键字优化Oracle数据备份与恢复 一、通过RMAN方式备份二、使用emp/imp和expdb/impdb工具进行备份和恢复三、使用Data guard进行备份与恢复 一、通过RMAN方式备份
通过 RMAN#xff08;Oracle 数据库备份和恢复管理器#xff09;方式备份 Oracle 数据库#xff0c;可以使用以下步骤Oracle 数据库备份和恢复管理器方式备份 Oracle 数据库可以使用以下步骤 启动归档模式 查看是否为存档模式 SQL archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 11
Current log sequence 13
SQL关闭数据库并启动归档模式 SQL shutdown;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL startup mount;
ORACLE instance started.Total System Global Area 1286066176 bytes
Fixed Size 2213016 bytes
Variable Size 352324456 bytes
Database Buffers 922746880 bytes
Redo Buffers 8781824 bytes
Database mounted.
SQL alter database archivelog;Database altered.SQL打开数据库并查看是否修改成功 SQL archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 11
Next log sequence to archive 13
Current log sequence 13
SQL如果想在归档模式下进行备份只能把数据库启动到mount状态下才可以进行 但在生产环境中一般情况下数据库是不能随意进行关闭的所以一般在安装好数据库之后直接就开启归档模式更改归档需要在mount模式下
打开 RMAN 命令行界面[oraclelocalhost ~]$ rman target /Recovery Manager: Release 11.2.0.1.0 - Production on Fri Nov 3 17:58:54 2023Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.connected to target database: ORCL (DBID1679473799)RMAN开始备份 (1) 使用backup database;对数据库直接备份
RMAN backup database;Starting backup at 03-NOV-23
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID192 device typeDISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number00001 name/u01/app/oracle/oradata/orcl/system01.dbf
input datafile file number00002 name/u01/app/oracle/oradata/orcl/sysaux01.dbf
input datafile file number00003 name/u01/app/oracle/oradata/orcl/undotbs01.dbf
input datafile file number00004 name/u01/app/oracle/oradata/orcl/users01.dbf
channel ORA_DISK_1: starting piece 1 at 03-NOV-23
channel ORA_DISK_1: finished piece 1 at 03-NOV-23
piece handle/u01/app/oracle/flash_recovery_area/ORCL/backupset/2023_11_03/o1_mf_nnndf_TAG20231103T194131_ln9q3d1w_.bkp tagTAG20231103T194131 commentNONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting 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 03-NOV-23
channel ORA_DISK_1: finished piece 1 at 03-NOV-23
piece handle/u01/app/oracle/flash_recovery_area/ORCL/backupset/2023_11_03/o1_mf_ncsnf_TAG20231103T194131_ln9q3wok_.bkp tagTAG20231103T194131 commentNONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 03-NOV-23RMAN进入相关目录查看备份结果
[oraclelocalhost ~]$ cd /u01/app/oracle/flash_recovery_area/ORCL/backupset/2023_11_03/
[oraclelocalhost 2023_11_03]$ ll
total 1016728
-rw-r----- 1 oracle oinstall 9830400 Nov 3 18:09 o1_mf_ncsnf_TAG20231103T180906_ln9kp3ff_.bkp
-rw-r----- 1 oracle oinstall 9830400 Nov 3 19:25 o1_mf_ncsnf_TAG20231103T192501_ln9p4gqt_.bkp
-rw-r----- 1 oracle oinstall 9830400 Nov 3 19:41 o1_mf_ncsnf_TAG20231103T194131_ln9q3wok_.bkp
-rw-r----- 1 oracle oinstall 1011638272 Nov 3 19:41 o1_mf_nnndf_TAG20231103T194131_ln9q3d1w_.bkp
[oraclelocalhost 2023_11_03]$rman不但可以备份也可以使用其进行管理示例如下 (2)使用backup tablespace USERS;对表空间的备份和恢复
RMAN backup tablespace USERS;Starting backup at 03-NOV-23
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number00004 name/u01/app/oracle/oradata/orcl/users01.dbf
channel ORA_DISK_1: starting piece 1 at 03-NOV-23
channel ORA_DISK_1: finished piece 1 at 03-NOV-23
piece handle/u01/app/oracle/flash_recovery_area/ORCL/backupset/2023_11_03/o1_mf_nnndf_TAG20231103T223829_lnb1h5d1_.bkp tagTAG20231103T223829 commentNONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 03-NOV-23RMAN若表空间已损毁恢复需要恢复表空间可以使用recover tablespace USERS;
使用 CROSSCHECK BACKUP 命令验证备份的完整性。RMAN 将检查备份文件的状态并将其标记为“EXPIRED”过期或“AVAILABLE”可用。 示例如下
RMAN crosscheck backup;using channel ORA_DISK_1
crosschecked backup piece: found to be AVAILABLE
backup piece handle/u01/app/oracle/flash_recovery_area/ORCL/backupset/2023_11_03/o1_mf_ncsnf_TAG20231103T180906_ln9kp3ff_.bkp RECID1 STAMP1151950147
crosschecked backup piece: found to be AVAILABLE
backup piece handle/u01/app/oracle/flash_recovery_area/ORCL/backupset/2023_11_03/o1_mf_ncsnf_TAG20231103T192501_ln9p4gqt_.bkp RECID2 STAMP1151954702
crosschecked backup piece: found to be AVAILABLE
backup piece handle/u01/app/oracle/flash_recovery_area/ORCL/backupset/2023_11_03/o1_mf_nnndf_TAG20231103T194131_ln9q3d1w_.bkp RECID3 STAMP1151955692
crosschecked backup piece: found to be AVAILABLE
backup piece handle/u01/app/oracle/flash_recovery_area/ORCL/backupset/2023_11_03/o1_mf_ncsnf_TAG20231103T194131_ln9q3wok_.bkp RECID4 STAMP1151955708
crosschecked backup piece: found to be AVAILABLE
backup piece handle/u01/app/oracle/flash_recovery_area/ORCL/backupset/2023_11_03/o1_mf_nnndf_TAG20231103T223829_lnb1h5d1_.bkp RECID5 STAMP1151966309
Crosschecked 5 objectsRMAN二、使用emp/imp和expdb/impdb工具进行备份和恢复
emp/imp是Oracle传统的备份和恢复工具它们使用二进制格式将数据导出和导入。
expdb/impdb是Oracle 10g及以上版本引入的新工具它们使用XML格式将数据导出和导入。
下面我们将详细讲解如何使用这些工具进行备份和恢复。使用emp/imp进行备份和恢复 emp和imp命令用于将Oracle数据库中的数据导出到一个文件中以便进行备份或迁移。它们可以将整个数据库或单个表导出到一个文件中然后再将文件导入到另一个数据库中。 备份数据 exp useridusername/password filed:\backup\emp_backup.dmp fully上述命令将整个数据库导出到d:\backup\emp_backup.dmp文件中。 恢复数据 imp useridusername/password filed:\backup\emp_backup.dmp fully上述命令将d:\backup\emp_backup.dmp文件中的数据导入到数据库中。 使用expdb/impdb进行备份和恢复 expdb和impdb命令是Oracle 10g及以上版本引入的新工具它们使用XML格式将数据导出和导入。它们的使用方法与emp/imp相似。 备份数据 expdp useridusername/password directorybackup_dir dumpfileexpdb_backup.dmp fully上述命令将整个数据库导出到backup_dir目录下的expdb_backup.dmp文件中。 恢复数据 impdp useridusername/password directorybackup_dir dumpfileexpdb_backup.dmp fully上述命令将backup_dir目录下的expdb_backup.dmp文件中的数据导入到数据库中。
三、使用Data guard进行备份与恢复
Oracle Data Guard是Oracle数据库提供的一种高可用性解决方案它可以实现数据备份和恢复的功能。它通过将主数据库的数据实时复制到一个或多个备用数据库以提供数据冗余和灾难恢复能力。
下面是使用Data Guard进行备份和恢复的详细讲解
配置Data Guard
首先需要在主数据库和备用数据库上配置Data Guard。在主数据库上进行以下操作 启用归档模式ALTER DATABASE ARCHIVELOG; 配置日志传输服务ALTER SYSTEM SET LOG_ARCHIVE_DEST_2SERVICEstandby_db ASYNC VALID_FOR(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAMEstandby_db;
在备用数据库上进行以下操作 启用归档模式ALTER DATABASE ARCHIVELOG; 配置日志应用服务ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;
启动Data Guard
在主数据库上启动Data Guard
启动日志传输服务ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2ENABLE;
在备用数据库上启动Data Guard
启动日志应用服务ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;
数据备份和恢复
在Data Guard配置完成后主数据库上的数据将实时复制到备用数据库。如果主数据库发生故障可以使用备用数据库进行数据恢复。
备份数据
在备用数据库上创建备份RMAN BACKUP DATABASE;
恢复数据 在备用数据库上进行故障切换ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; 将备用数据库切换为主数据库ALTER DATABASE ACTIVATE STANDBY DATABASE; 在新的备用数据库上进行数据恢复ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;
