重庆有哪些交通设计院:Oracle 10g for linux data guard安装手记

1.主数据库配置
  sid  primary
  ip   10.100.1.100

初始化参数
ORACLE_BASE=/u01/app/oracle
ORACLE_SID=primary
ORACLE_HOME=/u01/app/oracle/oracle/product/10.2.0/db_1
export ORACLE_BASE ORACLE_SID ORACLE_HOME
PATH=$PATH:/u01/app/oracle/oracle/product/10.2.0/db_1/bin
export PATH 

数据库文件存放目录为
/u01/app/oracle/oradata/test/

2.备用数据库配置(注意系统时间需要和主数据库一致)
  sid  standby
  ip   10.100.1.101

初始化参数
ORACLE_BASE=/u01/app/oracle
ORACLE_SID=standby
ORACLE_HOME=/u01/app/oracle/oracle/product/10.2.0/db_1
export ORACLE_BASE ORACLE_SID ORACLE_HOME
PATH=$PATH:/u01/app/oracle/oracle/product/10.2.0/db_1/bin
export PATH

数据库文件存放目录为
/u01/app/oracle/oradata/test/

3.物理data guard配置过程

主数据库：

》确定数据库为归档模式

》设置oracle为强制日志模式
SQL> alter database force logging;

》如果启动参数为spfile文件，则需要创建pfile
SQL> create pfile from spfile；

》修改dbs目录下的initprimary.ora
vi initprimary.ora
增加下面参数：
#################
#data guard#
#################
DB_UNIQUE_NAME=primary
LOG_ARCHIVE_CONFIG=
 'DG_CONFIG=(primary,standby)'
LOG_ARCHIVE_DEST_1=
 'LOCATION=/home/oracle/archive
  VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
  DB_UNIQUE_NAME=primary'
LOG_ARCHIVE_DEST_2=
 'SERVICE=standby LGWR ASYNC
  VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
  DB_UNIQUE_NAME=standby'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
STANDBY_ARCHIVE_DEST=/home/oracle/archive
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE

FAL_SERVER=standby
FAL_CLIENT=primary
STANDBY_FILE_MANAGEMENT=AUTO

》全备份主数据库
RMAN
RMAN>connect target
RMAN> backup database format='/home/oracle/%U_%s.bak';
RMAN> sql "Alter System Archive Log Current";
RMAN> Backup filesperset 10 ArchiveLog all format='/home/oracle/%U_%s.bak';

》拷贝备份文件到备份数据库主机
cd /home/oracle
scp *.bak 10.100.1.101:/home/oracle/

》准备备份数据库相应目录
mkdir -p /u01/app/oracle/admin/test/adump
mkdir -p /u01/app/oracle/admin/test/bdump
mkdir -p /u01/app/oracle/admin/test/cdump
mkdir -p /u01/app/oracle/admin/test/dpdump
mkdir -p /u01/app/oracle/admin/test/pfile
mkdir -p /u01/app/oracle/admin/test/udump
mddir -p /home/oracle/archive

》从主数据库拷贝相关文件到备份数据库
cd /u01/app/oracle/oracle/product/10.2.0/db_1/dbs/
scp initprimary.ora 10.100.1.101:/u01/app/oracle/oracle/product/10.2.0/db_1/dbs
scp orapwprimary 10.100.1.101:/u01/app/oracle/oracle/product/10.2.0/db_1/dbs

》修改备份数据库相关文件名
cd /u01/app/oracle/oracle/product/10.2.0/db_1/dbs/
mv initprimary.ora initstandby.ora
mv orapwprimary orapwstandby

》修改备份数据库初始化文件(修改data guard 部分)
vi initstandby.ora
#################
#data guard#
#################
DB_UNIQUE_NAME=standby
LOG_ARCHIVE_CONFIG=
 'DG_CONFIG=(primary,standby)'
LOG_ARCHIVE_DEST_1=
 'LOCATION=/home/oracle/archive
  VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
  DB_UNIQUE_NAME=standby'
LOG_ARCHIVE_DEST_2=
 'SERVICE=primary LGWR ASYNC
  VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
  DB_UNIQUE_NAME=primary'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
STANDBY_ARCHIVE_DEST=/home/oracle/archive
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE

FAL_SERVER=primary
FAL_CLIENT=standby
STANDBY_FILE_MANAGEMENT=AUTO

》修改主数据库和备数据库tnsname.ora
增加如下设置：
PRIMARY =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.100.1.100)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = primary)
    )
  )

STANDBY =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.100.1.101)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = standby)
    )
  )

》在主数据库上创建备用数据库控制文件
SQL> alter database create standby controlfile as '/home/oracle/standby.ctl';

scp standby.ctl 10.100.1.101:/u01/app/oracle/oradata/test/
》在备份数据库上建立控制文件
mv standby.ctl control01.ctl
cp control01.ctl control02.ctl
cp control01.ctl control03.ctl

》启动备数据库nomount状态准备恢复
startup nomount;
alter database mount standby database;
》恢复备用数据库
ramn target /
RMAN> restore database;
RMAN> restore archivelog all;

》备用数据库进入到后台管理恢复状态
SQL>alter database recover managed standby database disconnect from session;

4.验证datagu
》在主库上：
create user test identified by ftp123;
grant connect,resource to test;
conn test/ftp123@primary;
create table test(name varchar2(20));
insert into test values('hi, Data Guard');
commit;
conn / as sysdba;
alter system switch logfile;

》查看从库日志
以只读方式打开从库查看 insert into test values('hi, Data Guard'); 已经生效。
conn / as sysdba;
alter database recover managed standby database cancel;
alter database open read only;
conn test/ftp123
select * from test;

5.data guard日常管理
备用数据库
（1）启动到管理模式
shutdown immediate;
startup nomount;
alter database mount standby database;
alter database recover managed standby database disconnect from session;
（2）启动到只读方式
shutdown immediate;
startup nomount;
alter database mount standby database;
alter database open read only;

6.data guard切换
主数据库
（1）切换到备用方式
alter database commit to switchover to physical standby with session shutdown;
shutdown immediate;

备用数据库
（2）切换到主数据库方式
alter database commit to switchover to primary;
shutdown immediate;
startup

主数据库
（3）启动备用数据库方式
startup nomount;
alter database mount standby database;
alter database recover managed standby database disconnect from session;