Home > DGCA > physical standby的常用管理

physical standby的常用管理

–记录DGCA上常用的一些physical standby管理。
一、add datafile
一般standby_file_management设为AUTO不会有什么问题,但是如果设为manual了,或者因为备库存储的问题导致文件添加失败,主库加文件,需要备库做如下简单几步:
1.查看备库v$datafile,找出错误的文件A($ORACLE_HOME/dbs/UNNAMED…)。
2.停止recover(这种情况下,一般已经停掉了)。
SQL> RECOVER MANAGED STANDBY DATABASE CANCEL;
3.设置standby_file_management为manual
SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=MANUAL;
4.重新create datafile
SQL> ALTER DATABASE CREATE DATAFILE
2 ‘/u01/app/oracle/product/10.1.0/dbs/UNNAMED00007′
3 AS
4 ‘/u01/app/oracle/oradata/xxoo/TEST02.DBF’;
5. standby_file_management为auto(如果需要)
6. recover

二、drop tablespace
比较简单,不管standby_file_management的设置成什么值,备库的controlfile都会删除相应tbs信息,物理文件是否删除看standby_file_management以及drop tablespace是否带上including contents and datafiles.如果没有物理删除需要人工rm.
三、transport tablespace
主库从其他库transport一些表空间进来的情况,跟单裤transport tbs的情况类似,区别在于拷贝数据文件的时候,除了拷贝到主库,也要拷贝到其他所有备库(注意要是DB_FILE_NAME_CONVERT的相应路径),同时standby_file_management需设为auto(这一点如果设为manual猜测也可以用rename的方法解决,环境跟时间原因没试过),然后剩下的就是主库导入tbs元信息备库应用日志就OK了,这个跟单裤情况一样,只不过备库同样应用了一遍。
四、rename datafile
rename这种操作不会应用到备库(貌似对controlfile的操作不会记录到redo的?以前的知识又忘了,天。。),无视STANDBY_FILE_MANAGEMENT,所以需要人工干预。
DGCA上提供了一个官方方法:

The following steps describe how to rename a datafile in the primary database and manually propagate the changes to the standby database.

To rename the datafile in the primary database, take the tablespace offline:

SQL> ALTER TABLESPACE tbs_4 OFFLINE;
Exit from the SQL prompt and issue an operating system command, such as the following UNIX mv command, to rename the datafile on the primary system:

% mv /disk1/oracle/oradata/payroll/tbs_4.dbf 
/disk1/oracle/oradata/payroll/tbs_x.dbf
Rename the datafile in the primary database and bring the tablespace back online:

SQL> ALTER TABLESPACE tbs_4 RENAME DATAFILE      2> '/disk1/oracle/oradata/payroll/tbs_4.dbf' 
  3>  TO '/disk1/oracle/oradata/payroll/tbs_x.dbf';
SQL> ALTER TABLESPACE tbs_4 ONLINE;
Connect to the standby database, query the V$ARCHIVED_LOG view to verify all of the archived redo log files are applied, and then stop Redo Apply:

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;
Shut down the standby database:

SQL> SHUTDOWN;
Rename the datafile at the standby site using an operating system command, such as the UNIX mv command:

% mv /disk1/oracle/oradata/payroll/tbs_4.dbf /disk1/oracle/oradata/payroll/tbs_x.dbf
Start and mount the standby database:

SQL> STARTUP MOUNT;
Rename the datafile in the standby control file. Note that the STANDBY_FILE_MANAGEMENT initialization parameter must be set to MANUAL.

SQL> ALTER DATABASE RENAME FILE '/disk1/oracle/oradata/payroll/tbs_4.dbf' 
  2> TO '/disk1/oracle/oradata/payroll/tbs_x.dbf';
On the standby database, restart Redo Apply:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE
  2> DISCONNECT FROM SESSION;

自己测了下,发现不用shutdown 备库也是可以的。而且在mount跟open read only的情况下都可以执行rename。

--###mount with rename:
SQL> recover managed standby database cancel;
SQL> select a.name tsn,b.name,b.bytes/1024/1024,b.status from v$tablespace a,v$datafile b where a.ts#=b.ts# order by 1;

TSN        NAME                                                         B.BYTES/1024/1024 STATUS
---------- ------------------------------------------------------------ ----------------- -------
QIFENG     /data/oradata/johnnydb_dg/QIFENG03.DBF                                      50 ONLINE
SQL> alter database datafile '/data/oradata/johnnydb_dg/QIFENG03.DBF' offline for drop;

Database altered.

SQL> select a.name tsn,b.name,b.bytes/1024/1024,b.status from v$tablespace a,v$datafile b where a.ts#=b.ts# order by 1;

TSN        NAME                                                         B.BYTES/1024/1024 STATUS
---------- ------------------------------------------------------------ ----------------- -------
QIFENG     /data/oradata/johnnydb_dg/QIFENG03.DBF                                      50 RECOVER

[oracle@localhost johnnydb_dg]$ mv QIFENG03.DBF QIFENG01.DBF 

SQL> alter tablespace qifeng rename datafile '/data/oradata/johnnydb_dg/QIFENG03.DBF' to '/data/oradata/johnnydb_dg/QIFENG01.DBF';
alter tablespace qifeng rename datafile '/data/oradata/johnnydb_dg/QIFENG03.DBF' to '/data/oradata/johnnydb_dg/QIFENG01.DBF'
*
ERROR at line 1:
ORA-01109: database not open --open的情况才可以alter tablespace


SQL> alter database rename file '/data/oradata/johnnydb_dg/QIFENG03.DBF' to '/data/oradata/johnnydb_dg/QIFENG01.DBF';
alter database rename file '/data/oradata/johnnydb_dg/QIFENG03.DBF' to '/data/oradata/johnnydb_dg/QIFENG01.DBF'
*
ERROR at line 1:
ORA-01511: error in renaming log/data files
ORA-01275: Operation RENAME is not allowed if standby file management is automatic.


