DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: Creating a new schema in a Logical Standby Database

  1. #1
    Join Date
    Feb 2008
    Posts
    2

    Creating a new schema in a Logical Standby Database

    Hi All,
    I am experimenting with logical standby databases for the purpose of reporting, and have not been able to create a new schema in the logical standby database - one of the key features of logical standbys.

    I have setup primary and logical standby databases, and they seem to be running just fine - changes are moved from the primary to the standby and queries on the standby seem to run ok.

    However, If I try to create a new schema on the logical standby, that does not exist on the primary, I get "ORA-01031: insufficient privileges" errors when I try to create new objects.

    Show below are the steps I have taken to create the new schema on the logical standby. Any help would be appreciated.

    SYS@UATDR> connect / as sysdba
    Connected.
    SYS@UATDR>
    SYS@UATDR> select name, log_mode, database_role, guard_status, force_logging, flashback_on, db_unique_name
    2 from v$database
    3 /

    NAME LOG_MODE DATABASE_ROLE GUARD_S FOR FLASHBACK_ON DB_UNIQUE_NAME
    --------- ------------ ---------------- ------- --- ------------------ ------------------------------
    UATDR ARCHIVELOG LOGICAL STANDBY ALL YES YES UATDR

    SYS@UATDR>
    SYS@UATDR> create tablespace ts_gav
    2 /

    Tablespace created.

    SYS@UATDR>
    SYS@UATDR> create user gav
    2 identified by gav
    3 default tablespace ts_gav
    4 temporary tablespace temp
    5 quota unlimited on ts_gav
    6 /

    User created.

    SYS@UATDR>
    SYS@UATDR> grant connect, resource to gav
    2 /

    Grant succeeded.

    SYS@UATDR> grant unlimited tablespace, create table, create any table to gav
    2 /

    Grant succeeded.

    SYS@UATDR>
    SYS@UATDR> -- show privs given to gav
    SYS@UATDR> select * from dba_sys_privs where grantee='GAV'
    2 /

    GRANTEE PRIVILEGE ADM
    ------------------------------ ---------------------------------------- ---
    GAV CREATE TABLE NO
    GAV CREATE ANY TABLE NO
    GAV UNLIMITED TABLESPACE NO

    SYS@UATDR>
    SYS@UATDR> -- create objects in schema
    SYS@UATDR> connect gav/gav
    Connected.
    GAV@UATDR>
    GAV@UATDR> -- prove ability to create tables
    GAV@UATDR> create table gav
    2 (col1 number not null)
    3 tablespace ts_gav
    4 /
    create table gav
    *
    ERROR at line 1:
    ORA-01031: insufficient privileges


    GAV@UATDR>
    GAV@UATDR>

  2. #2
    Join Date
    Jan 2001
    Posts
    2,828
    Hi

    I have not done this but if i remember correctly then you need to first do this for any DDL.Remember datagaurd is for reporting and for what you are trying to i can only think of this way

    Code:
    ALTER DATABASE STOP LOGICAL STANDBY APPLY;
    ALTER SESSION DISABLE GUARD;
    
    create table gav.gav
    (col1 number not null)
    tablespace ts_gav
    
    ALTER SESSION ENABLE GUARD;
    
    ALTER DATABASE START LOGICAL STANDBY APPLY;

  3. #3
    Join Date
    Jan 2001
    Posts
    2,828
    Hi

    I searched the docs

    Code:
    SQL> ALTER DATABASE STOP LOGICAL STANDBY APPLY;
    Database altered.
    
    SQL> EXECUTE DBMS_LOGSTDBY.SKIP(stmt => 'SCHEMA_DDL',-
         schema_name => 'HR', -
         object_name => 'TESTEMP%');
    PL/SQL procedure successfully completed.
    
    SQL> EXECUTE DBMS_LOGSTDBY.SKIP('DML','HR','TESTEMP%');
    PL/SQL procedure successfully completed.
    
    SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;
    Database altered.
    http://download.oracle.com/docs/cd/B...htm#sthref1301

    regards
    Hrishy

  4. #4
    Join Date
    Feb 2008
    Posts
    2
    Hi Hrishy,
    Thanks heaps for your reply - it has worked perfectly.

    Thanks
    Gavin

  5. #5
    Join Date
    Mar 2008
    Posts
    3

    can logical standby have different physical structure

    gavingeorge/hrishy,

    You confirmed that you can create tablespaces in logical standby without impact the resync with primary.
    Can we have different table structure in standby? say more partitions..

    I have a situation like this,

    1)Primay only keep 6 month online data
    2)Data purge every end of month

    Can I use logical standby as an archvied database by

    1)create additional partitioning tablespaces and move historical data into these tablespace.
    2)blackout purge DML by using DBMS_LOGSTDBY.SKIP

    Thanks in advance!
    Sandy

  6. #6
    Join Date
    Jan 2001
    Posts
    2,828
    Hi

    You should have ideally started a new thread.

    Yes its possible to do what you are asking for.

    You need to write a custom procedure

    [code]
    alter database stop logical standby apply;
    execute dbms_logstdby.skip('alter table','myuser','my_table','sys.my_skip_handler_routine');
    alter database start logical standby apply;
    [code]

    my_skip_handler_rotine should do either of
    a)should trap the 'drop partition' and set the value for
    skip_action to sys.dbms_logstdby.SKIP_ACTION_SKIP;
    b)should trap the 'alter partition' and set the value for
    skip_action to sys.dbms_logstdby.SKIP_ACTION_APPLY;

    Note everything should be executed as SYS user and prefixed as SYS otherwise it will not work

    regards
    Hrishy

  7. #7
    Join Date
    Mar 2008
    Posts
    3
    Thanks for your help, Hrishy!

    We will do some POC of this and see how it goes.

  8. #8
    Join Date
    Mar 2008
    Posts
    3
    Thanks for your help, Hrishy!

    We will do some POC of this and see how it goes.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width