mysql standby_standby数据库的一些笔记

使用LGWR 进程的SYNC 方式

alter system set log_archive_dest_2 = 'SERVICE=ST  LGWR  SYNC  NET_TIMEOUT=30' scope=both;

alter system set log_archive_dest_2 = 'SERVICE=ST  LGWR  SYNC AFFIRM  NET_TIMEOUT=30' scope=both;

使用LGWR进程的ASYNC 方式

alter system set log_archive_dest_2 = 'SERVICE=ST  LGWR  ASYNC ' scope=both;

在日志接收中，需要注意的是归档日志会被放在什么位置：

1) 如果配置了STANDBY_ARCHIVE_DEST 参数，则使用该参数指定的目录。

2) 如果某个LOG_ARCHIVE_DEST_n 参数明确定义了VALID_FOR=(STANDBY_LOGFILE,*)选项，则使用这个参数指定的目录。

3) 如果数据库的COMPATIBLE参数大于等于10.0，则选取任意一个LOG_ARCHIVE_DEST_n的值。

4) 如果STANDBY_ARCHIVE_DEST 和 LOG_ARCHIVE_DEST_n 参数都没有配置，使用缺省的STANDBY_ARCHIVE_DEST参数值，这个缺省值是$ORACLE_HOME/dbs/arc.

修改数据保护模式步骤

1)关闭数据库，重启到Mount 状态，如果是RAC，需要关闭所有实例，然后只启动一个实例到mount状态。

2)修改模式：

语法：ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE {PROTECTION | AVAILABILITY | PERFORMANCE};

如：SQL>ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PROTECTION;

3) 打开数据库： alter database open;

4) 确认修改数据保护模式：

SQL>select protection_mode,protection_level from v$database;

alter database add standby logfile group 11 '/opt/oracle/oradata/orateststb/redo_11_1.log' size 200M;

如果是Physical Standby，可以使用下面命令启用Real-Time：

Alter database recover managed standby database using current logfile disconnect;

//Alter database recover managed standby database using current logfile;

如果是Logical Standby，可以使用下面命令启用Real-Time：

Alter database start logical standby apply immediate;

查看是否使用Real-Time apply：

Select recovery_mode from v$archive_dest_status;

SELECT * FROM V$DATAGUARD_STATS;

ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;

=======================================11g的搭建方法 ============================================

把主库的password文件拷贝到standby数据库上,

password文件的格式为orapw

主要主库上没有使用password文件，需要建在主库上建password文件：

orapwd file=orapworateststd password=sys entries=10

mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/adump

mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/bdump

mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/cdump

mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/create

mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/pfile

mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/scripts

mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/udump

mkdir -p $ORACLE_BASE/oradata/$ORACLE_SID

在要建standby数据库的主机上建安pfile：

vi $ORACLE_HOME/dbs/init$ORACLE_SID.ora，加入下面两行：

remote_login_passwordfile=exclusive

DB_NAME=oratest

在standby数据库上建立监听

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

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

)

)

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = oratest.db.alibaba.com)

(ORACLE_HOME = /opt/oracle/products/11.1.0)

(SID_NAME = orateststd)

)

)

在主库上的tnsnames.ora文件中添加到standby的服务名

orateststb =

(DESCRIPTION =

(ADDRESS_LIST =

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

)

(CONNECT_DATA =

(sid = orateststd)

)

)

在主库上：

先进sqlplus中切换几组日志：

sqlplus "/ as sysdba"

alter system switch logfile;

alter system switch logfile;

然后进rman复制数据库：

rman

connect target /;

connect auxiliary sys/sys@orateststb;

DUPLICATE TARGET DATABASE FOR STANDBY

FROM ACTIVE DATABASE

SPFILE PARAMETER_VALUE_CONVERT '/opt/oracle/oradata/oratest', '/opt/oracle/oradata/orateststb'

SET db_unique_name='oratest'

SET SGA_MAX_SIZE = '2608M'

SET SGA_TARGET = '2608M'

SET LOG_FILE_NAME_CONVERT = '/opt/oracle/oradata/oratest','/opt/oracle/oradata/orateststb'

DB_FILE_NAME_CONVERT '/opt/oracle/oradata/oratest','/opt/oracle/oradata/orateststb';

也可以指定：

set control_files='+data'

如果是复制数据库，然后并打开，而不是建一个standby数据库，则语句如下：

