Archive

sql> archive log list database log mode no archive mode automatic archival disabled archive destination d:\oracle\ora92\rdbms oldest online log sequence 24 current log sequence 26 sql> ---now this database is no archive mode sql> --------now change archive mode sql> alter database archive log; alter database archive log * error at line 1: ora-02231: missing or invalid option to alter database sql> ---------------because archive file only change in nomount stage only sql> shutdown immediate database closed. database dismounted. oracle instance shut down. sql> sql> sql> startup nomount oracle instance started. total system global area 135338868 bytes fixed size 453492 bytes variable size 109051904 bytes database buffers 25165824 bytes redo buffers 667648 bytes sql> --------now change archive mode sql> sql> alter database archive log; alter database archive log * error at line 1: ora-02231: missing or invalid option to alter database sql> alter database archivelog; alter database archivelog * error at line 1: ora-01507: database not mounted sql> alter database mount 2 ; database altered. sql> alter database archive log; alter database archive log * error at line 1: ora-02231: missing or invalid option to alter database

sql> alter database archivelog; database altered. sql> -----------because nomount stage not allowed any archive log file changes sql> sql> archive log list database log mode archive mode automatic archival disabled archive destination d:\oracle\ora92\rdbms oldest online log sequence 24 next log sequence to archive 26 current log sequence 26 sql> select * from v$log; group# thread# sequence# bytes members arc status ---------- ---------- ---------- ---------- ---------- --- ---------------first_change# first_tim ------------- --------1 1 25 104857600 1 yes inactive 823406 12-jan-08 2 1 853166 12-jan-08

26

104857600

1 no

current

3 1 766902 11-jan-08

24

104857600

1 yes inactive

sql> flagger off / group# thread# sequence# bytes members arc status ---------- ---------- ---------- ---------- ---------- --- ---------------first_change# first_tim ------------- --------1 1 25 104857600 1 yes inactive 823406 12-jan-08 2 1 853166 12-jan-08

26

104857600

1 no

current

3 1 766902 11-jan-08

24

104857600

1 yes inactive

group# thread# sequence# first_change# first_tim

bytes

sql> flagger off set linesize 250; sql> / members arc status

---------- ---------- ---------- ---------- ---------- --- ---------------------------- --------1 823406 12-jan-08

1

25

104857600

1 yes inactive

2 853166 12-jan-08

1

26

104857600

1 no

3 766902 11-jan-08

1

24

104857600

1 yes inactive

sql> sql> sql> sql> sql> sql> sql>

current

---------we have three log file --------switch and set checkpoint three time -------------before change archive log destingnation path

alter system set log_archive_dest='d:\oracle\sue-arc';

system altered. sql> archive log list database log mode archive mode automatic archival disabled archive destination d:\oracle\sue-arc oldest online log sequence 24 next log sequence to archive 26 current log sequence 26 sql> sql> sql> alter system switch logfile; alter system switch logfile * error at line 1: ora-01109: database not open sql> -----------so open database sql> alter database open; database altered. sql> alter system switch logfile; system altered. sql> alter system set checkpoint; alter system set checkpoint * error at line 1: ora-02065: illegal option for alter system sql> alter system set check point; alter system set check point * error at line 1: ora-00922: missing or invalid option sql> alter system checkpoint;

system altered. sql> select * from v$log; group# thread# sequence# first_change# first_tim

bytes

members arc status

---------- ---------- ---------- ---------- ---------- --- ---------------------------- --------1 823406 12-jan-08

1

25

104857600

1 yes inactive

2 853166 12-jan-08

1

26

104857600

1 no

inactive

3 66939160 19-jan-08

1

27

104857600

1 no

current

sql> alter system switch logfile; system altered. sql> alter system checkpoint; system altered. sql> alter system switch logfile; alter system switch logfile * error at line 1: ora-01089: immediate shutdown in progress - no operations are permitted sql> startup ora-12571: tns:packet writer failure sql> conn enter user-name: sys/oracle as sysdba connected to an idle instance. sql> startup oracle instance started. total system global area fixed size variable size database buffers redo buffers database mounted. database opened. sql> sql>

135338868 bytes 453492 bytes 109051904 bytes 25165824 bytes 667648 bytes

sql> sql> archive log list database log mode archive mode automatic archival disabled archive destination d:\oracle\sue-arc oldest online log sequence 26 next log sequence to archive 26 current log sequence 28 sql> sql> sql> ---------hang problem occure by automatic archival is disable and also not store any data in d:\oracle\sue-arc sql> sql> sql> alter system set log_archive_start=true scope=spfile; system altered. sql> alter system switch logfile; alter system switch logfile * error at line 1: ora-01089: immediate shutdown in progress - no operations are permitted sql> --------------now shutdown and start sql> sql> sql> startup ora-12571: tns:packet writer failure sql> conn enter user-name: sys/oracle as sysdba connected to an idle instance. sql> startup oracle instance started. total system global area fixed size variable size database buffers redo buffers

135338868 bytes 453492 bytes 109051904 bytes 25165824 bytes 667648 bytes

