oracle 11.2.0.4 dg搭建

denglj · 发表于 2021-12-7 18:07:50

本帖最后由 denglj 于 2021-12-7 18:18 编辑

1、规划

登录/注册后可看大图

7390161af342dd5f0c.png (17.08 KB, 下载次数: 248)

下载附件

2021-12-7 18:15 上传

2、磁盘处理2.1、划分磁盘空间

#fdisk/dev/sdb

为meddocbak备库划分20g.

[root@bigdata~]# partprobe

[root@bigdata~]# lsblk -p

NAME MAJ:MIN RM SIZE RO TYPE MOUNTPOINT

/dev/sdb 8:16 0 100G 0 disk

├─/dev/sdb2 8:18 0 30G 0 part

│└─/dev/mapper/meddocdg-meddocdg 252:2 0 30G 0lvm /meddocdg

├─/dev/sdb3 8:19 0 20G 0 part

└─/dev/sdb1 8:17 0 50G 0 part

└─/dev/mapper/hisorcldg-hisorcldg 252:3 0 50G 0 lvm /hisorcldg

/dev/sr0 11:0 1 4.5G 0 rom

/dev/sda 8:0 0 100G 0 disk

├─/dev/sda2 8:2 0 98G 0 part

│├─/dev/mapper/ol-swap 252:1 0 16G 0lvm [SWAP]

│└─/dev/mapper/ol-root 252:0 0 82G 0lvm /

└─/dev/sda1 8:1 0 2G 0 part /boot

#pvcreate /dev/sdb3 #创建pv

#vgcreate -s 4M orcl152 /dev/sdb3 #创建vgorcl152

# lvcreate -l 100%vg -n orcl152 orcl152 #创建lv orcl152

#mkfs.xfs /dev/orcl152/orcl152

2.2、挂载

创建挂载点：

#mkdir /orcl152

#chown -R oracle:oinstall /orcl152

#blkid |grep orcl152

/dev/mapper/orcl152-orcl152:UUID="7cd288a4-84b9-4d2a-b5bb-7d89815cfd54" TYPE="xfs"

#vim /etc/fstab

添加以下:

UUID="7cd288a4-84b9-4d2a-b5bb-7d89815cfd54" /orcl152 xfs defaults 0 0

#mount -a

3、主库操作3.1、归档情况

> archive log list;

>selectforce_logging from v$database;

3.2、查看STANDBY日志文件

SQL> selectthread#,group#,members,bytes/1024/1024 as M from v$log;

THREAD# GROUP# MEMBERS M

---------- ---------- ---------- ----------

1 1 1 50

1 2 1 50

1 3 1 50

SQL> select group#,bytes/1024/1024 as M fromv$standby_log;

GROUP# M

---------- ----------

11 50

12 50

13 50

14 50

Database altered.

3.3、修改参数文件

#设置log_archive_config

> alter system setlog_archive_config='dg_config=(orcl150,orcl151,orcl152)';

#设置备库归档目的地

> alter system setlog_archive_dest_3='service=orcl152 lgwr asyncVALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl152';

3.4、拷贝参数文件

用spfile文件创建pfile 文件：

> create pfile='/home/oracle/pfileorcl152.ora'from spfile;

$ ll

total 4

drwxr-xr-x 2 oracle oinstall 6 Nov 6 23:46 Desktop

drwxr-xr-x 2 oracle oinstall 6 Nov 6 23:46 Documents

drwxr-xr-x 2 oracle oinstall 6 Nov 6 23:46 Downloads

drwxr-xr-x 2 oracle oinstall 6 Nov 6 23:46 Music

-rw-r--r-- 1 oracle oinstall 2129 Dec 1 10:19 pfileorcl152.ora

drwxr-xr-x 2 oracle oinstall 6 Nov 6 23:46 Pictures

drwxr-xr-x 2 oracle oinstall 6 Nov 6 23:46 Public

drwxr-xr-x 2 oracle oinstall 6 Nov 6 23:46 Templates

drwxr-xr-x 2 oracle oinstall 6 Nov 6 23:46 Videos

$ scp pfileorcl152.oraoracle@192.168.133.226:/u01/app/oracle/product/11.2.0/db_1/dbs

3.5、拷贝密码文件

$ scp orapworcl150oracle@192.168.133.226:/u01/app/oracle/product/11.2.0/db_1/dbs

3.6、修改TNS 配置文件

