一主多从 PFILE 文件

1，其他的不说，首先命令查看：是否归档、是否强制日志、show parameter db_name、show parameter service_name、show parameter db_unique_name、select name from v$datafile、select member from v$log.

2，创建pfile-参数文件 create pfile='路径/s_ora' from spfile, 当然也可使用：alter system set xxx='xxx' scope=spfile.

   在这里，如果通过创建参数文件Pfile,设置参数的话，再通新参数过启动时有可能会在 alter system switch logfile 时hang住，所有的问题最好都先看看 告警日志，在做处理。

   eg: 故障案例：

    主节点在通过新pfile 文件打开以后，当日志切换以时，直接给hang住，最后重启库报错，对应的alter 日志：

    日志切换时，hang住， 

    ---------------------------------------------------------------------------------------------------------

    ARC1: Becoming the heartbeat ARCH

    Errors in file /u01/app/oracle/diag/rdbms/rac1_p/rac1/trace/rac1_ora_12815.trc:

    ORA-16014: log 2 sequence# 14 not archived, no available destinations ---问题根源，表示这个路径没有起作用：

    ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/rac1/redo02.log'

    USER (ospid: 12815): terminating the instance due to error 16014  

    -----------------------------------------------------------------------------------------------------------

    试作重启库，但到mount 以后报错：

    -----------------------------------------------------------------------------------------------------------

     SQL>alter database open;

         alter database open

          *

         ERROR at line 1:

         ORA-03113: end-of-file on communication channel   --这样的报错，并不是空间，或者其他的问题引起。

         Process ID: 12815

         Session ID: 1 Serial number: 5       

    -----------------------------------------------------------------------------------------------------------

 解决办法：

    mount 以后， show parameter dest_   show parameter name , show parameter dump 后，直接通过：

   alter system set log_archive_dest_1='location=/u01/app/oracle/archive_log’scope=both ; --指定就解决了

3，参数文件实例：

primary db_pfile:

rac1.__db_cache_size=159383552

rac1.__java_pool_size=4194304

rac1.__large_pool_size=4194304

rac1.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment

rac1.__pga_aggregate_target=289406976

rac1.__sga_target=444596224

rac1.__shared_io_pool_size=0

rac1.__shared_pool_size=264241152

*.audit_trail='db'

*.compatible='11.2.0.0.0'

*.control_files='/u01/app/oracle/oradata/rac1/control01.ctl','/u01/app/oracle/fast_recovery_area/rac1/control02.ctl'

*.db_block_size=8192

*.db_domain=''

*.db_name='rac1'

*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'

*.db_recovery_file_dest_size=4322230272

*.db_unique_name='rac1_p'

*.diagnostic_dest='/u01/app/oracle'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=rac1XDB)'

*.fal_client='rac2_s','rac3_s'

*.fal_server='rac1_p'

*.log_archive_config='dg_config=(rac1_p,rac2_s,rac3_s)'

*.log_archive_dest_1='location=/u01/app/oracle/archive_log valid_for=(all_logfiles,all_roles) db_unique_name=racl_p'

*.log_archive_dest_2='service=rac2_s sync affirm lgwr valid_for=(all_logfiles,primary_role) db_unique_name=rac2_s'

*.log_archive_dest_3='service=rac3_s sync affirm lgwr valid_for=(all_logfiles,primary_role) db_unique_name=rac3_s'

*.log_archive_dest_state_1='enable'

*.log_archive_dest_state_2='enable'

*.log_archive_dest_state_3='enable'

*.log_archive_format='%t_%s_%r.dbf'

*.log_file_name_convert='/u01/app/oracle/oradata/rac2','/u01/app/oracle/oradata/rac1',/u01/app/oracle/oradata/rac3'","/u01/app/oracle/oradata/rac1' 

*.db_file_name_convert='/u01/app/oracle/oradata/datafile_rac2','/u01/app/oracle/oradata/datafile_p','/u01/app/oracle/oradata/rac2','/u01/app/oracle/oradata/rac1','/u01/app/oracle/oradata/datafile_rac3','/u01/app/oracle/oradata/datafile_p','/u01/app/oracle/oradata/rac3','/u01/app/oracle/oradata/rac1'

