Linux下搭建Oracle Physical DG

一、准备primary database
1.force logging 强记日志
SYS@orcl>alter database force logging; Database altered.
2.password file
SYS@orcl>select force_logging from v$database;
FOR
—
YES

[oracle@Linux dbs]$ ll orapworcl 如果没有密码文件，需要手动创建:orapwd file=orapworcl password=oracle
-rw-r----- 1 oracle oinstall 1536 Jan 24 11:11 orapworcl

3.添加standby logfile Standby logfile要求至少比log组多一个，如果不是lgwr的模式就不用建standby log
SYS@orcl>select group#,bytes from v$Log;

GROUP#      BYTES

---

     1   52428800
     2   52428800
     3   52428800

SYS@orcl>alter database add standby logfile group 4 ‘/u01/app/oracle/oradata/orcl/stdredo04.log’ size 52428800;
SYS@orcl>alter database add standby logfile group 5 ‘/u01/app/oracle/oradata/orcl/stdredo05.log’ size 52428800;
SYS@orcl>alter database add standby logfile group 6 ‘/u01/app/oracle/oradata/orcl/stdredo06.log’ size 52428800;
SYS@orcl>alter database add standby logfile group 7 ‘/u01/app/oracle/oradata/orcl/stdredo07.log’ size 52428800;

SYS@orcl>select group#,thread#,sequence#,archived,status from v$standby_log;

GROUP#    THREAD#  SEQUENCE# ARC STATUS

---

     4          0          0 YES UNASSIGNED
     5          0          0 YES UNASSIGNED
     6          0          0 YES UNASSIGNED
     7          0          0 YES UNASSIGNED

4.配置主库的参数文件 SYS@orcl>create pfile=’/home/oracle/initorcl.ora’ from spfile; SYS@orcl>select name from v$datafile; 查看数据文件的位置，在写文件位置反转时如有多个位置都要写入 NAME

/u01/app/oracle/oradata/orcl/system01.dbf
/u01/app/oracle/oradata/orcl/undotbs01.dbf
/u01/app/oracle/oradata/orcl/sysaux01.dbf
/u01/app/oracle/oradata/orcl/users01.dbf
/u01/app/oracle/oradata/orcl/example01.dbf
/u01/app/oracle/oradata/orcl/part101.dbf
/u01/app/oracle/oradata/orcl/part201.dbf
/u01/app/oracle/oradata/orcl/part301.dbf

[oracle@Linux ~]$ vi initorcl.ora
orcl.__db_cache_size=306184192
orcl.__java_pool_size=8388608
orcl.__large_pool_size=4194304
orcl.__shared_pool_size=130023424
orcl.streams_pool_size=0
*.audit_file_dest=’/u01/app/oracle/admin/orcl/adump’
*.background_dump_dest=’/u01/app/oracle/admin/orcl/bdump’
*.compatible=‘10.2.0.5.0’
*.core_dump_dest=’/u01/app/oracle/admin/orcl/cdump’
*.db_block_size=8192
*.db_domain=‘oracle’
*.db_file_multiblock_read_count=16
*.db_name=‘orcl’
*.db_recovery_file_dest=’/u01/app/oracle/flash_recovery_area’
*.db_recovery_file_dest_size=2147483648
*.dispatchers=’(PROTOCOL=TCP) (SERVICE=orclXDB)’
*.job_queue_processes=10
*.log_archive_format=’%t%s%r.dbf’
*.open_cursors=300
*.pga_aggregate_target=149946368
*.processes=150
*.remote_login_passwordfile=‘EXCLUSIVE’
*.sga_target=449839104
*.undo_management=‘AUTO’
*.undo_tablespace=‘UNDOTBS1’
*.user_dump_dest=’/u01/app/oracle/admin/orcl/udump’

db_unique_name=pri
log_archive_config=‘dg_config=(pri,std)’ — 所有DG服务器的名，都要写里
log_archive_dest_1=‘location=/u01/app/oracle/arch/pri valid_for=(all_logfiles,all_roles) db_unique_name=pri’
log_archive_dest_2=‘service=std valid_for=(online_logfiles,primary_roles) db_unique_name=std’
log_archive_dest_state_1=enable
log_archive_dest_state_2=enable
log_archive_max_processes=4