$ vim tnsnames.ora

$ cat tnsnames.ora

# tnsnames.ora Network Configuration File:/u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

ORCL150 =

(DESCRIPTION=

(ADDRESS =(PROTOCOL = TCP)(HOST = 192.168.133.150)(PORT = 1521))

(CONNECT_DATA =

(SERVER= DEDICATED)

(SERVICE_NAME = orcl150)

)

ORCL151 =

(DESCRIPTION=

(ADDRESS =(PROTOCOL = TCP)(HOST = 192.168.133.151)(PORT = 1521))

(CONNECT_DATA =

(SERVER= DEDICATED)

(SERVICE_NAME = orcl151)

)

ORCL152 =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.133.226)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = orcl152)

)

#蓝色为修改部分

3.7、重启监听服务

$ lsnrctl stop

$ lsnrctl start

4、备库操作4.1、添加实例名

$ vi /etc/oratab

添加以下：

orcl152:/u01/app/oracle/product/11.2.0/db_1:N

$ export ORACLE_SID=orcl152

4.2、重命名文件

[oracle@bigdata dbs]$ ll

total 60

-rw-rw---- 1 oracle oinstall 1544 Dec 1 00:15 hc_hisorcldg.dat

-rw-rw---- 1 oracle oinstall 1544 Dec 1 09:20 hc_meddocdg.dat

-rw-r--r-- 1 oracle oinstall 1744 Nov 29 22:16inithisorcldg.ora

-rw-r--r-- 1 oracle oinstall 1675 Nov 30 14:42initmeddocdg.ora

-rw-r--r-- 1 oracle oinstall 2851 May 15 2009 init.ora

-rw-r----- 1 oracle oinstall 24 Nov 30 00:15 lkHISORCLDG

-rw-r----- 1 oracle oinstall 24 Nov 30 15:16 lkMEDDOCDG

-rw-r----- 1 oracle oinstall 1536 Nov 30 00:15orapwhisorcldg

-rw-r----- 1 oracle oinstall 1536 Nov 30 15:16orapwmeddocdg

-rw-r----- 1 oracle oinstall 1536 Dec 1 10:26 orapworcl150

-rw-r--r-- 1 oracle oinstall 2129 Dec 1 10:24 pfileorcl152.ora

-rw-r----- 1 oracle oinstall 5632 Nov 30 23:37spfilehisorcldg.ora

-rw-r----- 1 oracle oinstall 5632 Dec 1 09:11 spfilemeddocdg.ora

[oracle@bigdatadbs]$ mv orapworcl150 orapworcl152

4.3、修改参数文件

[oracle@bigdata dbs]$ vi pfileorcl152.ora

*._allow_resetlogs_corruption=FALSE

*.audit_trail='db'

*.compatible='11.2.0.4.0'

*.db_block_size=8192

*.db_domain=''

*.db_name='orcl150'

*.db_recovery_file_dest_size=4385144832

*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'

*.diagnostic_dest='/u01/app/oracle'

*.dispatchers='(PROTOCOL=TCP)(SERVICE=orcl151XDB)'

*.log_archive_dest_state_1='enable'

*.log_archive_dest_state_2='enable'

*.memory_target=3217031168

*.open_cursors=300

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.standby_file_management='AUTO'

*.undo_tablespace='UNDOTBS1'

*.log_archive_config='dg_config=(orcl150,orcl152)'

*.control_files='/u01/app/oracle/oradata/orcl152/control01.ctl','/u01/app/oracle/fast_recovery_area/orcl152/control02.ctl'

*.audit_file_dest='/u01/app/oracle/admin/orcl152/adump'

*.db_unique_name='orcl152'

*.fal_client='orcl152'

*.fal_server='orcl150'

*.log_archive_dest_1='location=/u01/app/oracle/oradata/orcl152/archivelog valid_for=(all_logfiles,all_roles)db_unique_name=orcl152'

*.log_archive_dest_2='service=orcl150 lgwr asyncvalid_for=(online_logfiles,primary_role) db_unique_name=orcl150'

*.db_file_name_convert='/u01/app/oracle/oradata/orcl150/','/orcl152/app/oracle/oradata/orcl152/','/u01/oracle/','/orcl152/oracle/'

*.log_file_name_convert='/u01/app/oracle/oradata/orcl150/','/orcl152/app/oracle/oradata/orcl152/','/u01/app/oracle/fast_recovery_area/orcl150/','/orcl152/app/oracle/fast_recovery_area/orcl152/'

