首页 > 其他分享 >运用rman备份集搭建物理备库

运用rman备份集搭建物理备库

时间：2023-04-30 12:31:39浏览次数：51  
标签：备库 log app orcl orcldg oracle rman 备份 u01 

文档课题：运用rman备份集搭建物理备库.
主库：192.168.133.110 + oracle 11.2.0.4 + centos 7.9 主机名：leo-oracle-11g
备库：192.168.133.113 + oracle 11.2.0.4 + centos 7.9 主机名：leo-oracle-11gdg
场景描述：最初的备库环境为主库的克隆.
1、环境处理
--对克隆的备库进行相关处理，如删监听文件、tnsnames.ora等文件.
[oracle@leo-oracle-11g admin]$ rm -rf tnsnames.ora
[oracle@leo-oracle-11g admin]$ rm -rf listener.ora
--修改ip、网卡uuid、mac地址，删数据文件、归档文件.
--修改/etc/oratab参数.
[oracle@leo-oracle-11g ~]$ vi /etc/oratab
orcldg:/u01/app/oracle/product/11.2.0/db:Y
说明：将此前orcl修改为orcldg.
--修改.bash_profile文件
[oracle@leo-oracle-11g ~]$ vi .bash_profile
……
export ORACLE_SID=orcldg
说明：将oracle_sid由orcl修改为orcldg.
--修改主机名
[root@leo-oracle-11g:~]# vi /etc/hostname
leo-oracle-11gdg
--重启系统生效.
[root@leo-oracle-11g:~]# reboot
2、主库配置
2.1、确认主库参数
SYS@orcl> select name,open_mode,database_role,log_mode,force_logging from v$database;

NAME      OPEN_MODE            DATABASE_ROLE    LOG_MODE     FOR
--------- -------------------- ---------------- ------------ ---
ORCL      READ WRITE           PRIMARY          ARCHIVELOG   NO

SYS@orcl> show parameter name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cell_offloadgroup_name               string
db_file_name_convert                 string
db_name                              string      orcl
db_unique_name                       string      orcl
global_names                         boolean     FALSE
instance_name                        string      orcl
lock_name_space                      string
log_file_name_convert                string
processor_group_name                 string
service_names                        string      orcl
SYS@orcl> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/app/oracle/oradata/archivelog
Oldest online log sequence     262
Next log sequence to archive   269
Current log sequence           269
如果主库未开启归档，则执行以下操作开启归档：
shutdown immediate；
alter database mount;
alter database archive log；
alter database open；
2.2、开启force logging
--若主库未开启force logging，则将其开启.
SYS@orcl> select force_logging from v$database;

FOR
---
NO

SYS@orcl> alter database force logging;

Database altered.

SYS@orcl> select force_logging from v$database;

FOR
---
YES

SYS@orcl> select name,db_unique_name,database_role,log_mode,force_logging from v$database;

NAME      DB_UNIQUE_NAME                 DATABASE_ROLE    LOG_MODE     FOR
--------- ------------------------------ ---------------- ------------ ---
ORCL      orcl                           PRIMARY          ARCHIVELOG   YES
2.3、配置tnsnames.ora
[oracle@leo-oracle-11g admin]$ vi tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = leo-oracle-11g)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

ORCLDG =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = leo-oracle-11gdg)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcldg)
    )
  )

说明：蓝颜色为新添加内容.
2.4、修改参数文件
--修改主库参数文件.
SYS@orcl> alter system set log_archive_config='dg_config=(orcl,orcldg)';

System altered.

SYS@orcl> alter system set log_archive_dest_1='location=/u01/app/oracle/oradata/archivelog/ valid_for=(all_logfiles,all_roles) db_unique_name=orcl';

System altered.

SYS@orcl> alter system set log_archive_dest_2='service=orcldg lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=orcldg';

System altered.

SYS@orcl> alter system set log_archive_dest_state_1='enable';

System altered.

SYS@orcl> alter system set log_archive_dest_state_2='defer';  --配置从库后再开启

System altered.

