Data Guard安装过程

最新推荐文章于 2021-09-20 17:03:12 发布

cuilan3310

最新推荐文章于 2021-09-20 17:03:12 发布

阅读量171

点赞数

Data Guard安装过程
一、首先安装Linux，本实验采用RedHat Linux9
  安装主机a ip 192.168.0.101，从机b 192.168.0.102
二、安装Oracle（略）此次采用版本Oracle9.2.4
（1）其中环境变量设置如下：主从两机设置一样
export LD_ASSUME_KERNEL=2.4.1
export ORACLE_BASE=/opt/oracle
export ORACLE_HOME=/opt/oracle/product
export ORACLE_SID=primary
export ORACLE_TERM=xterm
export NLS_LANG=AMERICAN;
#export NLS_LANG="SIMPLIFIED CHINESE_CHINA.ZHS16GBK";
#export NLS_LANG="SIMPLIFIED CHINESE_CHINA.ZHS16CGB231280"
export ORA_NLS33=$ORACLE_HOME/ocommon/nls/admin/data
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
LD_LIBRARY_PATH=$LD_LIBRARY_PATH:usr/local/lib
export LD_LIBRARY_PATH
export PATH=$PATH:$ORACLE_HOME/bin
CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib
CLASSPATH=$CLASSPATH:$ORACLE_HOME/network/jlib
LC_ALL=en_US
#LC_ALL=zh_CN
export LC_ALL
LANG=en_US
#LANG=zh_CN
export LANG
（2）在主机a上安装数据库，选择安装software，此时不建库
（3）打包主机a上$ORACLE_BASE下的所有内容并传到从机b相应的目录
      tar cvzf oracle.tgz oracle/
    从机 tar -zxvf oracle.tgz，同时将oracle目录所属用户和组设置正确
    chown -R oracle oracle chgrp -R oinstall oracle
（4）在主机上创建数据库，通过dbca创建，在linux9下创建oracle9.2.4的库会处错，请查看解决方法的备注
（5）关闭库，将数据文件cp到从机
  （6）打开主机a数据库将其修改为归档模式
[root@a root]# mkdir -p /opt/oracle/oradata/primary/archive
[oracle@a root]$ sqlplus /nolog
SQL*Plus: Release 9.2.0.4.0 - Production on Wed Jul 5 13:34:44 2006
copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
SQL> conn / as sysdba
SQL> starup mount;
...
SQL> archive log list;
  ...
SQL> alter database archivelog;
...
SQL> archive log list;
...
SQL> alter database open;
...
SQL> alter system set log_archive_dest_1='LOCATION=/opt/oracle/oradata/primary/archive' scope=spfile;
...
SQL> alter system set log_archive_format='%t_%s.dbf' scope=spfile;
...
SQL> alter system set log_archive_start=true scope=spfile;
...
（6）在主机a创建从机b的控制文件
SQL> alter database create standby controlfile as '/opt/oracle/standby.ctl';
...
将standby.ctl cp到从机/opt/oracle/oradata/primary/下
（7）配置主从机的linstener.ora和tnsnames.ora
在我用以上方法安装的数据库结果没有产生这两个文件，不过没有关系我们可以有动生成
路径是/opt/oracle/product/network/admin
# Generated by Oracle configuration tools.
主机配置如下 listener.ora
LISTENER =
   (DESCRIPTION_LIST =
     (DESCRIPTION =
       (ADDRESS_LIST =
         (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
       )
       (ADDRESS_LIST =
         (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.101)(PORT = 1521))
       )
     )
   )

SID_LIST_LISTENER =
   (SID_LIST =
     (SID_DESC =
       (SID_NAME = PLSExtProc)
       (ORACLE_HOME =/opt/oracle/product)
       (PROGRAM = extproc)
     )
     (SID_DESC =
       (GLOBAL_DBNAME = primary)
       (ORACLE_HOME = /opt/oracle/product)
       (SID_NAME = primary)
     )
   )

tnsnames.ora如下：
PRIMARY =
   (DESCRIPTION =
     (ADDRESS_LIST =
       (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.101)(PORT = 1521))
     )
     (CONNECT_DATA =
       (SID = primary)
       (SERVER = DEDICATED)
     )
   )

STANDBY =
   (DESCRIPTION =
     (ADDRESS_LIST =
       (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.102)(PORT = 1521))
     )
     (CONNECT_DATA =
       (SERVICE_NAME = primary)
     )
   )
注：主机a与从机b的listener.ora区别就是所对应的HOST不同

