Switch Oracle 10g RAC to archive Mode

[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,200 5, 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 "??????

 

Indeed, 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 :????????????

 

Only SPFILE is allowed

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;

 

??????

 

View activity instances

SQL> col inst_name for a30

SQL> select * from v $ active_instances;

 

INST_NUMBER INST_NAME

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

1 node1: MYRAC1

 

 

2 node2: MYRAC2

Use the SPFILE file to start

SQL> show parameter spfile

 

NAME TYPE VALUE

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

Spfile string + DATA/myrac/spfilemyrac. ora

 

Node 2 also uses the same file to start

SQL> show parameter pfile

 

NAME TYPE VALUE

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

Spfile string + DATA/myrac/spfilemyrac. ora

 

Node 2 parameter file updated immediately

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

A 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,200 5, 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

A ORA-01034: ORACLE not available

 

SQL> host

[Oracle @ node1 pfile] $ pwd

/Opt/oracle/database/admin/MYRAC/pfile

[Oracle @ node1 pfile] $ vi myracw.pfile.ora

MYRAC2. _ db_cache_size = 75497472

MYRAC1. _ db_cache_size = 62914560

MYRAC1. _ java_pool_size = 4194304

MYRAC2. _ java_pool_size = 4194304

MYRAC1. _ fig = 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

*. Processses = 150

*. Remote_listener = 'listeners _ MYRAC_OLTP'

*. Remote_login_passwordfile = 'clusive'

*. 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/myracw.pfile.ora ';

The 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

 

This parameter is obsolete and does not matter!

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.

Delete the original 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'

 

Delete SPFILE and recreate it, and modify the language to the English environment. There is no place to set the SSH client.

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

 

File created.

Specify the SPFILE directory and PFILE

Result Node 2 successfully starts SPFILE. Instead, node 1's SPFILE is located in the local directory.

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

After several tossing times, I moved the local SPFILE to another directory and finally started the SPFILE file on the shared disk.

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

[Oracle @ node1 dbs] $ exit

Disable previous instances

SQL> shutdown immediate;

ORA-01507: database not mounted

 

 

ORACLE instance shut down.

Enable memory mode

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

View the spfile path and find that the shared disk is used correctly.

SQL> show parameter pfile

 

NAME TYPE VALUE

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

Spfile string + DATA/myrac/spfilemyrac. ora

Load Control File

SQL> alter database mount

2;

 

Database altered.

Open Database

SQL> alter database open;

 

Database altered.

Check whether the file is archived

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

Node 1 switch log

SQL> alter system switch logfile;

 

System altered

 

Automatically generate multi-level Directories

ASMCMD> pwd

+ Archlog/myrac/archivelog/2012_09_27

ASMCMD> ls

Thread_eclipseq_88.256.795093263

Thread_eclipseq_89.257.795108837.

Start the node directly and enable the database mode.

And view the archive

Node 2 swap logs

SQL> alter system switch logfile;

 

ASMCMD> ls

Thread_eclipseq_88.256.795093263

Thread_eclipseq_89.257.795108837

Thread_2_seq_47.258.795108927

 

Notes

1. It is best to specify both the directory to create pfile and spfile.

2. delete or back up the spfile in the shared directory before creating and updating the spfile. it is best not to delete it first. If you delete it, the directory where the connection is located will be deleted, and DB_UNKONW will be created during reconstruction.

3. Note that the spfile on the shared disk does not end with a number.

4. You only need to specify a level-1 directory for the archive file path. It will automatically create a level-1 directory.