11g手工搭建ADG

最新推荐文章于 2024-06-20 14:31:02 发布

congjiaax703023

最新推荐文章于 2024-06-20 14:31:02 发布

阅读量98

点赞数

文章标签：数据库网络开发工具

主库：开启归档模式
SYS@PROD1> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch
Oldest online log sequence 6
Current log sequence 8

SYS@PROD1> show parameter recovery;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string
db_recovery_file_dest_size big integer 0
recovery_parallelism integer 0

SYS@PROD1> alter system set db_recovery_file_dest_size=4g;

System altered.

SYS@PROD1> alter system set db_recovery_file_dest='/home/oracle/flash' scope=spfile;

System altered.

SYS@PROD1> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@PROD1> startup mount;
ORACLE instance started.

Total System Global Area 941600768 bytes
Fixed Size 1348860 bytes
Variable Size 515902212 bytes
Database Buffers 419430400 bytes
Redo Buffers 4919296 bytes
Database mounted.

SYS@PROD1> alter database archivelog;

Database altered.

SYS@PROD1> alter database open;

Database altered.

SYS@PROD1> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 6
Next log sequence to archive 8
Current log sequence 8

主库：开启强制写日志功能
SYS@PROD1> select force_logging from v$database;

FOR
---
NO

SYS@PROD1> alter database force logging;

Database altered.

主库：配置静态监听
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = lfdz1.example.com)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)

ADR_BASE_LISTENER = /u01/app/oracle

SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=PROD1)
(ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME=PROD1)))

主库：配置tnsnames文件：
SBDB1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = lfdz2.example.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = SBDB1)
)
)

主库：增加standby logfile文件
SYS@PROD1> set linesize 200;
SYS@PROD1> col member for a60
SYS@PROD1> select group#, member from v$logfile;

GROUP# MEMBER
---------- ------------------------------------------------------------
3 /u01/app/oracle/oradata/PROD1/redo03.log
2 /u01/app/oracle/oradata/PROD1/redo02.log
1 /u01/app/oracle/oradata/PROD1/redo01.log

SYS@PROD1> select bytes/1024/1024 "Size MB" from v$log;
Size MB
----------
50
50
50

SYS@PROD1> ed 1
alter database add standby logfile '/u01/app/oracle/oradata/PROD1/redo04.log' size 50m;
alter database add standby logfile '/u01/app/oracle/oradata/PROD1/redo05.log' size 50m;
alter database add standby logfile '/u01/app/oracle/oradata/PROD1/redo06.log' size 50m;
alter database add standby logfile '/u01/app/oracle/oradata/PROD1/redo07.log' size 50m;

SYS@PROD1> @1
Database altered.
Database altered.
Database altered.
Database altered.

SYS@PROD1> select group#, member, type from v$logfile where type='STANDBY';

GROUP# MEMBER TYPE
---------- ------------------------------------------------------------ -------
4 /u01/app/oracle/oradata/PROD1/redo04.log STANDBY
5 /u01/app/oracle/oradata/PROD1/redo05.log STANDBY
6 /u01/app/oracle/oradata/PROD1/redo06.log STANDBY
7 /u01/app/oracle/oradata/PROD1/redo07.log STANDBY

主库：修改参数文件，将其下面内容添加到initPROD1.ora文件中
SYS@PROD1> create pfile from spfile;

File created.

SYS@PROD1> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

[oracle@lfdz1 dbs]$ vi initPROD1.ora
DB_UNIQUE_NAME=PROD1
LOG_ARCHIVE_CONFIG='DG_CONFIG=(PROD1,SBDB1)'
LOG_ARCHIVE_DEST_1=
'LOCATION=/home/oracle/flash
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=PROD1'
LOG_ARCHIVE_DEST_2=
'SERVICE=SBDB1 ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=SBDB1'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
LOG_ARCHIVE_MAX_PROCESSES=30
FAL_SERVER=SBDB1
DB_FILE_NAME_CONVERT='SBDB1','PROD1'
LOG_FILE_NAME_CONVERT='SBDB1','PROD1'
STANDBY_FILE_MANAGEMENT=AUTO

[oracle@lfdz1 .llf]$ sql
SQL*Plus: Release 11.2.0.3.0 Production on Mon May 9 15:37:41 2016
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to an idle instance.

SYS@PROD1> create spfile from pfile;

File created.

SYS@PROD1> startup
ORACLE instance started.

Total System Global Area 523108352 bytes
Fixed Size 1346052 bytes
Variable Size 314574332 bytes
Database Buffers 201326592 bytes
Redo Buffers 5861376 bytes
Database mounted.
Database opened.
SYS@PROD1>

备库：配置静态监听
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=SBDB1)
(ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1)
(SID_NAME=SBDB1)))

备库：配置tnsnames文件
PROD1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = lfdz1.example.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = PROD1)
)
)