（8）在主从机上分别使用tnsping primary，tnsping standby
[oracle@a root]$tnsping primary
...
[oracle@a root]$tnsping standby
...
经检验无误，注：在这里我们要注意不同编码会出现问题。
（9）在主机上创建参数文件pfile
SQL> create pfile from spfile;
将主机a数据库的参数文件cp到从机上并做相应的修改
*.aq_tm_processes=1
*.background_dump_dest='/opt/oracle/admin/primary/bdump'
*.compatible='9.2.0.0.0'
*.control_files='/opt/oracle/oradata/primary/standby.ctl'
*.core_dump_dest='/opt/oracle/admin/primary/cdump'
*.db_block_size=8192
*.db_cache_size=33554432
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='primary'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=primaryXDB)'
*.fast_start_mttr_target=300
*.hash_join_enabled=TRUE
*.instance_name='primary'
*.java_pool_size=83886080
*.job_queue_processes=10
*.large_pool_size=16777216
*.log_archive_dest_1='LOCATION=/opt/oracle/oradata/primary/archive'
*.log_archive_format='%t_%s.dbf'
*.log_archive_start=TRUE
*.open_cursors=300
*.pga_aggregate_target=25165824
*.processes=150
*.query_rewrite_enabled='FALSE'
*.remote_login_passwordfile='EXCLUSIVE'
*.shared_pool_size=83886080
*.sort_area_size=524288
*.star_transformation_enabled='FALSE'
*.timed_statistics=TRUE
*.undo_management='AUTO'
*.undo_retention=10800
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/opt/oracle/admin/primary/udump'
*.standby_archive_dest='/opt/oracle/oradata/primary/standbyarchive'
*.fal_server='PRIMARY'
*.fal_client='CLIENT'
*.standby_file_management='AUTO'
注：从机b数据库的参数文件主要修改了control_files，standby_archive_dest，fal_server
fal_client，standby_file_management，其中standby_file_management不是必须的但是为了后面主库
添加文件，从库自动处理而加上这个参数，避免麻烦。
建立从库的相关文档日志路径
[root@b root]# mkdir -p /opt/oracle/oradata/primary/standbyarchive
（10）将主机a数据库密码文件cp过来或者使用orapwd创建密码文件
注：所有从主机a拷贝的数据库文件都必须将其所属用户设置为oracle，将其所属组设置为oinstall（或dba）
（11）启动从库（从库的状态是mount）和在主机a数据库上设置到从库的归档
SQL> startup nomount;
ORACLE instance started.

Total System Global Area 236000356 bytes
Fixed Size                   451684 bytes
Variable Size             201326592 bytes
Database Buffers           33554432 bytes
Redo Buffers                 667648 bytes
SQL> alter database mount standby database;
从库在mount状态下就可以
设置主机a数据库上到从库的归档
SQL> alter system set log_archive_dest_2='SERVICE=standby MANDATORY REOPEN=60';
...
      　激活Primary Database的Forced Logging
　　
    　　SQL> ALTER DATABASE FORCE LOGGING;

至此安装已经小有所成，可以使用
SQL> alter system switch logfile;
...
查看从机b中有没有对应的日志文件。
下面我们就可以验证其安装正确与否
可以在主机a库中创建一些测试数据然后切换日志
[oracle@a root]$ sqlplus /nolog

SQL*Plus: Release 9.2.0.4.0 - Production on Wed Jul 5 13:34:44 2006

SQL> conn / as sysdba
SQL> create user olivenan identified by olivenan;
...
SQL> grant connect,resource,dba to olivenan;
...
SQL> conn olivenan/olivenan
...
SQL> create table test(name varchar2(100));
...
SQL> insert into test values('Hi,Data Guard,Now I will succeed');
...
SQL> insert into test values('I love Oracle very much!');
...
SQL> commit;
...
测试数据建立成功。

从机b数据库检验数据，以下操作过程中出现的错误没有过虑，以后的过程中要研究其原因。

SQL> alter database recover managed standby database cancel;
alter database recover managed standby database cancel
*
ERROR at line 1:
ORA-16136: Managed Standby Recovery not active

SQL> alter database open read only;
alter database open read only
*
ERROR at line 1:
ORA-16004: backup database requires recovery
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/opt/oracle/oradata/primary/system01.dbf'

SQL> recover managed standby database cancel;
ORA-16136: Managed Standby Recovery not active

SQL> recover managed standby database disconnect;
Media recovery complete.
SQL> alter database open read only;
alter database open read only
*
ERROR at line 1:
ORA-01154: database busy. Open, close, mount, and dismount not allowed now

