问答文章1 问答文章501 问答文章1001 问答文章1501 问答文章2001 问答文章2501 问答文章3001 问答文章3501 问答文章4001 问答文章4501 问答文章5001 问答文章5501 问答文章6001 问答文章6501 问答文章7001 问答文章7501 问答文章8001 问答文章8501 问答文章9001 问答文章9501

oracle11g dataguard参数修改主要以什么来定义主备库

发布网友 发布时间:2022-04-24 16:59

我来回答

1个回答

热心网友 时间:2022-04-08 11:17

1 说明
基于网上的文档做了补充说明改动形成的文档。
2 判断DataGuard是否安装
select * from v$option where parameter = 'Oracle Data Guard';
3 网络配置
192.168.1.110---------------------------主库-------------实例名----ora11g(ora11g_PD)
192.168.1.111---------------------------备库-------------实例名----ora11g(ora11g_ST)
192.168.1.112---------------------------备库-------------实例名----ora11g(ora11g_SD)
4 监听配置
主库
[oracle@node1~]$ cd /ora11g/oracle/proct/11.2.0/db_1/network/admin
[oracle@node1]$ cat listener.ora

# listener.ora Network Configuration File:/ora11g/oracle/proct/11.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL =TCP)(HOST = node1)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY= EXTPROC1521))
)
)

ADR_BASE_LISTENER = /ora11g/oracle
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = ora11g)
(ORACLE_HOME =/ora11g/oracle/proct/11.2.0/db_1)
(SID_NAME = ora11g)
)
)
[oracle@node1]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File:/ora11g/oracle/proct/11.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

ORA11G_PD =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST= 192.168.1.110)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ora11g)
)
)

ORA11G_ST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL =TCP)(HOST = 192.168.1.111)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ora11g)
)
)

ORA11G_SD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL =TCP)(HOST = 192.168.1.112)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ora11g)
)
)

备库1
[oracle@node2]$ cat listener.ora

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL =TCP)(HOST = node2)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY= EXTPROC1521))
)
)

ADR_BASE_LISTENER = /ora11g/oracle

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = ora11g)
(ORACLE_HOME = /ora11g/oracle/proct/11.2.0/db_1)
(SID_NAME = ora11g)
)
)
[oracle@node2]$ cat tnsnames.ora

ORA11G_PD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL =TCP)(HOST = 192.168.1.110)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ora11g)
)
)

ORA11G_ST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL =TCP)(HOST =192.168.1.111)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ora11g)
)
)

ORA11G_SD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL =TCP)(HOST =192.168.1.112)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ora11g)
)
)

备库2
[oracle@node3]$ cat listener.ora

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL =TCP)(HOST = node3)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY= EXTPROC1521))
)
)

ADR_BASE_LISTENER = /ora11g/oracle

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = ora11g)
(ORACLE_HOME = /ora11g/oracle/proct/11.2.0/db_1)
(SID_NAME = ora11g)
)
)
[oracle@node3]$ cat tnsnames.ora

ORA11G_PD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL =TCP)(HOST = 192.168.1.110)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ora11g)
)
)

ORA11G_ST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL =TCP)(HOST =192.168.1.111)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ora11g)
)
)

ORA11G_SD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL =TCP)(HOST =192.168.1.112)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ora11g)
)
)

5 主库前期准备
设置强制写日志
SQL> select FORCE_LOGGING from v$database;
NO
SQL> alter database force logging;
SQL> select FORCE_LOGGING from v$database;
YES
6 创建口令文件
orapwd file=$ORACLE_HOME/dbs/orapwora11g password=test entries=5
7 修改主库初始化参数
创建主库pfile
sql > create pfile from spfile;
修改pfile
DB_UNIQUE_NAME='ORA11G_PD'
LOG_ARCHIVE_CONFIG='DG_CONFIG=(ORA11G_PD,ORA11G_ST,ORA11G_SD)'
LOG_ARCHIVE_DEST_1= 'LOCATION=/ora11g/oracle/archiveVALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ORA11G_PD'
LOG_ARCHIVE_DEST_2= 'SERVICE=ORA11G_ST LGWR SYNCVALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORA11G_ST'
LOG_ARCHIVE_DEST_3= 'SERVICE=ORA11G_SD LGWR SYNCVALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORA11G_SD'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
LOG_ARCHIVE_DEST_STATE_3=ENABLE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
FAL_SERVER='ora11g_st,ora11g_sd'
FAL_CLIENT='ora11g_pd'
STANDBY_FILE_MANAGEMENT=AUTO
Pfile 拷贝到备库1、2上
[node1]$scp -rp /ora11g/oracle/proct/11.2.0/db_1/dbs/initorcl.oranode2:/ora11g/oracle/proct/11.2.0/db_1/dbs/
[node1]$scp -rp /ora11g/oracle/proct/11.2.0/db_1/dbs/initorcl.oranode2:/ora11g/oracle/proct/11.2.0/db_1/dbs/

