oracle 11g主从配置

主数据库安装数据库软件和实例；
备库不用安装实例，只安装数据库软件

一、主备查询是否归档

ps:查看是否处于log模式,如果不是执行如下命令

SQL > archive log list;

修改归档模式，需要重启数据库

--干净的关闭数据库
SQL> shutdown immediate
--以mount模式启动
SQL> startup mount
--切换到归档模式
SQL> alter database archivelog;
--开启强制日志
SQL> alter database force logging;
--打开数据库
SQL> alter database open;
--查看归档
SQL> archive log list;
--查看是否为强制日志
SQL> select force_logging from v$database;


注意：show parameter log_archive_dest查看归档日志的存放位置。

二、配置监听

1、主数据库监听配置

cd /data1/oracle/app/product/11.2.0/dbhome_1/network/admin

vi listener.ora

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = orcl)
      (ORACLE_HOME =  /data1/oracle/app/product/11.2.0/dbhome_1)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (SID_NAME = orcl)
      (GLOBAL_DBNAME = orcl)
      (ORACLE_HOME =  /data1/oracle/app/product/11.2.0/dbhome_1)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.57)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )
ADR_BASE_LISTENER = /data1/oracle/app

vi tnsnames.ora

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.57)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

LISTENER_ORCL =
  (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.57)(PORT = 1521))

PRIMARY =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.57)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

STANDBY =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.51)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

备注：主数据库和从数据库 tnsnames.ora 内容一样

2、从数据库监听配置

cd /data1/oracle/app/product/11.2.0/dbhome_1/network/admin

vi listener.ora （ip不同）

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = orcl)
      (ORACLE_HOME =  /data1/oracle/app/product/11.2.0/dbhome_1)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (SID_NAME = orcl)
      (GLOBAL_DBNAME = orcl)
      (ORACLE_HOME =  /data1/oracle/app/product/11.2.0/dbhome_1)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.51)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )
ADR_BASE_LISTENER = /data1/oracle/app

vi tnsnames.ora

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.51)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

LISTENER_ORCL =
  (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.51)(PORT = 1521))

PRIMARY =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.57)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

STANDBY =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.51)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

3、主从监听链接测试

oracle 用户下执行(主从两库都执行)

lsnrctl stop
lsnrctl start
lsnrctl status
tnsping primary
tnsping standby
sqlplus sys/密码@primary as sysdba
sqlplus sys/密码@standby as sysdba

二、主数据库 PRIMARY 启动参数设置

cd /data1/oracle/app/product/11.2.0/dbhome_1/dbs

1、生成默认参数文件initorcl.ora

SQL> sqlplus / as sysdba

SQL> create pfile from spfile;

2、编辑initorcl.ora

添加参数
/data1/oracle/app/product/11.2.0/dbhome_1/dbs

*.DB_UNIQUE_NAME='primary'
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(primary, standby)'
*.LOG_ARCHIVE_DEST_1='LOCATION=/data1/oracle/app/oradata/archive/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=primary'
*.log_file_name_convert='/data1/oracle/app/oradata/orcl','/data1/oracle/app/oradata/orcl'
*.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'
*.LOG_ARCHIVE_FORMAT='%t_%s_%r.arc'
*.LOG_ARCHIVE_MAX_PROCESSES=5
*.FAL_SERVER='standby'
*.FAL_CLIENT='primary'
*.STANDBY_FILE_MANAGEMENT='AUTO'

archive 为创建目录

mkdir -p /data1/oracle/app/oradata/archive

3、主库使用这个编辑后的参数文件

SQL> sqlplus / as sysdba
SQL> shutdown immediate;
SQL> create spfile from pfile;
SQL> startup;

--- 查看使用的配置文件

SQL> show parameter pfile

4、创建standby 日志文件

standby的日志大小，必须跟online redo log的大小一样，组数要比redo log多一组。


--查看Redo和Standby Redo
SQL> select * from v$logfile;									
--仅仅显示Online Redo，不显示Standby Redo
SQL> select * from v$log;										
--新增一组大小为500M的Standby Redo，这里的group号不得与Online redo重复


