Thursday 19 September 2013

ORA-02097: parameter cannot be modified because specified value is invalid

Please enable primary archiving to this location /db/arch1  and secondary archive location to /opt/arch2.Please switch logfile to ensure archive are generated in both the location.

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /db/vision/db/tech_st/11.1.0/dbs/arch
Oldest online log sequence     20
Next log sequence to archive   21
Current log sequence

SQL> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /db/vision/db/tech_st/11.1.0/d
                                                 bs/spfilevision.ora
SQL> alter system set log_archive_dest_1='LOCATION=/db/arch1';

System altered.

SQL> alter system set log_archive_dest_2='LOCATION=/opt/arch2';

System altered.

SQL>

SQL> show parameter arch

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
archive_lag_target                   integer     0
log_archive_config                   string
log_archive_dest                     string
log_archive_dest_1                   string      LOCATION=/db/arch1
log_archive_dest_10                  string
log_archive_dest_2                   string      LOCATION=/opt/arch2
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

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_8                   string
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

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
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
standby_archive_dest                 string      ?/dbs/arch
SQL>

SQL> alter system switch logfile;

System altered.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[dbvision@apps 11.1.0]$ pwd
/db/vision/db/tech_st/11.1.0
[dbvision@apps 11.1.0]$ cd /db/arch1
[dbvision@apps arch1]$ pwd
/db/arch1
[dbvision@apps arch1]$ ls -tlrh
total 180M
-rw-r----- 1 dbvision dba 387M Sep 20 07:30 1_21_790902108.dbf
[dbvision@apps arch1]$ cd /opt/arch2
[dbvision@apps arch2]$ ls -tlrh
total 298M
-rw-r----- 1 dbvision dba 387M Sep 20 07:31 1_21_790902108.dbf
[dbvision@apps arch2]$





If archive log is disabled then how to do archiving in two location.
===================================================================
create pfile from spfile;
shutdown immediate
add below parameters and start database.
log_archive_dest_1='LOCATION=/db/arch1'
log_archive_dest_2='LOCATION=/opt/arch2'
*.log_archive_dest_state_1=ENABLE
*.log_archive_dest_state_2=ENABLE

startup mount pfile='location'
alter database archivelog;
create spfile from pfile='loc';
shutdown immediate
startup
alter system logfile;


No comments:

Post a Comment