Created Two Databases On Same Server With Same Schema
Apr 21, 2011
Using suppress triggers options in golden gate. I was trying to work on the same but it doesn't seem to work at least for me.
Scenario:
Database Version 11.2.0.2
GoldenGate v11
OS Windows
Created two databases on the same server with same schmea idea was to replicate change from one database to other. One of the tables in the source database had trigger(every insertion in the table would cause a row to be inserted into a diff table). if row was inserted in table A it will insert a new row in table B . the same records will pass on target but the trigger defined on table A should not trigger on target as it has already fired on Source .
I tried using suppresstriggers but it doesnt seem to work everytime the trigger is getting fired on target database.
CODEReplicat process
replicat repname
assumetargetdefs
userid gguser@bbb password Oracle
dboptions suppresstriggers
map x.* ,target x.* (since same schema)
The extract files,pump files and replication are working fine.I also used the dbms_goldengate_auth.grant_admin_privilege for gguser.
Also I noticed when I give the dboptions suppresstriggers replication process doesnt startup with unrecognised word message when i give dboptions suppress triggers the replicat process does start is this the way it should be.i have gone through the docs and it should be supresstriggers.
View 3 Replies
ADVERTISEMENT
Jun 6, 2012
I need a clarification on the below query:
1) DROP USER MK CASCADE;
2) Created user
3) Created objects like procedure,index... and granted privileges.
4) Now i am performing the import as below.
impdp system/.... SCHEMAS=MK DIRECTORY=EXPBKUP DUMPFILE=ABC_Export.dmp LOGFILE=ABC_imp.log INCLUDE=TABLE TABLE_EXISTS_ACTION=REPLACE
But nothing is imported.
Is this the problem of the parameter "INCLUDE=TABLE TABLE_EXISTS_ACTION=REPLACE"? as the user is new.
View 5 Replies
View Related
Feb 14, 2013
Is there a way to find when was a database role created and who has created?
View 5 Replies
View Related
Aug 8, 2012
I have a task assigned to "grant select on sys.link$" to a user which exists on 500 different databases.
This needs to be done in Oracle database.
View 6 Replies
View Related
Oct 3, 2011
I am using Oracle 11g Release 11.2.0.1.0
OS: Windows
I am using the Employee table in Scott schema which created by default.
10,20,30,40,50 are the department ids.
I would like to have the output like below. I am having lots of values in dep id and lots of employees in each dept ids
Emp name Dep ID
John 20
Mike 40
Ram 10
Guru 50
Kumar 30
View 7 Replies
View Related
Jun 10, 2010
I have created trigger on database level in system schema. While i am creating new tables in system schema, trigger logged the entry but when i am creating table in scott schema it is not working for that.
CREATE OR REPLACE TRIGGER ddltrigger
AFTER DDL ON DATABASE
BEGIN
INSERT INTO aud_log
(user_name, ddl_date, ddl_type, object_type,
owner, object_name
)
VALUES (ora_login_user, SYSDATE, ora_sysevent, ora_dict_obj_type,
ora_dict_obj_owner, ora_dict_obj_name
);
END;
View 3 Replies
View Related
Aug 10, 2010
I want to import a schema from one database schema to another schema b from db STBTST to STATST and from schema CMSSTAGINGB to CMSSTAGINGA
I first want to test this to my own schema (mvanmannekes) CMSSTAGINGA is filled at the moment.
So i've created a dump from STBTST-CMSTAGINGB For importing im using this statement:
impdp mvanmannekes/password schemas=cmsstagingb remap_tablespace=cmsliveb_data:cmslivea_data
remap_tablespace=cmsliveb_index:cmslivea_index
remap_schema=cmsstagingb:mvanmannekes directory=expdp_dir dumpfile=cmstagingb.dmp
I'm getting this:
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "MVANMANNEKES"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "MVANMANNEKES"."SYS_IMPORT_SCHEMA_01": mvanmannekes/********
schemas=cmsstagingb remap_tablespace=cmsliveb_data:cmslivea_data
[code]....
View 4 Replies
View Related
Mar 27, 2012
I have the task to migrate the total databases(Exact copy to be moved to another server).The current server is going for format.After I did the following steps I am getting the tablespaces(databases)-4 sizes same ,but I am facing issue like some default tablespaces i.e temp,system are not matching.
temp tablespace
***************
current server - 4.0(approximately)
Migrating server - 160 MB
System tablespace
*****************
current server - 580 MB
Migrating server - 220 MB
Also I checked the tables are also matching for the 4 databases.Also Provide the solution or method which is correct.
steps done for migrating(By me)
********************************
EXPORTING DATA USING DATAPUMP
*********************************
1 From command prompt MKDIR 'c:oraclexeapp mp';
2 From SQL prompt conn system/kotak;
3 create or replace directory dmpdir as 'c:oraclexeapp mp';
4 grant read,write on directory dmpdir to kotak;
5 From command prompt
expdp system/kotak@xe full=Y directory=dmpdir dumpfile=xe.dmp logfile=expdpxe.log;
IMPORTING DATA USING DATAPUMP
*****************************
in another server machine
1 From SQL prompt conn system/kotak;
2 create or replace directory dmpdir as 'c:oraclexeapp mp';
3 grant read,write on directory dmpdir to kotak;
4 From command prompt set ORACLE_SID=xe;
5 impdp system/kotak@xe full=Y directory=dmpdir dumpfile=xe.dmp logfile=impdpxe.log;
IN OUR PROCESS we created the below tablespaces and user before IMPORTING created 4 tablespaces
1. kotak
2. kotakdb
3. wired_data
4. ferrari
Created Users which are there in 219 server
1. KOTAK
2. KOTAKDB
3. FC_80
4. DEMOINTERNETBANK
5. DEMOINTERNETBANKDB
6. CREDITCARD
View 1 Replies
View Related
May 16, 2011
A single master schema where many developers are accessing. all share same password.
now i would like to trace all the changes made by each users. so i create a individual users for all and grant permission to access that schema.do i have a possibility of auditing the changes did by each user for that particular schema
View 2 Replies
View Related
Jul 5, 2012
how to take all schema metadata export except one schema (scott)
can i use like EXCLUDE=schema:"IN('SCOTT')
View 4 Replies
View Related
Jan 5, 2009
move the tables with data present in the user scott(full) to another schema named test. In my case scott is in user tablespace and for test schema i have created different tablespace named test_tbs.
View 14 Replies
View Related
Feb 1, 2010
single schema setup or multiple schema setups for an application development. Which option is recommended and pros and cons of these methods?
View 4 Replies
View Related
Feb 20, 2011
How to install oracle 8i and 9i databases on same server for example i want that both type of databases run on same server.
View 7 Replies
View Related
Jul 30, 2010
I have installed three database in MY Linux server. I have started Instance for all database and started database also .
No I want to check my databases status or currently running database How can I know . Or which Dynamic View or Database dictionary I should use ?
View 2 Replies
View Related
Oct 3, 2012
I have a windows 2008 server.
I already installled oracle software for oracle_home C:/oracle/product/11.2.0/dbhome_1 for database DEV.
Now, I want to create new database with different oracle_home on same server with oracle_home as C:/oracle/product/11.2.0/dbhome_2 and SID as TEST.
Do I have to istall oracle software again ? if yes, what about inventoryfiles in C:\Program Files\Oracle\Inventory ? wont they be overwritten ?
View 3 Replies
View Related
Aug 17, 2012
tell me if a REFRESH GROUP is automatically created when a materialized view is created?
View 3 Replies
View Related
Apr 2, 2013
I have a Client Server application that I created using an MS-SQL Server Database (MS-SQL Server 2008 R2 Express).Since MS-SQL Server has wide range (and number) of DATA_TYPEs, I elected to dedicate a few of those DATA_TYPEs to application-specific processing (mainly the MONEY and BOOLEAN DATA_TYPES).
I further decided that within the Client Application that the general processing of a particular field would be determined by the corresponding DATA_TYPE stored in the database.
The DATA_TYPE of a particular field within the SQL Server database and may be determined by querying:
"select DATA_TYPE from INFORMATION_SCHEMA.Columns where ( ( Column_name = 'my_Field' ) and ( Table_Name = 'myTable' ) )"
I also extract/use other DATA_TYPE information as needed/appropriate.
With this information the Client Application can consistently process a MONEY (a DATA_TYPE in MS-SQL Server) differently than perhaps either a DECIMAL or a BIGINT (two other DATA_TYPEs in MS-SQL Server). I have to create the field with the correct DATA_TYPE in the database, but after that I don't have to worry about it.
I am now moving over to an Oracle Database (Oracle Database Express Edition 11g Release 2). And I would like to have the same (or similar) functionality as described above.
Oracle has fewer DATA_TYPES so it is more difficult to tie an application-specific-datatype to an Oracle-database-specific-DATA_TYPE. There simply aren't as many to "sacrifice" to my application-specific-processing. I don't want to treat every NUMERIC field as "the-apps-money-type."
With Oracle I can associate "the-apps-money-type" with, let's say, a NUMERIC(9,2) and then the Client Application will treat all NUMERIC(9,2) as "the-apps-money-type."The Client Application queries the Oracle database for DATA_TYPE, DATA_PRECISION, and DATA_SCALE to make this determination.
But this approach does not appeal to me, suppose that I have other NUMERIC(9,2) fields that are not of "the-apps-money-type." How can I set and then later determine, from the Oracle backend, if the NUMERIC(9,2) field is meant to be used as "the-apps-money-type" or just a regular number?
Is there some way I may create some sort of "sub DATA_TYPE" in Oracle?I considered indicating this in the COMMENTS field that is associated with particular field. Then query for that comment when I process NUMERIC DATA_TYPEs. But the COMMENTS field does not seem to make it into user_tab_columns or all_tab_columns.
(With Oracle SQL Developer one can create/modify/delete the user created tables/fields. COMMENTS may be added to particular fields within a table--these are the COMMENTS that I am referring to above).What I would like to do, and most direct approach to me, would be to somehow create a user defined DATA_TYPE that is simply:
1) a user defined DATA_TYPE name (like, MY_MONEY_TYPE),
2) associated with an Oracle Built-in datatype (like NUMERIC(9,2).
Then when the client application queries user_tab_columns the field may be processed appropriately. If I cannot do that, perhaps there is somewhere else that I may set and subsequently query for this sort of information. What is(are) the most direct way(s) to implement the functionality described above?
View 4 Replies
View Related
Jun 16, 2013
while using top command it shows multiple processes usage.Actually we have 3 databases in the same server.how to find out which database process and user session hogging the cpu resources
View 2 Replies
View Related
Oct 7, 2012
I have a requirement to move the databases to a new server.
Existing set up:
database1-40GB,
database2-40GB,
database3-180GB
Oracle version - 11.2.0.2
OS - UNIX AIX-5.3
New Server (setup):
database1-40GB,
database2-40GB,
database3-180GB
Oracle version - 11.2.0.3
OS - UNIX AIX-6.1
My query is what is the best way to move the database to new server(UNIX AIX 6.1)?
a) can i upgrade the database to 11.2.0.3 before moving to new server or its better to move the database in 11.2.0.2 and then upgrade there?
b)is export and import of database can work for moving this?
c) RMAN backup and restoration can be done for moving databases?
d)simply moving all the related files to new server and start upgrading it from there?
View 8 Replies
View Related
Dec 5, 2010
I have Oracle 11g installed on AIX and I want to install another 11gR2 single instance in a different oracle home, will it be possible?
View 3 Replies
View Related
Aug 27, 2013
we have 7 databases running in a server .
OS : solaris
i want to add a new database on that server . So i want to find , whether we have a sufficient memory to add a new database . how to find it ?
View 5 Replies
View Related
Apr 10, 2013
10g and 11g
SLES 10
My boss has assigned me a task to check/audit a existing PROD + other databases which was configured (in a server or servers) by previous dba who have left with no documentation.I am just given the server ip and root password.
-How do you attack this kind of task?
-How do you know how many databases were installed in a given server/machine?
-How do you get all the oracle_home being defined or used?
Is /etc/oratab enough to start with?Is oraInventory enough to know all the databases installed? Is this keyword unique, and kind be search using "find / -name "oraInventory"?Do I need to find the existent of the "oracle" exec in all "bin" since this is the unique identifier of an OracleHome?
View 6 Replies
View Related
Jul 17, 2013
I'm try to connect to databases with DBLINK. It works, but I drop the dblink to do it again in the final server. The problem that I now is that there is not any DBLINK in my database but I can see the data of the other database (with select 1 from DATABASE.TABLE)
Is my database saving the data of the other database without de DBLINK?
View 4 Replies
View Related
Feb 7, 2013
Can a server (node) have more than one databases in RAC environment?
View 2 Replies
View Related
Jul 11, 2012
I have to migrate two different databases to oracle.i have made two Migration Repository for each to do the migration, migration is done.but i would like to know can it be done with one migration repository.if yes then with one is best way to do it.data of two databases is different but table and sp are 99% same.
View 3 Replies
View Related
Feb 14, 2013
I am using Oracle database server and I want to show existing databases on that server. Are there any SQL plus command to list all databases there in the server?
View 1 Replies
View Related
Aug 15, 2013
How to change Character set for oracle 8i database. Is there anyway to change the Character set without affecting the current database.
View 11 Replies
View Related
Oct 24, 2013
I am on Oracle 11.2.0.3 on Linux. In my production database, I am getting this alert, in the alert log:
--the below two lines are from alert log.
Error 604
trapped in 2PC on transaction 50.73.546578. Cleaning up.
--query from a data dictionary view
SQL> select count(*) from DBA_2PC_PENDING;
COUNT(*)
----------
1
When I query the production database I see that there is one row in the DBA_2PC_PENDING view. But I dont' know how to identify which are the databases that are involved in this distributed transaction. That is my first issue - how to identify which are the two databases that are involved in the distributed transaction?
View 10 Replies
View Related
Jun 8, 2010
i want to create two databases with different sids on one oracle home.
how can ı handle it?
View 2 Replies
View Related
Oct 2, 2012
I want to compare two users on different databases, actaully there are two users one in user a on database a and another user b on database b they have same tables, and everytime when a table or object is created on user a (database a) i will take the table name ,procedure or any other object from user_objects based on ddl_created date and then i need to recreate the same on user b on database b, is there a way to find out tables which are not only created but also i need to check whether if there is any column added or any change in procedure or any other objects.Is there a way to generate the scripts based on list of objects selected from user_objects.
all i want is.
a)Find out the list of objects added along with creation scripts
b)find out the list of objects modified along with creation scripts.
View 2 Replies
View Related