SYS@orcl> alter system set standby_file_management='auto';

System altered.

SYS@orcl> alter system set fal_server=orcldg;

System altered.

SYS@orcl> alter system set fal_client=orcl;

System altered.
2.5、传输文件
--主库生成参数文件，并将其传输到备库.
SYS@orcl> create pfile from spfile;

File created.
--将参数文件、tnsnames.ora文件、监听文件、密码文件传递到备库.
$ pwd
/u01/app/oracle/product/11.2.0/db/dbs
$ scp initorcl.ora [email protected]:/u01/app/oracle/product/11.2.0/db/dbs
$ scp orapworcl [email protected]:/u01/app/oracle/product/11.2.0/db/dbs
$ pwd
/u01/app/oracle/product/11.2.0/db/network/admin
$ scp tnsnames.ora [email protected]:/u01/app/oracle/product/11.2.0/db/network/admin
$ scp listener.ora [email protected]:/u01/app/oracle/product/11.2.0/db/network/admin

2.6、域名解析
--添加域名解析.
[root@leo-oracle-11g:~]# cat /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
## OracleBegin
## Public IP
192.168.133.110 leo-oracle-11g
192.168.133.113 leo-oracle-11gdg

说明：蓝颜色为添加的域名解析.
3、备库配置
3.1、修改监听
--配置静态监听，以及修改host参数.
[oracle@leo-oracle-11gdg admin]$ vi listener.ora 
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = orcldg)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db)
      (SID_NAME = orcldg)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = leo-oracle-11gdg)(PORT = 1521))
    )
  )

ADR_BASE_LISTENER = /u01/app/oracle
说明：蓝色为修改后的监听文件.
--修改后重启监听.
[oracle@leo-oracle-11gdg admin]$ lsnrctl stop
[oracle@leo-oracle-11gdg admin]$ lsnrctl start
3.2、添加域名解析
--备库添加域名解析.
[root@leo-oracle-11gdg:~]# vi /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
## OracleBegin
## Public IP
192.168.133.110 leo-oracle-11g
192.168.133.113 leo-oracle-11gdg
说明：蓝颜色为添加的域名解析.
3.3、修改文件
--重命名密码文件、参数文件.
[oracle@leo-oracle-11gdg dbs]$ mv orapworcl orapworcldg
[oracle@leo-oracle-11gdg dbs]$ mv initorcl.ora initorcldg.ora
[oracle@leo-oracle-11gdg dbs]$ vi initorcldg.ora
--按如下内容修改参数文件.
*._b_tree_bitmap_plans=FALSE
*._cleanup_rollback_entries=2000
*._datafile_write_errors_crash_instance=FALSE
*._index_partition_large_extents='FALSE'
*._memory_imm_mode_without_autosga=FALSE
*._optimizer_adaptive_cursor_sharing=FALSE
*._optimizer_extended_cursor_sharing='NONE'
*._optimizer_extended_cursor_sharing_rel='NONE'
*._optimizer_null_aware_antijoin=FALSE
*._optimizer_use_feedback=FALSE
*._partition_large_extents='FALSE'
*._PX_use_large_pool=TRUE
*._undo_autotune=FALSE
*._use_adaptive_log_file_sync='FALSE'
*.audit_file_dest='/u01/app/oracle/admin/orcldg/adump'
*.audit_trail='NONE'
*.compatible='11.2.0.4.0'
*.control_file_record_keep_time=31
*.db_block_size=8192
*.db_domain=''
*.db_files=5000
*.db_name='orcl'
*.db_unique_name='orcldg'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=4385144832
*.deferred_segment_creation=FALSE
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.enable_ddl_logging=TRUE
*.fal_client='ORCLDG'
*.fal_server='ORCL'
*.log_archive_config='dg_config=(orcl,orcldg)'
*.log_archive_dest_1='location=/u01/app/oracle/archivelog/ valid_for=(all_logfiles,all_roles) db_unique_name=orcldg'
*.log_archive_dest_2='service=orcl lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=orcl'
*.log_archive_dest_state_1='enable'
*.log_archive_dest_state_2='enable'
*.log_archive_format='%t_%s_%r.dbf'
*.open_cursors=1000
*.parallel_max_servers=64
*.pga_aggregate_target=1107103908
*.processes=2000
*.remote_login_passwordfile='EXCLUSIVE'
*.resource_limit=TRUE
*.resource_manager_plan='force:'
*.sec_case_sensitive_logon=FALSE
*.session_cached_cursors=300
*.sga_max_size=1660655862
*.sga_target=1660655862
*.standby_file_management='auto'
*.undo_retention=10800
*.undo_tablespace='UNDOTBS1'
*.control_files='/u01/app/oracle/oradata/orcldg/control01.ctl'
*.log_file_name_convert='/u01/app/oracle/oradata/orcl/','/u01/app/oracle/oradata/orcldg/','/u01/app/oracle/fast_recovery_area/orcl/','/u01/app/oracle/fast_recovery_area/orcldg/','/u01/app/oracle/oradata/ORCL/onlinelog/','/u01/app/oracle/oradata/orcldg/','/u01/app/oracle/fast_recovery_area/ORCL/onlinelog/','/u01/app/oracle/oradata/orcldg/'
*.db_file_name_convert='/u01/app/oracle/oradata/orcl/','/u01/app/oracle/oradata/orcldg/', '/u02/app/oracle/oradata/orcl/','/u02/app/oracle/oradata/orcldg/'
说明：参数文件的修改根据实际环境进行修改.
3.4、创建文件目录
[oracle@leo-oracle-11gdg dbs]$ mkdir -p /u01/app/oracle/admin/orcldg/adump
[oracle@leo-oracle-11gdg dbs]$ mkdir -p /u01/app/oracle/oradata/orcldg/
[oracle@leo-oracle-11gdg dbs]$ mkdir -p /u01/app/oracle/fast_recovery_area/orcldg/
[oracle@leo-oracle-11gdg dbs]$ mkdir -p /u01/app/oracle/archivelog/
[oracle@leo-oracle-11gdg dbs]$ mkdir -p /u02/app/oracle/oradata/orcldg
3.5、测试互通性
--将备库以修改后的参数文件启动到nomount阶段进行互通性的测试.
SYS@orcldg> startup nomount pfile='/u01/app/oracle/product/11.2.0/db/dbs/initorcldg.ora';
ORACLE instance started.

