-
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>
-
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;
-
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
-
Hi Hrishy,
Thanks heaps for your reply - it has worked perfectly.
Thanks
Gavin
-
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
-
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
-
Thanks for your help, Hrishy!
We will do some POC of this and see how it goes.
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|