11g RAC修改归档模式

你的名字 2022-08-13 08:59 369阅读 0赞

11g RAC修改归档步骤  
1：节点修改归档路径  
2：停止数据库-srvctl stop database -d fyl    
3：启动数据库至mount--srvctl stop database -d fyl  -o mount  
4：实例分别执行alter database archivelog;

5：验证

1：节点修改归档路径

创建NFS共享文件系统存放归档日志（rac1 ：nfs server ；rac2：nfs client）
    节点1
    [root@rac1 arch]# vi /etc/exports
    /u02/arch  *(rw,sync)
    [root@rac1 arch]# chkconfig nfs on
    [root@rac1 arch]# service nfs start
    Starting NFS services:  exportfs: No options for /u02/arch *: suggest *(sync) to avoid warning
    exportfs: No host name given with /u02/arch (rw,sync), suggest *(rw,sync) to avoid warning
    exportfs: incompatible duplicated export entries:
    exportfs:       *:/u02/arch (0x424) [IGNORED]
    exportfs:       *:/u02/arch (0x425) [  OK  ]
    Starting NFS quotas: [  OK  ]
    Starting NFS daemon: [  OK  ]
    Starting NFS mountd: [  OK  ]
    
    [root@rac1 arch]# exportfs -v
    /u02/arch       <world>(rw,wdelay,root_squash,no_subtree_check,anonuid=65534,anongid=65534)
    节点2
    [root@rac2 arch]# showmount -e rac1
    Export list for rac1:
    /u02/arch *
    [root@rac2 arch]# mount -t nfs -o rw,bg,hard,nointr,rsize=32768,wsize=32768,timeo=600,actimeo=0 rac1:/u02/arch /u02/arch
    [root@rac2 arch]# df -h
    Filesystem            Size  Used Avail Use% Mounted on
    /dev/mapper/VolGroup00-LogVol00
                           17G   13G  2.9G  82% /
    /dev/sda1              99M   12M   82M  13% /boot
    tmpfs                 792M  454M  338M  58% /dev/shm
    rac1:/u02/arch         17G   11G  5.0G  69% /u02/arch
    [root@rac2 ~]# vi /etc/fstab 
    
    /dev/VolGroup00/LogVol00 /                       ext3    defaults        1 1
    LABEL=/boot             /boot                   ext3    defaults        1 2
    tmpfs                   /dev/shm                tmpfs   defaults        0 0
    devpts                  /dev/pts                devpts  gid=5,mode=620  0 0
    sysfs                   /sys                    sysfs   defaults        0 0
    proc                    /proc                   proc    defaults        0 0
    /dev/VolGroup00/LogVol01 swap                    swap    defaults        0 0
    
    rac1:/u02/arch     /u02/arch      nfs rw,bg,hard,nointr,rsize=32768,wsize=32768,timeo=600,actimeo=0  0 0

[oracle@rac1 ~]$ !sql
    sqlplus / as sysdba
    
    SQL*Plus: Release 11.2.0.1.0 Production on 星期四 10月 16 13:40:14 2014
    
    Copyright (c) 1982, 2009, Oracle.  All rights reserved.
    
    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
    With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
    Data Mining and Real Application Testing options
    
    SQL> archive log list
    Database log mode              No Archive Mode              ---非归档
    Automatic archival             Disabled
    Archive destination            /u01/app/oracle/product/11.2.0/db_1/dbs/arch
    Oldest online log sequence     44
    Current log sequence           45
    SQL> create pfile='/home/oracle/init.ora' from spfile;     ---备份spfie
    File created.
    
    SQL> show parameter log
    
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    audit_syslog_level                   string
    commit_logging                       string
    db_create_online_log_dest_1          string
    db_create_online_log_dest_2          string
    enable_ddl_logging                   boolean     FALSE
    log_archive_config                   string
    log_archive_dest                     string
    log_archive_dest_1                   string
    log_archive_dest_state_7             string      enable
    log_archive_dest_state_8             string      enable
    log_archive_dest_state_9             string      enable
    log_archive_duplex_dest              string
    log_archive_format                   string      %t_%s_%r.dbf
    log_archive_local_first              boolean     TRUE
    log_archive_max_processes            integer     4
    log_archive_min_succeed_dest         integer     1
    log_archive_start                    boolean     FALSE
    log_archive_trace                    integer     0
    log_buffer                           integer     5677056
    log_checkpoint_interval              integer     0
    log_checkpoint_timeout               integer     1800
    log_checkpoints_to_alert             boolean     FALSE
    log_file_name_convert                string
    remote_login_passwordfile            string      EXCLUSIVE
    sec_case_sensitive_logon             boolean     TRUE
    sec_max_failed_login_attempts        integer     10
    
    SQL> alter system set log_archive_dest_1='location=/u02/arch' scope=spfile sid='*';  --修改双节点本地归档目录
    
    System altered.
    
    SQL> alter system set log_archive_format='fyl%t_%s_%r.log' scope=spfile sid='*';   --修改归档格式
    
    System altered.
    
    SQL> alter system set log_archive_dest_2='location=+DATE1' scope=spfile sid='*';    ----修改双节点ASM归档目录
    
    System altered.
    
    查看ASM实例信息
    [grid@rac1 ~]$ sqlplus / as sysasm
    
    SQL*Plus: Release 11.2.0.1.0 Production on Thu Oct 16 14:08:53 2014
    
    Copyright (c) 1982, 2009, Oracle.  All rights reserved.
    
    
    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
    With the Real Application Clusters and Automatic Storage Management options
    SQL> select name, GROUP_NUMBER from v$asm_diskgroup;
    
    NAME                           GROUP_NUMBER
    ------------------------------ ------------
    DATE1                                     1
    OCR_VOTE                                  2