SQL> alter system set standby_file_management=manual; --这步必须

System altered.

SQL> alter database rename file '/data/oradata/johnnydb_dg/QIFENG03.DBF' to '/data/oradata/johnnydb_dg/QIFENG01.DBF';

Database altered.

SQL> select a.name tsn,b.name,b.bytes/1024/1024,b.status from v$tablespace a,v$datafile b where a.ts#=b.ts# order by 1;

TSN        NAME                                                         B.BYTES/1024/1024 STATUS
---------- ------------------------------------------------------------ ----------------- -------
QIFENG     /data/oradata/johnnydb_dg/QIFENG01.DBF                                      50 RECOVER

SQL> alter database datafile '/data/oradata/johnnydb_dg/QIFENG01.DBF' online;

Database altered.

SQL> recover managed standby database disconnect;
Media recovery complete.
SQL> recover managed standby database cancel;
Media recovery complete.
SQL> alter database open;

Database altered.

SQL> select * from t; --数据正常的

        ID NAME
---------- ------------------------------------------------------------
         1 xxoo


--###open with rename:
SQL> alter tablespace qifeng offline;
alter tablespace qifeng offline
                        *
ERROR at line 1:
ORA-16000: database open for read-only access

SQL> alter database datafile '/data/oradata/johnnydb_dg/QIFENG01.DBF' offline drop; --因为是归档模式,所以放心offline

Database altered.

SQL> select a.name tsn,b.name,b.bytes/1024/1024,b.status from v$tablespace a,v$datafile b where a.ts#=b.ts# order by 1;

TSN        NAME                                                         B.BYTES/1024/1024 STATUS
---------- ------------------------------------------------------------ ----------------- -------
QIFENG     /data/oradata/johnnydb_dg/QIFENG01.DBF                                      50 RECOVER

[oracle@localhost johnnydb_dg]$ mv QIFENG01.DBF QIFENG02.DBF 

SQL> alter database rename file '/data/oradata/johnnydb_dg/QIFENG01.DBF' to '/data/oradata/johnnydb_dg/QIFENG02.DBF';                     

Database altered.

SQL> alter database datafile '/data/oradata/johnnydb_dg/QIFENG02.DBF' online;
alter database datafile '/data/oradata/johnnydb_dg/QIFENG02.DBF' online
*
ERROR at line 1:
ORA-01113: file 5 needs media recovery --提示需要recover
ORA-01110: data file 5: '/data/oradata/johnnydb_dg/QIFENG02.DBF'


SQL> recover datafile '/data/oradata/johnnydb_dg/QIFENG02.DBF'; --。。。
ORA-00283: recovery session canceled due to errors 
ORA-01666: control file is for a standby database


SQL> select a.name tsn,b.file#,b.name,b.bytes/1024/1024,b.status from v$tablespace a,v$datafile b where a.ts#=b.ts# order by 1;

TSN             FILE# NAME                                                         B.BYTES/1024/1024 STATUS
---------- ---------- ------------------------------------------------------------ ----------------- -------
QIFENG              5 /data/oradata/johnnydb_dg/QIFENG02.DBF                                      50 RECOVER

SQL> alter database close;

Database altered.

SQL> alter database datafile 5 online;

Database altered.

SQL> select a.name tsn,b.file#,b.name,b.bytes/1024/1024,b.status from v$tablespace a,v$datafile b where a.ts#=b.ts# order by 1;

TSN             FILE# NAME                                                         B.BYTES/1024/1024 STATUS
---------- ---------- ------------------------------------------------------------ ----------------- -------
QIFENG              5 /data/oradata/johnnydb_dg/QIFENG02.DBF                                      50 RECOVER

SQL> recover datafile 5; --。。。
ORA-00283: recovery session canceled due to errors
ORA-01666: control file is for a standby database

SQL> recover managed standby database disconnect; --这边应用了一个归档
Media recovery complete.
SQL> select a.name tsn,b.file#,b.name,b.bytes/1024/1024,b.status from v$tablespace a,v$datafile b where a.ts#=b.ts# order by 1;

TSN             FILE# NAME                                                         B.BYTES/1024/1024 STATUS
---------- ---------- ------------------------------------------------------------ ----------------- -------
QIFENG              5 /data/oradata/johnnydb_dg/QIFENG02.DBF                                      50 ONLINE

SQL> recover managed standby database cancel;
Media recovery complete.
SQL> alter database open;

Database altered.

SQL> select * from t; --数据正常

        ID NAME
---------- ------------------------------------------------------------
         1 xxoo
         2 2b

五、Adding or Dropping Online Redo Log Files

Consequently, when you add or drop an online redo log file at the primary site, it is important that you synchronize the changes in the standby database by following these steps:

1. If Redo Apply is running, you must cancel Redo Apply before you can change the log files.

2. If the STANDBY_FILE_MANAGEMENT initialization parameter is set to AUTO, change the value to MANUAL.

3. Add or drop an online redo log file:

		To add an online redo log file, use a SQL statement such as this:

		SQL> ALTER DATABASE ADD LOGFILE '/disk1/oracle/oradata/payroll/prmy3.log' SIZE 100M;
		To drop an online redo log file, use a SQL statement such as this:

		SQL> ALTER DATABASE DROP LOGFILE '/disk1/oracle/oradata/payroll/prmy3.log';
4. Repeat the statement you used in Step 3 on each standby database.

5. Restore the STANDBY_FILE_MANAGEMENT initialization parameter and the Redo Apply options to their original states.
Categories: DGCA Tags:
  1. No comments yet.
  1. No trackbacks yet.