MAA配置文档

最新推荐文章于 2023-08-07 23:38:19 发布

crcz16618

最新推荐文章于 2023-08-07 23:38:19 发布

阅读量593

点赞数

文章标签：数据库网络

环境：两个双节点RAC

主库：

Hostname=rui1,rui2

db_name=prod

db_unique_name=primary

instance_name=prod1,prod2

service_names=primary

备库：

hostname=zhang1,zhang2

db_name=PROD

db_unique_name=standby

instance_name=std1,std2

service_names=standby

一、用rman冷备主库中的数据库文件和控制文件，并把主库中的参数文件以pfile的形式创建出来

1. 首先将数据库启动到nomount状态，创建pfile

[oracle@rui1 backup]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sat May 24 11:29:47 2014

Connected to an idle instance.

SQL> startup nomount

ORACLE instance started.

Total System Global Area 493813760 bytes

Fixed Size 1337436 bytes

Variable Size 218105764 bytes

Database Buffers 268435456 bytes

Redo Buffers 5935104 bytes

SQL> create pfile='/tmp/11.ora' from spfile;

File created.

[root@rui1 tmp]# ls | grep 11.ora

11.ora

[root@rui1 tmp]#

2. 启动到mount状态，用rman开始备份数据库（一定要先备份数据库在备份standby控制文件，否则standby控制文件中没有备份的信息，会导致在备库上无法进行恢复）

[oracle@rui1 backup]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Sat May 24 11:32:42 2014

connected to target database: PROD (not mounted)

RMAN> mount database;

using target database control file instead of recovery catalog

database mounted

RMAN>

3. 将AUTOBACKUP关掉

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP OFF;

old RMAN configuration parameters:

CONFIGURE CONTROLFILE AUTOBACKUP ON;

new RMAN configuration parameters:

CONFIGURE CONTROLFILE AUTOBACKUP OFF;

new RMAN configuration parameters are successfully stored

RMAN> show all;

RMAN configuration parameters for database with db_unique_name PRIMARY are:

CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default

CONFIGURE BACKUP OPTIMIZATION OFF; # default

CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default

CONFIGURE CONTROLFILE AUTOBACKUP OFF;

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u01/backup/prod_%F';

CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default

CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE MAXSETSIZE TO UNLIMITED; # default

CONFIGURE ENCRYPTION FOR DATABASE OFF; # default

CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default

CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default

CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default

CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0/db_1/dbs/snapcf_prod1.f'; # default

4. 开始备份数据库

RMAN> backup database format '/u02/backup/prod_%U';

Starting backup at 24-MAY-14

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=35 instance=prod1 device type=DISK

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00001 name=+DATA/prod/datafile/system.262.847435663

input datafile file number=00002 name=+DATA/prod/datafile/sysaux.256.847435665

input datafile file number=00005 name=+DATA/prod/datafile/example.264.847435799

input datafile file number=00003 name=+DATA/prod/datafile/undotbs1.265.847435665

input datafile file number=00006 name=+DATA/prod/datafile/undotbs2.266.847435955

input datafile file number=00004 name=+DATA/prod/datafile/users.257.847435665

channel ORA_DISK_1: starting piece 1 at 24-MAY-14

channel ORA_DISK_1: finished piece 1 at 24-MAY-14

piece handle=/u02/backup/prod_0hp93702_1_1 tag=TAG20140524T113738 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:01:05

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

including current control file in backup set

including current SPFILE in backup set

channel ORA_DISK_1: starting piece 1 at 24-MAY-14

channel ORA_DISK_1: finished piece 1 at 24-MAY-14

piece handle=/u02/backup/prod_0ip93725_1_1 tag=TAG20140524T113738 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

Finished backup at 24-MAY-14

RMAN>

[oracle@rui1 ~]$ cd /u02/backup/

[oracle@rui1 backup]$ ls

prod_0hp93702_1_1 prod_0ip93725_1_1

5. 备份standby controlfile

有两种方式，一种是在rman下进行备份，一种是在sqlplus下进行备份

rman下:

RMAN> backup current controlfile for standby format='/u02/backup/std_controlfile.ctl';

Starting backup at 24-MAY-14

using channel ORA_DISK_1

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

