Home » Server Options » Data Guard » Schema Import on a logical standby db
Schema Import on a logical standby db [message #240799] Fri, 25 May 2007 18:51 Go to next message
tmathew
Messages: 18
Registered: May 2007
Junior Member
Friends,

has anyone done a schema import on a logical standby?

Here is the scenario,

have a schema on dev environment manily for reporting purpose and I need to import this into logical standby of a primary production db.

Here is what I tried,

1) created seprate tbl spaces on logical standby
2) created the new schema user
3) granted following previleges to this new schema
CREATE SESSION
CREATE TABLE
SELECT ANY TABLE
CREATE SYNONYM
CREATE VIEW
CREATE SEQUENCE
CREATE PROCEDURE
CREATE TRIGGER
CREATE TYPE
CREATE OPERATOR
CREATE INDEXTYPE
4) CREATE TABLE test (ID VARCHAR2(10), MB_ID VARCHAR2(25) NOT NULL ENABLE)
CREATE TABLE test (ID VARCHAR2(10), MB_ID VARCHAR2(25) NOT NULL ENABLE)
*

ERROR at line 1:
ORA-01031: insufficient privileges

I've given quota unlimted for most of the tbl spaces. I'm little hesitant to grant dba role to this user.


any idea what's the issue here?


Thanks,

Mathew


Re: Schema Import on a logical standby db [message #240800 is a reply to message #240799] Fri, 25 May 2007 19:04 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
DID you take expdp or exp?
ru trying to imp it through the user which you just reacently created??
can you paste your imp command?


try this give imp_full_database privevge to the user you created and then try to imp.

[Updated on: Fri, 25 May 2007 19:31]

Report message to a moderator

Re: Schema Import on a logical standby db [message #240804 is a reply to message #240799] Fri, 25 May 2007 22:09 Go to previous messageGo to next message
tmathew
Messages: 18
Registered: May 2007
Junior Member
Thanks Dreams for your quick response!

DID you take expdp or exp?
I did use exp
exp userid=user_new/password file=user_schema052407.dmp log=user_schema052407.log statistics=none

ru trying to imp it through the user which you just reacently created??
can you paste your imp command?

Yes,
imp userid=user_new/password@db1 file=port_schema052407.dmp log=port_schema.log statistics=none



try this give imp_full_database privevge to the user you created and then try to imp.

I haven't given imp_full_database previlage.But new schema user not able to create any table through sqlplus and getting following error.

CREATE TABLE test (ID VARCHAR2(10), MB_ID VARCHAR2(25) NOT NULL ENABLE)
*

ERROR at line 1:
ORA-01031: insufficient privileges
[/I]

don't think i can do the import if i can't fix the above error.


Thanks,

Mathew
Re: Schema Import on a logical standby db [message #240805 is a reply to message #240799] Fri, 25 May 2007 22:25 Go to previous messageGo to next message
tmathew
Messages: 18
Registered: May 2007
Junior Member
But can query the database as this new schema user. I suspect datagurd is protecting the new schema which i creted. i wanted to try ALTER SESSION DISABLE DATA GUARD but not sure what previlage I need to grant for this new schema in order to run that command

Mathew
Re: Schema Import on a logical standby db [message #240815 is a reply to message #240805] Sat, 26 May 2007 00:47 Go to previous message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
YES FIRST DISABLE THEN TRY DESE COMMANDS..
EXPORT_FULL_DATABASE
OR BY USING SYS ENTER THIS COMMAND


FROMUSER ...TOUSER

[Updated on: Sat, 26 May 2007 00:48]

Report message to a moderator

Previous Topic: 12TB Data Guard Standby on a Wide Area Network
Next Topic: Oracle 9i Missing Conrtol and Redolog files
Goto Forum:
  


Current Time: Fri Jul 25 04:51:35 CDT 2014

Total time taken to generate the page: 0.09760 seconds