参数测试ADG

• 参试test
  1. log_archive_dest_state_2

SQL> alter system set log_archive_dest_state_2=defer;   System altered.   SQL> select process,status from v$managed_standby;   PROCESS   STATUS --------- ------------ ARCH       CLOSING ARCH       CLOSING ARCH       CONNECTED ARCH       CLOSING LGWR      CLOSING   SQL> SQL> archive log list Database log mode        Archive Mode Automatic archival        Enabled Archive destination        /u01/arch Oldest online log sequence     43 Next log sequence to archive   45 Current log sequence           45 SQL> select process,status from v$managed_standby;   PROCESS   STATUS --------- ------------ ARCH       CLOSING ARCH       CLOSING ARCH       CONNECTED ARCH       CLOSING LGWR      CLOSING   SQL> alter system set log_archive_dest_state_2=enable;   System altered.   SQL> archive log list Database log mode        Archive Mode Automatic archival        Enabled Archive destination        /u01/arch Oldest online log sequence     44 Next log sequence to archive   46 Current log sequence           46 SQL>

 

1. 1. Standby_file_management
      1. 创建数据文件

alter database create datafiile 1 as ‘’;
 

SQL> host rm -rf /u01/app/oracle/oradata/pri/dg001.dbf SQL> alter database create datafile '/u01/app/oracle/oradata/pri/dg001.dbf'; alter database create datafile '/u01/app/oracle/oradata/pri/dg001.dbf' * ERROR at line 1: ORA-01275: Operation CREATE DATAFILE is not allowed if standby file management is automatic. SQL>  recover managed standby database using current logfile disconnect from session;  Media recovery complete.   SQL> alter system set standby_file_management=manual;   System altered.   SQL>  alter database create datafile '/u01/app/oracle/oradata/pri/dg001.dbf';   Database altered. SQL>  recover managed standby database using current logfile disconnect from session;  Media recovery complete. SQL> recover managed standby database cancel;  SQL> recover managed standby database cancel;  Media recovery complete. SQL> alter database open read only;   Database altered. SQL> select status  from dba_data_files where tablespace_name='DG01';   STATUS --------- AVAILABLE

 

1. 1. 1. 添加数据文件

1 当设置为auto时，可以正确传播

###############主库 SQL> create tablespace dg01 datafile '/u01/app/oracle/oradata/std/dg001.dbf' size 20M;   Tablespace created.   SQL> alter tablespace dg01 add datafile '/u01/app/oracle/oradata/std/dg002.dbf' size 20M;   Tablespace altered.   ####################备库 SQL> select tablespace_name from dba_tablespaces;   TABLESPACE_NAME ------------------------------ SYSTEM SYSAUX UNDOTBS1 TEMP USERS EXAMPLE DG01   7 rows selected. SQL> set lines 120 SQL> col tablespace_name for a20 SQL> col file_name for a60 SQL> /   TABLESPACE_NAME      FILE_NAME -------------------- ------------------------------------------------------------ DG01                 /u01/app/oracle/oradata/pri/dg001.dbf DG01                 /u01/app/oracle/oradata/pri/dg002.dbf   SQL>

 

1. 1. 1. 删除数据文件

1 当设置为auto时，可以正确传播 ###############主库 SQL> alter tablespace dg01 drop datafile '/u01/app/oracle/oradata/std/dg002.dbf';   Tablespace altered. ####################备库 SQL> /   TABLESPACE_NAME      FILE_NAME -------------------- ------------------------------------------------------------ DG01                 /u01/app/oracle/oradata/pri/dg001.dbf   SQL>

 

1. 1. 1. 重命名数据文件

rename数据文件

在primary数据库端执行数据文件重命名操作： 
如果primary数据库重命名了一个或多个数据文件，该项修改并不会自动传播到standby数据库。 就算设置了初始化参数STANDBY_FILE_MANAGEMENT等于AUTO也不行，要让standby的数据文件与primary保持一致，只能手工操作。
1、alter tablespace/datafile offline;
2、使用操作系统命令来拷贝
3、alter databaser rename datafile '....' to '...' 
4、alter tablespace/datafile online;
5、确认STANDBY DB中所有归档已经apply
6、alter database recover managed standby database cancel停止redo apply 
7、拷贝并重命名数据文件 
8、alter database rename file '...' to '....';
9、alter databas recover managed standby database disconnect from session. 

7 alter database create databfile as

alter database create datafile '/u03/oradata/tbs_02.dbf' as '/u03/tbs_02.dbf';

1. 1. 1. resize数据文件

1 当设置为auto时，可以正确传播

SQL> alter database datafile '/u01/app/oracle/oradata/std/dg001.dbf' resize 200M;   Database altered. SQL> select bytes/1024/1024 from dba_Data_files;   BYTES/1024/1024 ---------------               5             100             540             750          346.25             200   6 rows selected.   SQL>

 

 

