What will happen if you lost all online log files...

I know you know what will happen if you lost one or all your online redo files.
Always multiplex your Redo logs (controlfile too) and store it several partitions.
So, one of student did not it and requested to me what to do. For him I prepare this tutorial.:)
If your Redo log files are multiplexed and you lost one of the member of redo file do not panic, just copy another member of same group and paste it with lost member place.
Suppose you have 3 groups (redo1, redo2, redo3) and all group has three members (redo1a, redo1b, redo1c, ...) If you lost redo1a.log just take redo1b.log and copy it where should be redo1a.log and do not forget to rename it to redo1a.log.
/u01/oradata/mydb/
redo1a.log redo2a.log redo3a.log
/u02/oradata/mydb/
redo1b.log redo2b.log redo3b.log
/u03/oradata/mydb/      
redo1c.log redo2c.log redo3c.log

If your redo logs were not multiplexed and you lost redo files then follow me:

[oracle@localhost ~]$ hostname
localhost.localdomain
[oracle@localhost ~]$ uname -a
Linux localhost.localdomain 2.6.18-164.el5 #1 SMP Thu Sep 3 03:33:56 EDT 2009 i686 i686 i386 GNU/Linux

[oracle@localhost ARCH]$ sql

SQL*Plus: Release 10.2.0.4.0 - Production on Mon Jul 16 08:09:45 2012

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

SQL> select * from v$version;

BANNER

----------------------------------------------------------------

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod

PL/SQL Release 10.2.0.4.0 - Production

CORE    10.2.0.4.0      Production

TNS for Linux: Version 10.2.0.4.0 - Production

NLSRTL Version 10.2.0.4.0 - Production

SQL> select name from v$database;

NAME

---------

MYDB

SQL> select group#, member from v$logfile order by 1;

    GROUP#   MEMBER
-------------  -----------------------------------------------------------------
         1           /home/oracle/oracle/product/10.2.0/db_1/oradata/mydb/redo01.log
         2           /home/oracle/oracle/product/10.2.0/db_1/oradata/mydb/redo02.log
         3           /home/oracle/oracle/product/10.2.0/db_1/oradata/mydb/redo03.log

SQL> alter system switch logfile;


System altered.



SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /home/oracle/oracle/product/10.2.0/db_1/oradata/mydb/ARCH
Oldest online log sequence     2
Next log sequence to archive   4
Current log sequence           4
SQL>

[oracle@localhost mydb]$ rm redo*.log

Check alert log

[oracle@localhost bdump]$ tail alert_mydb.log 

ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/home/oracle/oracle/product/10.2.0/db_1/oradata/mydb/redo01.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Mon Jul 16 08:49:21 2012
Errors in file /home/oracle/oracle/product/10.2.0/db_1/admin/mydb/bdump/mydb_arc1_4568.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/home/oracle/oracle/product/10.2.0/db_1/oradata/mydb/redo01.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Mon Jul 16 08:49:21 2012
ARCH: Archival stopped, error occurred. Will continue retrying
Mon Jul 16 08:49:21 2012
ORACLE Instance mydb - Archival Error
Mon Jul 16 08:49:21 2012
ORA-16038: log 1 sequence# 4 cannot be archived
ORA-00312: online log 1 thread 1: '/home/oracle/oracle/product/10.2.0/db_1/oradata/mydb/redo01.log'
Mon Jul 16 08:49:21 2012
Errors in file /home/oracle/oracle/product/10.2.0/db_1/admin/mydb/bdump/mydb_arc1_4568.trc:
ORA-16038: log 1 sequence# 4 cannot be archived
ORA-00312: online log 1 thread 1: '/home/oracle/oracle/product/10.2.0/db_1/oradata/mydb/redo01.log'
Mon Jul 16 08:50:11 2012
Errors in file /home/oracle/oracle/product/10.2.0/db_1/admin/mydb/bdump/mydb_arc1_4568.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/home/oracle/oracle/product/10.2.0/db_1/oradata/mydb/redo01.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Mon Jul 16 08:50:11 2012
Errors in file /home/oracle/oracle/product/10.2.0/db_1/admin/mydb/bdump/mydb_arc1_4568.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/home/oracle/oracle/product/10.2.0/db_1/oradata/mydb/redo01.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3

SQL> select * from v$Log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS

---------- ---------- ---------- ---------- ---------- --- ----------------

FIRST_CHANGE# FIRST_TIM

------------- ---------

         1          1          4   52428800          1 NO  ACTIVE

       527044 16-JUL-12

         2          1          5   52428800          1 NO  CURRENT

       527110 16-JUL-12

         3          1          3   52428800          1 YES INACTIVE

       526520 16-JUL-12

SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)
--------------
             5


Restart database (of course database will not open and will stay in mount stage:) )

SQL> startup force;
ORACLE instance started.

Total System Global Area  285212672 bytes
Fixed Size                  1267068 bytes
Variable Size              92277380 bytes
Database Buffers          188743680 bytes
Redo Buffers                2924544 bytes
Database mounted.
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1:
'/home/oracle/oracle/product/10.2.0/db_1/oradata/mydb/redo01.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3

[oracle@localhost ARCH]$ ls -lrt

total 336

-rw-r----- 1 oracle oinstall 338432 Jul 16 08:46 arch__1_3_788775848.arc

[oracle@localhost ARCH]$ 

Before this I already took whole backup of database and archivelogs. Now using backup I try to restore.

[oracle@localhost ARCH]$ rman target /

Recovery Manager: Release 10.2.0.4.0 - Production on Mon Jul 16 08:56:41 2012

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

connected to target database: MYDB (DBID=2703699908, not open)

RMAN> run
2> {
3> set until sequence 4;
4> restore database;
5> recover database;
6> }

executing command: SET until clause
using target database control file instead of recovery catalog

Starting restore at 16-JUL-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /home/oracle/oracle/product/10.2.0/db_1/oradata/mydb/system01.dbf
restoring datafile 00002 to /home/oracle/oracle/product/10.2.0/db_1/oradata/mydb/undotbs01.dbf
restoring datafile 00003 to /home/oracle/oracle/product/10.2.0/db_1/oradata/mydb/sysaux01.dbf
restoring datafile 00004 to /home/oracle/oracle/product/10.2.0/db_1/oradata/mydb/users01.dbf
restoring datafile 00005 to /home/oracle/oracle/product/10.2.0/db_1/oradata/mydb/example01.dbf
channel ORA_DISK_1: reading from backup piece /flash_recovery_area/MYDB/backupset/2012_07_16/o1_mf_nnndf_FULL_BACKUP_16072012_8073026n_.bkp
channel ORA_DISK_1: restored backup piece 1
piece handle=/flash_recovery_area/MYDB/backupset/2012_07_16/o1_mf_nnndf_FULL_BACKUP_16072012_8073026n_.bkp tag=FULL_BACKUP_16072012
channel ORA_DISK_1: restore complete, elapsed time: 00:00:26
Finished restore at 16-JUL-12

Starting recover at 16-JUL-12
using channel ORA_DISK_1

starting media recovery

archive log thread 1 sequence 3 is already on disk as file /home/oracle/oracle/product/10.2.0/db_1/oradata/mydb/ARCH/arch__1_3_788775848.arc
channel ORA_DISK_1: starting archive log restore to default destination
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=2
channel ORA_DISK_1: reading from backup piece /flash_recovery_area/MYDB/backupset/2012_07_16/o1_mf_annnn_TAG20120716T083556_807310q0_.bkp
channel ORA_DISK_1: restored backup piece 1
piece handle=/flash_recovery_area/MYDB/backupset/2012_07_16/o1_mf_annnn_TAG20120716T083556_807310q0_.bkp tag=TAG20120716T083556
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
archive log filename=/home/oracle/oracle/product/10.2.0/db_1/oradata/mydb/ARCH/arch__1_2_788775848.arc thread=1 sequence=2
archive log filename=/home/oracle/oracle/product/10.2.0/db_1/oradata/mydb/ARCH/arch__1_3_788775848.arc thread=1 sequence=3
media recovery complete, elapsed time: 00:00:03
Finished recover at 16-JUL-12

RMAN>

Try to open database (but you have to open it with resetlogs option!)


RMAN> alter database open;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 07/16/2012 08:59:24
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

RMAN>

I have said! :)

RMAN> alter database open resetlogs;

database opened

RMAN> 

Check files.

[oracle@localhost mydb]$ ls -lrt redo0*

-rw-r----- 1 oracle oinstall 52429312 Jul 16 08:59 redo03.log
-rw-r----- 1 oracle oinstall 52429312 Jul 16 08:59 redo02.log
-rw-r----- 1 oracle oinstall 52429312 Jul 16 09:00 redo01.log
[oracle@localhost mydb]$ 


