ORA-19905:- this error generate due to incorrect Archive log format in oracle Release 12.1.0.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 command
SQL> create pfile from spfile;
File created.
- got to oracle pfile location and edit the 'log_archive_format' parameter
[oracle@dns1 prmdb]$ cd /u01/app/oracle/product/12.1.0.0/db_1/dbs/
[oracle@dns1 dbs]$ vi initprmdb.ora
-----------------before update----------------------------------
prmdb.__db_cache_size=1979711488
prmdb.__java_pool_size=16777216
prmdb.__large_pool_size=16777216
prmdb.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
prmdb.__pga_aggregate_target=2097152000
prmdb.__sga_target=3154116608
prmdb.__shared_io_pool_size=0
prmdb.__shared_pool_size=1073741824
prmdb.__streams_pool_size=33554432
*.audit_file_dest='/u01/app/oracle/admin/prmdb/adump'
*.audit_trail='db'
*.compatible='12.1.0.0.0.0'
*.control_files='/u01/app/oracle/oradata/prmdb/control01.ctl','/u01/app/oracle/oradata/prmdb/control02.ctl'
*.cursor_sharing='FORCE'
*.db_block_size=8192
*.db_domain=''
*.db_name='prmdb'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=prmdbXDB)'
*.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------------------------------------
prmdb.__db_cache_size=1979711488
prmdb.__java_pool_size=16777216
prmdb.__large_pool_size=16777216
prmdb.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
prmdb.__pga_aggregate_target=2097152000
prmdb.__sga_target=3154116608
prmdb.__shared_io_pool_size=0
prmdb.__shared_pool_size=1073741824
prmdb.__streams_pool_size=33554432
*.audit_file_dest='/u01/app/oracle/admin/prmdb/adump'
*.audit_trail='db'
*.compatible='12.1.0.0.0.0'
*.control_files='/u01/app/oracle/oradata/prmdb/control01.ctl','/u01/app/oracle/oradata/prmdb/control02.ctl'
*.cursor_sharing='FORCE'
*.db_block_size=8192
*.db_domain=''
*.db_name='prmdb'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=prmdbXDB)'
*.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 12.1.0.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>create spfile from pfile;
File created.
SQL> startup
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> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/Archive
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 command
SQL> create pfile from spfile;
File created.
- got to oracle pfile location and edit the 'log_archive_format' parameter
[oracle@dns1 prmdb]$ cd /u01/app/oracle/product/12.1.0.0/db_1/dbs/
[oracle@dns1 dbs]$ vi initprmdb.ora
-----------------before update----------------------------------
prmdb.__db_cache_size=1979711488
prmdb.__java_pool_size=16777216
prmdb.__large_pool_size=16777216
prmdb.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
prmdb.__pga_aggregate_target=2097152000
prmdb.__sga_target=3154116608
prmdb.__shared_io_pool_size=0
prmdb.__shared_pool_size=1073741824
prmdb.__streams_pool_size=33554432
*.audit_file_dest='/u01/app/oracle/admin/prmdb/adump'
*.audit_trail='db'
*.compatible='12.1.0.0.0.0'
*.control_files='/u01/app/oracle/oradata/prmdb/control01.ctl','/u01/app/oracle/oradata/prmdb/control02.ctl'
*.cursor_sharing='FORCE'
*.db_block_size=8192
*.db_domain=''
*.db_name='prmdb'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=prmdbXDB)'
*.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------------------------------------
prmdb.__db_cache_size=1979711488
prmdb.__java_pool_size=16777216
prmdb.__large_pool_size=16777216
prmdb.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
prmdb.__pga_aggregate_target=2097152000
prmdb.__sga_target=3154116608
prmdb.__shared_io_pool_size=0
prmdb.__shared_pool_size=1073741824
prmdb.__streams_pool_size=33554432
*.audit_file_dest='/u01/app/oracle/admin/prmdb/adump'
*.audit_trail='db'
*.compatible='12.1.0.0.0.0'
*.control_files='/u01/app/oracle/oradata/prmdb/control01.ctl','/u01/app/oracle/oradata/prmdb/control02.ctl'
*.cursor_sharing='FORCE'
*.db_block_size=8192
*.db_domain=''
*.db_name='prmdb'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=prmdbXDB)'
*.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 12.1.0.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>create spfile from pfile;
File created.
SQL> startup
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> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/Archive