database mounted. database opened. sql> sql> sql> sql> ----now switch sql> alter system switch logfile; system altered. sql> --------now we open archive path------->d:\oracle\sue-arc sql>

sql> show parameter log_archive; name

type

value

------------------------------------ ----------- -----------------------------log_archive_dest

string

log_archive_dest_1

string

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

log_archive_dest_9

string

name

type

d:\oracle\sue-arc

value

------------------------------------ ----------- -----------------------------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

log_archive_duplex_dest

string

name

type

value

------------------------------------ ----------- -----------------------------log_archive_format

string

arc%s.%t

log_archive_max_processes

integer

2

log_archive_min_succeed_dest

integer

1

log_archive_start

boolean

true

log_archive_trace

integer

0

sql> sql> sql> sql> -------------change the archive path destingnation sql> sql> alter system set log_archive_dest_1='location=d:\oracle\admin\sue-arc'; alter system set log_archive_dest_1='location=d:\oracle\admin\sue-arc' * error at line 1: ora-02097: parameter cannot be modified because specified value is invalid ora-16019: cannot use log_archive_dest_1 with log_archive_dest or log_archive_duplex_dest sql> ---------because log_archive_dest have already set sql> -------so set log_archive_dest and log_archive_duplex_dest to null sql> sql> sql> alter system set log_archive_dest=' ' and log_archive_duplex_dest= ' '; alter system set log_archive_dest=' ' and log_archive_duplex_dest= ' ' * error at line 1: ora-00922: missing or invalid option sql> alter system set log_archive_dest=' '; system altered. sql> ---because already log_archive_duplex_dest have null sql> sql> ---now we change the path sql> alter system set log_archive_dest_1='location=d:\oracle\admin\sue-arc'; system altered. sql> show parameter v$logfile; sql> show parameter log_archive; name

type

value

------------------------------------ ----------- -----------------------------log_archive_dest

string

log_archive_dest_1

string

location=d:\oracle\admin\sue-a

rc 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

arc%s.%t

log_archive_max_processes

integer

2

log_archive_min_succeed_dest

integer

1

log_archive_start

boolean

true

log_archive_trace

integer

0

sql> alter system set log_archive_dest_2='location=d:\oracle\sue-arc'; system altered. sql> show parameter log_archive; name

type

value

------------------------------------ ----------- -----------------------------log_archive_dest

string

log_archive_dest_1

string

location=d:\oracle\admin\sue-a rc

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

location=d:\oracle\sue-arc

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

arc%s.%t

log_archive_max_processes

integer

2

log_archive_min_succeed_dest

integer

1

log_archive_start

boolean

true

log_archive_trace

integer

0

sql> sql> sql> sql> alter system switch logfile; system altered. sql> -------------if u don't want the log_archive_dest_1 then set null or set log_archive_dest_state_1 state is defer(disable) sql> sql> sql> alter system set log_archive_dest_state_1=defer; system altered. sql> sql> sql> show parameter log_archive; name

type

value

------------------------------------ ----------- -----------------------------log_archive_dest

string

log_archive_dest_1

string

location=d:\oracle\admin\sue-a rc

log_archive_dest_10

string

log_archive_dest_2

string

log_archive_dest_3

string

location=d:\oracle\sue-arc

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

defer

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

arc%s.%t

log_archive_max_processes

integer

2

log_archive_min_succeed_dest

integer

1

log_archive_start

boolean

true

log_archive_trace

integer

0

sql> sql> sql> -------------add additional sql>

archive process

sql> alter system set log_archive_max_processes=8 2 ; system altered. sql> ----max limit is 10 sql> sql> show parameter log_archive; name

type

value

------------------------------------ ----------- -----------------------------log_archive_dest

string

log_archive_dest_1

string

location=d:\oracle\admin\sue-a rc

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

location=d:\oracle\sue-arc

value

------------------------------------ ----------- -----------------------------log_archive_dest_9

string

log_archive_dest_state_1

string

defer

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

arc%s.%t

log_archive_max_processes

integer

8

log_archive_min_succeed_dest

integer

1

log_archive_start

boolean

true

log_archive_trace

integer

0

sql> sql> sql> sql> ---------chnage log_archive_format sql> sql> alter system set log_archive_format=sue%s.t%; alter system set log_archive_format=sue%s.t% * error at line 1: ora-00911: invalid character sql> alter system set log_archive_format='sue%s.t%'; alter system set log_archive_format='sue%s.t%' * error at line 1: ora-02095: specified initialization parameter cannot be modified sql> alter system set log_archive_format='sue%s.t%' scope=spfile; system altered. sql> shutdown immediate database closed. database dismounted. oracle instance shut down. sql> startup oracle instance started. total system global area fixed size variable size database buffers

135338868 bytes 453492 bytes 109051904 bytes 25165824 bytes

redo buffers

667648 bytes

database mounted. database opened. sql> show parameter log name

type

value

------------------------------------ ----------- -----------------------------db_create_online_log_dest_1

string