fal_server=std 对方的名
fal_client=pri 本机名
db_file_name_convert=’/u01/app/oracle/oradata/std’,’/u01/app/oracle/oradata/orcl’ 有几个写几个，先写对方，再写自己，成对写
log_file_name_convert=’/u01/app/oracle/oradata/std’,’/u01/app/oracle/oradata/orcl’
standby_file_management=auto

创建参数文件中缺少的路径：
[oracle@Linux /]$ mkdir /u01/app/oracle/arch/pri -pv 因为，改变了归档的位置，所以要创建

mkdir: created directory `/u01/app/oracle/arch’

mkdir: created directory `/u01/a pp/oracle/arch/pri’

5.primary database configure to archive mode 要确保在归档模式
SYS@orcl>archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 27
Next log sequence to archive 29
Current log sequence 29

6.backup a parameter file 先备份下spfile，然后从新修改的pfile启动数据库
SYS@orcl>shutdown immediate;
SYS@orcl>create pfile from spfile;

SYS@orcl>create spfile from pfile=’/home/oracle/initorcl.ora’
SYS@orcl>startup mount

7.primary database configure network service 更改Service Name

SYS@orcl>alter system set service_names=‘orcl.oracle,pri’; 虽然设的是Pri,但是如果在建库时设置了dbDomain，在监听中就会自动加入Domain
SYS@orcl>show parameter service

NAME TYPE VALUE

---

service_names string orcl.oracle,pri

做静态注册，与本地服务名
[oracle@Linux admin]$ vi listener.ora
LISTEN =
(DESCRIPTION_LIST =
(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = Linux.oracle)(PORT = 1521)))
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC = (GLOBAL_DBNAME = orcl.oracle) (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db) (SID_NAME = orcl))
)

[oracle@Linux admin]$ vi tnsnames.ora
pri =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = Linux.oracle)(PORT = 1521))
(CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = pri.oracle)) 因为设了DbDomain所以在这里加入了.oracle
)
EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)

[oracle@Linux admin]$ sqlplus sys/oracle@pri as sysdba 做一下测试

8.为Standby库创建一个standby control file
SYS@pri>alter database create standby controlfile as ‘/home/oracle/control01.ctl’;

++++++++++++++++++++++++++++++++++++++++++从库配置+++++++++++++++++++++++++++++++++++++++++++
二、Standby database
1.password file 需要密码文件

[oracle@Linux admin]$ cd $ORACL{E}_{H}OME/dbs[oracle@Linuxdbs]$ export ORACLE_SID=std
[oracle@Linux dbs]$ orapwd file=orapwstd password=oracle
[oracle@Linux dbs]$ ll orapwstd
-rw-r----- 1 oracle oinstall 1536 Jan 24 16:44 orapwstd

2.parameter file for standby database 修改参数文件
[oracle@Linux dbs]$ cp /home/oracle/initorcl.ora ./initstd.ora
[oracle@Linux dbs]$ vi initstd.ora
*.audit_file_dest=’/u01/app/oracle/admin/std/adump’
*.background_dump_dest=’/u01/app/oracle/admin/std/bdump’
*.compatible=‘10.2.0.5.0’
*.core_dump_dest=’/u01/app/oracle/admin/std/cdump’
*.db_block_size=8192
*.db_domain=‘oracle’
*.db_file_multiblock_read_count=16
*.db_name=‘orcl’ 不能改
*.db_recovery_file_dest=’/u01/app/oracle/flash_recovery_area’
*.db_recovery_file_dest_size=2147483648
*.dispatchers=’(PROTOCOL=TCP) (SERVICE=orclXDB)’
*.job_queue_processes=10
*.log_archive_format=’%t_%s_%r.dbf’
*.open_cursors=300
*.pga_aggregate_target=149946368
*.processes=150
*.remote_login_passwordfile=‘EXCLUSIVE’
*.sga_target=449839104
*.undo_management=‘AUTO’
*.undo_tablespace=‘UNDOTBS1’
*.user_dump_dest=’/u01/app/oracle/admin/std/udump’

db_unique_name=std
log_archive_config=‘dg_config=(pri,std)’
log_archive_dest_1=‘location=/u01/app/oracle/arch/std valid_for=(all_logfiles,all_roles) db_unique_name=std’
log_archive_dest_2=‘service=pri valid_for=(online_logfiles,primary_roles) db_unique_name=pri’
log_archive_dest_state_1=enable
log_archive_dest_state_2=enable
log_archive_max_processes=4