2：停止数据库  
\[grid@rac1 ~\]$ srvctl stop database -d fyl                
3：启动数据库至mount  
\[grid@rac1 ~\]$ srvctl start database -d fyl -o mount       
4：实例分别执行alter database archivelog;

实例1
    SQL> conn / as sysdba
    Connected.
    SQL> archive log list
    Database log mode              No Archive Mode
    Automatic archival             Disabled
    Archive destination            +DATE1
    Oldest online log sequence     44
    Current log sequence           45
    
    SQL> alter database archivelog;
    
    Database altered.
    
    SQL> archive log list
    Database log mode              Archive Mode
    Automatic archival             Enabled
    Archive destination            +DATE1
    Oldest online log sequence     44
    Next log sequence to archive   45
    Current log sequence           45
    SQL> alter database open;
    
    Database altered.
    
    实例2（操作同实例1）
    SQL> alter database archivelog;
    SQL> alter database open;

5：验证

SQL> show parameter log
    
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    audit_syslog_level                   string
    commit_logging                       string
    db_create_online_log_dest_1          string
    db_create_online_log_dest_2          string
    enable_ddl_logging                   boolean     FALSE
    log_archive_config                   string
    log_archive_dest                     string
    log_archive_dest_1                   string      location=/u02/arch
    log_archive_dest_10                  string
    log_archive_dest_18                  string
    log_archive_dest_19                  string
    log_archive_dest_2                   string      location=+DATE1
    log_archive_duplex_dest              string
    log_archive_format                   string      fyl%t_%s_%r.log
    验证
    SQL> alter system archive log current;  --与单实例不同，rac下2个实例的currrent 都会归档
    
    System altered.
    
    SQL> select name,thread#,sequence#,status from v$archived_log;
    
    NAME                                                                      THREAD#  SEQUENCE# S
    ---------------------------------------------------------------------- ---------- ---------- -
    /u02/arch/fyl1_45_858119547.log                                                 1         45 A
    +DATE1/fyl/archivelog/2014_10_16/thread_1_seq_45.268.861113889                  1         45 A
    /u02/arch/fyl1_46_858119547.log                                                 1         46 A
    +DATE1/fyl/archivelog/2014_10_16/thread_1_seq_46.269.861114021                  1         46 A
    /u02/arch/fyl2_8_858119547.log                                                  2          8 A
    +DATE1/fyl/archivelog/2014_10_16/thread_2_seq_8.270.861114021                   2          8 A
    /u02/arch/fyl2_9_858119547.log                                                  2          9 A
    +DATE1/fyl/archivelog/2014_10_16/thread_2_seq_9.271.861114115                   2          9 A
    /u02/arch/fyl1_47_858119547.log                                                 1         47 A
    +DATE1/fyl/archivelog/2014_10_16/thread_1_seq_47.272.861114117                  1         47 A
    /u02/arch/fyl2_10_858119547.log                                                 2         10 A
    
    NAME                                                                      THREAD#  SEQUENCE# S
    ---------------------------------------------------------------------- ---------- ---------- -
    +DATE1/fyl/archivelog/2014_10_16/thread_2_seq_10.273.861114549                  2         10 A
    /u02/arch/fyl1_48_858119547.log                                                 1         48 A
    +DATE1/fyl/archivelog/2014_10_16/thread_1_seq_48.274.861114549                  1         48 A
    /u02/arch/fyl2_11_858119547.log                                                 2         11 A
    +DATE1/fyl/archivelog/2014_10_16/thread_2_seq_11.275.861114613                  2         11 A
    /u02/arch/fyl1_49_858119547.log                                                 1         49 A
    +DATE1/fyl/archivelog/2014_10_16/thread_1_seq_49.276.861114613                  1         49 A
    
    18 rows selected.
    
    ASMCMD> pwd
    +DATE1/fyl/ARCHIVELOG/2014_10_16
    ASMCMD> ls
    thread_1_seq_45.268.861113889
    thread_1_seq_46.269.861114021
    thread_1_seq_47.272.861114117
    thread_1_seq_48.274.861114549
    thread_1_seq_49.276.861114613
    thread_2_seq_10.273.861114549
    thread_2_seq_11.275.861114613
    thread_2_seq_8.270.861114021
    thread_2_seq_9.271.861114115
    
    [root@rac1 arch]# ls -l
    total 14128
    -rw-r----- 1 oracle asmadmin 6155264 Oct 16 14:18 fyl1_45_858119547.log
    -rw-r----- 1 oracle asmadmin  230912 Oct 16 14:20 fyl1_46_858119547.log
    -rw-r----- 1 oracle asmadmin   30208 Oct 16 14:21 fyl1_47_858119547.log
    -rw-r----- 1 oracle asmadmin   90624 Oct 16 14:29 fyl1_48_858119547.log
    -rw-r----- 1 oracle asmadmin   13824 Oct 16 14:30 fyl1_49_858119547.log