1. 1. 1. 添加redo文件

 

主库操作 SQL> alter database add logfile group 8 '/u01/app/oracle/oradata/std/redo08' size 50M;   Database altered. 查看备库 SQL> select member from v$logfile;   MEMBER -------------------------------------------------------------------------------- /u01/app/oracle/oradata/pri/redo03.log /u01/app/oracle/oradata/pri/redo02.log /u01/app/oracle/oradata/pri/redo01.log /u01/app/oracle/oradata/pri/styredo04.log /u01/app/oracle/oradata/pri/styredo05.log /u01/app/oracle/oradata/pri/styredo06.log /u01/app/oracle/oradata/pri/styredo07.log   7 rows selected. alter database add logfile group 8 '/u01/app/oracle/oradata/pri/redo08' size 50M;   SQL> alter database add logfile group 9 '/u01/app/oracle/oradata/std/redo09' size 50M; alter database add logfile group 9 '/u01/app/oracle/oradata/std/redo09' size 50M * ERROR at line 1: ORA-01275: Operation ADD LOGFILE is not allowed if standby file management is automatic. SQL> alter system set standby_file_management=manual;   System altered.   SQL> alter database add logfile group 9 '/u01/app/oracle/oradata/pri/redo09' size 50M;   Database altered.   SQL> alter system set standby_file_management=auto;   System altered.   SQL>

 

 

1. 1. 1. 删除redo文件

1 set standby_file_management manual

2 主库add drop logfile

3 备库 add drop logfile

4 set standby_file_management auto

主库操作如下： SQL> alter database drop logfile group 8;   Database altered.   SQL> alter database drop logfile group 9;   Database altered.   SQL> 查看备库： SQL> select member from v$Logfile;   MEMBER -------------------------------------------------------------------------------- /u01/app/oracle/oradata/pri/redo03.log /u01/app/oracle/oradata/pri/redo02.log /u01/app/oracle/oradata/pri/redo01.log /u01/app/oracle/oradata/pri/styredo04.log /u01/app/oracle/oradata/pri/styredo05.log /u01/app/oracle/oradata/pri/styredo06.log /u01/app/oracle/oradata/pri/styredo07.log /u01/app/oracle/oradata/pri/redo08 /u01/app/oracle/oradata/pri/redo09   9 rows selected. SQL> alter database drop logfile group 8; alter database drop logfile group 8 * ERROR at line 1: ORA-01275: Operation DROP LOGFILE is not allowed if standby file management is automatic. SQL> alter system set standby_file_management=manual;   System altered.   SQL> alter database drop logfile group 8;   Database altered.   SQL> alter database drop logfile group 9;   Database altered.   SQL> alter system set standby_file_management=auto;   System altered.   SQL> SQL> select open_mode from v$database;   OPEN_MODE -------------------- READ ONLY   SQL>  recover managed standby database using current logfile disconnect from session;  Media recovery complete. SQL> select process,status from v$managed_standby;   PROCESS   STATUS --------- ------------ ARCH      CLOSING ARCH      CLOSING ARCH      CONNECTED ARCH      CLOSING RFS       IDLE RFS       IDLE RFS       IDLE MRP0      APPLYING_LOG   8 rows selected.   SQL> select open_mode from v$database;   OPEN_MODE -------------------- READ ONLY WITH APPLY   SQL> SQL>

 

 

1. 1. 1. 添加srl文件

standby数据库可以直接添加srl文件，和参数无关。

primary数据库

---主库操作如下，但是不传播 SQL> alter database add standby logfile group 10 '/u01/app/oracle/oradata/std/styredo010.log' size 50M;   Database altered.   ---备库可以直接操作，不需要修改参数 SQL> alter database add standby logfile group 10 '/u01/app/oracle/oradata/pri/styredo010.log' size 50M;   Database altered.

 

1. 1. 1. 删除srl文件

standby数据库可以直接添加srl文件，和参数无关。

---主库操作如下，但是不传播 SQL> alter database drop standby logfile group 10;   Database altered. ---备库可以直接操作，不需要修改参数 SQL> select member from v$Logfile;   MEMBER -------------------------------------------------------------------------------- /u01/app/oracle/oradata/pri/redo03.log /u01/app/oracle/oradata/pri/redo02.log /u01/app/oracle/oradata/pri/redo01.log /u01/app/oracle/oradata/pri/styredo04.log /u01/app/oracle/oradata/pri/styredo05.log /u01/app/oracle/oradata/pri/styredo06.log /u01/app/oracle/oradata/pri/styredo07.log /u01/app/oracle/oradata/pri/redo08 /u01/app/oracle/oradata/pri/redo09 /u01/app/oracle/oradata/pri/styredo010.log   10 rows selected.   SQL> SQL> alter database drop standby logfile group 10;   Database altered.