*.memory_target=734003200

*.open_cursors=300

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.sessions=1500

*.standby_file_management='auto'

*.undo_retention=1800

*.undo_tablespace='UNDOTBS1'

---------------------------------------------------------------------------------------------------------------

standby _s2_pfile:

*.audit_file_dest='/u01/app/oracle/admin/rac2/adump'

*.audit_trail='db'

*.compatible='11.2.0.0.0'

*.control_files='/u01/app/oracle/oradata/rac2/control01.ctl','/u01/app/oracle/fast_recovery_area/rac2/control02.ctl'

*.db_block_size=8192

*.db_domain=''

*.db_file_name_convert='/u01/app/oracle/oradata/datafile_rac2','/u01/app/oracle/oradata/datafile_p','/u01/app/oracle/oradata/rac2','/u01/app/oracle/oradata/rac1'

*.db_name='rac1'

*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'

*.db_recovery_file_dest_size=4322230272

*.db_unique_name='rac2_s'

*.diagnostic_dest='/u01/app/oracle'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=rac1XDB)'

*.fal_client='rac2_s'

*.fal_server='rac1_p'

*.log_archive_config='dg_config=(rac1_p,rac2_s)'

*.log_archive_dest_1='location=/u01/app/oracle/archive_log valid_for=(all_logfiles,all_roles) db_unique_name=rac2_s'

*.log_archive_dest_2='service=rac1_p sync affirm lgwr valid_for=(all_logfiles,primary_role) db_unique_name=rac1_p'

*.log_archive_dest_state_1='enable'

*.log_archive_dest_state_2='enable'

*.log_archive_format='%t_%s_%r.dbf'

*.log_file_name_convert='/u01/app/oracle/oradata/rac2','/u01/app/oracle/oradata/rac1'

*.memory_target=734003200

*.open_cursors=300

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.sessions=1500

*.standby_file_management='auto'

*.undo_retention=1800

*.undo_tablespace='UNDOTBS1'

---------------------------------------------------------------------------------------------------------------

standby _s3_pfile:

*.audit_file_dest='/u01/app/oracle/admin/rac3/adump'

*.audit_trail='db'

*.compatible='11.2.0.0.0'

*.control_files='/u01/app/oracle/oradata/rac3/control01.ctl','/u01/app/oracle/fast_recovery_area/rac3/control02.ctl'

*.db_block_size=8192

*.db_domain=''

*.db_file_name_convert='/u01/app/oracle/oradata/datafile_rac3','/u01/app/oracle/oradata/datafile_p','/u01/app/oracle/oradata/rac3','/u01/app/oracle/oradata/rac1'

*.db_name='rac1'

*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'

*.db_recovery_file_dest_size=4322230272

*.db_unique_name='rac3_s'

*.diagnostic_dest='/u01/app/oracle'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=rac1XDB)'

*.fal_client='rac3_s'

*.fal_server='rac1_p'

*.log_archive_config='dg_config=(rac1_p,rac3_s)'

*.log_archive_dest_1='location=/u01/app/oracle/archive_log valid_for=(all_logfiles,all_roles) db_unique_name=rac3_s'

*.log_archive_dest_2='service=rac1_p sync affirm lgwr valid_for=(all_logfiles,primary_role) db_unique_name=rac1_p'

*.log_archive_dest_state_1='enable'

*.log_archive_dest_state_2='enable'

*.log_archive_format='%t_%s_%r.dbf'

*.log_file_name_convert='/u01/app/oracle/oradata/rac3','/u01/app/oracle/oradata/rac1'

*.memory_target=734003200

*.open_cursors=300

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'   ----在客户端登陆sysdba，这时才会用到passwordfile.

*.sessions=1500

*.standby_file_management='auto'

*.undo_retention=1800

*.undo_tablespace='UNDOTBS1'