Total System Global Area 1653518336 bytes
Fixed Size                  2253784 bytes
Variable Size            1241517096 bytes
Database Buffers          402653184 bytes
Redo Buffers                7094272 bytes
SYS@orcldg> create spfile from pfile='/u01/app/oracle/product/11.2.0/db/dbs/initorcldg.ora';

File created.
主备测试连通性
tnsping orcldg
tnsping orcl
sqlplus sys/oracle@orcl as sysdba
sqlplus sys/oracle@orcldg as sysdba
4、主库备份集
4.1、备份
run{
allocate channel c1 type disk;
allocate channel c2 type disk;
sql' alter system archive log current';
backup as compressed backupset database format '/home/oracle/backup/rman/full_db_%U';
sql' alter system archive log current';
backup archivelog all format '/home/oracle/backup/rman/archlog_%U';
backup current controlfile format '/home/oracle/backup/rman/controlfile_%U';
backup spfile format '/home/oracle/backup/rman/spfile_%U';
release channel c1;
release channel c2;
}
4.2、传递备库
[oracle@leo-oracle-11g rman]$ pwd
/home/oracle/backup/rman
[oracle@leo-oracle-11g rman]$ scp * [email protected]:/home/oracle/backup
5、备库恢复
[oracle@leo-oracle-11gdg ~]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Sat Apr 29 17:35:18 2023

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL (not mounted)

RMAN> restore standby controlfile from '/home/oracle/backup/controlfile_151qr1id_1_1';