主库：拷贝主库参数文件、密码文件到备库并改名
[oracle@lfdz1 dbs]$ scp initPROD1.ora lfdz2:/u01/app/oracle/product/11.2.0/db_1/dbs/initSBDB1.ora
[oracle@lfdz1 dbs]$ scp orapwPROD1 lfdz2:/u01/app/oracle/product/11.2.0/db_1/dbs/orapwSBDB1

备库：修改参数文件：（将原始的PROD1和SBDB1位置进行调换）
[oracle@lfdz2 dbs]$ vim initSBDB1.ora
DB_UNIQUE_NAME=SBDB1
LOG_ARCHIVE_CONFIG='DG_CONFIG=(SBDB1,PROD1)'
LOG_ARCHIVE_DEST_1=
'LOCATION=/home/oracle/flash
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=SBDB1'
LOG_ARCHIVE_DEST_2=
'SERVICE=PROD1 ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=PROD1'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
LOG_ARCHIVE_MAX_PROCESSES=30
FAL_SERVER=PROD1
DB_FILE_NAME_CONVERT='PROD1','SBDB1'
LOG_FILE_NAME_CONVERT='PROD1','SBDB1'
STANDBY_FILE_MANAGEMENT=AUTO

备库：创建相应的目录
[oracle@lfdz2 ~]$ mkdir -p /u01/app/oracle/oradata/SBDB1/
[oracle@lfdz2 ~]$ mkdir -p /u01/app/oracle/admin/SBDB1/adump
[oracle@lfdz2 ~]$ mkdir flash
[oracle@lfdz2 ~]$ export ORACLE_SID=SBDB1
[oracle@lfdz2 ~]$ sqlplus "/as sysdba"
SQL*Plus: Release 11.2.0.3.0 Production on Mon May 9 15:59:03 2016
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to an idle instance.

SYS@SBDB1> create spfile from pfile;

File created.

SYS@SBDB1> startup
ORACLE instance started.

Total System Global Area 523108352 bytes
Fixed Size 1346052 bytes
Variable Size 314574332 bytes
Database Buffers 201326592 bytes
Redo Buffers 5861376 bytes
ORA-00205: error in identifying control file, check alert log for more info

主库：通过rman duplicate方式进行备库恢复
[oracle@lfdz1 .llf]$ rman target / auxiliary sys/oracle@sbdb1
Recovery Manager: Release 11.2.0.3.0 - Production on Mon May 9 16:02:17 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: PROD1 (DBID=2082231315)
connected to auxiliary database: PROD1 (not mounted)

RMAN> duplicate target database for standby from active database;

Starting Duplicate Db at 09-MAY-16
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=10 device type=DISK

contents of Memory Script:
{
backup as copy reuse
targetfile '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwPROD1' auxiliary format
'/u01/app/oracle/product/11.2.0/db_1/dbs/orapwSBDB1' ;
}
executing Memory Script

Starting backup at 09-MAY-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=34 device type=DISK
Finished backup at 09-MAY-16

contents of Memory Script:
{
backup as copy current controlfile for standby auxiliary format '/u01/app/oracle/oradata/SBDB1/control01.ctl';
restore clone controlfile to '/u01/app/oracle/oradata/SBDB1/control02.ctl' from
'/u01/app/oracle/oradata/SBDB1/control01.ctl';
}
executing Memory Script

Starting backup at 09-MAY-16
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying standby control file
output file name=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_PROD1.f tag=TAG20160509T160249 RECID=2 STAMP=911404969
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 09-MAY-16

Starting restore at 09-MAY-16
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: copied control file copy
Finished restore at 09-MAY-16

contents of Memory Script:
{
sql clone 'alter database mount standby database';
}
executing Memory Script