#红色为修改内容

4.4、创建目录

[oracle@bigdata dbs]$ mkdir -p/u01/app/oracle/oradata/orcl152/

[oracle@bigdata dbs]$ mkdir -p /u01/app/oracle/fast_recovery_area/orcl152/

[oracle@bigdata dbs]$ mkdir -p /u01/app/oracle/admin/orcl152/adump

[oracle@bigdata dbs]$ mkdir -p /u01/app/oracle/oradata/orcl152/archivelog/

[oracle@bigdata dbs]$ mkdir -p /orcl152/app/oracle/oradata/orcl152/

[oracle@bigdata dbs]$ mkdir -p /orcl152/oracle/

[oracle@bigdata dbs]$ mkdir -p /orcl152/app/oracle/oradata/orcl152/

[oracle@bigdata dbs]$ mkdir -p /orcl152/app/oracle/fast_recovery_area/orcl152/

4.5、修改监听配置文件

修改监听配置文件：

[oracle@bigdata admin]$ vi listener.ora

[oracle@bigdata admin]$ cat listener.ora

# listener.ora Network Configuration File:/u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora

# Generated by Oracle configuration tools.

SID_LIST_LISTENER =

(SID_LIST=

(SID_DESC =

(GLOBAL_DBNAME = hisorcldg)

(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)

(SID_NAME = hisorcldg)

)

(SID_DESC =

(GLOBAL_DBNAME = meddocdg)

(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)

(SID_NAME = meddocdg)

)

(SID_DESC =

(GLOBAL_DBNAME = orcl152)

(ORACLE_HOME =/u01/app/oracle/product/11.2.0/db_1)

(SID_NAME = orcl152)

)

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.133.226)(PORT = 1521))

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

)

ADR_BASE_LISTENER = /u01/app/oracle

#蓝色为添加部分

4.6、修改 TNS 配置文件

[oracle@bigdataadmin]$ vi tnsnames.ora

[oracle@bigdataadmin]$ cat tnsnames.ora

#tnsnames.ora Network Configuration File:/u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora

# Generatedby Oracle configuration tools.

HISORCL =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST =192.168.133.227)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = hisorcl)

)

HISORCLDG =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST =192.168.133.226)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = hisorcldg)

)

ORCL =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST =192.168.133.225)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = orcl)

)

MEDDOCDG =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST =192.168.133.226)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = meddocdg)

)

ORCL152=

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST =192.168.133.226)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = orcl152)

)

ORCL150=

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST =192.168.133.150)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = orcl150)

)

备注：蓝色为添加部分

4.7、重启监听服务

[oracle@bigdata admin]$ lsnrctl stop

[oracle@bigdata admin]$ lsnrctl start

4.8、备库启动到nomount 状态

[oracle@bigdata admin]$ echo $ORACLE_SID

orcl152

[oracle@bigdata admin]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on WedDec 1 11:51:36 2021

Connected to an idle instance.

SQL> startup nomountpfile='/u01/app/oracle/product/11.2.0/db_1/dbs/pfileorcl152.ora';

ORACLE instance started.

Total System Global Area 3206836224 bytes

Fixed Size 2257520 bytes

Variable Size 1811942800 bytes

Database Buffers 1375731712 bytes

Redo Buffers 16904192 bytes

SQL> create spfile frompfile='/u01/app/oracle/product/11.2.0/db_1/dbs/pfileorcl152.ora';

File created.

SQL> shutdown immediate;

ORA-01507: database not mounted

ORACLE instance shut down.

SQL> startup nomount;

ORACLE instance started.

Total System Global Area 3206836224 bytes

Fixed Size 2257520 bytes

Variable Size 1811942800 bytes

Database Buffers 1375731712 bytes

Redo Buffers 16904192 bytes

5、验证监听和TNS配置

主库：

ping 192.168.133.226

tnsping orcl152

备库：

ping 192.168.133.150

tnsping orcl150

注：测试OK后，执行以下：

5.1、主库验证

$ sqlplus sys/oracle_4U@192.168.133.150:1521/orcl150as sysdba

> show parameter db_unique_name;

$ sqlplus sys/oracle_4U@192.168.133.226:1521/orcl152as sysdba

5.2、备库验证

$ export ORACLE_SID=orcl152