8 修改数据库运行在归档模式下
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;
9 创建备份库需要的控制文件
创建控制文件
Shutdown immediate
STARTUP MOUNT;
ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/ora11g/oracle/ora11g01.ctl';
ALTER DATABASE OPEN;
创建主库spfile
Shutdown immediate
startuppfile='/ora11g/oracle/oracle/proct/11.2.0/db_1/dbs/initora11g.ora'
sql>create spfile from pfile='/ora11g/oracle/proct/11.2.0/db_1/dbs/initora11g.ora'
;
(先把原来的干掉)
shutdown immediate;
startup

10 备份生产数据库
scp -rp /ora11g/oracle/oradata/ORA11G node2:/u01/oradata/
scp -rp /ora11g/oracle/admin/ORA11G node2:/ora11g/oracle/admin(记得在备库1、2创建admin)
11 修改备库1的pfile

DB_UNIQUE_NAME='ORA11G_ST'
LOG_ARCHIVE_CONFIG='DG_CONFIG=(ORA11G_PD,ORA11G_ST)'
LOG_ARCHIVE_DEST_1= 'LOCATION=/ora11g/oracle/archiveVALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ORA11G_ST'
LOG_ARCHIVE_DEST_2= 'SERVICE=ORA11G_PD LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)DB_UNIQUE_NAME=ORA11G_PD'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
FAL_SERVER=ORA11G_PD
FAL_CLIENT=ORA11G_ST
STANDBY_FILE_MANAGEMENT=AUTO

12 修改备库2的pfile
DB_UNIQUE_NAME='ORA11G_SD'
LOG_ARCHIVE_CONFIG='DG_CONFIG=(ORA11G_PD,ORA11G_SD)'
LOG_ARCHIVE_DEST_1= 'LOCATION=/ora11g/oracle/archiveVALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ORA11G_SD'
LOG_ARCHIVE_DEST_2= 'SERVICE=ORA11G_PD LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)DB_UNIQUE_NAME=ORA11G_PD'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
FAL_SERVER=ORA11G_PD
FAL_CLIENT=ORA11G_SD
STANDBY_FILE_MANAGEMENT=AUTO
13 将控制文件拷贝到备库1、2
[node1]$scp -rp /ora11g/oracle/ora11g01.ctlnode2:/ora11g/oracle/oradata/ORA11G/
[node1]$scp -rp /ora11g/oracle/ora11g01.ctlnode2:/ora11g/oracle/fast_recovery_area/orcl/controlfile/

[node1]$scp –rp /ora11g/oracle/ora11g01.ctl node3:/ora11g/oracle/oradata/ORA11G/
[node1]$scp -rp /ora11g/oracle/ora11g01.ctlnode3:/ora11g/oracle/fast_recovery_area/orcl/controlfile/

14 在备库1、2上创建口令文件
orapwd file=$ORACLE_HOME/dbs/orapwora11g password=oracle entries=5
15 在备库1、2上创建spfile
SQL>Shutdown immediate;
SQL>startup;
SQL>pfile='/ora11g/oracle/oracle/proct/11.2.0/db_1/dbs/initora11g.ora';
SQL>CREATE SPFILE FROM PFILE;
16 启动物理备用数据库1、2
STARTUPMOUNT;
17 配置Standby Redo Log
在主备库两边都配置standby redo log
在主库查看日志组的数量和每个日志文件的大小
SQL> SELECT GROUP#, BYTES FROM V$LOG;
在备库库查看日志组的数量和每个日志文件的大小
SQL> SELECT GROUP#, BYTES FROM V$STANDBY_LOG;
创建日志组和redo log文件
SQL> alter database add standby logfile
group 4 ('/ora11g/oracle/oracle/oradata/highfly/stdby_redo04.log') size50m,
group 5 ('/ora11g/oracle/oracle/oradata/highfly/stdby_redo05.log')size 50m,
group 6 ('/ora11g/oracle/oracle/oradata/highfly/stdby_redo06.log')size 50m,
group 7 ('/ora11g/oracle/oracle/oradata/highfly/stdby_redo07.log') size50m;
18 Start Redo Apply
在备库
SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USINGCURRENT LOGFILE DISCONNECT FROM SESSION;
SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
SQL>alter user scott account lock;
查看哪些归档日志被APPLY了
在备库
SQL>SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
在主库强制日志切换到当前的online redo log file.
SQL>ALTER SYSTEM ARCHIVE LOG CURRENT;
在备库查看新的被归档的redo data
SQL>SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROMV$ARCHIVED_LOG ORDER BY SEQUENCE#;
在备库查看接收到的被应用的redo
SQL> SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BYSEQUENCE#;
查看数据库的角色
SQL>select database_role,protection_mode,protection_levelfrom v$database;
19 主备库切换

1.查看主库的状态
SQL> SELECTSWITCHOVER_STATUS FROM V$DATABASE;
2.将主库切换至备用模式

SQL> ALTERDATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH -
> SESSION SHUTDOWN;

