dg配置及维护命令

dg 配置：
standby log :
alter database add standby logfile group 4('/u01/app/oracle/oradata/r5/std04.log') size 50M;
alter database add standby logfile group 5('/u01/app/oracle/oradata/r5/std05.log') size 50M;
alter database add standby logfile group 6('/u01/app/oracle/oradata/r5/std06.log') size 50M;
alter database add standby logfile group 7('/u01/app/oracle/oradata/r5/std07.log') size 50M;

pfile :
primary :
*.DB_UNIQUE_NAME='PRIMARY'
*.FAL_CLIENT='PRIMARY'
*.FAL_SERVER='STANDBY'
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(PRIMARY,STANDBY)'
*.LOG_ARCHIVE_DEST_1='LOCATION=/u01/archivelog  VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=PRIMARY'
*.LOG_ARCHIVE_DEST_2='SERVICE=STANDBY LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=STANDBY'
*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'
*.LOG_ARCHIVE_DEST_STATE_2='DEFER'
*.STANDBY_FILE_MANAGEMENT='AUTO'
*.DB_FILE_NAME_CONVERT='PRIMARY','STANDBY'
*.LOG_FILE_NAME_CONVERT='PRIMARY','STANDBY'
STANDBY_FILE_MANAGEMENT=AUTO

standby:
*.DB_UNIQUE_NAME='STANDBY'
*.FAL_CLIENT='STANDBY'
*.FAL_SERVER='PRIMARY'
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(PRIMARY,STANDBY)'
*.LOG_ARCHIVE_DEST_1='LOCATION=/u01/archivelog  VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=STANDBY'
*.LOG_ARCHIVE_DEST_2='SERVICE=PRIMARY LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=PRIMARY'
*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'
*.LOG_ARCHIVE_DEST_STATE_2='ENABLE'
*.STANDBY_FILE_MANAGEMENT='AUTO'
*.DB_FILE_NAME_CONVERT='PRIMARY','STANDBY'
*.LOG_FILE_NAME_CONVERT='PRIMARY','STANDBY'
STANDBY_FILE_MANAGEMENT=AUTO

密码：
orapwd file=orapwr5 password=admin entries=10

backup current controlfile for standby format='/u01/backup/control_%U';

pfile :

rman target /  auxiliary  sys/admin@STANDBY CMDFILE=/home/oracle/dg.rman LOG=DG.LOG
----------------------------------------------------dg.rman-------------------------
duplicate target database for standby dorecover from active database  nofilenamecheck
spfile set db_unique_name='standby'
set LOG_ARCHIVE_DEST_1='LOCATION=/u01/archivelog'
set LOG_ARCHIVE_DEST_2='SERVICE=PRIMARY LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=PRIMARY'
set fal_server='primary'
set fal_client='standby'
set STANDBY_FILE_MANAGEMENT='AUTO'
set control_files='/u01/app/oracle/oradata/r5/control01.ctl','/u01/app/oracle/flash_recovery_area/r5/control02.ctl'
set log_archive_dest_state_2='ENABLE' ;

lisener.ora & tnsname.ora
lisener.ora： （需静态监听）：
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = r5)
      (ORACLE_HOME = /u01/app/oracle/product/11g )
      (SID_NAME = r5)
    )
  )
 
tnsname.ora ：
NODE1 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = node1)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = node1)
    )
  )

primary =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = node1)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = r5)
    )
  )

standby =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = node2)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = r5)
    )
  )


开启：
主库：SQL>alter system set log_archive_dest_state_2=enable;
备库：SQL>alter database open;
SQL>alter database recover managed standby database using current logfile disconnect from session;