Starting restore at 29-APR-23
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/app/oracle/oradata/orcldg/control01.ctl
Finished restore at 29-APR-23
RMAN> alter database mount;
RMAN> catalog start with '/home/oracle/backup';
RMAN> restore database;

Starting restore at 29-APR-23
using channel ORA_DISK_1

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 00002 to /u01/app/oracle/oradata/orcldg/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/orcldg/users01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/backup/full_db_101qr1gd_1_1
channel ORA_DISK_1: piece handle=/home/oracle/backup/full_db_101qr1gd_1_1 tag=TAG20230429T173156
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
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/orcldg/system01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/orcldg/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /u02/app/oracle/oradata/orcldg/users02.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/backup/full_db_0v1qr1gd_1_1
channel ORA_DISK_1: piece handle=/home/oracle/backup/full_db_0v1qr1gd_1_1 tag=TAG20230429T173156
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:15
Finished restore at 29-APR-23

RMAN> recover database;

Starting recover at 29-APR-23
using channel ORA_DISK_1

starting media recovery

channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=276
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=277
channel ORA_DISK_1: reading from backup piece /home/oracle/backup/archlog_141qr1ib_1_1
channel ORA_DISK_1: piece handle=/home/oracle/backup/archlog_141qr1ib_1_1 tag=TAG20230429T173259
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/u01/app/oracle/archivelog/1_276_1130877590.dbf thread=1 sequence=276
archived log file name=/u01/app/oracle/archivelog/1_277_1130877590.dbf thread=1 sequence=277
unable to find archived log
archived log thread=1 sequence=278
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 04/29/2023 20:37:34
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 278 and starting SCN of 2631818

RMAN> recover database until scn 2631818;

Starting recover at 29-APR-23
using channel ORA_DISK_1

starting media recovery
Oracle Error: 
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01152: file 1 was not restored from a sufficiently old backup 
ORA-01110: data file 1: '/u01/app/oracle/oradata/orcldg/system01.dbf'

media recovery complete, elapsed time: 00:00:00

Finished recover at 29-APR-23
6、恢复后操作
--主库开启log_archive_dest_state_2.
SYS@orcl> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2='ENABLE';

System altered.
--备库open数据库.
SYS@orcldg> alter database open;

Database altered.
7、添加standby redo log
--主备添加
ALTER DATABASE ADD STANDBY LOGFILE GROUP 11 '/u01/app/oracle/oradata/orcl/standby_redo11.log' size 50M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 12 '/u01/app/oracle/oradata/orcl/standby_redo12.log' size 50M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 13 '/u01/app/oracle/oradata/orcl/standby_redo13.log' size 50M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 14 '/u01/app/oracle/oradata/orcl/standby_redo14.log' size 50M;
备库增加
ALTER DATABASE ADD STANDBY LOGFILE GROUP 11 '/u01/app/oracle/oradata/orcldg/standby_redo11.log' size 50M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 12 '/u01/app/oracle/oradata/orcldg/standby_redo12.log' size 50M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 13 '/u01/app/oracle/oradata/orcldg/standby_redo13.log' size 50M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 14 '/u01/app/oracle/oradata/orcldg/standby_redo14.log' size 50M;
8、开启实时同步
--备库开启实时同步.
SYS@orcldg> alter database recover managed standby database using current logfile disconnect from session;

Database altered.

SYS@orcldg> SELECT pid, PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY where process='RFS' or process='MRP0';

       PID PROCESS   STATUS          THREAD#  SEQUENCE#     BLOCK#     BLOCKS
---------- --------- ------------ ---------- ---------- ---------- ----------
     13283 RFS       IDLE                  0          0          0          0
     13256 RFS       IDLE                  0          0          0          0
     13258 RFS       IDLE                  1        280       1257          1
     17515 MRP0     WAIT_FOR_LOG          1        280          0          0

参考网址：https://www.cnblogs.com/lijiaman/p/11404413.html

标签：备库,log,app,orcl,orcldg,oracle,rman,备份,u01
From： https://blog.51cto.com/u_12991611/6237848

