This discussion is archived
5 Replies Latest reply: Apr 19, 2013 4:47 AM by MahirM.Quluzade RSS

Creating new tables in Logical Standby database

user8819121 Newbie
Currently Being Moderated
Hi

I have a requirement to create new tables in logical standby database. These tables will not be present on primary database. Is it possible to do this ?
I have a new schema already created which has the privilege to CREATE new table.
I have stopped the logical standby apply.

When I am now trying to create a new table but it is failing with error : insufficient privileges.


When I am trying to run below statement on new schema it is also failing with error of insufficient privileges.

alter session disable dataguard;

Please help.
  • 1. Re: Creating new tables in Logical Standby database
    MahirM.Quluzade Guru
    Currently Being Moderated
    user8819121 wrote:
    Hi

    I have a requirement to create new tables in logical standby database. These tables will not be present on primary database. Is it possible to do this ?
    Yes it is possible.
    I have a new schema already created which has the privilege to CREATE new table.
    Yes you must create a new user, which has the privilege to CREATE TABLE.
    I have stopped the logical standby apply.
    It is not need, stay Apply service running
    >
    When I am now trying to create a new table but it is failing with error : insufficient privileges.
    >
    >
    When I am trying to run below statement on new schema it is also failing with error of insufficient privileges.

    alter session disable dataguard;
    Hi,

    Can you try folowing steps ?


    Connect database as sysdba
    SQL> alter database stop logical standby  apply;
    SQL> alter session disable guard;
    
    SQL> create table <yur username>.<tablename> ...
    
    SQL> alter session enable guard;
    SQL> alter database start logical standby  apply;
    Regards
    Mahir M. Quluzade
  • 2. Re: Creating new tables in Logical Standby database
    user8819121 Newbie
    Currently Being Moderated
    Thanks Mahir,

    I was able to create the table after logging in as sysdba.
    But I need my user on that table to execute DML statements. My user has privileges to insert,delete and update any table.

    I tried the following statements to disable the guard but it is sill not working

    ALTER DATABASE GUARD STANDBY.

    Do I need to skip the tables created using dbms_logstdby package to not making it part of SQL Apply ? I guess not since the table is not in primary database.


    Regards
    Amit
  • 3. Re: Creating new tables in Logical Standby database
    MahirM.Quluzade Guru
    Currently Being Moderated
    user8819121 wrote:
    Thanks Mahir,

    I was able to create the table after logging in as sysdba.
    But I need my user on that table to execute DML statements. My user has privileges to insert,delete and update any table.

    I tried the following statements to disable the guard but it is sill not working

    ALTER DATABASE GUARD STANDBY.

    Do I need to skip the tables created using dbms_logstdby package to not making it part of SQL Apply ? I guess not since the table is not in primary database.
    Amit

    You can skip only on primary, your created schema on Standby is not in primary.

    Then you must change Status of data guard to NONE. NONE is means is not any security on your data.
    In Guard status NONE can change all schema data.

    Please check link: http://docs.oracle.com/cd/E11882_01/server.112/e10700/manage_ls.htm#CHDGFGHG

    Following tests on user created before guard status is change from ALL to STANDBY.
    C:\Users\Administrator>sqlplus / as sysdba
    
    SQL> conn test/test
    Connected.
    SQL> select table_name from user_tables;
    
    TABLE_NAME
    ------------------------------
    T
    
    SQL> insert into t values(22);
    insert into t values(22)
                *
    ERROR at line 1:
    ORA-16224: Database Guard is enabled
    
    
    SQL> conn / as sysdba
    Connected.
    SQL> select guard_Status from  v$database;
    
    GUARD_S
    -------
    ALL
    
    SQL> alter  database guard standby;
    
    Database altered.
    
    SQL> conn test/test
    Connected.
    
    SQL> insert into t values(1);
    insert into t values(1)
                *
    ERROR at line 1:
    ORA-16224: Database Guard is enabled
    
    
    SQL> conn / as sysdba
    Connected.
    SQL> select guard_Status from  v$database;
    
    GUARD_S
    -------
    STANDBY
    
    SQL> alter  database guard none;
    
    Database altered.
    
    SQL> select guard_Status from  v$database;
    
    GUARD_S
    -------
    NONE
    
    SQL> conn test/test
    Connected.
    SQL> insert into t values(1);
    
    1 row created.
    
    SQL> commit;
    
    Commit complete.
    And Now I want share with you new tests :)

    Now user creating when after guard status change
    SQL> drop  user test cascade;
    
    User dropped.
    
    SQL> select guard_status from v$database;
    
    GUARD_S
    -------
    STANDBY
    
    SQL> create user test identified by test;
    
    User created.
    
    SQL> grant create session,  resource, create table to test;
    
    Grant succeeded.
    
    SQL> conn test/test
    Connected.
    
    SQL> create table t (n number);
    
    Table created.
    
    SQL> insert into t values(1);
    
    1 row created.
    
    SQL> commit;
    
    Commit complete.
    
    SQL>
    It means when guard status is ALL then all of user created guarding.
    When you changed status to STANDBY then Logical Standby guard only primary schema and created schema before change.
    NONE is not guard any schema. it means you can delete standby schema data too.


    Regards
    Mahir M. Quluzade

    Edited by: Mahir M. Quluzade on Apr 19, 2013 4:07 PM
  • 4. Re: Creating new tables in Logical Standby database
    user8819121 Newbie
    Currently Being Moderated
    Thanks a lot Mahir,

    it worked and I am not only able to create new tables but also update existing tables

    amit
  • 5. Re: Creating new tables in Logical Standby database
    MahirM.Quluzade Guru
    Currently Being Moderated
    Then you must change to guard status to NONE.

    You are welcome!

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points