太懒
分类： Oracle
2013-06-07 15:55:36
Oracle 11G R2 RAC修改归档模式
库建好以后默认是非归档模式，需要修改一下
测试第一种方法：
SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 19
Current log sequence 20
SQL>
SQL> Select NAME, CREATED, LOG_MODE, CHECKPOINT_CHANGE#, ARCHIVE_CHANGE# from V$DATABASE;
NAME CREATED LOG_MODE CHECKPOINT_CHANGE# ARCHIVE_CHANGE#
--------- --------- ------------ ------------------ ---------------
MOMORAC 04-JUN-13 NOARCHIVELOG 2709486 2583268
SQL>
SQL> conn / as sysdba
Connected.
SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 20
Current log sequence 21
SQL> show parameter USE_DB_RECOVERY_FILE_DEST
SQL> show parameter DB_RECOVERY_FILE_DEST
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string +FRA
db_recovery_file_dest_size big integer 5G
SQL>
SQL> show parameter LOG_ARCHIVE_START
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_start boolean FALSE
SQL>
SQL> host srvctl stop database -d momorac
SQL> host srvctl status database -d momorac
Instance momo1 is not running on node node1
Instance momo2 is not running on node node2
Instance momo3 is not running on node node3
SQL> show user;
USER is "SYS"
SQL> startup mount exclusive
ORA-03135: connection lost contact
SQL> conn / as sysdba
Connected to an idle instance.
SQL>
SQL> startup mount exclusive
ORACLE instance started.
Total System Global Area 297119744 bytes
Fixed Size 1344540 bytes
Variable Size 184552420 bytes
Database Buffers 104857600 bytes
Redo Buffers 6365184 bytes
Database mounted.
SQL> host srvctl status database -d momorac
Instance momo1 is running on node node1
Instance momo2 is not running on node node2
Instance momo3 is not running on node node3
SQL>
SQL> select OPEN_MODE from v$database;
OPEN_MODE
--------------------
MOUNTED
SQL>
SQL> conn / as sysdba
Connected to an idle instance.
SQL>
SQL> startup mount exclusive restrict;
ORACLE instance started.
Total System Global Area 297119744 bytes
Fixed Size 1344540 bytes
Variable Size 184552420 bytes
Database Buffers 104857600 bytes
Redo Buffers 6365184 bytes
Database mounted.
SQL>
同时看日志 ：
ALTER SYSTEM SET local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=10.101.5.73)(PORT=1521))))' SCOPE=MEMORY SID='momo1';
Fri Jun 07 14:58:27 2013
NOTE: Loaded library: System
Fri Jun 07 14:58:27 2013
SUCCESS: diskgroup ASM1 was mounted
Fri Jun 07 14:58:28 2013
NOTE: dependency between database momorac and diskgroup resource ora.ASM1.dg is established
Fri Jun 07 14:58:34 2013
Successful mount of redo thread 1, with mount id 1497705681
Database mounted in Shared Mode (CLUSTER_DATABASE=TRUE)
Lost write protection disabled
Fri Jun 07 14:58:35 2013
Completed: ALTER DATABASE MOUNT
SQL>
SQL> alter database archivelog;
Database altered.
Completed: ALTER DATABASE MOUNT
Fri Jun 07 15:00:17 2013
alter database archivelog
Completed: alter database archivelog
SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> host srvctl start database -d momorac
SQL> host srvctl status database -d momorac
Instance momo1 is running on node node1
Instance momo2 is running on node node2
Instance momo3 is running on node node3
SQL>
SQL> conn / as sysdba
Connected.
SQL> archivg log list;
SP2-0734: unknown command beginning "archivg lo..." - rest of line ignored.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 20
Next log sequence to archive 21
Current log sequence 21
SQL>
node2 node3上查看一下
[oracle@node2 ~]$ db_env
momo2
ORACLE_SID= momo2
[oracle@node2 ~]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.3.0 Production on Fri Jun 7 15:05:33 2013
Copyright (c) 1982, 2011, Oracle. All rights reserved.
SQL> conn / as sysdba
Connected.
SQL> archiveg log list;
SP2-0734: unknown command beginning "archiveg l..." - rest of line ignored.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 14
Next log sequence to archive 15
Current log sequence 15
SQL>
[oracle@node3 ~]$ db_env
momo3
ORACLE_SID= momo3
[oracle@node3 ~]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.3.0 Production on Fri Jun 7 15:05:25 2013
Copyright (c) 1982, 2011, Oracle. All rights reserved.
SQL> conn / as sysdba
Connected.
SQL> archiveg log list;
SP2-0734: unknown command beginning "archiveg l..." - rest of line ignored.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 7
Next log sequence to archive 8
Current log sequence 8
SQL>
SQL> Select NAME, INST_ID, OPEN_MODE, CREATED, LOG_MODE, CHECKPOINT_CHANGE#, ARCHIVE_CHANGE# from GV$DATABASE;
NAME INST_ID OPEN_MODE CREATED LOG_MODE CHECKPOINT_CHANGE# ARCHIVE_CHANGE#
--------- ---------- -------------------- --------- ------------ ------------------ ---------------
MOMORAC 3 READ WRITE 04-JUN-13 ARCHIVELOG 2869044 2583268
MOMORAC 1 READ WRITE 04-JUN-13 ARCHIVELOG 2869044 2583268
MOMORAC 2 READ WRITE 04-JUN-13 ARCHIVELOG 2869044 2583268
SQL>
这样竟然也可以？
SQL> alter system switch logfile;
System altered.
SQL>
日志：
Fri Jun 07 15:10:18 2013
Thread 1 advanced to log sequence 22 (LGWR switch)
Current log# 2 seq# 22 mem# 0: +ASM1/momorac/onlinelog/group_2.262.817236135
Fri Jun 07 15:10:19 2013
SUCCESS: diskgroup FRA was mounted
Archived Log entry 1 added for thread 1 sequence 21 ID 0x59417054 dest 1:
Fri Jun 07 15:10:32 2013
NOTE: dependency between database momorac and diskgroup resource ora.FRA.dg is established
ASMCMD> ls
test/
ASMCMD> ls
MOMORAC/
test/
ASMCMD> pwd
+FRA
ASMCMD> ls
MOMORAC/
test/
ASMCMD> ls MOMORAC
ARCHIVELOG/
ASMCMD> ls MOMORAC/ARCHIVELOG
2013_06_07/
ASMCMD> ls MOMORAC/ARCHIVELOG/2013_06_07/
thread_1_seq_21.256.817485023
ASMCMD>
测试第二种方法：换到非归档模式
总结一下顺序：
SQL> host srvctl stop database -d momorac
SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup mount
ORACLE instance started.
Total System Global Area 297119744 bytes
Fixed Size 1344540 bytes
Variable Size 184552420 bytes
Database Buffers 104857600 bytes
Redo Buffers 6365184 bytes
Database mounted.
lSQL>show parameter cluster
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cluster_database boolean TRUE
cluster_database_instances integer 3
cluster_interconnects string
SQL> alter system set cluster_database=false scope=both sid='*';
alter system set cluster_database=false scope=both sid='*'
*
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified
SQL> alter system set cluster_database=false scope=spfile sid='*';
System altered.
SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 297119744 bytes
Fixed Size 1344540 bytes
Variable Size 184552420 bytes
Database Buffers 104857600 bytes
Redo Buffers 6365184 bytes
Database mounted.
SQL>检查日志
NOTE: dependency between database momorac and diskgroup resource ora.ASM1.dg is established
Fri Jun 07 15:24:15 2013
Successful mount of redo thread 1, with mount id 1497685208
Database mounted in Exclusive Mode
Lost write protection disabled
Completed: ALTER DATABASE MOUNTSQL> alter database noarchivelog;
Database altered.
SQL> show parameter cluster
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cluster_database boolean FALSE
cluster_database_instances integer 1
cluster_interconnects string
SQL> alter database set cluster_database=true scope=spfile sid='*';
alter database set cluster_database=true scope=spfile sid='*' *
ERROR at line 1:
ORA-02231: missing or invalid option to ALTER DATABASE
SQL> alter system set cluster_database=true scope=spfile sid='*';
System altered.
SQL> show parameter cluster
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cluster_database boolean FALSE
cluster_database_instances integer 1
cluster_interconnects string
SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> archive log list;
ORA-01012: not logged on
SQL> conn / as sysdba
Connected.
SQL>
SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 24
Current log sequence 25
SQL>node2 node3 上检查：
node2：
SQL> conn / as sysdba
Connected.
SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 18
Current log sequence 19
SQL>node3：
[oracle@node3 ~]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.3.0 Production on Fri Jun 7 15:36:20 2013
Copyright (c) 1982, 2011, Oracle. All rights reserved.
SQL> archive log list;
ORA-03114: not connected to ORACLE
SQL> conn / as sysdba
Connected.
SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 9
Current log sequence 10
SQL>
第一种：
$ srvctl stop database -d momorac
sqlplus任一节点 startup mount
alter database archivelog 或者 alter database noarchivelog;
shutdown immediate
$ srvctl start database -d momorac第二种：
sqlplus任一节点 alter system set cluster_database=false scope=spfile sid='*';
$ srvctl stop database -d momorac
sqlplus任一节点 startup mount
alter database archivelog 或者 alter database noarchivelog;
alter system set cluster_database=true scope=spfile sid='*';
shutdown immediate
$ srvctl start database -d momorac