相关文章

  • SQLServer 批量备份与还原
    在现在都讲究自动化管理的时代，传统的界面操作备份还原的做法不仅浪费时间和精力，而且还很容易出错，并且这次完成后，下次再有这样的要求，必须又重头开始（估计做5次就能做得人狂吐）；于是，我们需要一套应对这种频繁操作、耗时、耗精力的通用处理方法，所以以下批处理脚本就诞生了。    脚本......
  • openGauss之物理备份与恢复实践操作(openGauss课程openGauss3.0.0)
    一、opengauss的背景和行业现状 2022年，七大openGauss商业版发布，是基于openGauss3.0推出商业发行版目前海量数据库Vastbase表现最佳，一直是TOP1作者认为之所以海量数据库Vastbase目前无法被同行超越，和各家研发实力和技术背景有关 众所周知，opengauss起源于postgresql，在此基......
  • 数据库还原失败System.Data.SqlClient.SqlError: 无法执行 BACKUP LOG，因为当前没有数
    https://www.shuzhiduo.com/A/1O5EbK6yd7/高版本可以兼容低版本的数据库哎。所以低版本可以直接还原到高版本。过程中提示数据库还原失败System.Data.SqlClient.SqlError:无法执行BACKUPLOG，因为当前没有数据库备份，按照链接中的第二个方法解决了： 在还原的界面中，取消勾选还......
  • Performance_schema中的主从复制系列表总结
    主从半同步复制是目前用得最多的MySQL复制方案，日常工作中我们一般通过showslave status语句查看当前复制过程中状态信息，基本上能满足大多数场景下的需求。Performance_schema中提供了16个关于复制的监控表（包括组复制、过滤复制等，这里我们先不讨论），showslavestatus中的大多数信......
  • 快速上手Linux核心命令（九）：文件备份与压缩
    目录tar打包备份gzip压缩或解压文件zip打包和压缩文件unzip解压zip文件scp远程文件复制rsync文件同步工具这期呢主要说一说Linux中文件备份与压缩命令，一共6个命令。这6个命令都是平常工作中非常非常常用的。tar打包备份1、简介tar可以将多个文件压缩打包、压缩。是......
  • gitLab数据备份、恢复和迁移
    自建的Gitlab服务器常常会因为使用时间的增长，其空间容量等硬件需求都需要升级，或者迁移至更高配置的服务器上。备份、迁移、恢复、升级过程如下一、gitlab备份备份前gitlab的项目如图所示 1.1修改仓库存储位置gitlab通过rpm包安装后，默认存储位置在/var/opt/gitlab/git-dat......
  • MySQL（二）备份与还原
    一.MySQL备份1.本地备份1.win+r后输入cdm2.命令行界面输入命令mysqldump-uroot-p 数据库名>存放备份sql文件的全目录，eg:  mysqldump-uroot-pbooksystem> D:\backup\booksystem.sql3.控制台没有输出~等待~结束完成2.异......
  • xtrabackup 备份与恢复
        #!/bin/bash#备份目录和文件名backup_dir=/var/backups/mysqlbackup_name=mysql_backup_$(date+%Y-%m-%d_%H-%M-%S)#MySQL连接信息mysql_user=rootmysql_password=your_password#备份命令xtrabackup--backup--user=$mysql_user--password=$mysql_......
  • 一篇文章教你学会数据备份利器rsync
     作为一个系统管理员，数据备份是非常重要的。阿铭有一次没有做好备份策略，结果磁盘坏了，数据全部丢失。所以在以后的系统维护工作中，你一定要时刻牢记给数据做备份。在Linux系统下数据备份的工具很多，但阿铭只用一种，那就是rsync，从字面意思上可以理解为remotesync（远程同步）。rsync不......
  • 10 iozone Examples for Disk I/O Performance Measurement on Linux
    https://www.thegeekstuff.com/2011/05/iozone-examples/ Aswediscussedinour Linuxperformancemonitoringintroduction article,measuringIOsubsystemperformanceisveryimportant.Ifsomeoneiscomplainingthatadatabase(oranyapplication)running......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99