Wednesday 5 March 2014

how to recover System table space After lost/corrupt?

In the following cases, it is assumed that database is running in archive log
mode.I explain all steps before and after file lost or Corrupt.

[oracle@dns1 ~]$ sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Tue Mar 4 23:19:18 2014

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Enter user-name: /as sysdba

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select name from v$database;

NAME
---------
DB11G

SQL> shut immediate;
Database closed.
Database dismounted.

[oracle@dns1 DB11G]$ cd /u01/app/oracle/oradata/DB11G/

[oracle@dns1 DB11G]$ ls
control01.ctl  control02.ctl  redo01.log  redo02.log  redo03.log  sysaux01.dbf  system01.dbf  temp01.dbf  ts_ing_ecs01  undotbs01.dbf  users01.dbf

-- I am taking system file backup trough OS command.

[oracle@dns1 DB11G]$ cp system01.dbf  /u01/Archive/

[oracle@dns1 DB11G]$ sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Tue Mar 4 23:21:22 2014

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Enter user-name: /as sysdba
Connected to an idle instance.

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> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /

System altered.
SQL> shut abort;
ORACLE instance shut down.
SQL> !
[oracle@dns1 DB11G]$ rm system01.dbf
[oracle@dns1 DB11G]$ ls
control01.ctl  control02.ctl  redo01.log  redo02.log  redo03.log  sysaux01.dbf  temp01.dbf  ts_ing_ecs01  undotbs01.dbf  users01.dbf

[oracle@dns1 DB11G]$ exit
exit

SQL> startup mount;
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.
SQL> alter database rename file '/u01/app/oracle/oradata/DB11G/system01.dbf' to '/u01/Archive/system01.dbf'; ---rename lost file

Database altered.

SQL> recover datafile 1;
ORA-00279: change 9369328 generated at 03/04/2014 23:20:14 needed for thread 1
ORA-00289: suggestion : /u01/Archive/arch_837387824_1_771
ORA-00280: change 9369328 for thread 1 is in sequence #771


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto             ---recover using AUTO option.
Log applied.
Media recovery complete.
SQL> alter database open;

Database altered.

SQL> select name,OPEN_MODE from v$database;

NAME      OPEN_MODE
--------- --------------------
DB11G     READ WRITE

===========================END=======================================

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

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