DUPLICATE TARGET DATABASE for standby

TO orateststb

FROM ACTIVE DATABASE

SPFILE PARAMETER_VALUE_CONVERT '/opt/oracle/oradata/oratest', '/opt/oracle/oradata/orateststb'

SET SGA_MAX_SIZE = '2608M'

SET SGA_TARGET = '2608M'

SET LOG_FILE_NAME_CONVERT = '/opt/oracle/oradata/oratest','/opt/oracle/oradata/orateststb'

DB_FILE_NAME_CONVERT '/opt/oracle/oradata/oratest','/opt/oracle/oradata/orateststb';

如果报下面的错误，可能是主库的归档设置不正确，导致无法正确归档

RMAN-03002: failure of Duplicate Db command at

RMAN-20208: UNTIL CHANGE is before RESETLOGS change

alter system switch logfile;

主库上：

log_archive_dest_1='location=/u01/app/oracle/arch/test/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES)'

log_archive_dest_2='SERVICE=orateststb LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)'

*.fal_client=oratest

*.fal_server=orateststb

alter system set db_file_name_convert='/opt/oracle/oradata/orateststb','/opt/oracle/oradata/oratest' scope=spfile;

alter system set log_file_name_convert='/opt/oracle/oradata/orateststb','/opt/oracle/oradata/oratest' scope=spfile;

备库上：

log_archive_dest_1='location=/u01/app/oracle/arch/test/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES)'

log_archive_dest_2='SERVICE=oratest LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)'

fal_client=orateststb

fal_server=oratest

alter system set db_file_name_convert='/opt/oracle/oradata/oratest','/opt/oracle/oradata/orateststb' scope=spfile;

另外的示例：

connect target sys/oracle123@prolin11

connect auxiliary sys/oracle123@pro11sb

run {

allocate channel c1 type disk;

allocate auxiliary channel s1 type disk;

duplicate target database

for standby

from active database

dorecover

spfile

parameter_value_convert 'prolin11','pro11sb'

set db_unique_name='pro11sb'

set db_file_name_convert='/prolin11/','/pro11sb/'

set log_file_name_convert='/prolin11/','/pro11sb/'

set control_files='/oradata/pro11sb/control01.ctl'

set fal_client='pro11sb'

set fal_server='prolin11'

set standby_file_management='AUTO'

set log_archive_config='dg_config=(prolin11,pro11sb)'

set log_archive_dest_2='service=prolin11 LGWR ASYNC valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=pro11sb'

set log_archive_dest_state_2='enable'

set log_archive_format='pro11sb_%t_%s_%r.arc'

;

sql channel c1 "alter system archive log current";

sql channel s1 "alter database recover managed standby database using current logfile disconnect";

}

=========================================== 方法一 ===========================================

一、搭建standby实例环境。

1.

在主数据库中：

create pfile from spfile;

通过产生的这个initboss.ora文件拷贝到standby数据库的机器上(如果在同一台机器中改名为initbossstd.ora)，修改或添加如下内容:

*.background_dump_dest='D:\oracle\admin\bossstd\bdump'

*.control_files='D:\oracle\oradata\bossstd\control01.ctl'

*.core_dump_dest='D:\oracle\admin\bossstd\cdump'

*.db_file_name_convert='D:\oracle\oradata\boss','D:\oracle\oradata\bossstd'

*.log_file_name_convert='D:\oracle\oradata\boss','D:\oracle\oradata\bossstd'

*.standby_archive_dest='D:\oracle\oradata\bossstd\LOG'

*.log_archive_dest_1='location=D:\oracle\oradata\bossstd\log'

*.user_dump_dest='D:\oracle\admin\bossstd\udump'

*.lock_name_space=bossstd

*.instance_name='bossstd'

*.fal_client=boss

*.fal_server=bossstd

*.standby_file_management=AUTO

注意如果在与主数据库相同的机器上建standby数据库，需要修改instance_name和lock_name_space。

*.db_files=2000

2.把主库的passwd文件拷贝到standby库的主机上：

orapwd file=PWDbossstd password=sys entries=10

检查standby数据库是否使用了password file:

select * from v$pwfile_users;

show parameter remote_login_passwordfile;

remote_login_passwordfile参数需要是：EXCLUSIVE

测试是否能连接到数据库：

sqlplus sys/sys@bossstb as sysdba

3.启动standby实例，看是否正常

