Oracle10g DG快速配置

DB version:oracle10.2.0.5


primary 环境
IP:192.168.1.103
hostname:dgprimany
db_name:primany
db_unique_name:unipri
global_database_name:primany
SID:primany
service_name:primany
instance_name:primany


standby 环境
IP:192.168.1.104
hostname:dgstandby
db_name:standby
db_unique_name:unistan
global_database_name:standby
SID:standby
service_name:standby
instance_name:standby




primany操作sqlplus


startup mount
alter database force logging;
alter database archivelog;
alter database open;
alter system set log_archive_dest_1='location=/data/arch';


alter database create standby controlfile as '/u01/app/oracle/standby.ctl';
create pfile='/u01/app/oracle/init.ora' from spfile;


!scp $ORACLE_BASE/*.ora $ORACLE_BASE/*.ctl $ORACLE_HOME/dbs/orapw* oracle@dgstandby:$ORACLE_BASE


standby操作
mv $ORACLE_BASE/orapw*  $ORACLE_HOME/dbs/orapwstandby
mv $ORACLE_BASE/init.ora $ORACLE_HOME/dbs/initstandby.ora


mkdir -p $ORACLE_BASE/admin/standby/{a,b,c,u}dump
mkdir -p /data/standby


修改standby pfile
cat >>$ORACLE_HOME/dbs/initstandby.ora<<EOF


*.audit_file_dest='/u01/app/oracle/admin/standby/adump'
*.background_dump_dest='/u01/app/oracle/admin/standby/bdump'
*.compatible='10.2.0.5.0'
*.control_files='/u01/app/oracle/standby.ctl''
*.core_dump_dest='/u01/app/oracle/admin/standby/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='primany'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=primanyXDB)'
*.job_queue_processes=10
*.log_archive_dest_1='location=/data/arch'
*.open_cursors=300
*.pga_aggregate_target=199229440
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=597688320
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/u01/app/oracle/admin/standby/udump'
##standby add parameters
*.db_unique_name='unistan'
*.db_file_name_convert='/data/primany','/data/standby'
*.log_file_name_convert='/data/primany','/data/standby'
*.log_archive_config='dg_config=(unistan,unipri)'
*.log_archive_dest_1='location=/data/arch valid_for=(all_logfiles,all_roles) db_unique_name=unistan'
*.log_archive_dest_2='service=primany lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=unipri'
*.log_archive_dest_state_1=enable
*.log_archive_dest_state_2=enable
*.log_archive_max_processes=10
*.fal_server=primany
*.fal_client=standby
*.standby_file_management=auto
EOF


配置listener.ora
cat >>$ORACLE_HOME/network/admin/listener.ora<<EOF
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = standby)
      (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
      (SID_NAME = standby)
    )
  )
EOF


配置tnsnames.ora
cat >>$ORACLE_HOME/network/admin/tnsnames.ora<<EOF
PRIMANY =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.103)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = primany)
    )
  )


STANDBY =
   (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.104)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = standby)
    )
  )
EOF


lsnrctl start
sqlplus system@primany/123qwe


sqlplus / as sysdba
create spfile='/u01/app/oracle/standbyspfile.ora' from pfile;
startup nomount pfile='/u01/app/oracle/product/10.2.0/db_1/dbs/initstandby.ora';




primany操作


配置listener.ora
cat >>$ORACLE_HOME/network/admin/listener.ora<<EOF
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = primany)
      (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
      (SID_NAME = primany)
    )
  )
EOF




配置tnsnames.ora
cat >>$ORACLE_HOME/network/admin/tnsnames.ora<<EOF
PRIMANY =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = dgprimany)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = primany)
    )
  )


STANDBY =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = dgstandby)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = standby)
    )
  )
EOF


alter system set log_archive_dest_1='location=/data/arch valid_for=(all_logfiles,all_roles) db_unique_name=unipri';
alter system set log_archive_dest_2='service=standby lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=unistan';
alter system set log_archive_config='dg_config=(unipri,unistan)';
alter system set log_archive_dest_state_1=enable;
alter system set log_archive_dest_state_2=enable;
alter system set log_archive_max_processes=10;
alter system set fal_server=standby;
alter system set fal_client=primany;
alter system set standby_file_management=auto;
alter system set db_unique_name='unipri' scope=spfile;
alter system set db_file_name_convert='/data/standby','/data/primany' scope=spfile;
alter system set log_file_name_convert='/data/standby','/data/primany' scope=spfile;


shutdown immediate
startup




rman target /


run{
allocate channel c1 type disk;
backup database format '/backup/%U';
release channel c1;
}




scp -rp /backup/* oracle@dgstandby:/backup/


rman target / auxiliary sys/123qwe@standby


run{
allocate auxiliary channel c1 type disk;
duplicate target database for standby nofilenamecheck;
release channel c1;
}




查看primany redolog
sqlplus / as sysdba
select group#,bytes from v$log;




查看standby实例状态
select status from v$instance;
配置standby redolog
alter database add standby logfile ('/data/standby/standby01.log') size 50M;
alter database add standby logfile ('/data/standby/standby02.log') size 50M;
alter database add standby logfile ('/data/standby/standby03.log') size 50M;
alter database add standby logfile ('/data/standby/standby04.log') size 50M;


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


查看状态
select sequence#,applied from v$archived_log;

select database_role,protection_mode,switchover_status,name from v$database;

Learning from ZhongWeicheng