Monday, 3 March 2014

ORA-19905: log_archive_format must contain %s, %t and %r

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

1 comment:

work on autovacuum postgreSQL parameter

 In This blog, we are discussing the auto vacuum parameter on a small scale. we will understand the below parameters and will see how to mod...