一:physic standby 的维护:
5:取消重做应用
1:启动并挂载 physic standby database
startup mount;
|
2: 开始重做应用或 实时重做应用:
alter database recover managed standby database disconnect from session;
alter database reocver managed standby database using current logfile disconnect from session;
|
a
3: 主数据库查看重做应用模式(普通,实时)例子是普通重做应用
SQL> SELECT RECOVERY_MODE FROM V$ARCHIVE_DEST_STATUS; RECOVERY_MODE ----------------------- IDLE MANAGED MANAGED IDLE IDLE IDLE IDLE IDLE IDLE IDLE |
4:背库上查看重做应用模式,如果MRP0 或MRP 进程存在,则备数据库应用重做
SQL> SELECT PROCESS, STATUS FROM V$MANAGED_STANDBY; PROCESS STATUS --------- ------------ ARCH CLOSING ARCH CLOSING RFS IDLE RFS IDLE RFS IDLE RFS IDLE RFS IDLE RFS IDLE RFS IDLE RFS IDLE RFS IDLE PROCESS STATUS --------- ------------ RFS IDLE RFS IDLE RFS IDLE MRP0 WAIT_FOR_LOG |
alter database recover managed standby database cancel; |
6:评估是否需要打开备库:
只要物理备库一直处于mount 应用状态。如果之前没有只读打开过,那么可以直接open,而不需要重启。
注:
10g物理备库open之后,不能应用日志。
11g 物理备库open 之后,可以应用日志。
|
7:DEPENDENCY 属性实现归档目的地共享
当在同一系统配置超过一个备数据库,
考虑使用LOG_ARCHIVE_DEST_n初始化参数的DEPENDENCY属性来定义一个归档目的地来代表所有目的地接收重做数据,而不是传送重做数据到每个单独目的地
考虑使用LOG_ARCHIVE_DEST_n初始化参数的DEPENDENCY属性来定义一个归档目的地来代表所有目的地接收重做数据,而不是传送重做数据到每个单独目的地
LOG_ARCHIVE_DEST_1='LOCATION=DISK1 MANDATORY' LOG_ARCHIVE_DEST_2='SERVICE=stdby1 OPTIONAL' LOG_ARCHIVE_DEST_3='SERVICE=stdby2 OPTIONAL DEPENDENCY=LOG_ARCHIVE_DEST_2' |
8:打开备库
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; ALTER DATABASE OPEN; |
9:DG 的降级
当备库打开之后,可以运行重做运用命令来实现降级,此时数据库状态由open到 mount
alter database recover managed standby database disconnect from session;
|
10:添加数据文件或表空间
a 如果你在备数据库服务器参数文件(SPFILE)中设置STANDBY_FILE_MANAGEMENT初始化参数为AUTO,则任何在主数据库上创建的新数据文件一样自动创建在备数据库上。 b 如果你没有指定STANDBY_FILE_MANAGEMENT初始化参数,或者你设置为MANUAL,则当你添加数据文件到主数据库时你必须手工拷贝新数据文件到备数据库。 举例:
STANDBY_FILE_MANAGEMENT=auto
主数据库建立表空间
:
SQL> CREATE TABLESPACE MTS3 DATAFILE '/dev/raw/raw101' size 1m;
Tablespace created. SQL> ALTER SYSTEM SWITCH LOGFILE; System altered. Fri Apr 8 10:00:22 2005 Media Recovery Log /u01/MILLER/flash_recovery_area/MTS_STBY/archivelog/2005_04_08/o1_mf_1_8_15ffjrov_.arc File #7 added to control file as 'UNNAMED00007'. Originally created as: '/dev/raw/raw101' Recovery was unable to create the file as: '/dev/raw/raw101' MRP0: Background Media Recovery terminated with error 1274 Fri Apr 8 10:00:22 2005 Errors in file /u01/MILLER/MTS/dump/mts_mrp0_21851.trc: ORA-01274: cannot add datafile '/dev/raw/raw101' - file could not be created ORA-01119: error in creating database file '/dev/raw/raw101' ORA-27041: unable to open file Linux Error: 13: Permission denied Additional information: 1 Some recovered datafiles maybe left media fuzzy Media recovery may continue but open resetlogs may fail Fri Apr 8 10:00:22 2005 Errors in file /u01/MILLER/MTS/dump/mts_mrp0_21851.trc: ORA-01274: cannot add datafile '/dev/raw/raw101' - file could not be created ORA-01119: error in creating database file '/dev/raw/raw101' ORA-27041: unable to open file Linux Error: 13: Permission denied Additional information: 1 Fri Apr 8 10:00:22 2005 MTS; MRP0: Background Media Recovery process shutdown
ARCH: Connecting to console port...
解决办法:
1.在备数据库上创建裸分区,并赋予权限给 Oracle用户。
2 查询V$DATAFILE视图获取相关信息。
SQL> SELECT NAME FROM V$DATAFILE;
NAME -------------------------------------------------------------------------------- /u01/MILLER/MTS/system01.dbf /u01/MILLER/MTS/undotbs01.dbf /u01/MILLER/MTS/sysaux01.dbf /u01/MILLER/MTS/users01.dbf /u01/MILLER/MTS/mts.dbf /dev/raw/raw100 /u01/app/oracle/product/10.1.0/dbs/UNNAMED00007
3:
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=MANUAL;
ALTER DATABASE CREATE DATAFILE '/u01/app/oracle/product/10.1.0/dbs/UNNAMED00007' AS '/dev/raw/raw101';
4.在备数据库上,设置 STANDBY_FILE_MANAGEMENT为 AUTO并重启重做应用: ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO; RECOVER MANAGED STANDBY DATABASE DISCONNECT; |
11:删除数据文件或表空间:
当备库standby_archive_dest=manual 时候 在主库执行删除表空间操作: SQL> drop tablespace testimp including contents and datafiles; Tablespace dropped. SQL> select name from v$datafile; NAME -------------------------------------------------------------------------------- /home/oracle/product/oradata/dg1/system01.dbf /home/oracle/product/oradata/dg1/undotbs01.dbf /home/oracle/product/oradata/dg1/sysaux01.dbf /home/oracle/product/oradata/dg1/users01.dbf /home/oracle/product/oradata/dg1/example01.dbf /home/oracle/product/oradata/dg1/users02.dbf /home/oracle/product/oradata/dg1/test01.dbf /home/oracle/product/oradata/dg1/test_lob01.dbf /home/oracle/product/oradata/dg1/ogg_tab.dbf /tmp/test1.dbf /tmp/test2.dbf NAME -------------------------------------------------------------------------------- /home/oracle/product/oradata/dg1/tbs_test01.dbf 备库查看日志应用信息: Redo Shipping Client Connected as PUBLIC -- Connected User is Valid RFS[6]: Assigned to RFS process 5806 RFS[6]: Identified database type as 'physical standby' Primary database is in MAXIMUM PERFORMANCE mode Primary database is in MAXIMUM PERFORMANCE mode RFS[6]: Successfully opened standby log 4: '/home/oracle/product/oradata/dg2/stdlog1' Thu Mar 15 15:26:55 2012 Redo Shipping Client Connected as PUBLIC -- Connected User is Valid RFS[7]: Assigned to RFS process 5808 RFS[7]: Identified database type as 'physical standby' RFS[7]: Successfully opened standby log 5: '/home/oracle/product/oradata/dg2/stdlog2' Thu Mar 15 15:26:55 2012 Recovery of Online Redo Log: Thread 1 Group 5 Seq 902 Reading mem 0 Mem# 0: /home/oracle/product/oradata/dg2/stdlog2 Recovery deleting file #13:'/tmp/testimp.dbf' from controlfile. Recovery dropped tablespace 'TESTIMP' Thu Mar 15 15:27:05 2012 Media Recovery Waiting for thread 1 sequence 903 (in transit) Thu Mar 15 15:27:05 2012 Recovery of Online Redo Log: Thread 1 Group 4 Seq 903 Reading mem 0 Mem# 0: /home/oracle/product/oradata/dg2/stdlog1 查看备库相关视图: SQL> select name from v$datafile; NAME -------------------------------------------------------------------------------- /home/oracle/product/oradata/dg2/system01.dbf /home/oracle/product/oradata/dg2/undotbs01.dbf /home/oracle/product/oradata/dg2/sysaux01.dbf /home/oracle/product/oradata/dg2/users01.dbf /home/oracle/product/oradata/dg2/example01.dbf /home/oracle/product/oradata/dg2/users02.dbf /home/oracle/product/oradata/dg2/test01.dbf /home/oracle/product/oradata/dg2/test_lob01.dbf /home/oracle/product/oradata/dg2/ogg_tab.dbf /tmp/test1.dbf /tmp/test2.dbf NAME -------------------------------------------------------------------------------- /home/oracle/product/oradata/dg2/tbs_test01.dbf 发现已经同步drop 了该表空间,但是物理上并没有真正删除。 SQL> host [oracle@dg2 ~]$ ls -la /tmp/testimp.dbf -rw-r----- 1 oracle oinstall 20979712 Mar 15 15:26 /tmp/testimp.dbf [oracle@dg2 ~]$ rm /tmp/testimp.dbf [oracle@dg2 ~]$ ls -la /tmp/testimp.dbf ls: /tmp/testimp.dbf: No such file or directory
当备库standby_archive_dest=auto 时候
主数据库删除表空间
SQL> drop tablespace test including contents and datafiles;
Tablespace dropped.
SQL> select name from v$datafile;
NAME -------------------------------------------------------------------------------- /home/oracle/product/oradata/dg1/system01.dbf /home/oracle/product/oradata/dg1/undotbs01.dbf /home/oracle/product/oradata/dg1/sysaux01.dbf /home/oracle/product/oradata/dg1/users01.dbf /home/oracle/product/oradata/dg1/example01.dbf /home/oracle/product/oradata/dg1/users02.dbf /home/oracle/product/oradata/dg1/test01.dbf /home/oracle/product/oradata/dg1/test_lob01.dbf /home/oracle/product/oradata/dg1/ogg_tab.dbf /tmp/test1.dbf /tmp/test2.dbf
备库应用日志如下:
Media Recovery Waiting for thread 1 sequence 904 (in transit) Thu Mar 15 16:01:22 2012 Recovery of Online Redo Log: Thread 1 Group 4 Seq 904 Reading mem 0 Mem# 0: /home/oracle/product/oradata/dg2/stdlog1 Thu Mar 15 16:27:31 2012 ALTER SYSTEM SET standby_file_management='AUTO' SCOPE=BOTH; Thu Mar 15 16:32:03 2012 Recovery deleting file #12:'/home/oracle/product/oradata/dg2/tbs_test01.dbf' from controlfile. Deleted file /home/oracle/product/oradata/dg2/tbs_test01.dbf Recovery dropped tablespace 'TEST' 查看物理是否删除: SQL> host ls -la /home/oracle/product/oradata/dg2/tbs_test01.dbf ls: /home/oracle/product/oradata/dg2/tbs_test01.dbf: No such file or directory 发现自动删除了物理文件。这里体现了auto 和manual的区别 |
12:在主数据库中重命名数据文件
1.要在主数据库中重命名数据文件,将表空间脱机: SQL> ALTER TABLESPACE tbs_4 OFFLINE; 2.从 SQL提示符退出并执行操作系统命令,如下面的 UNIX mv命令,来在主数据库上重命名数据文件: mv /disk1/oracle/oradata/payroll/tbs_4.dbf /disk1/oracle/oradata/payroll/tbs_x.dbf 3.在主数据库中重命名数据文件并将表空间联机: SQL> ALTER TABLESPACE tbs_4 RENAME DATAFILE '/disk1/oracle/oradata/payroll/tbs_4.dbf' TO '/disk1/oracle/oradata/payroll/tbs_x.dbf'; SQL> ALTER TABLESPACE tbs_4 ONLINE; 4.连接到备数据库,查询 V$ARCHIVED_LOG视图来检验所有归档重做日志文件都被应用了,然后停止重做应用: SQL> SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#; SEQUENCE# APP --------- --- 8 YES 9 YES 10 YES 11 YES 4 rows selected. SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; 5.关闭备数据库: SQL> SHUTDOWN; 6.在备站点使用操作系统命令重命名数据文件,如 UNIX mv命令: mv /disk1/oracle/oradata/payroll/tbs_4.dbf /disk1/oracle/oradata/payroll/tbs_x.dbf 7.启动并安挂载(mount)数据库: SQL> STARTUP MOUNT; 8.重命名备控制文件中的数据文件。注意STANDBY_FILE_MANAGEMENT初始化参数必须设置为MANUAL SQL> ALTER DATABASE RENAME FILE '/disk1/oracle/oradata/payroll/tbs_4.dbf' 2> TO '/disk1/oracle/oradata/payroll/tbs_x.dbf'; 9.在备数据库上,重启重做应用: SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION; 如果你没有在备系统上重命名相应的数据文件,然后试图刷新备数据库控制文件,备数据库将会企图使用重命名的数据文件,但是它将无法找到 因此,你将在警告日志中看到类似于如下的错误信息: ORA-00283: recovery session canceled due to errors ORA-01157: cannot identify/lock datafile 4 - see DBWR trace file ORA-01110: datafile 4: '/Disk1/oracle/oradata/payroll/tbs_x.dbf' |
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/713360/viewspace-719158/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/713360/viewspace-719158/