including standby control file in backup set

channel ORA_DISK_1: starting piece 1 at 24-MAY-14

channel ORA_DISK_1: finished piece 1 at 24-MAY-14

piece handle=/u02/backup/std_controlfile.ctl tag=TAG20140524T114622 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

Finished backup at 24-MAY-14

RMAN>

[oracle@rui1 backup]$ ls

prod_0hp93702_1_1 prod_0ip93725_1_1 std_controlfile.ctl

[oracle@rui1 backup]$

Sqlplus下：

[oracle@rui1 backup]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sat May 24 11:47:23 2014

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

SQL> alter database create standby controlfile as '/tmp/stdcontrol.ctl';

Database altered.

SQL>

[oracle@rui1 backup]$ cd /tmp

[oracle@rui1 tmp]$ ls | grep st

stdcontrol.ctl

[oracle@rui1 tmp]$

6. 将以上文件（外加一个口令文件）传到备库的其中任意一个节点上

[oracle@rui1 tmp]$ scp 11.ora oracle@192.168.8.163:/u01/backup/

The authenticity of host '192.168.8.163 (192.168.8.163)' can't be established.

RSA key fingerprint is 16:28:88:50:27:30:92:cb:49:be:55:61:f6:c2:a1:3f.

Are you sure you want to continue connecting (yes/no)? yes

Warning: Permanently added '192.168.8.163' (RSA) to the list of known hosts.

oracle@192.168.8.163's password:

11.ora 100% 1986 1.9KB/s 00:00

[oracle@rui1 tmp]$ cd /u02/backup/

[oracle@rui1 backup]$ scp * oracle@192.168.8.163:/u01/backup/

oracle@192.168.8.163's password:

prod_0hp93702_1_1 100% 1039MB 559.5KB/s 31:42

prod_0ip93725_1_1 100% 18MB 448.8KB/s 00:41

std_controlfile.ctl 100% 18MB 612.3KB/s 00:30

[oracle@rui1 backup]$

二、修改pfile文件，并根据备库修改的pfile文件在备库中创建相应的目录

1. 主库修改后的pfile文件：

prod2.__db_cache_size=268435456

prod1.__db_cache_size=268435456

prod2.__java_pool_size=4194304

prod1.__java_pool_size=4194304

prod2.__large_pool_size=4194304

prod1.__large_pool_size=4194304

prod2.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment

prod1.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment

prod2.__pga_aggregate_target=209715200

prod1.__pga_aggregate_target=209715200

prod2.__sga_target=494927872

prod1.__sga_target=494927872

prod2.__shared_io_pool_size=0

prod1.__shared_io_pool_size=0

prod2.__shared_pool_size=209715200

prod1.__shared_pool_size=209715200

prod2.__streams_pool_size=0

prod1.__streams_pool_size=0

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

*.audit_trail='db'

*.cluster_database=true

*.compatible='11.2.0.0.0'

*.control_files='+DATA/prod/controlfile/current.258.847435763','+RCY/prod/controlfile/current.260.847435765'

*.db_block_size=8192

*.db_create_file_dest='+DATA'

*.db_domain=''

*.DB_FILE_NAME_CONVERT='+DATA/standby/datafile','+DATA/prod/datafile'

*.db_name='prod'

*.db_recovery_file_dest='+RCY'

*.db_recovery_file_dest_size=6291456000

*.db_unique_name='primary'

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

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

*.FAL_CLIENT='primary'

*.FAL_SERVER='standby'

prod2.instance_number=2

prod1.instance_number=1

*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(primary,standby)'

*.LOG_ARCHIVE_DEST_1='LOCATION=+RCY VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=primary'

*.LOG_ARCHIVE_DEST_2='SERVICE=stand LGWR SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=standby'

*.LOG_FILE_NAME_CONVERT='+DATA/standby/onlinelog','+DATA/prod/onlinelog','+RCY/standby/onlinelog','+RCY/prod/onlinelog'

*.open_cursors=300

*.pga_aggregate_target=209715200

*.processes=150

*.remote_listener='scan-vip:1521'

*.remote_login_passwordfile='exclusive'

*.sga_target=493879296

*.STANDBY_FILE_MANAGEMENT='AUTO'