3.关闭、装载主数据库
SQL> SHUTDOWNABORT;
SQL> STARTUP MOUNT;
4.查看备库准备向主库模式切换
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS
-----------------
TO_PRIMARY
1 row selected
5.切换备库至主库模式
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSIONSHUTDOWN;

6.打开新的主数据库

SQL> ALTER DATABASEOPEN;

7. 在新的备库服务器上启动 REDO apply。
SQL> ALTER DATABASE RECOVER MANAGED STANDBYDATABASE USING CURRENT LOGFILE -
> DISCONNECT FROMSESSION;

20 灾难恢复(failover)

Step 1
Flushany unsent redo from the primary database to the target standby
database

SQL> ALTER SYSTEMFLUSH REDO TO target_db_name;

Step 2
Verify that the standby database has the mostrecently archived redo log
filefor each primary database redo thread.

SQL>SELECT UNIQUE THREAD# AS THREAD, MAX(SEQUENCE#) -
> OVER (PARTITION BYthread#) AS LAST from V$ARCHIVED_LOG;

SQL> ALTER DATABASEREGISTER PHYSICAL LOGFILE 'filespec1';

Step 3
Identifyand resolve any archived redo log gaps.

SQL> SELECT THREAD#,LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;

SQL> ALTER DATABASEREGISTER PHYSICAL LOGFILE 'filespec1';

Step 4
RepeatStep 3 until all gaps are resolved.

Step 5
StopRedo Apply.
Issuethe following SQL statement on the target standby database:
SQL> ALTER DATABASERECOVER MANAGED STANDBY DATABASE CANCEL;

Step 6
Finishapplying all received redo data.
Issuethe following SQL statement on the target standby database:
SQL> ALTER DATABASERECOVER MANAGED STANDBY DATABASE FINISH;

Step 7
Verifythat the target standby database is ready to become a primary
database.
声明声明:本网页内容为用户发布,旨在传播知识,不代表本网认同其观点,若有侵权等问题请及时与本网联系,我们将在第一时间删除处理。E-MAIL:11247931@qq.com
万宁到连云港龙洞优选线路推荐 烟台到南京多远多少公里:距离762公里 762km是多少公里 连云港自驾游到北京国子监路费多少 螃蟹横行霸道是被逼无奈 帝国时代3 第6关把莉丝的美国土著朋友带回附近的岛怎么完成?那个土著... 帝国时代3亚洲王朝那个大国怎么召唤土著部落 组装电脑论坛哪个好电脑组装论坛都有哪些 推荐哪个网站的笔记本电脑好笔记本电脑在哪里买比较好 电脑diy网上哪个靠谱有哪些可靠的电脑DIY网站 求《使命召唤4:现代战争》中的两段话 可以追加300分 中翻英翻译一段话,谢谢! 哪位大哥有特气气柜中各阀门的英文缩写代表的意思?谢谢! 东方神起的罗马音歌词 请问哪位亲知道神话当年离开SM公司的时候也把前6辑的版权给一块儿带走了吗? fly怎么读 怎么激活vivo手机? 什么是Standby 说粤语的电视剧 粤语怎么说啊? 电视剧忠奸人的插曲啊啊啊啊啊啊啊的叫什么名字? tvb电视剧《忠奸人》背景音乐 TVB电视剧忠奸人粤语的迅雷下载不到?为什么呢?有好的地址吗? 忠奸人完整版在线观看地址? 忠奸人一共多少集 跪求1997年上映的由阿尔·帕西诺主演的忠奸人百度云资源 想看忠奸人1997年上映的由阿尔·帕西诺主演的免费高清资源 tvb电视剧粤语版《真相》《忠奸人》百度云 忠奸人高清全集在哪里能看 求电影忠奸人无删减版下载! 求Iggy Azalea-Change Your Life歌词 好听的日文歌曲推荐 在精舞门中出演的南贤俊是哪个南贤俊?(听说有两个南贤俊。。) 明明知道没有结果,却还飞蛾扑火 这是什么歌的歌词? 明明知道没有结果是什么歌词 明知没有结果却放不下执着什么歌? 有句歌词是明知道爱你没结果歌名是什么,女生唱的 如果你爱我请对我说不要让我的爱对你恋恋不舍明明知道没有结果这什么歌曲? 明明知道我们之间没有结果 为何还要飞蛾扑火。我想知道谁首歌曲的名字。麻烦大家了! 明知道爱你不会有结果是哪首歌的歌词 明明知道没有结果,为什么还坚持执着出自哪首歌的翻译 明知道没有结果而我却一在沦洛是什么歌曲 【明知道我们不会有结果】这歌词的歌名叫? 明知道爱情没有结局我还是爱的那么彻底是什么歌? 怎么会爱上你我总会问我自己明明知道爱情没有结局求歌名我自己 怎么会爱上你我总会问我自己明知道爱情没有结局求歌名我自己 明明知道没有结果却还飞蛾扑火 这是什么歌的歌词? 怎么会爱上你,我总在问自己,明知道爱情没有结局,我还是爱的那么彻底啥歌? 求些比较有气魄,气势的词语! 描写场面,气势的成语