$ sqlplus sys/oracle_4U@192.168.133.226:1521/orcl152as sysdba

$ sqlplussys/oracle_4U@192.168.133.150:1521/orcl150 as sysdba

备注：在主备库上都能测试通过的条件下才执行下面步骤

6、恢复6.1、恢复数据库

注：确认备库nomount

然后执行以下：

[oracle@bigdata dbs]$rman target sys/oracle_4U@orcl150auxiliary sys/oracle_4U@orcl152

Recovery Manager: Release 11.2.0.4.0 - Productionon Wed Dec 1 12:53:27 2021

connected to target database: ORCL150(DBID=4034131047)

connected to auxiliary database: ORCL150 (notmounted)

RMAN> duplicate target database for standbyfrom active database;

Starting Duplicate Db at 01-DEC-21

using target database control file instead ofrecovery catalog

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=193 device type=DISK

contents of Memory Script:

{

backupas copy reuse

targetfile '/u01/app/oracle/product/11.2.0/db_1/dbs/orapworcl150' auxiliary format

'/u01/app/oracle/product/11.2.0/db_1/dbs/orapworcl152' ;

}

executing Memory Script

Starting backup at 01-DEC-21

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=71 device type=DISK

Finished backup at 01-DEC-21

contents of Memory Script:

{

backupas copy current controlfile for standby auxiliary format '/u01/app/oracle/oradata/orcl152/control01.ctl';

restoreclone controlfile to '/u01/app/oracle/fast_recovery_area/orcl152/control02.ctl' from

'/u01/app/oracle/oradata/orcl152/control01.ctl';

}

executing Memory Script

Starting backup at 01-DEC-21

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile copy

copying standby control file

output file name=/u01/app/oracle/product/11.2.0/db_1/dbs/snapcf_orcl150.ftag=TAG20211201T130321 RECID=17 STAMP=1090155801

channel ORA_DISK_1: datafile copy complete,elapsed time: 00:00:01

Finished backup at 01-DEC-21

Starting restore at 01-DEC-21

using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: copied control file copy

Finished restore at 01-DEC-21

contents of Memory Script:

{

sqlclone 'alter database mount standby database';

}

executing Memory Script

sql statement: alter database mount standbydatabase

contents of Memory Script:

{

set newnamefor tempfile 1 to

"/orcl152/app/oracle/oradata/orcl152/temp01.dbf";

switchclone tempfile all;

setnewname for datafile 1 to

"/orcl152/app/oracle/oradata/orcl152/system01.dbf";

setnewname for datafile 2 to

"/orcl152/app/oracle/oradata/orcl152/sysaux01.dbf";

setnewname for datafile 3 to

"/orcl152/app/oracle/oradata/orcl152/undotbs01.dbf";

setnewname for datafile 4 to

"/orcl152/app/oracle/oradata/orcl152/users01.dbf";

set newnamefor datafile 5 to

"/orcl152/app/oracle/oradata/orcl152/example01.dbf";

setnewname for datafile 6 to

"/orcl152/oracle/audit01.dbf";

backupas copy reuse

datafile 1 auxiliary format

"/orcl152/app/oracle/oradata/orcl152/system01.dbf" datafile

2auxiliary format

"/orcl152/app/oracle/oradata/orcl152/sysaux01.dbf" datafile

3auxiliary format

"/orcl152/app/oracle/oradata/orcl152/undotbs01.dbf" datafile

4auxiliary format

"/orcl152/app/oracle/oradata/orcl152/users01.dbf" datafile

5auxiliary format

"/orcl152/app/oracle/oradata/orcl152/example01.dbf" datafile

6auxiliary format

"/orcl152/oracle/audit01.dbf" ;

sql'alter system archive log current';

}

executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /orcl152/app/oracle/oradata/orcl152/temp01.dbfin control file

executing command: SET NEWNAME

Starting backup at 01-DEC-21

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile copy

input datafile file number=00001name=/u01/app/oracle/oradata/orcl150/system01.dbf

output file name=/orcl152/app/oracle/oradata/orcl152/system01.dbftag=TAG20211201T130329

channel ORA_DISK_1: datafile copy complete,elapsed time: 00:00:45

channel ORA_DISK_1: starting datafile copy

input datafile file number=00002name=/u01/app/oracle/oradata/orcl150/sysaux01.dbf

output file name=/orcl152/app/oracle/oradata/orcl152/sysaux01.dbftag=TAG20211201T130329