a. 主库建立表格或添加数据后，提交数据库，然后再备库查看。
b. 主库：SQL>select max(sequence#) from v$archived_Log;
备库：SQL>select max(sequence#) from v$archived_Log;
SQL>select max(sequence#) from v$Log_history;
对比主备库得到的数字是否一样。



主备切换：
primary :
select database_role,switchover_status from v$database;
select database_role,switchover_status from v$database;
standby:
select switchover_status,database_role from v$database;
alter database commit to switchover to primary;
alter database open;
primary :
shutdown immediate
startup mount
select switchover_status,database_role from v$database;
alter database recover managed standby database disconnect from session;
standby :
alter system switch logfile ;
select switchover_status,database_role from v$database;

 
/* 注意：
1. 主库在正常开机是开启，备库需用pfile文件打开，startup nomount pfile=?/dbs/initr5.oras
2. 如果路径一样 ，回报错。提示是否要覆盖
RMAN-05001: auxiliary file name /data/bill01.dbf conflicts with a file used by the target database
RMAN-05001: auxiliary file name /data/bill.dbf conflicts with a file used by the target database
添加nofilenamecheck参数。可取消提示
duplicate target database for standby dorecover from active database  nofilenamecheck
3. lisenter.ora 的sid_name 与 tnsname.ora 中的 service_name 一致


RUN {
allocate channel c1 type disk;
allocate channel c2 type disk;
sql 'alter system archive log current';
BACKUP FORMAT 'E:\std\orcl_%U_%T' skip inaccessible filesperset 5 DATABASE ;
sql 'alter system archive log current';
BACKUP FORMAT 'E:\std\arch_%U_%T' skip inaccessible filesperset 5 ARCHIVELOG ALL DELETE INPUT;
backup current controlfile for standby format='E:\std\control_%U';
release channel c2;
release channel c1;
}



-------------------------------------------------------------------------------------
dg 配置：
创建空实例
oradim –new –sid lmis
oradim -NEW -SID lmis


standby log :
alter database add standby logfile group 4('D:\LMIS\LMIS\std04.log') size 50M;
alter database add standby logfile group 5('D:\LMIS\LMIS\std05.log') size 50M;
alter database add standby logfile group 6('D:\LMIS\LMIS\std06.log') size 50M;
alter database add standby logfile group 7('D:\LMIS\LMIS\std07.log') size 50M;

primary:
 alter database force logging;
 alter system set db_unique_name='primary' scope=spfile;   
 alter system set log_archive_config='DG_CONFIG=(primary,standby)'  scope=spfile;  
 alter system set log_archive_dest_1='LOCATION=E:\archivelog valid_for=(ALL_LOGFILES,ALL_ROLES) db_unique_name=primary' scope=spfile;    
 alter system set log_archive_dest_2='SERVICE=STANDBY LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=STANDBY' scope=spfile;;  
 alter system set fal_client='primary'  scope=spfile;   
 alter system set fal_server='standby'  scope=spfile ;  
 alter system set DB_FILE_NAME_CONVERT='PRIMARY','STANDBY'  scope=spfile;   
 alter system set LOG_FILE_NAME_CONVERT='PRIMARY','STANDBY'  scope=spfile;
 alter system set standby_file_management=auto  scope=spfile;   
   


*.DB_FILE_NAME_CONVERT='PRIMARY','STANDBY'
*.LOG_FILE_NAME_CONVERT='PRIMARY','STANDBY'



pfile :
primary :
*.DB_UNIQUE_NAME='PRIMARY'
*.FAL_CLIENT='PRIMARY'
*.FAL_SERVER='STANDBY'
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(PRIMARY,STANDBY)'
*.LOG_ARCHIVE_DEST_1='LOCATION=/u01/archivelog  VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=PRIMARY'
*.LOG_ARCHIVE_DEST_2='SERVICE=STANDBY LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=STANDBY'
*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'
*.LOG_ARCHIVE_DEST_STATE_2='DEFER'
*.STANDBY_FILE_MANAGEMENT='AUTO'
*.DB_FILE_NAME_CONVERT='PRIMARY','STANDBY'
*.LOG_FILE_NAME_CONVERT='PRIMARY','STANDBY'
STANDBY_FILE_MANAGEMENT=AUTO

standby:
*.DB_UNIQUE_NAME='STANDBY'
*.FAL_CLIENT='STANDBY'
*.FAL_SERVER='PRIMARY'
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(PRIMARY,STANDBY)'
*.LOG_ARCHIVE_DEST_1='LOCATION=/u01/archivelog  VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=STANDBY'
*.LOG_ARCHIVE_DEST_2='SERVICE=PRIMARY LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=PRIMARY'
*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'
*.LOG_ARCHIVE_DEST_STATE_2='ENABLE'
*.STANDBY_FILE_MANAGEMENT='AUTO'
*.DB_FILE_NAME_CONVERT='PRIMARY','STANDBY'
*.LOG_FILE_NAME_CONVERT='PRIMARY','STANDBY'
STANDBY_FILE_MANAGEMENT=AUTO

密码：
orapwd file=orapwr5 password=admin entries=10

pfile :

rman target /  auxiliary  sys/admin@STANDBY CMDFILE=rman.txt LOG=DG.LOG

rman target /  auxiliary  sys/admin@STANDBY CMDFILE=/home/oracle/dg.rman LOG=DG.LOG
----------------------------------------------------dg.rman-------------------------
duplicate target database for standby dorecover from active database  nofilenamecheck
spfile set db_unique_name='standby'
set LOG_ARCHIVE_DEST_1='LOCATION=E:\archivelog'
set LOG_ARCHIVE_DEST_2='SERVICE=PRIMARY LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=PRIMARY'
set fal_server='primary'
set fal_client='standby'
set STANDBY_FILE_MANAGEMENT=AUTO
set control_files='D:\LMIS\LMIS\CONTROL01.ctl','C:\ORACLE11\flash_recovery_area\LMIS\control02.ctl'
set log_archive_dest_state_2='ENABLE' ;

lisener.ora & tnsname.ora
lisener.ora： （需静态监听）：
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = r5)
      (ORACLE_HOME = /u01/app/oracle/product/11g )
      (SID_NAME = r5)
    )
  )
 
tnsname.ora ：
NODE1 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = node1)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = node1)
    )
  )

primary =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = node1)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = r5)
    )
  )

standby =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = node2)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = r5)
    )
  )


开启：
主库：SQL>alter system set log_archive_dest_state_2=enable;
备库：SQL>alter database open;
SQL>alter database recover managed standby database using current logfile disconnect from session;

a. 主库建立表格或添加数据后，提交数据库，然后再备库查看。
b. 主库：SQL>select max(sequence#) from v$archived_Log;
备库：SQL>select max(sequence#) from v$archived_Log;
SQL>select max(sequence#) from v$Log_history;
对比主备库得到的数字是否一样。



主备切换：
primary :
select database_role,switchover_status from v$database;
select database_role,switchover_status from v$database;
standby:
select switchover_status,database_role from v$database;
alter database commit to switchover to primary;
alter database open;
primary :
shutdown immediate
startup mount
select switchover_status,database_role from v$database;
alter database recover managed standby database disconnect from session;
standby :
alter system switch logfile ;
select switchover_status,database_role from v$database;

 
/* 注意：
1. 主库在正常开机是开启，备库需用pfile文件打开，startup nomount pfile=?/dbs/initr5.ora
2. 如果路径一样 ，回报错。提示是否要覆盖
RMAN-05001: auxiliary file name /data/bill01.dbf conflicts with a file used by the target database
RMAN-05001: auxiliary file name /data/bill.dbf conflicts with a file used by the target database
添加nofilenamecheck参数。可取消提示
duplicate target database for standby dorecover from active database  nofilenamecheck
3. lisenter.ora 的sid_name 与 tnsname.ora 中的 service_name 一致








-------更改重做日志大小－－－－－－－－－－－－－－－－－－－－－－－
-------备库：
alter database recover managed standby database cancel; #取消APPLIED
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT='MANUAL';

alter database drop logfile group 4;
alter database drop logfile group 5;
alter database drop logfile group 6;
alter database drop logfile group 7;
alter database add standby logfile group 4 ('E:\LMIS\LOG\REDO04.LOG','E:\LMIS\LOG\REDO041.LOG') size 500M reuse;
alter database add standby logfile group 5 ('E:\LMIS\LOG\REDO05.LOG','E:\LMIS\LOG\REDO051.LOG') size 500M reuse;
alter database add standby logfile group 6 ('E:\LMIS\LOG\REDO06.LOG','E:\LMIS\LOG\REDO061.LOG') size 500M reuse;
alter database add standby logfile group 7 ('E:\LMIS\LOG\REDO07.LOG','E:\LMIS\LOG\REDO071.LOG') size 500M reuse;

ALTER DATABASE CLEAR LOGFILE GROUP 2;
报ORA-19527
alter system set LOG_FILE_NAME_CONVERT='E:\LMIS\LOG', 'E:\LMIS\LOG' scope=spfile ;
重启数据库

ALTER DATABASE CLEAR LOGFILE GROUP 1;
alter database drop logfile group 1;
alter database add logfile group 1 ('E:\LMIS\LOG\REDO01.LOG','E:\LMIS\LOG\REDO011.LOG') size 500M  reuse;
ALTER DATABASE CLEAR LOGFILE GROUP 2;
alter database drop logfile group 2;
alter database add logfile group 2 ('E:\LMIS\LOG\REDO02.LOG','E:\LMIS\LOG\REDO021.LOG') size 500M  reuse;
-- alter database add logfile member 'E:\LMIS\LOG\REDO021.LOG'  to group 2 ;

报ora-01623 等待（或者重启备库　开启　恢复　）日志切换状态到，不为current 就可以正常操作　　
ALTER DATABASE CLEAR LOGFILE GROUP 3;
alter database drop logfile group 3;
alter database add logfile group 3 ('E:\LMIS\LOG\REDO03.LOG','E:\LMIS\LOG\REDO031.LOG') size 500M  reuse;

alter database recover managed standby database using current logfile disconnect from session;  


 alter system set standby_file_management=auto  ;   -- 重启备库使其生效
 
 select process,status,sequence# from v$managed_standby ;
 select gap_status ,archived_seq#,applied_seq# from v$archive_dest_status where rownum < 4 ;
 
 
  select * from (select  SEQUENCE#,ARCHIVED,APPLIED from v$archived_log  order by SEQUENCE# desc  )where rownum < 20
 
 col member format a40
 select group#,member,type from v$logfile  order by group# ;
 select group#,bytes/1024/1024 m ,status from v$log order by group# ;
 select group#,bytes/1024/1024 m ,status from v$standby_log  order by group# ；
 
 alter system switch logfile ;
 

----------主库
alter database drop logfile group 4;
alter database drop logfile group 5;
alter database drop logfile group 6;
alter database drop logfile group 7;
alter database add standby logfile group 4 ('E:\LMIS\LOG\REDO04.LOG','E:\LMIS\LOG\REDO041.LOG') size 500M reuse;
alter database add standby logfile group 5 ('E:\LMIS\LOG\REDO05.LOG','E:\LMIS\LOG\REDO051.LOG') size 500M reuse;
alter database add standby logfile group 6 ('E:\LMIS\LOG\REDO06.LOG','E:\LMIS\LOG\REDO061.LOG') size 500M reuse;
alter database add standby logfile group 7 ('E:\LMIS\LOG\REDO07.LOG','E:\LMIS\LOG\REDO071.LOG') size 500M reuse;


alter system  checkpoint ;
alter database drop logfile group 1;
alter database add logfile group 1 ('E:\LMIS\LOG\REDO01.LOG','E:\LMIS\LOG\REDO011.LOG') size 500M  reuse;
alter database drop logfile group 2;
alter database add logfile group 2 ('E:\LMIS\LOG\REDO02.LOG','E:\LMIS\LOG\REDO021.LOG') size 500M  reuse;
-- alter database add logfile member 'E:\LMIS\LOG\REDO021.LOG'  to group 2 ;
alter database drop logfile group 3;
alter database add logfile group 3 ('E:\LMIS\LOG\REDO03.LOG','E:\LMIS\LOG\REDO031.LOG') size 500M  reuse;







alter database mount standby database;


alter database open read only ;


alter database recover managed standby database cancel;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;
alter database recover managed standby database using current logfile disconnect from session;
 select process, status, thread#,sequence#, block#, blocks from v$managed_standby;
 
 alter database create standby controlfile as 'F:\ctl.ctl';

 
 
 
 一般技术方案模板：
 1.现状
 2.存在问题
 3.改进建议
 4.具体改进实施步骤
 5.验证步骤
 6.回退步骤
 
 检查项目：
 1.数据库基本情况检查
 2.基于awr报告的性能检查
 3.数据库相关参数检查
 4.问题sql检查
 5.系统memory,io,cpu检查
 
 
1.主库LOCAL REDO LOG:3组，2个成员，每个成员500M                                                                                                                             
                                                                                                                                                                            
2.备库先停MRP:alter database recover managed standby database cancel;                                                                                                       
  调整 STANDBY REDO LOG:4组，每个成员500M                                                                                                                                   
  然后启动MRP:alter database recover managed standby database using current logfile disconnect from session;                                                                
                                                                                                                                                                            
3.主库调整DG模式                                                                                                                                                            
alter system set log_archive_dest_2='SERVICE=LMISSTD LGWR SYNC AFFIRM NET_TIMEOUT=90 COMPRESSION=enable VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=LMISSTD';   
alter database set standby database to maximize availability;                  



alter system set log_archive_dest_2='SERVICE=STANDBY LGWR SYNC AFFIRM NET_TIMEOUT=90 COMPRESSION=enable VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=STANDBY';   
             
 
 
 alter database drop logfile group 4;
alter database drop logfile group 5;
alter database drop logfile group 6;
alter database drop logfile group 7;
alter database add standby logfile group 4 ('E:\LMIS\LOG\REDO04.LOG','E:\LMIS\LOG\REDO041.LOG') size 500M reuse;
alter database add standby logfile group 5 ('E:\LMIS\LOG\REDO05.LOG','E:\LMIS\LOG\REDO051.LOG') size 500M reuse;
alter database add standby logfile group 6 ('E:\LMIS\LOG\REDO06.LOG','E:\LMIS\LOG\REDO061.LOG') size 500M reuse;
alter database add standby logfile group 7 ('E:\LMIS\LOG\REDO07.LOG','E:\LMIS\LOG\REDO071.LOG') size 500M reuse;
 
  alter database drop standby logfile group 4;
  alter database drop standby logfile group 5;
  alter database drop standby logfile group 6;
  alter database drop standby logfile group 7;

alter database add standby logfile group 4 ('E:\lmisdate\logfile\STDREDO01.LOG') size 500M reuse;
alter database add standby logfile group 5 ('E:\lmisdate\logfile\STDREDO02.LOG') size 500M reuse;
alter database add standby logfile group 6 ('E:\lmisdate\logfile\STDREDO03.LOG') size 500M reuse;
alter database add standby logfile group 7 ('E:\lmisdate\logfile\STDREDO04.LOG') size 500M reuse;


ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT='AUTO';


STDREDO01.LOG
 
 E:\lmisdate\logfile
 
 
 
 ALTER DATABASE REGISTER LOGFILE 'E:\archivelog\ARC0000000554_0893192950.0001';

 
 Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT
Recovery Slave PR00 previously exited with exception 1111
Errors in file d:\app\administrator\diag\rdbms\standby\lmis\trace\lmis_mrp0_19448.trc:
ORA-01111: 数据文件 12 名称未知 - 请重命名以更正文件
ORA-01110: 数据文件 12: 'D:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\UNNAMED00012'
ORA-01157: 无法标识/锁定数据文件 12 - 请参阅 DBWR 跟踪文件
ORA-01111: 数据文件 12 名称未知 - 请重命名以更正文件
ORA-01110: 数据文件 12: 'D:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\UNNAMED00012'




alter database datafile 'D:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\UNNAMED00012'  offline drop;