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

The instance '****.**.**:3306' cannot be added to an InnoDB cluster because it has asynchronous (source-replica) replication channel(s) configured. MySQL InnoDB Cluster

  Error :    MySQL  JS > dba.checkInstanceConfiguration("root@192.*.*.*")  NOTE: Found unexpected replication channel '...