5 Replies Latest reply: Apr 19, 2013 6:47 AM by Mahir M. Quluzade RSS

    Creating new tables in Logical Standby database

    user8819121
      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
          Mahir M. Quluzade
          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
            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
              Mahir M. Quluzade
              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
                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
                  Mahir M. Quluzade
                  Then you must change to guard status to NONE.

                  You are welcome!