db_create_online_log_dest_2

string

db_create_online_log_dest_3

string

db_create_online_log_dest_4

string

db_create_online_log_dest_5

string

dblink_encrypt_login

boolean

log_archive_dest

string

log_archive_dest_1

string

false

location=d:\oracle\admin\sue-a rc

log_archive_dest_10

string

log_archive_dest_2

string

location=d:\oracle\sue-arc

name

type

value

------------------------------------ ----------- -----------------------------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

log_archive_dest_9

string

log_archive_dest_state_1

string

defer

log_archive_dest_state_10

string

enable

log_archive_dest_state_2

string

enable

log_archive_dest_state_3

string

enable

name

type

value

------------------------------------ ----------- -----------------------------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

log_archive_duplex_dest

string

log_archive_format

string

sue%s.t%

log_archive_max_processes

integer

8

log_archive_min_succeed_dest

integer

1

log_archive_start

boolean

true

name

type

value

------------------------------------ ----------- -----------------------------log_archive_trace

integer

0

log_buffer

integer

524288

log_checkpoint_interval

integer

0

log_checkpoint_timeout

integer

1800

log_checkpoints_to_alert

boolean

false

log_file_name_convert

string

log_parallelism

integer

1

logmnr_max_persistent_sessions

integer

1

remote_login_passwordfile

string

exclusive

sql> show parameter log_archive name type value ------------------------------------ ----------- -----------------------------log_archive_dest string

log_archive_dest_1

string

log_archive_dest_10 log_archive_dest_2 log_archive_dest_3 log_archive_dest_4 log_archive_dest_5 log_archive_dest_6 log_archive_dest_7 log_archive_dest_8

string string string string string string string string

name -----------------------------------log_archive_dest_9 log_archive_dest_state_1 log_archive_dest_state_10 log_archive_dest_state_2 log_archive_dest_state_3 log_archive_dest_state_4 log_archive_dest_state_5 log_archive_dest_state_6 log_archive_dest_state_7 log_archive_dest_state_8 log_archive_dest_state_9

type ----------string string string string string string string string string string string

location=d:\oracle\admin\sue-a rc location=d:\oracle\sue-arc

value -----------------------------defer enable enable enable enable enable enable enable enable enable

name type value ------------------------------------ ----------- -----------------------------log_archive_duplex_dest string log_archive_format string sue%s.t% log_archive_max_processes integer 8 log_archive_min_succeed_dest integer 1 log_archive_start boolean true log_archive_trace integer 0 sql> sql> sql> sql> sql> -----change log_archive_min_succeed_dest sql> sql> alter system set log_archive_min_succeed_dest=2 2 ; alter system set log_archive_min_succeed_dest=2 * error at line 1: ora-02097: parameter cannot be modified because specified value is invalid ora-16020: less destinations available than specified by log_archive_min_succeed_dest sql> alter system set log_archive_min_succeed_dest=2; alter system set log_archive_min_succeed_dest=2 * error at line 1: ora-02097: parameter cannot be modified because specified value is invalid ora-16020: less destinations available than specified by log_archive_min_succeed_dest ------because already have two dest but one dest have defer state

sql> alter system set log_archive_dest_state_1=enable; system altered. sql>

alter system set log_archive_min_succeed_dest=2;

system altered. sql> show parameter log_archive name -----------------------------------log_archive_dest log_archive_dest_1 log_archive_dest_10 log_archive_dest_2 log_archive_dest_3 log_archive_dest_4 log_archive_dest_5 log_archive_dest_6 log_archive_dest_7 log_archive_dest_8

type value ----------- -----------------------------string string location=d:\oracle\admin\sue-a rc string string location=d:\oracle\sue-arc string string string string string string

name -----------------------------------log_archive_dest_9 log_archive_dest_state_1 log_archive_dest_state_10 log_archive_dest_state_2 log_archive_dest_state_3 log_archive_dest_state_4 log_archive_dest_state_5 log_archive_dest_state_6 log_archive_dest_state_7 log_archive_dest_state_8 log_archive_dest_state_9

type ----------string string string string string string string string string string string

value -----------------------------enable enable enable enable enable enable enable enable enable enable

name type value ------------------------------------ ----------- -----------------------------log_archive_duplex_dest string log_archive_format string sue%s.t% log_archive_max_processes integer 8 log_archive_min_succeed_dest integer 2 log_archive_start boolean true log_archive_trace integer 0 sql> alter system set log_archive_dest_state_1=deffer; alter system set log_archive_dest_state_1=deffer * error at line 1: ora-00096: invalid value deffer for parameter log_archive_dest_state_1, must be from among alternate, reset, defer, enable sql> alter system set log_archive_dest_state_1=defer; alter system set log_archive_dest_state_1=defer * error at line 1:

ora-02097: parameter cannot be modified because specified value is invalid ora-16028: new log_archive_dest_state_1 causes less destinations than log_archive_min_succeed_dest requires sql> ----------because log_archive_min_succeed_dest are enable sql> sql> spool off

have two so atleast two dest