export ORACLE_SID=bossstd

sqlplus "sys/sys as sysdba"

startup nomount pfile='....../initbossstd.ORA';

能正常启动。

二、拷贝standby的数据文件。

生成standby的控制文件：

alter database create standby controlfile as '/u01/oracle/backup/contrl01.ctl';

使用冷拷贝或rman工具把数据文件拷贝到新的standby数据库环境。

在standby数据库环境：

alter database mount standby database;

三、配置监听，让主库能把log文件归档到standby数据库环境中

#主库的监听

BOSS =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = hq-it-092486m)(PORT = 1521))

)

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = boss)

)

)

#standby库的监听

BOSSSTD =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = hq-it-092486m)(PORT = 1521))

)

(CONNECT_DATA =

(SERVER = DEDICATED)

(SID = bossstd)

)

)

用sqlplus "sys/sys@bossstd"测试监听是否正常。

到主数据库中，修改参数，让其能归档到standby数据库中：

alter system set log_archive_dest_2='SERVICE=bossstd REOPEN=300' scope=both;

alter system set log_archive_dest_state_2=enable scope=both;

在主数据库中做测试，看归档是否正常

alter system switch logfile;

四、在standby库中恢复日志

可以先：

recover standby database;

最后让其自动滚日志：

alter database recover managed standby database disconnect from session;

select sequence#,applied from v$archived_log;

=========================================== 方法二 duplicate standby  ===========================================

duplicate standby

在Oracle9i中使用RMAN进行DataGuard数据库的创建过程中，可能会遇到如下错误：

RMAN> duplicate target database for standby dorecover;

RMAN-03002: failure of Duplicate Db command at 11/16/2007 10:23:12

RMAN-05507: standby controlfile checkpoint (23456133) is more recent than duplication point in time (23412363)

这个错误是说控制文件教新，也就是说相较数据文件而言，控制文件是新的，这是由于控制文件的持续更新的原因。

所以在执行Duplicate之前，我们需要在源数据库执行一次LOG ARCHIVE动作，增加一下检查点。

SQL> alter system archive log current;

System altered.

Oracle文档对这个错误的解释如下：

RMAN-05507: standby controlfile checkpoint (string) is more recent than duplication point in time (string)

Cause: A DUPLICATE FOR STANDBY command was issued, but the checkpoint of the controlfile is more recent than the last archivelog or the specified point in time.

Action: If an explicit point in time was specified, change it to be at least the controlfile checkpoint; otherwise archive (and backup/copy) the current log.

archive log current正式解决这个问题的方法之一！

===========================通过修改数据库实例名的方法复制数据库=====================

在复制操作中最重要的步骤一个是需要修改参数配置中的相应实例名信息，

另一个就是需要删除控制文件，并且重建控制文件时要更改 reuse database old_sid noresetlogs 为 set database new_sid resetlogs.

步骤如下:

源数据库为oratest1，复制的目标数据库为oratest2

1.

export ORACLE_SID=oratest2

mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/adump

mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/bdump

mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/cdump

mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/udump

istener.ora中增加监听

vi /opt/oracle/products/10.2/network/admin/listener.ora

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS_LIST =

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

)

)

)

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = oratest.db.alibaba.com)

(ORACLE_HOME = /opt/oracle/products/10.2)

(SID_NAME = oratest)

)

(SID_DESC =

(GLOBAL_DBNAME = oratest2.db.alibaba.com)

(ORACLE_HOME = /opt/oracle/products/10.2)

(SID_NAME = oratest2)

)

)

orapwd file=orapwaux password=sys entries=10

select * from v$pwfile_users;

创建init.ora文件

cp $ORACLE_HOME/dbs/initoratest1.ora $ORACLE_HOME/dbs/initoratest2.ora

注意修改db_name，instance_name及control_files参数

2.创建服务(WINDOWS平台使用)

cd %ORACLE_HOME%/bin

oradim -new -sid orcl -startmode manual -pfile "c:/.../initorcl.ora"

3.准备重建控制文件的语句

在原先的实例中：

alter database backup controlfile to trace;

找到相应的trace file，修改中间的建control file的语句，

主要修改

REUSE DATABASE "ORATEST" NORESETLOGS

为set DATABASE "ORATEST2" RESETLOGS

4. 把新的数据文件复制到新的位置

需要把源数据库停下来拷贝