prod1.thread=1

prod2.thread=2

prod2.undo_tablespace='UNDOTBS1'

prod1.undo_tablespace='UNDOTBS2'

（黑体部分为新增部分）

2. 备库修改后的pfile

std2.__db_cache_size=268435456

std1.__db_cache_size=268435456

std2.__java_pool_size=4194304

std1.__java_pool_size=4194304

std2.__large_pool_size=4194304

std1.__large_pool_size=4194304

std2.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment

std1.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment

std2.__pga_aggregate_target=209715200

std1.__pga_aggregate_target=209715200

std2.__sga_target=494927872

std1.__sga_target=494927872

std2.__shared_io_pool_size=0

std1.__shared_io_pool_size=0

std2.__shared_pool_size=209715200

std1.__shared_pool_size=209715200

std2.__streams_pool_size=0

std1.__streams_pool_size=0

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

*.audit_trail='db'

*.cluster_database=true

*.compatible='11.2.0.0.0'

*.control_files='+DATA/standby/controlfile/current.257.848668433','+RCY/standby/controlfile/current.256.848668435'#Restore Controlfile

*.db_block_size=8192

*.db_create_file_dest='+DATA'

*.db_domain=''

*.DB_FILE_NAME_CONVERT='+DATA/prod/datafile','+DATA/standby/datafile'

*.db_name='PROD'

*.db_recovery_file_dest='+RCY'

*.db_recovery_file_dest_size=6291456000

*.db_unique_name='standby'

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

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

*.FAL_CLIENT='standby'

*.FAL_SERVER='primary'

std2.instance_number=2

std1.instance_number=1

*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(standby,primary)'

*.LOG_ARCHIVE_DEST_1='LOCATION=+RCY VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=standby'

*.LOG_ARCHIVE_DEST_2='SERVICE=PROD LGWR SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=primary'

*.LOG_FILE_NAME_CONVERT='+DATA/prod/onlinelog','+DATA/standby/onlinelog','+RCY/prod/onlinelog','+RCY/standby/onlinelog'

*.open_cursors=300

*.pga_aggregate_target=209715200

*.processes=150

*.remote_listener='zhang-scan:1521'

*.remote_login_passwordfile='exclusive'

*.sga_target=493879296

*.STANDBY_FILE_MANAGEMENT='AUTO'

std1.thread=1

std2.thread=2

std2.undo_tablespace='UNDOTBS1'

std1.undo_tablespace='UNDOTBS2'

将所有的实例名改成对应的备库的实例名，其中黑体部分为新增部分。其余标红部分为修改部分

3. 修改完之后，在主库备库上用修改后的pfile将数据库启动到nomount状态，并转化成spfile