That`s all. Even now make redolog multiplex and of course after restore/recover (open resetlog) take whole backup of your database!

Oracle Streams - replication data on schema base

Oracle Streams is one of the solution to replication. Oracle has several solutions : Replication, Golden Gate, Streams. Streams has a lot of advantages, one and main is data can be replicate between Oracle to non Oracle services. Brief info about streams.

Oracle Streams captures database changes at a source database, stages the changes, propagates the changes to one or more destination databases, and then applies the changes at the destination database(s). Using Oracle Streams, enterprise systems can capture, propagate, and apply information as follows:

• Within an Oracle database
• Between two Oracle databases
• Among multiple Oracle databases
• Between an Oracle database and a non-Oracle database

How Streams Works

Oracle Streams begins by capturing changes. The changes (to data, tables, schemas, and so on) that take place in a database are recorded in the database redo log files. The Streams capture process extracts these changes from the redo log files and formats each change into a logical change record (LCR). The LCRs are then stored in a queue (staged). Next, Streams propagates LCRs from one queue (the producer queue) to another (the consumer queue) and can then apply (or consume) the LCRs from the consumer queue to the destination database.

In this tutorial I will show you how stream works on base one database between schema. Oracle version 10.2.x:

Objectives:  Replicate HR.EMPLOYEES table with DEMO.EMP in the same database.



Note: Database should be work on ARCHIVELOG mode and global_names value should be TRUE

SQL> alter system set global_names = TRUE scope=both;

1.  Prepare source database parameters

    
2.  Create Streams Administrator          

SQL> create tablespace streams_tbs datafile '/home/oracle/oracle/product/10.2.0/oradata/mydb/streams_tbs01.dbf' size 25M autoextend on maxsize unlimited;

Tablespace created.

SQL>  grant dba to strmadmin identified by streams;

Grant succeeded.

SQL>  alter user strmadmin default tablespace streams_tbs quota unlimited on streams_tbs;

User altered.

SQL>

SQL> exec dbms_streams_auth.grant_admin_privilege(grantee  => 'strmadmin', grant_privileges => true);

PL/SQL procedure successfully completed.

SQL> 

   

3.  Prepare HR.EMPLOYEES and DEMO.EMP objects.

SQL> grant dba to demo identified by demo;    


Grant succeeded.
   

 SQL> create table DEMO.EMP as select * from HR.EMPLOYEES;


Table created.

 Add constraint emp_emp_id_pk primary key (employee_id);

SQL> alter table DEMO.EMP add constraint emp_emp_id_pk primary key (employee_id);


Table altered.

And give select permission to demo user

SQL > grant select on hr.employees to demo

4.  Setup staging QUEUE

   

SQL> begin

      dbms_streams_adm.set_up_queue (

           queue_table => 'strmadmin.streams_queue_table',

           queue_name => 'strmadmin.streams_queue');

    end;

    /  2    3    4    5    6  

PL/SQL procedure successfully completed.

SQL> select name, queue_table from dba_queues where owner='STRMADMIN';

NAME                                        QUEUE_TABLE

------------------------------                 ------------------------------

STREAMS_QUEUE                        STREAMS_QUEUE_TABLE

AQ$_STREAMS_QUEUE_TABLE_E    STREAMS_QUEUE_TABLE

SQL> set linesize 150

col rule_owner for a10

SQL> select rule_owner, streams_type, streams_name, rule_set_name, rule_name from dba_streams_rules;


RULE_OWNER STREAMS_TYP  STREAMS_NAME                         RULE_SET_NAME                  RULE_NAME
----------          -----------         ------------------------------                 ------------------------------ ------------------------------
SYS               CAPTURE       CAPTURE_EMP                             RULESET$_13                    EMPLOYEES12

SQL> 

5.  Define the CAPTURE process and associated rules.

SQL> BEGIN
 dbms_streams_adm.add_table_rules(
   table_name  => 'HR.EMPLOYEES',
   streams_type  => 'CAPTURE',
   streams_name  => 'CAPTURE_EMP',
   queue_name  => 'STRMADMIN.STREAMS_QUEUE',
   include_dml  => TRUE,
   include_ddl  => FALSE,
   inclusion_rule => TRUE);
END;
    /  2    3    4    5    6    7    8    9   10   11  


PL/SQL procedure successfully completed.


SQL> 

SQL> select capture_name, rule_set_name,capture_user from dba_capture;


CAPTURE_NAME                   RULE_SET_NAME         CAPTURE_USER
------------------------------          ------------------------------ ------------------------------
CAPTURE_EMP                     RULESET$_13             SYS


SQL> 

-- Optionally, we can include extra attributes in the capture streams.

       

SQL> BEGIN
        DBMS_CAPTURE_ADM.INCLUDE_EXTRA_ATTRIBUTE(
             capture_name   => 'CAPTURE_EMP',
             attribute_name => 'USERNAME',
             include  => true);
        END;
    /  2    3    4    5    6    7  


PL/SQL procedure successfully completed.


SQL> 

6.  Tell Oracle where to start the catpure.  Instantiate the scn for hr.employees.

SQL> select source_object_owner, source_object_name, instantiation_scn from dba_apply_instantiated_objects;


SOURCE_OBJECT_OWNER            SOURCE_OBJECT_NAME             INSTANTIATION_SCN
------------------------------                ------------------------------               -----------------
HR                                          EMPLOYEES                              1591945


SQL> 

SQL>  conn strmadmin/streams

Connected.

    

SQL> DECLARE

         iscn  NUMBER;

         BEGIN

               iscn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_nUMBER();

               DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN(

                      source_object_name => 'HR.EMPLOYEES',

                      source_database_name => 'MYDB',

                      instantiation_scn => iscn);

         END;

    /  2    3    4    5    6    7    8    9   10  

PL/SQL procedure successfully completed.

SQL> 

7.  Create the APPLY process.

SQL>  conn strmadmin/streams

Connected.

SQL> SET SERVEROUTPUT ON

DECLARE

 emp_rule_name_dml VARCHAR2(30);

 emp_rule_name_ddl VARCHAR2(30);

BEGIN

 DBMS_STREAMS_ADM.ADD_TABLE_RULES(

   table_name   => 'hr.employees',

   streams_type   => 'apply',

   streams_name   => 'apply_emp',

   queue_name   => 'strmadmin.streams_queue',

   include_dml   => true,

   include_ddl   => false,

   source_database  => 'MYDB',

   dml_rule_name  => emp_rule_name_dml,

   ddl_rule_name  => emp_rule_name_ddl);

        DBMS_OUTPUT.PUT_LINE('DML rule name: '||emp_rule_name_dml);

        DBMS_OUTPUT.PUT_LINE('DDL rule name: '||emp_rule_name_ddl);

END;

/SQL>   2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18   19  

DML rule name: "STRMADMIN"."EMPLOYEES20"

DDL rule name:

PL/SQL procedure successfully completed.

SQL> 

--We don't want to stop applying changes when there is an error, so:

SQL> BEGIN

 DBMS_APPLY_ADM.SET_PARAMETER(

   apply_name => 'apply_emp',

   parameter => 'disable_on_error',

   value => 'n');

END;

/  2    3    4    5    6    7  

PL/SQL procedure successfully completed.

SQL> 

--Tranform the CAPTURED event.

--Find the APPLY rule name and change the rule declarativly.

   
SQL> SELECT a.apply_name, a.rule_set_name, r.rule_owner, r.rule_name
FROM   dba_apply a, dba_streams_rules r
WHERE  a.rule_set_name=r.rule_set_name;  2    3  


APPLY_NAME              RULE_SET_NAME        RULE_OWNER    RULE_NAME
------------------------------ ------------------------------ --------------------- ------------------------------
APPLY_EMP                RULESET$_21             STRMADMIN      EMPLOYEES20


SQL>    

take rule_name of the above select and put below

SQL> BEGIN

DBMS_STREAMS_ADM.RENAME_TABLE(

   rule_name   => 'STRMADMIN.EMPLOYEES20',

   from_table_name => 'HR.EMPLOYEES',

    to_table_name  => 'DEMO.EMP',

   operation  => 'ADD');  -- can be ADD or REMOVE

END;

/  2    3    4    5    6    7    8  

PL/SQL procedure successfully completed.

SQL> 

8.  Turn on the apply process:

    

 SQL>BEGIN

DBMS_APPLY_ADM.START_APPLY(apply_name => 'apply_emp');

 END;

 /

9.  Turn on the capture process:

 SQL> BEGIN
 DBMS_CAPTURE_ADM.START_CAPTURE(capture_name => 'capture_emp');
END;
/  2    3    4  


PL/SQL procedure successfully completed.


SQL> 

Check if hr`s user password was changed or account locked: 
select account_status from dba_users where username='HR'

alter user hr identified by hr;

alter user hr account unlock;

10. Test

SQL> select count(1) from hr.employees;


  COUNT(1)
----------
  107
SQL> 


 AS HR user add a row to the employees table

SQL> conn hr/hr

Connected.

SQL> insert into employees values 

(300,'Ulfet','Tanriverdiyev','teulfet@azercell.com','123456789',sysdate,'IT_PROG',30000,0,103,60); 

1 row created.

SQL> commit;

Commit complete.

SQL> 

As DEMO user check the EMP table.

SQL> select employee_id, first_name, email, job_id from emp where employee_id=300;


EMPLOYEE_ID   FIRST_NAME       EMAIL                        JOB_ID
-------------------- --------------------   -------------------------        ----------
300                 Ulfet                teulfet@azercell.com   IT_PROG


SQL> 

Data captured, propagated and finally applied. 
P.S: Possible data not immediately applied, please to be patient :) Next time I will show you using streams technique between databases.

Good luck :)