fal_server=pri
fal_client=std
db_file_name_convert=’/u01/app/oracle/oradata/orcl’,’/u01/app/oracle/oradata/std’
log_file_name_convert=’/u01/app/oracle/oradata/orcl’,’/u01/app/oracle/oradata/std’
standby_file_management=auto

3.创建参数文件中的目录
[oracle@Linux ~]$ mkdir /u01/app/oracle/oradata/std -pv
mkdir: created directory `/u01/app/oracle/oradata/std’

[oracle@Linux ~]$ mkdir /u01/app/oracle/admin/std/{a,b,c,u}dump -pv
mkdir: created directory `/u01/app/oracle/admin/std’

mkdir: created directory `/u01/app/oracle/admin/std/adump’

mkdir: created directory `/u01/app/oracle/admin/std/bdump’

mkdir: created directory `/u01/app/oracle/admin/std/cdump’

mkdir: created directory `/u01/app/oracle/admin/std/udump’

4.拷贝standby controlfile ，datafiles to standby database
[oracle@Linux ~]$ mkdir /u01/app/oracle/arch/std
[oracle@Linux ~]$ cp control01.ctl /u01/app/oracle/oradata/std/
[oracle@Linux ~]$ cp control01.ctl /u01/app/oracle/oradata/std/control02.ctl
[oracle@Linux ~]$ cp control01.ctl /u01/app/oracle/oradata/std/control03.ctl

[oracle@Linux ~]$ cp /u01/app/oracle/oradata/orcl/*.dbf /u01/app/oracle/oradata/std/
[oracle@Linux ~]$ ls /u01/app/oracle/oradata/std/
control01.ctl control03.ctl part101.dbf part301.dbf system01.dbf undotbs01.dbf
control02.ctl example01.dbf part201.dbf sysaux01.dbf temp01.dbf users01.dbf

5.启动standby 到mount
[oracle@Linux ~]$ export ORACLE_SID=std
[oracle@Linux ~]$ sqlplus / as sysdba
SYS@std>create spfile from pfile;
SYS@std>startup nomount;

SYS@std>archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/arch/std
Oldest online log sequence 27
Next log sequence to archive 29
Current log sequence 29

6.standby network service
[oracle@Linux admin]$ vi listener.ora
LISTEN =
(DESCRIPTION_LIST =
(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = Linux.oracle)(PORT = 1521)))
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC = (GLOBAL_DBNAME = orcl.oracle) (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db) (SID_NAME = orcl))
(SID_DESC = (GLOBAL_DBNAME = orcl.oracle) (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db) (SID_NAME = std))
)

[oracle@Linux admin]$ vi tnsnames.ora
std =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = Linux.oracle)(PORT = 1521))
(CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = std.oracle))
)

[oracle@Linux admin]$ sqlplus sys/oracle@pri as sysdba
SYS@pri>alter database open;

三、主库启动，日志切换

SYS@pri>select instance_name,status from v$instance;
INSTANCE_NAME STATUS

---

orcl OPEN
SYS@pri>alter system switch logfile;

[oracle@Linux arch]$ ls *
pri:
1_29_804418306.dbf 1_30_804418306.dbf 1_31_804418306.dbf

std:
1_29_804418306.dbf 1_30_804418306.dbf 1_31_804418306.dbf

可看到两个目录的Archive log 是一致的
如果不同步，可以看两个库的日志，如果传不过去，会有报错
当做完日志切换两个库才能一致，同步的时Archive Log

备库，一定要是在mount装态！！
要打开 应用，否则日志传过来也不跑！
sql apply service：
Standby database： 这可以解决session active 的问题，相当于后台运行，即使终端停掉也能继续执行
SYS@std> alter database recover managed standby database disconnect from session; 一定要在mount下打开 ，
//打开sql apply service

停止sql apply：
SYS@std> alter database recover managed standby database cancel;

Database altered.

SYS@std> alter database open read only;

Database altered.

Alter database open; 默认是read only的状态打开

查看：select database_role from v$databse

SYS@std>archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/arch/std
Oldest online log sequence 30
Next log sequence to archive 0
Current log sequence 32