sql statement: alter database mount standby database
contents of Memory Script:
{
set newname for tempfile 1 to
"/u01/app/oracle/oradata/SBDB1/temp01.dbf";
switch clone tempfile all;
set newname for datafile 1 to
"/u01/app/oracle/oradata/SBDB1/system01.dbf";
set newname for datafile 2 to
"/u01/app/oracle/oradata/SBDB1/sysaux01.dbf";
set newname for datafile 3 to
"/u01/app/oracle/oradata/SBDB1/undotbs01.dbf";
set newname for datafile 4 to
"/u01/app/oracle/oradata/SBDB1/users01.dbf";
set newname for datafile 5 to
"/u01/app/oracle/oradata/SBDB1/example01.dbf";
backup as copy reuse
datafile 1 auxiliary format
"/u01/app/oracle/oradata/SBDB1/system01.dbf" datafile
2 auxiliary format
"/u01/app/oracle/oradata/SBDB1/sysaux01.dbf" datafile
3 auxiliary format
"/u01/app/oracle/oradata/SBDB1/undotbs01.dbf" datafile
4 auxiliary format
"/u01/app/oracle/oradata/SBDB1/users01.dbf" datafile
5 auxiliary format
"/u01/app/oracle/oradata/SBDB1/example01.dbf" ;
sql 'alter system archive log current';
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /u01/app/oracle/oradata/SBDB1/temp01.dbf in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting backup at 09-MAY-16
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/u01/app/oracle/oradata/PROD1/system01.dbf
output file name=/u01/app/oracle/oradata/SBDB1/system01.dbf tag=TAG20160509T160257
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/u01/app/oracle/oradata/PROD1/sysaux01.dbf
output file name=/u01/app/oracle/oradata/SBDB1/sysaux01.dbf tag=TAG20160509T160257
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=/u01/app/oracle/oradata/PROD1/example01.dbf
output file name=/u01/app/oracle/oradata/SBDB1/example01.dbf tag=TAG20160509T160257
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/u01/app/oracle/oradata/PROD1/undotbs01.dbf
output file name=/u01/app/oracle/oradata/SBDB1/undotbs01.dbf tag=TAG20160509T160257
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/u01/app/oracle/oradata/PROD1/users01.dbf
output file name=/u01/app/oracle/oradata/SBDB1/users01.dbf tag=TAG20160509T160257
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 09-MAY-16

sql statement: alter system archive log current

contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script

datafile 1 switched to datafile copy
input datafile copy RECID=2 STAMP=911405028 file name=/u01/app/oracle/oradata/SBDB1/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=3 STAMP=911405028 file name=/u01/app/oracle/oradata/SBDB1/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=4 STAMP=911405028 file name=/u01/app/oracle/oradata/SBDB1/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=5 STAMP=911405028 file name=/u01/app/oracle/oradata/SBDB1/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=6 STAMP=911405028 file name=/u01/app/oracle/oradata/SBDB1/example01.dbf
Finished Duplicate Db at 09-MAY-16

验证是否搭建成功：
SYS@SBDB1> select DATABASE_ROLE, OPEN_MODE from v$database;

DATABASE_ROLE OPEN_MODE
---------------- --------------------
PHYSICAL STANDBY MOUNTED

[oracle@lfdz2 dbs]$ cd /u01/app/oracle/oradata/SBDB1/
[oracle@lfdz2 SBDB1]$ ls
control01.ctl redo01.log redo04.log redo07.log undotbs01.dbf
control02.ctl redo02.log redo05.log sysaux01.dbf users01.dbf
example01.dbf redo03.log redo06.log system01.dbf

SYS@PROD1> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /home/oracle/flash
Oldest online log sequence 11
Next log sequence to archive 13
Current log sequence 13

SYS@PROD1> alter system switch logfile;

System altered.

SYS@PROD1> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /home/oracle/flash
Oldest online log sequence 12
Next log sequence to archive 14
Current log sequence 14

SYS@PROD1> alter system switch logfile;

System altered.

SYS@PROD1> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /home/oracle/flash
Oldest online log sequence 13
Next log sequence to archive 15
Current log sequence 15

SYS@SBDB1> select process, pid, status, client_process from v$managed_standby;

PROCESS PID STATUS CLIENT_P
--------- ---------- ------------ --------
ARCH 14671 CLOSING ARCH
ARCH 14674 CONNECTED ARCH
ARCH 14676 CONNECTED ARCH
ARCH 14678 CONNECTED ARCH
RFS 14996 IDLE ARCH
RFS 14943 IDLE UNKNOWN
RFS 14945 IDLE UNKNOWN
RFS 14948 IDLE LGWR
RFS 14950 IDLE UNKNOWN

SYS@SBDB1> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /home/oracle/flash
Oldest online log sequence 12
Next log sequence to archive 0
Current log sequence 14

SYS@SBDB1> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /home/oracle/flash
Oldest online log sequence 13
Next log sequence to archive 0
Current log sequence 15

备库：开启ADG模式
SYS@SBDB1> SELECT PROTECTION_MODE, PROTECTION_LEVEL,DATABASE_ROLE ROLE, SWITCHOVER_STATUS FROM V$DATABASE;

PROTECTION_MODE PROTECTION_LEVEL ROLE SWITCHOVER_STATUS
-------------------- -------------------- ---------------- --------------------
MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE PHYSICAL STANDBY RECOVERY NEEDED

SYS@SBDB1> recover managed standby database using current logfile disconnect from session;
Media recovery complete.

SYS@SBDB1> recover managed standby database cancel;
Media recovery complete.

SYS@SBDB1> alter database open;

Database altered.

SYS@SBDB1> recover managed standby database using current logfile disconnect from session;
Media recovery complete.

SYS@SBDB1> select open_mode from v$database;

OPEN_MODE
--------------------
READ ONLY WITH APPLY

主备库数据测试
SYS@PROD1> create table t(id number);

Table created.

SYS@PROD1> insert into t values(1);

1 row created.

SYS@PROD1> commit;

Commit complete.

SYS@PROD1> select * from t;

ID
----------
1

SYS@SBDB1> select * from t;

ID
----------
1