channel ORA_DISK_1: datafile copy complete,elapsed time: 00:00:36

channel ORA_DISK_1: starting datafile copy

input datafile file number=00005name=/u01/app/oracle/oradata/orcl150/example01.dbf

output file name=/orcl152/app/oracle/oradata/orcl152/example01.dbftag=TAG20211201T130329

channel ORA_DISK_1: datafile copy complete,elapsed time: 00:00:15

channel ORA_DISK_1: starting datafile copy

input datafile file number=00003name=/u01/app/oracle/oradata/orcl150/undotbs01.dbf

output file name=/orcl152/app/oracle/oradata/orcl152/undotbs01.dbftag=TAG20211201T130329

channel ORA_DISK_1: datafile copy complete,elapsed time: 00:00:15

channel ORA_DISK_1: starting datafile copy

input datafile file number=00006name=/u01/oracle/audit01.dbf

output file name=/orcl152/oracle/audit01.dbftag=TAG20211201T130329

channel ORA_DISK_1: datafile copy complete,elapsed time: 00:00:03

channel ORA_DISK_1: starting datafile copy

input datafile file number=00004name=/u01/app/oracle/oradata/orcl150/users01.dbf

output file name=/orcl152/app/oracle/oradata/orcl152/users01.dbftag=TAG20211201T130329

channel ORA_DISK_1: datafile copy complete,elapsed time: 00:00:01

Finished backup at 01-DEC-21

sql statement: alter system archive log current

contents of Memory Script:

{

switchclone datafile all;

}

executing Memory Script

datafile 1 switched to datafile copy

input datafile copy RECID=17 STAMP=1090155926file name=/orcl152/app/oracle/oradata/orcl152/system01.dbf

datafile 2 switched to datafile copy

input datafile copy RECID=18 STAMP=1090155926file name=/orcl152/app/oracle/oradata/orcl152/sysaux01.dbf

datafile 3 switched to datafile copy

input datafile copy RECID=19 STAMP=1090155926file name=/orcl152/app/oracle/oradata/orcl152/undotbs01.dbf

datafile 4 switched to datafile copy

input datafile copy RECID=20 STAMP=1090155926file name=/orcl152/app/oracle/oradata/orcl152/users01.dbf

datafile 5 switched to datafile copy

input datafile copy RECID=21 STAMP=1090155926file name=/orcl152/app/oracle/oradata/orcl152/example01.dbf

datafile 6 switched to datafile copy

input datafile copy RECID=22 STAMP=1090155926file name=/orcl152/oracle/audit01.dbf

Finished Duplicate Db at 01-DEC-21

6.2、开启实时同步

备库执行：

> alter database open;

> select status from v$instance;

> select database_role,open_mode fromv$database;

DATABASE_ROLE OPEN_MODE

---------------- --------------------

PHYSICAL STANDBY READ ONLY WITH APPLY

> alter database recover managed standbydatabase using current logfile disconnect from session;

7、验证同步7.1、最大归档序号

主库执行：

SQL> select max(sequence#) fromv$archived_log;

MAX(SEQUENCE#)

--------------

39

备库验证：

SQL> select max(sequence#) fromv$archived_log;

MAX(SEQUENCE#)

--------------

39

7.2、日志切换

主库执行：

SQL> alter system archive log current;

System altered.

SQL> select max(sequence#) fromv$archived_log;

MAX(SEQUENCE#)

--------------

40

备库验证：

SQL> select max(sequence#) fromv$archived_log;

MAX(SEQUENCE#)

--------------

40

7.3、主备库状态

主库执行：

SQL> select switchover_status,database_rolefrom v$database;

SWITCHOVER_STATUS DATABASE_ROLE

------------------------------------------------------------------------

SESSIONS ACTIVE PRIMARY

备库执行：

SQL>selectswitchover_status,database_role from v$database;

SWITCHOVER_STATUS DATABASE_ROLE

------------------------------------------------------------------------

NOT ALLOWED PHYSICAL STANDBY

7.4、创建表测试

主库执行：

SQL> create table dg(id number);

SQL> insert into dg values(1);

SQL> commit;

SQL> select * from dg;

ID

----------

1

备库验证：

SQL> select * from dg;

ID

----------

1

DG 同步，DG搭建结束.

帐号		自动登录	找回密码
密码			注册

[Oracle] oracle 11.2.0.4 dg搭建

浏览过的版块