搭建物理DG
建立物理datagurad的前提条件
archivelog, 开启 强制日志alter database force logging 意思 primary无论什么情况都做redo,通过select force_logging from v$database可以看到当前数据库强制日志模式的状态
手动方式
SQL> alter database force logging;
数据库已更改。
SQL> select force_logging from v$database;
FOR
---
YES
SQL> archive log list
数据库日志模式 存档模式
自动存档 启用
存档终点 USE_DB_RECOVERY_FILE_DEST
最早的联机日志序列 1
下一个存档日志序列 3
当前日志序列 3
SQL> select 'host copy '||name||' d:\osbackup' from v$tablespace;
'HOSTCOPY'||NAME||'D:\OSBACKUP'
----------------------------------------------------
host copy SYSTEM d:\osbackup
host copy UNDOTBS1 d:\osbackup
host copy SYSAUX d:\osbackup
host copy USERS d:\osbackup
host copy TEMP d:\osbackup
host copy EXAMPLE d:\osbackup
已选择6行。
SQL> host mkdir e:\standby
SQL> host mkdir e:\standby\adump
SQL> host mkdir e:\standby\bdump
SQL> host mkdir e:\standby\udump
SQL> host mkdir e:\standby\cdump
SQL> host mkdir e:\standby\archive
SQL> host mkdir e:\standby\datafile
进行热备份
SQL> alter database begin backup
2 ;
数据库已更改。
SQL> @ d:\standbyscript\backup.txt
已复制 1 个文件。
已复制 1 个文件。
已复制 1 个文件。
已复制 1 个文件。
已复制 1 个文件。
SQL>
SQL> alter database end backup;
数据库已更改。
SQL> alter system switch logfile;
系统已更改。
SQL> alter system checkpoint;
系统已更改。
创建standby controlfile
SQL> alter database create standby controlfile as 'e:\standby\datafile\control01
.ctl';
数据库已更改。
SQL> alter database create standby controlfile as 'e:\standby\datafile\control02
.ctl';
数据库已更改。
SQL> alter database create standby controlfile as 'e:\standby\datafile\control03
.ctl';
数据库已更改。
修改primary target pfile
xh.__db_cache_size=201326592
xh.__java_pool_size=4194304
xh.__large_pool_size=4194304
xh.__shared_pool_size=71303168
xh.__streams_pool_size=0
*.audit_file_dest='g:\oracle\product\10.2.0/admin/xh/adump'
*.background_dump_dest='g:\oracle\product\10.2.0/admin/xh/bdump'
*.compatible='10.2.0.1.0'
*.control_files='g:\oracle\product\10.2.0\oradata\xh\control01.ctl','g:\oracle\product\10.2.0\oradata\xh\control02.ctl','g:\oracle\product\10.2.0\oradata\xh\control03.ctl'
*.core_dump_dest='g:\oracle\product\10.2.0/admin/xh/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='xh'
*.db_recovery_file_dest='g:\oracle\product\10.2.0/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.db_unique_name='xh'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=xhXDB)'
*.fal_client='xh'
*.fal_server='standby1'
*.job_queue_processes=10
*.log_archive_dest_1='location=G:\archivelog valid_for=(all_logfiles,all_roles) db_unique_name=xh'
*.log_archive_dest_2='service=standby1 valid_for=(online_logfiles,primary_role) db_unique_name=standby1'
*.open_cursors=300
*.pga_aggregate_target=95420416
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=287309824
*.standby_file_management='auto'
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='g:\oracle\product\10.2.0/admin/xh/udump'
*.log_archive_config='dg_config=(xh,standby1)'
SQL> create spfile from pfile;
文件已创建。
SQL> shutdown immediate;
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> create spfile from pfile;
文件已创建。
SQL> startup
ORACLE 例程已经启动。
Total System Global Area 289406976 bytes
Fixed Size 1248576 bytes
Variable Size 79692480 bytes
Database Buffers 201326592 bytes
Redo Buffers 7139328 bytes
数据库装载完毕。
数据库已经打开。
SQL>
建立standby pfile
standby1.__db_cache_size=201326592
standby1.__java_pool_size=4194304
standby1.__large_pool_size=4194304
standby1.__shared_pool_size=71303168
standby1.__streams_pool_size=0
*.audit_file_dest='e:\standby/adump'
*.background_dump_dest='e:\standby/bdump'
*.compatible='10.2.0.1.0'
*.control_files='e:\standby\datafile\control01.ctl'
*.core_dump_dest='e:\standby/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='xh'
*.db_recovery_file_dest='e:\standby\archive'
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=xhXDB)'
*.job_queue_processes=10
*.open_cursors=300
*.pga_aggregate_target=95420416
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=287309824
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='e:\standby/udump'
*.fal_client='standby1'
*.fal_server='xh'
*.db_unique_name='standby1'
*.log_archive_dest_1='location=e:\standby\archive valid_for=(all_logfiles,all_roles) db_unique_name=standby1'
*.log_archive_dest_2='service=xh valid_for=(online_logfiles,primary_role) db_unique_name=xh'
*.standby_file_management='auto'
*.service_names=standby1
*.instance_name=standby1
*.standby_archive_dest='e:\standby\archive'
*.db_file_name_convert='g:\oracle\product\10.2.0\oradata\xh','e:\standby\datafile'
*.log_file_name_convert='g:\oracle\product\10.2.0\oradata\xh','e:\standby\datafile'
*.log_archive_config='dg_config=(xh,standby1)'
C:\>oradim -new -sid standby1 -intpwd a831115
实例已创建。
C:\>set oracle_sid=standby1
C:\>sqlplus "/ as sysdba"
SQL*Plus: Release 10.2.0.1.0 - Production on 星期二 11月 3 10:30:13 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
已连接到空闲例程。
SQL> startup nomount pfile='e:\standby\datafile\initstandby1.ora'
ORACLE 例程已经启动。
Total System Global Area 289406976 bytes
Fixed Size 1248576 bytes
Variable Size 79692480 bytes
Database Buffers 201326592 bytes
Redo Buffers 7139328 bytes
SQL> create spfile from pfile='e:\standby\datafile\initstandby1.ora'
2 ;
文件已创建。
SQL> shutdown immediate;
ORA-01507: ??????
ORACLE 例程已经关闭。
SQL> startup nomount
ORACLE 例程已经启动。
Total System Global Area 289406976 bytes
Fixed Size 1248576 bytes
Variable Size 79692480 bytes
Database Buffers 201326592 bytes
Redo Buffers 7139328 bytes
～～～～～～～～～～～～～～～～～～～～～～～～～～～将备份数据文件 考过来
SQL> startup mount
ORACLE 例程已经启动。
Total System Global Area 289406976 bytes
Fixed Size 1248576 bytes
Variable Size 79692480 bytes
Database Buffers 201326592 bytes
Redo Buffers 7139328 bytes
数据库装载完毕。
ORACLE NET
primary target
C:\>hostname
PC-200901221248
listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = g:\oracle\product\10.2.0\db_1)
(PROGRAM = extproc)
)
(SID_DESC =
(SID_NAME = xh)
(ORACLE_HOME = g:\oracle\product\10.2.0\db_1)
(GLOBAL_DBNAME = xh)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = PC-200901221248)(PORT = 1521))
)
)
LSTANDBY =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC2))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = PC-200901221248)(PORT = 1522))
)
)
SID_LIST_LSTANDBY=
(SID_DESC =
(SID_NAME = standby1)
(ORACLE_HOME = g:\oracle\product\10.2.0\db_1)
(GLOBAL_DBNAME = standby1)
)
)
配置完后 要重启lsnrctl stop
lsnrctl start
lsnrctl status(查看状态)
tnsname.ora
XH =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = PC-200901221248)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = xh)
)
)
EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)
STANDBY1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = PC-200901221248)(PORT = 1522))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = STANDBY1)
)
)
SQL> host tnsping standby1
TNS Ping Utility for 32-bit Windows: Version 10.2.0.1.0 - Production on 03-11月-
2009 11:13:38
Copyright (c) 1997, 2005, Oracle. All rights reserved.
已使用的参数文件:
g:\oracle\product\10.2.0\db_1\network\admin\sqlnet.ora
已使用 TNSNAMES 适配器来解析别名
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = PC-20090
1221248)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = STAN
DBY1)))
OK (30 毫秒)
SQL> host tnsping xh(standby操作)
TNS Ping Utility for 32-bit Windows: Version 10.2.0.1.0 - Production on 03-11月-
2009 11:11:10
Copyright (c) 1997, 2005, Oracle. All rights reserved.
已使用的参数文件:
g:\oracle\product\10.2.0\db_1\network\admin\sqlnet.ora
已使用 TNSNAMES 适配器来解析别名
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = PC-20090
1221248)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = xh))
)
OK (30 毫秒)
C:\>sqlplus system/a831115@standby1
SQL*Plus: Release 10.2.0.1.0 - Production on 星期日 11月 8 12:54:48 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> select open_mode,db_unique_name from v$database;
OPEN_MODE DB_UNIQUE_NAME
---------- ------------------------------
READ ONLY standby1
SQL> conn system/a831115@xh
已连接。
SQL> select open_mode,db_unique_name from v$database;
OPEN_MODE DB_UNIQUE_NAME
---------- ------------------------------
READ WRITE xh
standby
SQL> select name,applied from v$archived_log;
未选定行
target
SQL> alter system switch logfile;
系统已更改。
standby
SQL> select count(*) from v$archived_log;
COUNT(*)
----------
7
target
SQL> select * from t1;
A
----------
1
2
SQL> insert into t1 values(3);
已创建 1 行。
SQL> commit;
提交完成。
SQL> alter system switch logfile;
系统已更改。
standby
SQL> alter database recover managed standby database disconnect from session;
数据库已更改。
SQL> select applied from v$archived_log;
APP
---
NO
NO
NO
NO
NO
YES
YES
YES
YES
YES
已选择10行。
SQL> select applied from v$archived_log;
APP
---
NO
NO
NO
NO
NO
YES~~~~~~~~~~~~~~~~~~应用日志
YES
YES
YES
YES
已选择10行。
switch over
SQL> alter database recover managed standby database cancel;
数据库已更改。
SQL> alter database open read only;
数据库已更改。
SQL> select * from t1;
A
----------
1
2
3
SQL>
SQL> select DB_UNIQUE_NAME ,DATABASE_ROLE,OPEN_MODE from v$database;
DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE
------------------------------ ---------------- ----------
standby1 PHYSICAL STANDBY READ ONLY
SQL> select DB_UNIQUE_NAME ,DATABASE_ROLE,OPEN_MODE from v$database;
DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE
------------------------------ ---------------- ----------
xh PRIMARY READ WRITE
SQL> select database_role,switchover_status from v$database
2 ;
DATABASE_ROLE SWITCHOVER_STATUS
---------------- --------------------
PRIMARY SESSIONS ACTIVE
SQL> alter database commit to switchover to physical standby;
alter database commit to switchover to physical standby
*
第 1 行出现错误:
ORA-01093: ALTER DATABASE CLOSE 仅允许在没有连接会话时使用
提交完成。
SQL> select database_role,switchover_status from v$database;
DATABASE_ROLE SWITCHOVER_STATUS
---------------- --------------------
PRIMARY SESSIONS ACTIVE~~~~~~~~~~~加with session shutdown
SQL> alter database commit to switchover to physical standby with session shutdo
wn;
数据库已更改。
SQL> select database_role,switchover_status from v$database;
DATABASE_ROLE SWITCHOVER_STATUS
---------------- --------------------
PHYSICAL STANDBY SWITCHOVER PENDING~~~~~~~~~~pending(或SWITCHOVER LATENT)状态 要先恢复下
SQL> alter database commit to switchover to primary;
alter database commit to switchover to primary
*
第 1 行出现错误:
ORA-16139: 需要介质恢复
alter database recover managed standby database disconnect from session;*************
SQL> select database_role,switchover_status from v$database;
DATABASE_ROLE SWITCHOVER_STATUS
---------------- --------------------
PHYSICAL STANDBY TO PRIMARY
SQL> alter database commit to switchover to primary;
数据库已更改。
。
SQL> startup
ORACLE 例程已经启动。
Total System Global Area 289406976 bytes
Fixed Size 1248576 bytes
Variable Size 79692480 bytes
Database Buffers 201326592 bytes
Redo Buffers 7139328 bytes
数据库装载完毕。
SQL> select DB_UNIQUE_NAME ,DATABASE_ROLE,OPEN_MODE from v$database;
DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE
------------------------------ ---------------- ----------
standby1 PRIMARY READ WRITE
SQL> select database_role,switchover_status from v$database;
DATABASE_ROLE SWITCHOVER_STATUS
---------------- --------------------
PHYSICAL STANDBY TO PRIMARY
SQL> select DB_UNIQUE_NAME ,DATABASE_ROLE,OPEN_MODE from v$database;
DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE
------------------------------ ---------------- ----------
xh PHYSICAL STANDBY MOUNTED
交换回来************
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/12020513/viewspace-619708/ ，如需转载，请注明出处，否则将追究法律责任。
