Create Logical Standby Database

A logical standby database is created by firs tcreating a physicale standby database.
For how to do this see my blog Create physical standby database with Oracle 11gr2 from active database

The primary database is the asm451p1
The logical standby database will be the asm452p1

First stop recovery on the physical standby database asm452p1
SQL> alter database recover managed standby database cancel;

 On the primary database asm451p1 create the logminer catalog;
SQL> execute dbms_logstdby.build;
SQL> alter system switch logfile;

Now start the standby database asm452p1 with open resetlogs
SQL>alter database recover to logical standby asm452p;
SQL> alter database open resetlogs;
SQL> alter database start logical standby apply immediate;

With the apply immediate changes on the primary are send to the logical standby database
standby redologfiles; Otherwise you have to waite for a log switch to send the data.

Now you can create procedures  for instance to create datafile on the logical standby database
after is created on the primary database.

create or replace procedure sys.handle_tbs_ddl (
      old_stmt  in  varchar2
    , stmt_typ  in  varchar2
    , schema    in  varchar2
    , name      in  varchar2
    , xidusn    in  number
    , xidslt    in  number
    , xidsqn    in  number
    , action    out number
    , new_stmt  out varchar2
) as
begin
  new_stmt := replace(old_stmt, 'ASM451P', 'ASM452P');
  action := dbms_logstdby.skip_action_replace;
 
  exception
      when others then
          action := dbms_logstdby.skip_action_error;
          new_stmt := null;
end handle_tbs_ddl;
/

Now stop the logical apply process
SQL> alter database stop logical standby apply;
SQL> execute dbms_logstdby.skip(stmt => 'TABLESPACE', proc_name => 'sys.handle_tbs_ddl');
SQL> alter database start logical standby apply immediate;

Check if logical standby database is applied.

SQL> select * from dba_logstdby_progress;
READ_SCN                   READ_THREAD#                 READ_SEQUENCE# READ_TIME                     NEWEST_SCN                 NEWEST_THREAD#               NEWEST_SEQUENCE#
------------------------------ ------------------------------ ------------------------------ --------- ------------------------------ ------------------------------ ------------------------------ --------- ------------------------------ ------------------------------ ------------------------------
NEWEST_TI
---------
                   79088719872                              1                           7906 30-JUL-12                    79088718344                              1                           7906 30-JUL-12                    79088719872                              1                           7906
30-JUL-12

To see why the logical standby database is not applying anymore
SQL> select * from logstdby_events
          order by 1 desc

To skip a failed transaction.

SQL>alter database start logical standby apply immediate skip failed transaction;

When the logical standby database is running behind you can speed a things.

SQL>ALTER DATABASE STOP LOGICAL STANDBY APPLY;
SQL> execute dbms_logstdby.apply_set('MAX_SGA',500);
SQL>ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;

SQL>ALTER DATABASE STOP LOGICAL STANDBY APPLY;
SQL>EXECUTE DBMS_LOGSTDBY.APPLY_SET(‘MAX_SERVERS’,15);
SQL>ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;

Temporary tables can't be created on a logical standby. To prevent this
SQL> ALTER DATABASE STOP LOGICAL STANDBY APPLY;
SQL>EXECUTE DBMS_LOGSTDBY.SKIP (stmt => 'DML', schema_name => 'GINO', object_name => 'DBMS_TABCOMP_TEMP_UNCMP', proc_name => null);
SQL>ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;

To skip that the AUD$ get filled on the logical standby database
SQL> NOAUDIT ALL;
SQL> NOAUDIT ALL PRIVILEGES;
SQL> TRUNCATE TABLE SYS.AUD$;
SQL> ALTER DATABASE STOP LOGICAL STANDBY APPLY;
SQL> exec dbms_logstdby.skip('DML','SYS','AUD$');
SQL> exec dbms_logstdby.skip('SCHEMA_DDL','SYS','AUD$');
SQL>ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;

To temporary set off the logical standby database

On the primary database ASM451P1
SQL> alter system set log_archive_dest_state_2 = 'DEFER' scope = both;
SQL> alter system set log_archive_dest_2 = '' scope = both;

To put it on again
On the primary database ASM451P1
SQL>SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2 = 'SERVICE=ASM452P LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ASM452P REGISTER NET_TIMEOUT=30' SCOPE=BOTH;
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2 = 'ENABLE' SCOPE=BOTH;
Thats it.