alter database add standby logfile group  11 '/data1/oracle/app/oradata/orcl/standby11.log' size 50M;
alter database add standby logfile group  12 '/data1/oracle/app/oradata/orcl/standby12.log' size 50M;
alter database add standby logfile group  13 '/data1/oracle/app/oradata/orcl/standby13.log' size 50M;
alter database add standby logfile group  14 '/data1/oracle/app/oradata/orcl/standby14.log' size 50M;

5、主库参数文件和密码文件拷贝到从库

/data1/oracle/app/product/11.2.0/dbhome_1/dbs/initorcl.ora
/data1/oracle/app/product/11.2.0/dbhome_1/dbs/orapworcl

-- 传输到 相对应的 相同目录下，修改为Oracle 权限
chown oracle:oinstall initorcl.ora
chown oracle:oinstall orapworcl

三、从数据库dbstandby 配置

oracle 用户下操作
su - oracle

1、vi initorcl.ora参数文件

*.DB_UNIQUE_NAME='standby'
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=( primary , standby)'
*.log_file_name_convert='/data1/oracle/app/oradata/orcl','/data1/oracle/app/oradata/orcl'
*.LOG_ARCHIVE_DEST_1='LOCATION=/data1/oracle/app/oradata/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'
*.LOG_ARCHIVE_FORMAT='%t_%s_%r.arc'
*.LOG_ARCHIVE_MAX_PROCESSES=5
*.FAL_SERVER='primary'
*.FAL_CLIENT='standby'
*.STANDBY_FILE_MANAGEMENT='AUTO'

2、建立从库所需目录


cd /data1/oracle/app
mkdir -p oradata/orcl
mkdir -p oradata/archive
mkdir -p admin/orcl/adump
mkdir -p admin/orcl/dpdump
mkdir -p admin/orcl/pfile
mkdir -p flash_recovery_area/orcl

3、启动从库为nomount 模式

sql>create spfile from pfile;
sql>startup nomount;

四、RMAN 拷贝主库数据到从库

1、RMAN连接到目标数据库和辅助数据库

rman target sys/xxxx@PRIMARY auxiliary sys/xxxx@STANDBY

2、使用RMAN的duplicate命令进行复制，两边目录结构相同，需要添加nofilenamecheck参数不然会报错

duplicate target database for standby from active database nofilenamecheck;

3、复制成功后，备库自动被加载为mount模式，进入sqlplus查看

select status from v$instance;

4、在备库开启实时日志应用

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

5、主备库角色状态查询

SQL> select switchover_status,database_role from v$database;
--主库显示：TO STANDBY/PRIMARY，如果显示SESSION ACTIVE表示还有活动的会话，需要关闭活动的会话再检查
--备库显示：NOT ALLOWED/PHYSICAL STANDBY

oracle 11g主从配置

一、主备查询是否归档

二、配置监听

1、主数据库监听配置

2、从数据库监听配置

3、主从监听链接测试

二、主数据库 PRIMARY 启动参数设置

1、生成默认参数文件initorcl.ora

2、编辑initorcl.ora

3、主库使用这个编辑后的参数文件

4、创建standby 日志文件

5、 主库参数文件 和密码文件拷贝到 从库

三、从数据库dbstandby 配置

1、vi initorcl.ora参数文件

2、建立从库所需目录

3、启动从库 为nomount 模式

四、RMAN 拷贝主库数据 到 从库

1、RMAN连接到目标数据库和辅助数据库

2、使用RMAN的duplicate命令进行复制，两边目录结构相同，需要添加nofilenamecheck参数 不然会报错

3、复制成功后，备库自动被加载为mount模式，进入sqlplus查看

4、在备库开启实时日志应用

5、主备库角色状态查询

5、主库参数文件和密码文件拷贝到从库

3、启动从库为nomount 模式

四、RMAN 拷贝主库数据到从库

2、使用RMAN的duplicate命令进行复制，两边目录结构相同，需要添加nofilenamecheck参数不然会报错