cp /opt/oracle/oradata/oratest1/*  /opt/oracle/oradata/oratest2

5. 创建控制文件，打开新的数据库

startup nomount

CREATE CONTROLFILE set DATABASE "ORATEST2" RESETLOGS  NOARCHIVELOG

MAXLOGFILES 20

MAXLOGMEMBERS 5

MAXDATAFILES 500

MAXINSTANCES 2

MAXLOGHISTORY 584

LOGFILE

GROUP 1 '/opt/oracle/oradata/oratest2/redo_1_1.log'  SIZE 500M,

GROUP 2 '/opt/oracle/oradata/oratest2/redo_2_1.log'  SIZE 500M,

GROUP 3 '/opt/oracle/oradata/oratest2/redo_3_1.log'  SIZE 500M

-- STANDBY LOGFILE

DATAFILE

'/opt/oracle/oradata/oratest2/system01.dbf',

'/opt/oracle/oradata/oratest2/undotbs01.dbf',

'/opt/oracle/oradata/oratest2/sysaux01.dbf',

'/opt/oracle/oradata/oratest2/user01.dbf'

CHARACTER SET US7ASCII

;

alter database open resetlogs;

给数据库的临时表空间增加文件

alter tablespace temp add tempfile '/opt/oracle/oradata/oratest2/temp01.dbf' size 2048M reuse;

=============================duplicate_database==========================

orapwd file=orapwaux password=sys entries=10

select * from v$pwfile_users;

建aux实例的init$ORACLE_SID.ora文件：

db_domain='db.alibaba.com'

db_name='aux'

sga_max_size=2600M

sga_target=2600M

java_pool_size=16777216

large_pool_size=16777216

shared_pool_size=838860800

pga_aggregate_target=1G

processes=500

audit_file_dest='/opt/oracle/admin/aux/adump'

background_dump_dest='/opt/oracle/admin/aux/bdump'

user_dump_dest='/opt/oracle/admin/aux/udump'

core_dump_dest='/opt/oracle/admin/aux/cdump'

compatible='10.2.0.1.0'

db_block_size=8192

job_queue_processes=10

open_cursors=300

remote_os_authent=FALSE

remote_login_passwordfile=EXCLUSIVE

export ORACLE_SID=aux

mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/adump

mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/bdump

mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/cdump

mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/udump

listener.ora中增加监听

vi /opt/oracle/products/10.2/network/admin/listener.ora

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS_LIST =

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

)

)

)

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = oratest.db.alibaba.com)

(ORACLE_HOME = /opt/oracle/products/10.2)

(SID_NAME = oratest)

)

(SID_DESC =

(GLOBAL_DBNAME = oratest.db.alibaba.com)

(ORACLE_HOME = /opt/oracle/products/10.2)

(SID_NAME = oratest2)

)

(SID_DESC =

(GLOBAL_DBNAME = oratest.db.alibaba.com)

(ORACLE_HOME = /opt/oracle/products/10.2)

(SID_NAME = aux)

)

)

connect target sys/sys@oratest;

connect auxiliary sys/sys@oratest2;

run {

ALLOCATE AUXILIARY CHANNEL aux1 DEVICE TYPE DISK;

set newname for datafile '/opt/oracle/oradata/oratest/system01.dbf' to '/opt/oracle/oradata/oratest2/system01.dbf';

set newname for datafile '/opt/oracle/oradata/oratest/undotbs01.dbf' to '/opt/oracle/oradata/oratest2/undotbs01.dbf';

set newname for datafile '/opt/oracle/oradata/oratest/sysaux01.dbf' to '/opt/oracle/oradata/oratest2/sysaux01.dbf';

set newname for datafile '/opt/oracle/oradata/oratest/user01.dbf' to '/opt/oracle/oradata/oratest2/user01.dbf';

set newname for tempfile '/opt/oracle/oradata/oratest/temp01.dbf' to '/opt/oracle/oradata/oratest2/temp01.dbf';

duplicate target database to oratest2

logfile

GROUP 1 ('/opt/oracle/oradata/oratest2/tmpfs/redo_1_1.log') SIZE 500M REUSE,

GROUP 2 ('/opt/oracle/oradata/oratest2/tmpfs/redo_2_1.log') SIZE 500M REUSE,

GROUP 3 ('/opt/oracle/oradata/oratest2/tmpfs/redo_3_1.log') SIZE 500M REUSE;

}