0.主备环境
|
属性 |
主库 |
备库 |
|
IP |
192.168.24.7 |
192.168.24.8 |
|
架构 |
单实例 |
单实例 |
|
db_name |
prod |
proddg |
|
db_unique_name |
prod |
proddg |
|
instance |
prod |
proddg |
|
data files |
/oracle/app/oracle/oradata/PROD/ |
/oracle/app/oracle/oradata/PROD/ |
|
control files |
/oracle/app/oracle/oradata/PROD/control01.ctl,/oracle/app/oracle/oradata/PROD/control02.ctl |
/oracle/app/oracle/oradata/PROD/control01.ctl,/oracle/app/oracle/oradata/PROD/control02.ctl |
|
redolog files |
/oracle/app/oracle/oradata/PROD/ |
/oracle/app/oracle/oradata/PROD/ |
1.主库环境操作
1.1.检查数据库状态
1.1.1 检查数据库监听
ps -ef|grep tns
lsnrctl status LISTENER1.1.2 检查数据库进程
ps -ef |grep pmon1.1.3 检查数据库的状态
select open_mode from v$database;1.1.4 建数据库的告警日志(最近2天)
set lines 199 pagesize 300set COLSEP '|'col id for 9999999col created for a18col message_text for a140 SELECT record_id ID,
to_char(originating_timestamp, 'yyyymmdd hh24:mi:ss') created,
message_text
FROM x$dbgalertext
where originating_timestamp >= sysdate - 2
and (message_text like '%ORA-%'
OR message_text LIKE '%fatal%'
OR message_text LIKE '%error%'
OR message_text LIKE '%terminating the instance%')
ORDER BY RECORD_ID asc;1.2.业务检查
由应用维护人员停止相关业务
1.3.数据库备份
1.3.1 创建备份目录/backup，并授予响应用户权限
mkdir -p /backup
chmod 775 /backup
chown oracle:oinstall /backup1.3.2 检查数据库是否打开归档
select log_mode from v$database;如果没有打开归档，需要重启数据库到mount，打开归档
- 新建归档目录
mkdir -p /archivelog
chmod 775 /archivelog
chown oracle:oinstall /archivelog- 修改归档路径
su - oracle
sqlplus / as sysdba
alter system set log_archive_dest_1='LOCATION=/archivelog';
shutdown immediate
startup mountalter database archivelog;alter database open;- 再次检查归档情况
select log_mode from v$database;1.3.3 切换到Oracle用户执行备份
su - oracle
rman target /
run
{
allocate channel c1 type disk;
allocate channel c2 type disk;backup filesperset 3 database format '/backup/full_%d_%T_%s_%p';
sql 'alter system archive log current';
sql 'alter system archive log current';
sql 'alter system archive log current';
backup archivelog all format '/backup/arch_%d_%T_%s_%p' ;
backup current controlfile format '/backup/ctl_%d_%T_%s_%p';
backup spfile format '/backup/spfile_%d_%T_%s_%p';
release channel c1;
release channel c2;
}1.4.检查密码文件是否创建
密码文件一般创建数据库的时候就已经默认创建了的
cd $ORACLE_HOME/dbs
ls -ltr orapw*1.5.复制密码文件到备份目录
cp orapwprod /backup/orapwproddg1.6.检查force_logging模式是否开启
1.6.1 先确认是否在force_logging模式
select force_logging from v$database;1.6.2 如果force_logging未打开，需要打开force_logging
强制对数据库中所有对象启用LOGGING,避免有些表没使用LOG。
打开force_logging不用重启数据库。
alter database force logging;1.6.3 再确认是否成功修改force_logging模式
select force_logging from v$database;1.7.创建Standby redolog
给主库创建standby的redo日志组，方便角色切换为备库时使用，容量要和redo一样，数量是redo当前数量+1
要求：Standby Redo Log与Online Redo Log大小一致；
组数：【(maximum number of logfiles for each thread + 1) * maximum number of threads】即（每个节点的logfile数+1）*节点数
1.7.1 检查主库的日志情况
set lines 168 pages 168col member for a50select GROUP#,THREAD#,BYTES/1024/1024 mb,MEMBERS from v$log;select group#,type,member from v$logfile;select THREAD#,group#,sequence#,status, bytes/1024/1024 from v$standby_log;1.7.2 创建standby redolog
alter database add standby logfile thread 1 group 4 '/oracle/app/oracle/oradata/PROD/redo04.log' size 200M;alter database add standby logfile thread 1 group 5 '/oracle/app/oracle/oradata/PROD/redo05.log' size 200M;alter database add standby logfile thread 1 group 6 '/oracle/app/oracle/oradata/PROD/redo06.log' size 200M;alter database add standby logfile thread 1 group 7 '/oracle/app/oracle/oradata/PROD/redo07.log' size 200M;1.7.3 再次检查主库的日志情况
set lines 168 pages 168col member for a50select GROUP#,THREAD#,BYTES/1024/1024 mb,MEMBERS from v$log;select group#,type,member from v$logfile;select THREAD#,group#,sequence#,status, bytes/1024/1024 from v$standby_log;1.8.配置listener.ora文件
一般需要为adg单独配置一个监听，以免影响生产环境。监听文件添加以下内容：
cd $ORACLE_HOME/network/admin
vi listener.ora
SID_LIST_LISTENER_ADG =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = prod)
(ORACLE_HOME = /oracle/app/oracle/product/19.3.0/db_1)
(SID_NAME = prod)
)
)
LISTENER_ADG =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.24.7)(PORT = 1530))
)启动adg监听
lsnrctl start LISTENER_ADG检查adg监听状态
lsnrctl status LISTENER_ADG检查静态监听是否可以使用
sqlplus sys/oracle@192.168.24.7:1530/prod as sysdba1.9.配置tnsnames.ora文件
配置的是专有连接
cd $ORACLE_HOME/network/admin
vi tnsnames.ora
prodpri=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST=192.168.24.7)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME =prod)
)
)
prodstd=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST=192.168.24.8)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME =proddg)
)
)检查tnsnames.ora配置是否有问题，备库还没有配置，所以不用检测
tnsping prodpri非专有连接配置，只是给出来参考，不用配置。
prodpri=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST =192.168.24.7)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = prod)
)
)1.10.配置参数文件
1.10.1 检查关于dg配置的几个参数
检查相关参数，并记录相关参数
SQL> show parameter db_unique_name --当前为prodSQL> show parameter service_names --当前为prodSQL> show parameter log_archive_config --当前为空SQL> show parameter fal_server --当前为空SQL> show parameter standby_file_management --当前为MANUALSQL> show parameter log_archive_dest_1 --当前为 LOCATION=/archivelogSQL> show parameter log_archive_dest_2 --当前为空SQL> archive log list --当前为/archivelogSQL> show parameter db_recovery --NAME TYPE VALUE------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /oradata
db_recovery_file_dest_size big integer 50G1.10.2 修改参数
--修改参数alter system set db_unique_name=prod;
--有值可以不用修改alter system set log_archive_config='DG_CONFIG=(prod,proddg)';
--主备库db_unique_name列表alter system set fal_server=prodpri;
--tnsnames.ora配置的连接主库的连接串名，主库切换成备库的时候使用alter system set fal_client=prodstd;
--tnsnames.ora配置的连接备库库的连接串名，该参数11g已经弃用，可以不用配置alter system set standby_file_management='AUTO';
--主库切换成备库的时候使用，主库添加数据文件时自动同步创建数据文件alter system set log_archive_dest_2='SERVICE=prodstd LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=proddg'; alter system set log_archive_dest_state_2=defer;
--先停用该参数，备库恢复到mount的时候再启用alter system set db_file_name_convert='/oradata/','/u01/oradata/' scope=spfile;
--主库切换成备库的时候使用,当主备库数据文件路径不一样时进行转换alter system set log_file_name_convert='/oradata/','/u01/oradata/' scope=spfile;
--主库切换成备库的时候使用,当主备库日志文件路径不一样时进行转换1.11备份参数文件到备份目录
备份参数文件到备份目录
create pfile='/backup/pfile20230908.ora' from spfile;2.将主库的备份文件拷贝备库
2.1 检查主库的备份文件
ls -l /backup/2.2 备库环境上创建存放主库备份的目录
mkdir /backup
chmod 775 /backup
chown oracle:oinstall /backup2.3 拷贝主库文件到备库
scp -P 22 /backup/* 192.168.24.8:/backup3.备库环境操作
3.1 检查主库拷贝过来的备份文件
ls -l /backup/3.2 配置备库监听文件
一般需要为adg单独配置一个监听，以免影响生产环境。监听文件添加以下内容：
cd $ORACLE_HOME/network/admin
vi listener.ora
SID_LIST_LISTENER_ADG =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = prod)
(ORACLE_HOME = /oracle/app/oracle/product/19.3.0/db_1)
(SID_NAME = prod)
)
)
LISTENER_ADG =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.24.8)(PORT = 1530))
)
启动adg监听
lsnrctl start LISTENER_ADG
检查adg监听状态
lsnrctl status LISTENER_ADG
检查静态监听是否可以使用
sqlplus sys/oracle@192.168.24.8:1530/prod as sysdba3.3 配置tnsnames.ora文件
配置的是专有连接
cd $ORACLE_HOME/network/admin
vi tnsnames.ora
prodpri=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST=192.168.24.7)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME =prod)
)
)
prodstd=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST=192.168.24.8)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME =proddg)
)
)
检查tnsnames.ora配置是否有问题，备库已经配置监听，可以检测
tnsping prodpri
tnsping prodstd3.4 恢复口令文件
cd /backup/
cp orapwproddg $ORACLE_HOME/dbscd $ORACLE_HOME/dbs
ls -l3.5 恢复参数文件并配置为备库参数文件
cd /backup/
cp pfile20230908.ora $ORACLE_HOME/dbs/initproddg.ora
*.audit_file_dest='/oracle/app/oracle/admin/prod/adump'
*.audit_trail='db'
*.compatible='19.0.0'
*.control_files='/oracle/app/oracle/oradata/PROD/control01.ctl','/oracle/app/oracle/oradata/PROD/control02.ctl'
*.db_block_size=8192
*.db_name='prod'
*.db_unique_name=proddg
*.diagnostic_dest='/oracle/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=prodXDB)'
*.fal_client='PRODPRI'
*.fal_server='PRODSTD'
*.local_listener='LISTENER_PROD'
*.log_archive_config='DG_CONFIG=(prod,proddg)'
*.log_archive_dest_1='LOCATION=/archivelog'
*.log_archive_dest_2='SERVICE=prodpri LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=prod'
*.log_archive_dest_state_2='enable'
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.pga_aggregate_target=2048m
*.processes=640
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=6144m
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'
mkdir -p /oracle/app/oracle/admin/prod/adump
mkdir -p /archivelog
chmod 775 /archivelog
chown oracle:oinstall /archivelog3.6 启动数据库到nomount
3.6.1 将数据库启动nomount状态
startup nomount3.6.2 验证口令文件是否可以使用
sqlplus sys/oracle@192.168.3.119:1530/proddg as sysdba3.6.3 创建spfile
create spfile from pfile;3.6.4使用spfile启动数据库到mount
shutdown immediate
startup nomountshow parameter spfile3.7 恢复控制文件并启动到mount
3.7.1 恢复控制文件
cd /backup/
ls -l
rman target /
restore standby controlfile from '/backup/ctl_PROD_20230908_7_1';3.7.2 启动数据库到mount
alter database mount;3.7.3 恢复standby redolog
alter database add standby logfile thread 1 group 4 '/oracle/app/oracle/oradata/PROD/redo04.log' size 200M;alter database add standby logfile thread 1 group 5 '/oracle/app/oracle/oradata/PROD/redo05.log' size 200M;alter database add standby logfile thread 1 group 6 '/oracle/app/oracle/oradata/PROD/redo06.log' size 200M;alter database add standby logfile thread 1 group 7 '/oracle/app/oracle/oradata/PROD/redo07.log' size 200M;3.8 恢复数据文件
扫描识别备份文件，如果主库备份时的路径与备库备份存放的路径一样则可以不用重新扫描识别。
catalog start with '/backup';还原数据文件
restore database;3.9 打开主库log_archive_dest_state_2
3.9.1 打开主库log_archive_dest_state_2
alter system set log_archive_dest_state_2=enable;3.9.2 切主库归档，多切几次
alter system switch logfile;alter system switch logfile;alter system switch logfile;3.9.2 检查主库日志是否传输到了备库，可以切几次归档再检查
alter session set nls_date_format='yyyy-mm-dd hh:mi:ss';
col MESSAGE for a98set lines 199select FACILITY,SEVERITY,DEST_ID,MESSAGE_NUM,ERROR_CODE,CALLOUT,TIMESTAMP,MESSAGE from v$dataguard_status;3.9.3 检查备库日志是否接收到了日志
alter session set nls_date_format='yyyy-mm-dd hh:mi:ss';
col MESSAGE for a98set lines 199select FACILITY,SEVERITY,DEST_ID,MESSAGE_NUM,ERROR_CODE,CALLOUT,TIMESTAMP,MESSAGE from v$dataguard_status;3.8 recover database
3.8.1 在rman中操作
recover database;3.8.2 打开数据
alter database open;3.9 启用日志实时应用
alter database recover managed standby database using current logfile disconnect from session;如果要撤销日志实时应用，则执行以下语句
alter database recover managed standby database cancel;3.10 检查备库的数据同步情况
一般情况会使用以下方法查询同步情况，但是有时候不准，需要检查备库与主库的scn号是否同步才行，不能同步的时候一般是重启日志的实时应用，就是3.9的步骤。
set line 1000 pages 1000col value for a30select SOURCE_DBID,SOURCE_DB_UNIQUE_NAME,NAME,VALUE, from V$DATAGUARD_STATS ;检查主库的current_scn
select current_scn from v$database;检查备库库的current_scn
select current_scn from v$database;「链接」Oracle 11g DataGuard搭建保姆级教程
© 版权声明
文章版权归作者所有，未经允许请勿转载。如内容涉嫌侵权，请在本页底部进入<联系我们>进行举报投诉!
THE END
- 最新
- 最热
只看作者