[oracle@zhang1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Tue May 27 20:15:05 2014

Connected to an idle instance.

SQL> startup nomount pfile='/tmp/11.ora';

ORACLE instance started.

Total System Global Area 493813760 bytes

Fixed Size 1337436 bytes

Variable Size 218105764 bytes

Database Buffers 268435456 bytes

Redo Buffers 5935104 bytes

SQL> select status from v$instance;

STATUS

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

STARTED

然后，创建spfile

SQL> create spfile='+DATA/standby/spfilestandby.ora' from pfile;

然后将所有节点下的$ORACLE_HOME/dbs下的pfile改为下面的指向

[oracle@zhang1 dbs]$ cat initstd1.ora

SPFILE='+DATA/standby/spfilestand.ora'

[oracle@zhang1 dbs]$

三、配网络、监听

1. 配置/etc/hosts

主库1节点：

[root@rui1 tmp]# cat /etc/hosts

# Do not remove the following line, or various programs

# that require network functionality will fail.

127.0.0.1 localhost.localdomain localhost

::1 localhost6.localdomain6 localhost6

192.168.8.160 rui1

192.168.8.161 rui2

10.10.10.100 rui1-priv

10.10.10.101 rui2-priv

192.168.8.170 rui1-vip

192.168.8.171 rui2-vip

192.168.8.179 scan-vip

192.168.8.172 zhang1-vip

192.168.8.173 zhang2-vip

192.168.8.178 zhang-scan

主库2节点：

[oracle@rui2 ~]$ cat /etc/hosts

# Do not remove the following line, or various programs

# that require network functionality will fail.

127.0.0.1 localhost.localdomain localhost

::1 localhost6.localdomain6 localhost6

192.168.8.160 rui1

192.168.8.161 rui2

10.10.10.100 rui1-priv

10.10.10.101 rui2-priv

192.168.8.170 rui1-vip

192.168.8.171 rui2-vip

192.168.8.179 scan-vip

192.168.8.172 zhang1-vip

192.168.8.173 zhang2-vip

192.168.8.178 zhang-scan

备库1节点：

[oracle@zhang1 dbs]$ cat /etc/hosts

# Do not remove the following line, or various programs

# that require network functionality will fail.

127.0.0.1 localhost.localdomain localhost

::1 localhost6.localdomain6 localhost6

192.168.8.162 zhang1

192.168.8.163 zhang2

10.10.10.200 zhang1-priv

10.10.10.201 zhang2-priv

192.168.8.172 zhang1-vip

192.168.8.173 zhang2-vip

192.168.8.178 zhang-scan

192.168.8.170 rui1-vip

192.168.8.171 rui2-vip

192.168.8.179 scan-vip

备库2节点：

[root@zhang2 ~]# cat /etc/hosts

# Do not remove the following line, or various programs

# that require network functionality will fail.

127.0.0.1 localhost.localdomain localhost

::1 localhost6.localdomain6 localhost6

192.168.8.162 zhang1

192.168.8.163 zhang2

10.10.10.200 zhang1-priv

10.10.10.201 zhang2-priv

192.168.8.172 zhang1-vip

192.168.8.173 zhang2-vip

192.168.8.178 zhang-scan

192.168.8.170 rui1-vip

192.168.8.171 rui2-vip

192.168.8.179 scan-vip

2. 配监听

主库1、2节点：

PROD =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = scan-vip)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = primary)

)

stand =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = zhang-scan)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = standby)

)

备库1、2节点：

PROD =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = scan-vip)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = primary)

)

stand =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = zhang-scan)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = standby)

)

然后用tnsping命令测试数据库监听是否启动

四、在备RAC上用RMAN备份恢复数据库

1. 启动数据库到nomount状态，然后restore出控制文件和数据文件

[oracle@zhang1 dbs]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Tue May 27 21:09:59 2014

connected to target database (not started)

RMAN> startup nomount

Oracle instance started

Total System Global Area 493813760 bytes

Fixed Size 1337436 bytes

Variable Size 218105764 bytes

Database Buffers 268435456 bytes

Redo Buffers 5935104 bytes

RMAN>restore standby controlfile from ‘/u01/backup/std_control.ctl’;

Starting restore at 14-OCT-11

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=150 instance=TIANJIN1 devtype=DISK

channel ORA_DISK_1: restoring control file

channel ORA_DISK_1: restore complete, elapsed time: 00:00:13

output filename=+DISK1/beijing/controlfile/current.256.764546675

output filename=+FLA/beijing/controlfile/backup.256.764546677

Finished restore at 14-OCT-11

RMAN> alter database mount;

database mounted

released channel: ORA_DISK_1

RMAN> restore database;

五、在主库与备库创建standby logs

ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 SIZE 50M;

ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 SIZE 50M;

该日志大小要与正常的大小相等，且要比联机日志要多一个

六、将主库打开至open状态并将备库启动到mount状态

使备库处于disconnect from session状态

SQL> alter database recover managed standby database disconnect from session;

Database altered.

七、测试MAA是否完成

打开备库的告警日志，切换主库日志，如告警日志未报错误，切看到有日志被传输和恢复，即证明MAA搭建成功

期间出现的问题：

ORA-15081:failed to submit I/O operation to a disk

处理方法：

将$ORACLE_HOME/bin/oracle的属组和权限修改为一下即可

[oracle@zhang1 admin]$ ll /u01/app/oracle/product/11.2.0/db_1/bin/oracle

-rwsr-s--x 1 oracle asmadmin 173515905 May 26 16:16 /u01/app/oracle/product/11.2.0/db_1/bin/oracle

[oracle@zhang1 admin]$

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29802484/viewspace-2078269/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29802484/viewspace-2078269/

crcz16618

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