Oracle 10g RAC 切換到歸檔模式

[Oracle@node1 pfile]$ export ORACLE_SID=MRAC1

[oracle@node1 pfile]$ sqlplus / as sysdba

 

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Sep 27 10:27:35 2012

 

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, Real Application Clusters, OLAP and Data Mining options

 

SQL> alter system set log_archive_dest_1='+archlog' scope=both;

alter system set log_archive_dest_1='+archlog' scope=both

*

? 1 ?????:

ORA-32017: ?? SPFILE ???

ORA-16179: ????? SPFILE ? "log_archive_dest_1" ??????

 

確實LOCATION

SQL> alter system set log_archive_dest_1='location=+archlog' scope=both;

 

??????

 

SQL> alter system set log_archive_format='arch_%s_%t.arc' scope=both;

alter system set log_archive_format='arch_%s_%t.arc' scope=both

                 *

? 1 ?????:

ORA-02095: ????????????

 

只能SPFILE

SQL> alter system set log_archive_format='arch_%s_%t.arc' scope=spfile;

 

??????

 

SQL> alter system set log_archive_start=true;

alter system set log_archive_start=true

                 *

? 1 ?????:

ORA-02095: ????????????

 

 

SQL> alter system set log_archive_start=true scope=spfile;

 

??????

 

查看活動執行個體

SQL> col inst_name for a30

SQL> select * from v$active_instances;

 

INST_NUMBER INST_NAME

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

          1 node1:MYRAC1

 

 

          2 node2:MYRAC2

使用SPFILE檔案啟動的

SQL> show parameter spfile

 

NAME                                 TYPE   VALUE

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

spfile                               string  +DATA/myrac/spfilemyrac.ora

 

節點2也使用同一個檔案啟動的

SQL> show parameter pfile

 

NAME                                 TYPE     VALUE

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

spfile                               string   +DATA/myrac/spfilemyrac.ora

 

節點2參數檔案得到立即更新

SQL> show parameter log_archive_dest_1

 

NAME                                 TYPE     VALUE

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

log_archive_dest_1                   string   location=+archlog

log_archive_dest_10                  string

 

SQL> shutdown immediate

ORA-01507: ??????

 

 

ORACLE ???????

SQL> startup mount

 

SQL> show parameter log

ORA-01034: ORACLE not available

 

 

SQL> shutdown immediate;

ORA-01012: not logged on

SQL> exit

? Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, Real Application Clusters, OLAP and Data Mining options ??

[oracle@node1 pfile]$ export ORACLE_SID=MYRAC1

[oracle@node1 pfile]$ sqlplus / as sysdba

 

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Sep 27 10:51:08 2012

 

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

 

Connected to an idle instance.

 

SQL> startup nomount

ORA-32004: obsolete and/or deprecated parameter(s) specified

ORA-19905: log_archive_format must contain %s, %t and %r

SQL> show parameter log_archive

ORA-01034: ORACLE not available

 

SQL> host

[oracle@node1 pfile]$ pwd

/opt/oracle/database/admin/MYRAC/pfile

[oracle@node1 pfile]$ vi myrac1_pfile.ora

MYRAC2.__db_cache_size=75497472

MYRAC1.__db_cache_size=62914560

MYRAC1.__java_pool_size=4194304

MYRAC2.__java_pool_size=4194304

MYRAC1.__large_pool_size=4194304

MYRAC2.__large_pool_size=4194304

MYRAC2.__shared_pool_size=79691776

MYRAC1.__shared_pool_size=92274688

MYRAC1.__streams_pool_size=0

MYRAC2.__streams_pool_size=0

*.audit_file_dest='/opt/oracle/database/admin/MYRAC/adump'

*.background_dump_dest='/opt/oracle/database/admin/MYRAC/bdump'

*.cluster_database_instances=2

*.cluster_database=true

*.compatible='10.2.0.1.0'

*.control_files='+DATA/myrac/controlfile/current.256.772910373'

*.core_dump_dest='/opt/oracle/database/admin/MYRAC/cdump'

*.db_block_size=8192

*.db_create_file_dest='+DATA'

*.db_domain=''

*.db_file_multiblock_read_count=16

*.db_name='MYRAC'

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

MYRAC1.instance_number=1

MYRAC2.instance_number=2

*.job_queue_processes=10

*.nls_language='SIMPLIFIED CHINESE'

*.nls_territory='CHINA'

*.open_cursors=300

*.pga_aggregate_target=16777216

*.processes=150

*.remote_listener='LISTENERS_MYRAC_OLTP'

*.remote_login_passwordfile='exclusive'

*.sga_target=167772160

MYRAC2.thread=2

MYRAC1.thread=1

*.undo_management='AUTO'

MYRAC1.undo_tablespace='UNDOTBS1'

MYRAC2.undo_tablespace='UNDOTBS2'

*.user_dump_dest='/opt/oracle/database/admin/MYRAC/udump'

 

*.log_archive_start=true

*.log_archive_dest_1='location=+archlog'

*.log_archive_format='arch_%s_%t_%r.arc'

[oracle@node1 pfile]$ exit

exit

 

SQL> startup nomount pfile='/opt/oracle/database/admin/MYRAC/pfile/myrac1_pfile.ora';

ORA-32006: LOG_ARCHIVE_START initialization parameter has been deprecated

ORACLE instance started.

 

Total System Global Area  167772160 bytes

Fixed Size                  1218316 bytes

Variable Size             100665588 bytes

Database Buffers           62914560 bytes

Redo Buffers                2973696 bytes

 