---- 当时各节点的sid分别是： primary SID=RAC1  standby_s2=RAC2   standby_s3=RAC3

                             db_name=RAC1     db_unique_name=不同

3 监听文件： 

 

 listener.ora    文件 主备基本一致：

-------------------------------------------------------------------------------------------------

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (GLOBAL_DBNAME = RAC3)

      (ORACLE_HOME = /u01/app/oracle/product/11.2/rac_3)

      (SID_NAME = RAC3)

    )

  )

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.129)(PORT = 1521))

    )

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

    )

  )

ADR_BASE_LISTENER = /u01/app/oracle

-------------------------------------------------------------------------------------------------------------

tnsname.ora  文件 给服务名需和pfile里的部分一致：

# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2/rac_1/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

RAC1_P =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.127)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = RAC1_P)

       (SID = RAC1)

      (SERVICE_NAME = RAC1_P)

    )

  )

RAC2_S =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.128)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = RAC2_S)

       (SID = RAC2)

      (SERVICE_NAME = RAC2_S)

    )

  )

RAC3_S =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.129)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = RAC3_S)

       (SID = RAC3)

      (SERVICE_NAME = RAC3_S)

    )

  )

  

4， 密码文件：

  --把primary db 路径下的密码文件拷贝其他节点，并改名：  路径，$ cd $ORACLE_HOME/dbs

  --或者自己重新在备节点上创建密码文件：

    orapwd file=<> password=<> entries=最大数目

    这里的file命名规则在unix/linux下orapw<ORACLE_SID>，在windows下为 pwd<SID>.ora

5，备份数据文件，归档日志，控制文件  --2钟方法：    

   ---rman ， 拷贝备份的文件后，通过duplicate 恢复

   ---冷备的方法，在所有的拷贝备节点。

   

rman 方式： backup database format '/home/oracle/rman_back/%U' plus archivelog;

            backup current controlfile for standby  format '/home/oracle/rman_back/%U_%T' ;

6,scp 拷贝 standby   --放在一个文件下，便于管理

7,根据pfile ,创建文件路径  

  

SQL>alter  system set  db_file_name_convert='/u01/app/oracle/oradata/chicago/','/u01/app/oracle/oradata/lixia/'  scope=both;

      SQL>alter   system set  db_name='chicago'   scope=both;

      SQL>alter   system  set  db_unique_name='chicago'  scope=both;

      SQL>alter   system  set  fal_client='CHICAGO'  scope=both;

      SQL>alter   system  set  fal_server='LIXIA'  scope=both;

       SQL>alter   system  set  log_archive_config='dg_config=(chicago,boston,lixia)'  scope=both;

       SQL>alter    system  set  log_archive_dest_1='location=/u01/app/oracle/arch1/lixia/ valid_for=(all_logfiles,all_roles) db_unique_name=lixia'   scope=both;

       SQL>alter   system   set   log_archive_dest_2='service=chicago lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=chicago'  scope=both;

       SQL>  alter   system  set  log_archive_dest_3='service=lixia  lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=lixia'  both;

       SQL>  alter   system  set log_archive_dest_state_1='ENABLE'  scope=both;

       SQL>  alter   system   set  log_archive_dest_state_2='ENABLE'  scope=both;

       SQL>  alter    system  set   log_archive_dest_state_3='ENABLE'  scope=both;

        SQL>  alter    system  set  log_archive_format='%t_%s_%r.arc'  scope=both;

       SQL>   alter    system  set  log_archive_max_processes=30   scope=both;

  SQL>alter     system set            log_file_name_convert='/u01/app/oracle/arch1/boston/','/u01/app/oracle/arch1/chicago/','/u01/app/oracle/arch2/boston/','/u01/app/oracle/arch2/chicago/','/u01/app/oracle/arch1/lixia/','/u01/app/oracle/arch1/chicago/','/u01/app/oracle/arch2/lixia/','/u01/app/oracle/arch2/chicago/','/u01/app/oracle/oradata/chicago/','/u01/app/oracle/oradata/lixia/'  scope=both;