ORA-19905:- this error generate due to incorrect Archive log format in oracle Release 11.2.0.1.0.
Problem:-
SQL> startup;
ORA-19905: log_archive_format must contain %s, %t and %r
SQL> startup mount;
ORA-19905: log_archive_format must contain %s, %t and %r
Solution:
Also Solution is given in metalink note 283507.1
- create pfile from spfile using below Comand.
SQL> create pfile from spfile;
File created.
- got to oracle pfile location and edit the 'log_archive_format' parameter
[oracle@dns1 DB11G]$ cd /u01/app/oracle/product/11.2.0/db_1/dbs/
[oracle@dns1 dbs]$ vi initDB11G.ora
-----------------before update----------------------------------
DB11G.__db_cache_size=1979711488
DB11G.__java_pool_size=16777216
DB11G.__large_pool_size=16777216
DB11G.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
DB11G.__pga_aggregate_target=2097152000
DB11G.__sga_target=3154116608
DB11G.__shared_io_pool_size=0
DB11G.__shared_pool_size=1073741824
DB11G.__streams_pool_size=33554432
*.audit_file_dest='/u01/app/oracle/admin/DB11G/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oracle/oradata/DB11G/control01.ctl','/u01/app/oracle/oradata/DB11G/control02.ctl'
*.cursor_sharing='FORCE'
*.db_block_size=8192
*.db_domain=''
*.db_name='DB11G'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=DB11GXDB)'
*.log_archive_dest_1='LOCATION=/u01/Archive'
*.log_archive_format='arch_%t_%s'
*.memory_max_target=0
*.memory_target=0
*.open_cursors=1500
*.pga_aggregate_target=2097152000
*.processes=1000
*.remote_login_passwordfile='EXCLUSIVE'
*.session_cached_cursors=1000
*.sga_target=3154116608
*.timed_statistics=TRUE
*.undo_tablespace='UNDOTBS1'
--------------------After update------------------------------------
DB11G.__db_cache_size=1979711488
DB11G.__java_pool_size=16777216
DB11G.__large_pool_size=16777216
DB11G.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
DB11G.__pga_aggregate_target=2097152000
DB11G.__sga_target=3154116608
DB11G.__shared_io_pool_size=0
DB11G.__shared_pool_size=1073741824
DB11G.__streams_pool_size=33554432
*.audit_file_dest='/u01/app/oracle/admin/DB11G/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oracle/oradata/DB11G/control01.ctl','/u01/app/oracle/oradata/DB11G/control02.ctl'
*.cursor_sharing='FORCE'
*.db_block_size=8192
*.db_domain=''
*.db_name='DB11G'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=DB11GXDB)'
*.log_archive_dest_1='LOCATION=/u01/Archive'
*.log_archive_format='arch_%r_%t_%s'
*.memory_max_target=0
*.memory_target=0
*.open_cursors=1500
*.pga_aggregate_target=2097152000
*.processes=1000
*.remote_login_passwordfile='EXCLUSIVE'
*.session_cached_cursors=1000
*.sga_target=3154116608
*.timed_statistics=TRUE
*.undo_tablespace='UNDOTBS1'
[oracle@dns1 dbs]$ sqlplus
SQL*Plus: Release 11.2.0.1.0 Production on Mon Mar 3 01:32:10 2014
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Enter user-name: /as sysdba
Connected to an idle instance.
SQL> startup pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initDB11G.ora';
ORACLE instance started.
Total System Global Area 5261217792 bytes
Fixed Size 2222560 bytes
Variable Size 3271558688 bytes
Database Buffers 1979711488 bytes
Redo Buffers 7725056 bytes
Database mounted.
Database opened.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/Archive
Oldest online log sequence 760
Next log sequence to archive 762
Current log sequence 762
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
/
System altered.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/Archive
Oldest online log sequence 764
Next log sequence to archive 766
Current log sequence 766
Problem:-
SQL> startup;
ORA-19905: log_archive_format must contain %s, %t and %r
SQL> startup mount;
ORA-19905: log_archive_format must contain %s, %t and %r
Solution:
Also Solution is given in metalink note 283507.1
- create pfile from spfile using below Comand.
SQL> create pfile from spfile;
File created.
- got to oracle pfile location and edit the 'log_archive_format' parameter
[oracle@dns1 DB11G]$ cd /u01/app/oracle/product/11.2.0/db_1/dbs/
[oracle@dns1 dbs]$ vi initDB11G.ora
-----------------before update----------------------------------
DB11G.__db_cache_size=1979711488
DB11G.__java_pool_size=16777216
DB11G.__large_pool_size=16777216
DB11G.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
DB11G.__pga_aggregate_target=2097152000
DB11G.__sga_target=3154116608
DB11G.__shared_io_pool_size=0
DB11G.__shared_pool_size=1073741824
DB11G.__streams_pool_size=33554432
*.audit_file_dest='/u01/app/oracle/admin/DB11G/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oracle/oradata/DB11G/control01.ctl','/u01/app/oracle/oradata/DB11G/control02.ctl'
*.cursor_sharing='FORCE'
*.db_block_size=8192
*.db_domain=''
*.db_name='DB11G'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=DB11GXDB)'
*.log_archive_dest_1='LOCATION=/u01/Archive'
*.log_archive_format='arch_%t_%s'
*.memory_max_target=0
*.memory_target=0
*.open_cursors=1500
*.pga_aggregate_target=2097152000
*.processes=1000
*.remote_login_passwordfile='EXCLUSIVE'
*.session_cached_cursors=1000
*.sga_target=3154116608
*.timed_statistics=TRUE
*.undo_tablespace='UNDOTBS1'
--------------------After update------------------------------------
DB11G.__db_cache_size=1979711488
DB11G.__java_pool_size=16777216
DB11G.__large_pool_size=16777216
DB11G.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
DB11G.__pga_aggregate_target=2097152000
DB11G.__sga_target=3154116608
DB11G.__shared_io_pool_size=0
DB11G.__shared_pool_size=1073741824
DB11G.__streams_pool_size=33554432
*.audit_file_dest='/u01/app/oracle/admin/DB11G/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oracle/oradata/DB11G/control01.ctl','/u01/app/oracle/oradata/DB11G/control02.ctl'
*.cursor_sharing='FORCE'
*.db_block_size=8192
*.db_domain=''
*.db_name='DB11G'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=DB11GXDB)'
*.log_archive_dest_1='LOCATION=/u01/Archive'
*.log_archive_format='arch_%r_%t_%s'
*.memory_max_target=0
*.memory_target=0
*.open_cursors=1500
*.pga_aggregate_target=2097152000
*.processes=1000
*.remote_login_passwordfile='EXCLUSIVE'
*.session_cached_cursors=1000
*.sga_target=3154116608
*.timed_statistics=TRUE
*.undo_tablespace='UNDOTBS1'
[oracle@dns1 dbs]$ sqlplus
SQL*Plus: Release 11.2.0.1.0 Production on Mon Mar 3 01:32:10 2014
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Enter user-name: /as sysdba
Connected to an idle instance.
SQL> startup pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initDB11G.ora';
ORACLE instance started.
Total System Global Area 5261217792 bytes
Fixed Size 2222560 bytes
Variable Size 3271558688 bytes
Database Buffers 1979711488 bytes
Redo Buffers 7725056 bytes
Database mounted.
Database opened.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/Archive
Oldest online log sequence 760
Next log sequence to archive 762
Current log sequence 762
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
/
System altered.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/Archive
Oldest online log sequence 764
Next log sequence to archive 766
Current log sequence 766
thanks
ReplyDelete