這個參數被廢棄了無關緊要!

SQL> show parameter log_archive

 

NAME                                 TYPE   VALUE

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

log_archive_config                   string

log_archive_dest                     string

log_archive_dest_1                   string  location=+archlog

log_archive_dest_10                  string

log_archive_dest_2                   string

log_archive_dest_3                   string

log_archive_dest_4                   string

log_archive_dest_5                   string

log_archive_dest_6                   string

log_archive_dest_7                   string

log_archive_dest_8                   string

 

NAME                                 TYPE   VALUE

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

log_archive_dest_9                   string

log_archive_dest_state_1             string  enable

log_archive_dest_state_10            string  enable

log_archive_dest_state_2             string  enable

log_archive_dest_state_3             string  enable

log_archive_dest_state_4             string  enable

log_archive_dest_state_5             string  enable

log_archive_dest_state_6             string  enable

log_archive_dest_state_7             string  enable

log_archive_dest_state_8             string  enable

log_archive_dest_state_9             string  enable

 

NAME                                 TYPE   VALUE

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

log_archive_duplex_dest              string

log_archive_format                   string  arch_%s_%t_%r.arc

log_archive_local_first              boolean     TRUE

log_archive_max_processes            integer     2

log_archive_min_succeed_dest         integer     1

log_archive_start                    boolean     TRUE

log_archive_trace                    integer  

 

SQL> alter database archivelog;

 

???????

 

SQL> archive log list;

???????            ????

????             ??

????            +ARCHLOG

?????????     86

?????????   88

??????           88

SQL> alter database open;

 

???????

 

SQL> col instance_name a30

SP2-0158: ??? COLUMN ?? "a30"

SQL> col inst_name for a30

SQL> select * from v$active_instances;

 

INST_NUMBER INST_NAME

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

          1 node1:MYRAC1

SQL> alter session set nls_language=american;

 

Session altered.

先把原來的AMSCMD裡的刪除

 

SQL> create spfile='+DATA/MyRAC/spfileMYRAC.ora' from pfile;

 

File created.

 

SQL> startup

ORA-01565: error in identifying file '+DATA/MYRAC/spfileMYRAC.ora'

ORA-17503: ksfdopn:2 Failed to open file +DATA/MYRAC/spfileMYRAC.ora

ORA-01000: maximum open cursors exceeded

 

[oracle@node1 oracle]$ cat pfile_01.ora

*.SPFILE='+DATA/MYRAC/spfileMYRAC.ora'

 

刪除SPFILE 重新建立並且修改語言到英文環境 SSH用戶端沒地方設定

SQL> create spfile='+data/myrac/spfileMYRAC.ora' from pfile='/opt/oracle/database/admin/MYRAC/pfile/myrac1_pfile.ora';

 

File created.

指定SPFILE目錄和指定PFILE

結果節點2成功啟動了SPFILE, 反而節點1的SPFILE 定位在本地目錄上

SQL> startup nomount

ORACLE instance started.

 

Total System Global Area  167772160 bytes

Fixed Size                  1218316 bytes

Variable Size             100665588 bytes

Database Buffers           62914560 bytes

Redo Buffers                2973696 bytes

SQL> show parameter pfile        

 

NAME                                 TYPE   VALUE

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

spfile                               string  /opt/oracle/database/product/1

                                      0.2.0/db_1/dbs/spfileMYRAC1.or

                                      a

經過幾番折騰把本地SPFILE移走到其他目錄下終於啟動了共用磁碟上的SPFILE檔案

[oracle@node1 dbs]$ mv spfileMYRAC1.ora  /opt/oracle/database

[oracle@node1 dbs]$ exit

關閉先前的執行個體

SQL> shutdown immediate;

ORA-01507: database not mounted

 

 

ORACLE instance shut down.

啟動記憶體模式

SQL> startup nomount

ORACLE instance started.

 

Total System Global Area  167772160 bytes

Fixed Size                  1218316 bytes

Variable Size             100665588 bytes

Database Buffers           62914560 bytes

Redo Buffers                2973696 bytes

查看spfile路徑發現正確用到了共用磁碟上的

SQL> show parameter pfile

 

NAME                                 TYPE   VALUE

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

spfile                               string  +DATA/myrac/spfilemyrac.ora

載入控制檔案

SQL> alter database mount

  2  ;

 

Database altered.

開啟資料庫

SQL> alter database open;

 

Database altered.

查看是否歸檔

SQL> archive log list

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            +ARCHLOG

Oldest online log sequence     87

Next log sequence to archive   89

Current log sequence           89

節點1 交換日誌

SQL> alter system switch logfile;

 

System altered

 

自動產生多級目錄

ASMCMD> pwd

+archlog/myrac/archivelog/2012_09_27

ASMCMD> ls

thread_1_seq_88.256.795093263

thread_1_seq_89.257.795108837.

節點直接啟動開啟資料庫模式

並且查看歸檔

節點2 交換下日誌

SQL> alter system switch logfile;

 

ASMCMD> ls

thread_1_seq_88.256.795093263

thread_1_seq_89.257.795108837

thread_2_seq_47.258.795108927

 

注意事項

1 建立pfile和spfile 最好指定目錄兩邊都要

2 建立和更新spfile時先刪除或者備份共用目錄上的spfile. 最好先不刪除,如果刪除它會把串連所在的目錄刪除掉,重建的時候會搞個DB_UNKONW 怪怪地.

3 注意共用磁碟上的spfile 結尾是不帶數位

4 歸檔檔案路徑只要指定一級目錄就夠了,它會自動建立多級目錄的.