1.开启force logging
alter database force logging;
注：这样就算no loging 也会产生相应的日志。
2.开启主库归档日志模式
shutdown immediate;
startup nomount ;
alter database mount ;
alter database archivelog;
3.在主库创建dbpaydby redo log 并修改db_unique_name名字
SELECT GROUP#, BYTES FROM V$LOG; //检查log数量
SQL> ALTER DATABASE ADD standby logfile group 4 '/u01/app/oradata/dbpay/s_redo01a.rdo' size 150m;
SQL> ALTER DATABASE ADD standby logfile group 5 '/u01/app/oradata/dbpay/s_redo02a.rdo' size 150m;
SQL> ALTER DATABASE ADD standby logfile group 6 '/u01/app/oradata/dbpay/s_redo03a.rdo' size 150m;
SQL> ALTER DATABASE ADD standby logfile group 7 '/u01/app/oradata/dbpay/s_redo04a.rdo' size 150m;
NOTE:1.计算创建SRL的个数：(每个thread的最大日志数+1)* 最大thread数；
4.先查看是否是spfile模式
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /u01/app/oracle/dbs/spfileppac
count.ora
如果不是 需要创建spfile文件。
SQL>create spfile from pfile;
SQL>shutdown immediate;
SQL>startup;
在主库
alter system set db_unique_name=dbpay1 scope=spfile;
在备库
alter system set db_unique_name=dbpay2 scope=spfile;
修改db_unique_name需要重启
5.修改主库参数文件
*.audit_file_dest='/u01/app/admin/dbpay/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oradata/dbpay/control01.ctl','/u01/app/flash_recovery_area/dbpay/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_file_name_convert='/u01/app/oradata/dbpay/','/u01/app/oradata/dbpay/'
*.db_name='dbpay'
*.db_recovery_file_dest='/u01/app/flash_recovery_area'
*.db_recovery_file_dest_size=4070572032
*.db_unique_name='dbpay1'
*.diagnostic_dest='/u01/app'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=dbpayXDB)'
*.fal_client='DBPAY2'
*.fal_server='DBPAY1'
*.log_archive_config='DG_CONFIG=(dbpay1,dbpay2)'
*.log_archive_dest_1='LOCATION=/u01/app/oradata/archlog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=dbpay1'
*.log_archive_dest_2='SERVICE=dbpay2 LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=dbpay2'
*.log_archive_dest_state_1='ENABLE'
*.log_file_name_convert='/u01/app/oradata/archlog/','/u01/app/oradata/archlog/','/u01/app/oradata/archlog/','/u01/app/oradata/archlog/'
*.memory_target=2084569088
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
注：如果出现ORA-32001: write to SPFILE requested but no SPFILE is in use 错误
需要动态修改一定要用spfile启动。如果现在是用pfile启动，可以这样切换成spfile启动：
SQL>create spfile from pfile;
SQL>shutdown immediate;
SQL>startup;
用show parameter spfile查看如果values对应有值，表示是spfile启动的。否则就是pfile启动的。
mkdir -p /u01/app/fast_recovery_area/dbpay/
cp /u01/app/oradata/dbpay/control01.ctl /u01/app/flash_recovery_area/dbpay/control02.ctl
6.创建pfile文件并修改
SQL>create pfile='/u01/app/oracle/dbs/initDBPAY.ora' from spfile;
7.备份主库
修改并执行
rman_backup_lev0.sh
8.修改/etc/hosts文件
192.168.31.66 node1
192.168.31.228 node2
9.建立主库到备库、备库到主库的网络连接 (主备库都需要改)
listener.ora
----------------------------------------------------------------
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = DBPAY)
(ORACLE_HOME = /u01/app/oracle)
(SID_NAME = DBPAY)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = node1)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
ADR_BASE_LISTENER = /u01/app
-----------------------------------------------------------------
tnsname.ora
-----------------------------------------------------------------
dbpay1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = node1)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = DBPAY)
)
)
dbpay2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = node2)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = DBPAY)
)
)
-----------------------------------------------------------------
10.在备库创建和主库相关的目录并传输文件
mkdir /home/rman
chmod 775 /home/rman/
chown oracle:oinstall /home/rman/
在主库
scp /home/rman/* oracle@192.168.31.228:/home/rman
scp /u01/app/oracle/network/admin/listener.ora oracle@192.168.31.228:/u01/app/oracle/network/admin/
scp /u01/app/oracle/network/admin/tnsnames.ora oracle@192.168.31.228:/u01/app/oracle/network/admin/
scp /u01/app/oracle/dbs/initDBPAY.ora oracle@192.168.31.228:/u01/app/oracle/dbs
11.修改备库的参数文件
*.audit_file_dest='/u01/app/admin/dbpay/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oradata/dbpay/control01.ctl','/u01/app/flash_recovery_area/dbpay/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_file_name_convert='/u01/app/oradata/dbpay/','/u01/app/oradata/dbpay/'
*.db_name='dbpay'
*.db_recovery_file_dest='/u01/app/flash_recovery_area'
*.db_recovery_file_dest_size=4070572032
*.db_unique_name='dbpay2'
*.diagnostic_dest='/u01/app'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=dbpayXDB)'
*.fal_client='DBPAY1'
*.fal_server='DBPAY2'
*.log_archive_config='DG_CONFIG=(dbpay1,dbpay2)'
*.log_archive_dest_1='LOCATION=/u01/app/oradata/archlog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=dbpay2'
*.log_archive_dest_2='SERVICE=dbpay1 LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=dbpay1'
*.log_archive_dest_state_1='ENABLE'
*.log_file_name_convert='/u01/app/oradata/archlog/','/u01/app/oradata/archlog/','/u01/app/oradata/archlog/','/u01/app/oradata/archlog/'
*.memory_target=2084569088
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
12.备库创建目录
mkdir -p /u01/app/oradata/dbpay/arch1
mkdir -p /u01/app/oradata/dbpay/rmanbak
cd $ORACLE_BASE
mkdir -p /u01/app/oracle/flash_recovery_area
mkdir -p /u01/app/fast_recovery_area
mkdir -p /u01/app/fast_recovery_area/dbpay/
mkdir -p /u01/app/admin/dbpay
cd /u01/app/admin/dbpay/
mkdir adump dpdump pfile
13.备库创建passwd file
在主库
orapwd file=/u01/app/oracle/dbs/orapwDBPAY password=zsht2014 entries=10 force=y;
cd /u01/app/oracle/dbs
scp orapwDBPAY root@192.168.31.228:/u01/app/oracle/dbs/
在备库
orapwd file=/u01/app/oracle/dbs/orapwDBPAY password=zsht2014 entries=10 force=y;
14.拷贝主机的ora文件
scp -p listener.ora tnsnames.ora root@192.168.31.228:/u01/app/oracle/network/admin/
在主备机测试
tnsping DBPAY
tnsping DBPAY2
15.在备库创建dbpaydby redo log
SELECT GROUP#, BYTES FROM V$LOG; //检查log数量
SQL> ALTER DATABASE ADD standby logfile group 4 '/u01/app/oradata/dbpay/s_redo01a.rdo' size 150m;
SQL> ALTER DATABASE ADD standby logfile group 5 '/u01/app/oradata/dbpay/s_redo02a.rdo' size 150m;
SQL> ALTER DATABASE ADD standby logfile group 6 '/u01/app/oradata/dbpay/s_redo03a.rdo' size 150m;
SQL> ALTER DATABASE ADD standby logfile group 7 '/u01/app/oradata/dbpay/s_redo04a.rdo' size 150m;
SQL> ALTER DATABASE ADD standby logfile group 8 '/u01/app/oradata/dbpay/s_redo05a.rdo' size 150m;
15.备库恢复数据
把备库启动到nomount状态
sqlplus / as sysdba
shutdown
startup nomount
exit
rman auxiliary /
Rman> connect target sys/zsht2014@DBPAY1
Rman> Duplicate target database for standby nofilenamecheck;
444
16.检查备库状态
SQL> select open_mode from v$database;
SQL> select status from v$datafile;
17.修改备库处于应用归档状态（备库会自动应用主库传来的重做日志）
SQL> recover managed standby database disconnect from session;
18.启动备库(用作查询)
SQL> Recover managed standby database cancel;
SQL> Alter database open read only;
19.测试备库是否正常
在主库手动切换日志
alter system switch logfile;
然后在备库查看是否切换了日志
------------------------------------------------------------------------------------------------------------------
可能遇到的错误
ORA-01186/ORA-01122/ORA-01110/ORA-01206
2013年09月11日 ? 综合 ? 共 5745字 ? 字号 小 中 大 ? 评论关闭
author：skate
time：2011/12/14
在前几天检查一套DG库的时候，当read only打开stanby库的时候，在alert.log里发现错误log如下
........
Wed Dec 14 15:45:19 2011
Completed: alter database recover managed standby database cancel
Wed Dec 14 15:46:37 2011
alter database open read only
Wed Dec 14 15:46:38 2011
Errors in file /oracle/app/admin/skatestdby/bdump/skatestdby_dbw0_11326.trc:
ORA-01186: file 201 failed verification tests
ORA-01122: database file 201 failed verification check
ORA-01110: data file 201: '/oracle/oradata/skatestdby/temp01.dbf'
ORA-01206: file is not part of this database - wrong database id
Wed Dec 14 15:46:38 2011
File 201 not verified due to error ORA-01122
Wed Dec 14 15:46:38 2011
SMON: enabling cache recovery
Wed Dec 14 15:46:40 2011
Cannot re-create tempfile /oracle/oradata/skatestdby/temp01.dbf, the same name file exists
Database Characterset is UTF8
Opening with internal Resource Manager plan
where NUMA PG = 1, CPUs = 16
replication_dependency_tracking turned off (no async multimaster replication found)
Physical standby database opened for read only access.
Completed: alter database open read only
......
从log里可以看到，这个数据库的tempfile文件有问题，应该是在创建dg的时候遗留的，虽然这个文件的损坏影响不大，
但是当需要切换DG的时候，会耽误很多时间，所以在这里先修复这个问题。
解决方法：重建tempfile文件
步骤：
查询本库的角色
SQL> select name,log_mode,controlfile_type,open_mode,protection_mode,database_role,force_logging from v$database;
NAME LOG_MODE CONTROLFILE_TYPE OPEN_MODE PROTECTION_MODE DATABASE_ROLE FORCE_LOGGING
--------- ------------ ---------------- ---------- -------------------- ---------------- -------------
SKATEDB ARCHIVELOG STANDBY MOUNTED MAXIMUM PERFORMANCE PHYSICAL STANDBY YES
查看standby库进程状态
SQL> select process, status, thread#, sequence#, block#, blocks
2 from v$managed_standby;
PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS
--------- ------------ ---------- ---------- ---------- ----------
ARCH CLOSING 1 10095 43009 1912
ARCH CLOSING 1 10096 1 149
MRP0 WAIT_FOR_LOG 1 10097 0 0
RFS IDLE 0 0 0 0
RFS IDLE 1 10097 23465 827
查看standby库的recover模式
SQL> select dest_name,archived_thread#,archived_seq#,applied_thread#,
2 applied_seq#,db_unique_name,recovery_mode
3 from v$archive_dest_status
4 where status='VALID';
DEST_NAME ARCHIVED_THREAD# ARCHIVED_SEQ# APPLIED_THREAD# APPLIED_SEQ# DB_UNIQUE_NAME RECOVERY_MODE
-------------------------------------------------------------------------------- ---------------- ------------- --------------- ------------ ------------------------------ -----------------------
LOG_ARCHIVE_DEST_1 1 10096 0 0 NONE MANAGED
LOG_ARCHIVE_DEST_2 0 0 0 0 skatedb MANAGED
STANDBY_ARCHIVE_DEST 1 10095 1 10095 NONE MANAGED
SQL>
取消standby的recover
SQL> alter database recover managed standby database cancel;
Database altered.
以read only 打开数据库
SQL> alter database open read only;
Database altered.
查看数据库状态
SQL> select name,open_mode from v$database;
NAME OPEN_MODE
--------- ----------
SKATEDB READ ONLY
因为要修改standby数据库的数据文件，所以这里要修改参数”standby_file_management=manual“
SQL> show parameter standby
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
standby_archive_dest string ?/dbs/arch
standby_file_management string AUTO
SQL>
SQL>
SQL>
SQL> alter system set standby_file_management=manual scope=memory;
System altered.
创建新的tempfile文件
SQL> alter tablespace temp add tempfile '/oracle/oradata/skatestdby/temp02.dbf' size 20G
2 autoextend on next 500m maxsize 25G;
Tablespace altered.
使已有的tempfile文件 offline，准备删除
SQL> alter database tempfile '/oracle/oradata/skatestdby/temp01.dbf' offline;
Database altered.
删除tempfile文件
SQL> alter database tempfile '/oracle/oradata/skatestdby/temp01.dbf' drop including datafiles;
Database altered.
准备启动standby的recover
SQL> alter database recover managed standby database disconnect using current logfile;
alter database recover managed standby database disconnect using current logfile
*
ERROR at line 1:
ORA-01093: ALTER DATABASE CLOSE only permitted with no sessions connected
SQL> alter database close;
alter database close
*
ERROR at line 1:
ORA-01093: ALTER DATABASE CLOSE only permitted with no sessions connected
因为standby库有连接，所有无法直接close库。
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
启动数据库nomount
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 1.7180E+10 bytes
Fixed Size 2114104 bytes
Variable Size 2214596040 bytes
Database Buffers 1.4948E+10 bytes
Redo Buffers 14659584 bytes
以standby模式mount数据库
SQL> alter database mount standby database;
Database altered.
启动standby的recover
SQL> alter database recover managed standby database disconnect using current logfile;
Database altered.
SQL> alter database recover managed standby database cancel;
Database altered.
再次read only库，看是否还报错
SQL> alter database open read only;
Database altered.
检查alert.log文集，数据库正常启动，没有报错了，其实在这个操作期间，一直在看alertlog文件的变化。
SQL> alter database close;
alter database close
*
ERROR at line 1:
ORA-01093: ALTER DATABASE CLOSE only permitted with no sessions connected
断掉和standby库的连接，直接close standby库
SQL> alter database close;
Database altered.
启动standby的实时recover
SQL> alter database recover managed standby database disconnect using current logfile;
Database altered.
也可以用如下步骤，把实时的recover转化为一般的recover
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> alter database recover managed standby database disconnect;
Database altered.
SQL>
至此ok，当DG切换时，直接切换即可，减少了切换时间
阅读(2061) | 评论(0) | 转发(0) |