SQL> recover managed standby database cancel;
Media recovery complete.
SQL> alter database open read only;

Database altered.

SQL> select * from olivenan.test;

NAME
--------------------------------------------------------------------------------
Hi,Data Guard,Now I will succeed
I love Oracle very much!

SQL>

以下是主从库切换的实现过程：

在主机a

[oracle@a oradata]$ sqlplus /nolog

SQL*Plus: Release 9.2.0.4.0 - Production on Thu Jul 13 13:56:38 2006

SQL> conn sys/xueyao as sysdba
Connected.

SQL> alter system set standby_archive_dest='/opt/oracle/oradata/primary/standbyarchive' scope=spfile;

System altered.

SQL> alter database commit to switchover to physical standby;

Database altered.

SQL> shutdown immediate;
ORA-01507: database not mounted

ORACLE instance shut down.
SQL> startup nomount
ORACLE instance started.

Database altered.

SQL> select name,open_mode,PROTECTION_MODE,DATABASE_ROLE from v$database;

NAME OPEN_MODE PROTECTION_MODE DATABASE_ROLE
--------- ---------- -------------------- ----------------
PRIMARY MOUNTED MAXIMUM PERFORMANCE PHYSICAL STANDBY

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

Database altered.

SQL>

在b机器上
SQL> alter database commit to switchover to primary;

Database altered.

SQL> shutdown immediate;
ORA-01507: database not mounted

ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 236000356 bytes
Fixed Size                   451684 bytes
Variable Size             201326592 bytes
Database Buffers           33554432 bytes
Redo Buffers                 667648 bytes
Database mounted.
Database opened.

SQL> alter system switch logfile;

System altered.

SQL> alter system set log_archive_dest_2='service=primary mandatory reopen=60';

System altered.

SQL> alter system switch logfile;

System altered.

SQL> conn test/ftp123
Connected.
SQL> create table olive as select * from dba_users;

Table created.

SQL> create table haha as select * from user_tables;

Table created.

SQL> alter system switch logfile;

System altered.

SQL>

在a机器上

由于a机器alter database recover managed standby database disconnect from session;在这个session是恢复状态，
所以以下语句执行失败
SQL> alter database open read only;
alter database open read only
*
ERROR at line 1:
ORA-01154: database busy. Open, close, mount, and dismount not allowed now
解决这个问题执行以下语句即可
SQL> recover managed standby database cancel;
Media recovery complete.
SQL> alter database open read only;

Database altered.

SQL>

SQL> shutdown immediate;
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 236000356 bytes
Fixed Size                   451684 bytes
Variable Size             201326592 bytes
Database Buffers           33554432 bytes
Redo Buffers                 667648 bytes
ORA-01666: controlfile is for a standby database

SQL> alter database mount standby database;

Database altered.

SQL> alter database open read only;

Database altered.

SQL> conn test/ftp123
Connected.
SQL> select count(*) from olive;

COUNT(*)
----------
31

SQL> select count(*) from haha;

COUNT(*)
----------
3

SQL>
切换成功

切换回去，即将a机器重新作为primary，b作为standby
在b机器上操作
SQL> alter database commit to switchover to physical standby;

Database altered.

SQL> shutdown immediate;
ORA-01507: database not mounted

ORACLE instance shut down.
SQL> startup nomount;
ORACLE instance started.

Database altered.

SQL> select name,open_mode,PROTECTION_MODE,DATABASE_ROLE from v$database;

NAME OPEN_MODE PROTECTION_MODE DATABASE_ROLE
--------- ---------- -------------------- ----------------
PRIMARY MOUNTED MAXIMUM PERFORMANCE PHYSICAL STANDBY

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

Database altered.

SQL>
在a机器操作，此时a机器数据库的状态是open read only
SQL> alter database commit to switchover to primary;
alter database commit to switchover to primary
*
ERROR at line 1:
ORA-16139: media recovery required

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup nomount;
ORACLE instance started.

Total System Global Area 236000356 bytes
Fixed Size                   451684 bytes
Variable Size             201326592 bytes
Database Buffers           33554432 bytes
Redo Buffers                 667648 bytes
SQL> SQL> alter database mount standby database;

Database altered.

SQL> recover managed standby database timeout 1;
Media recovery complete.
SQL> alter database commit to switchover to primary;

Database altered.

SQL> shutdown immediate;
ORA-01507: database not mounted

ORACLE instance shut down.
SQL> startup
ORACLE instance started.