Think that emp as an oltp table i want to sync the data from emp to emp_target table daily(sysdate - 1) records
SQL> create table emp_target as select * from emp where 1=2;
Table created.
platform: linux64 + glassfish I run msync as a client on the same server, but on the mobile manager web page, only download sync publications history can be found. What shall I do to sync local Berkeley database content to oracle?Do I need to create upload sync publications? Do I need to use mobile server workbench to create new applications?
View 1 Replies View RelatedI'm trying to select some data from table base on SYSDATE. The below query does not return any data.
My query is:
select count(TICKET_ID) "ECEMEA" from QTMT_TICKETS where STATUS_ID=1 and TEAM_ID=3 and RECEIVED_DATE=sysdate
why this does not work? Date format in my application is: DD-MMM-YY (16-AUG-12). In the database the dates are stored in this format: MM/DD/YYYY (08/25/2012). Does it mean, that I have to play with the format?
I have few queries regarding standby database.
1)On primary Database Standby Redo log is required for switchover and on standby database Standby Redo log is required for
--Real time Apply
--Maximum protection or Maximum availability
Am I correct?
2)My database is in Maximum Performance mode. I set up following entry on init.ora:
LOG_ARCHIVE_DEST_2='service=standby LGWR ASYNC'. My question is do I need to have STANDBY Redo log file on standby database in order to use LGWR transport (LGWR ASYNC)mode from primary? Without Standby redo log on standby database can it transport redo data from primary to standby using LGWR transport mode (LGWR ASYNC)?
3)I have changed from the "ARCH" attribute to "LGWR" attribute of the LOG_ARCHIVE_DEST_n initialization parameter. But I have not changed the protection mode. I would like to know whether is there any impact in the behavior of the database, if we do not change the mode from "MAXIMUM PERFORMANCE" to "MAXIMUM AVAILABILITY"?
OS: Solaris
DB: Oracle 11.2.0.1 EE
Not Using ASM or RAC
I have a Production database that is in archivelog mode and a Standby DR server. Both servers (Prod, Standby) have exact same structure and db name/version.
We manually scp archive logs and recover them to a manual standby database via SQL Scripts "cron". (I.E. set autorecovery on; recover standby database;)
We recently got out of sync with our log files and have not been applying them to the standby. As part of Prod Maintenance, these log files were deleted and are not available anymore.
I've tried several ways to "rebuild" our standby database. I have tried to Shutdown prod, backup all the db files and scp them to standby, re-create standby controlfile and startup mount and recover standby. Every time I try to apply a new archive log via recover standby, these are the errors:
ORA-00279: change 211077622 generated at 1/27/2012 12:18:42 needed for thread 1
ORA-00289: suggestion : /oradump/arch/PROD/PROD_arch_1_69486_736618850.arc
ORA-00280: change 211077622 for thread 1 is in sequence #69486
ORA-00308: cannot open archived log '/oradump/arch/PROD/PROD_arch_1_69486_736618850.arc'
ORA-27037: unable to obtain file status
SVR4 Error: 2: No such file or directory
Additional information: 3
ORA-10879: error signaled in parallel recovery slave
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: '/oradevices/PROD/oraPRODsystem1.dbf'
When I check v$log_history, the new logs have not been applied. I've also tried the "Restore from incremental backup via SCN" method with same results. Is there a way to re-create the standby clean and ensure that the log chain that is currently broken gets fixed or reset? I would eventually like to get DataGuard in here, but that's not the case at the moment.
I need any good suggestion basically I have two different location
1- Factory
2- Head office
I have developed PRODUCTION module in factory and it is working fine and now I want to send data on daily basis to head office therefore I develop a form which will create backup in export format (.dmp) then user will send via email to head office.
Backup file should be save in pre-defined location then user will use a form which I developed for loading data into head office there are two different buttons in this form;
First is used to load data, actually first I load data in a temporary user which creates whenever user will press this button. Second is used to copy data in application user but first it checks if data exists then update otherwise insert.
i succeeded to expdp to ASM diskgroup such as
create directory asmexpdir as '+RECO/FILTDB/EXPDP';
grant read,write on directory asmexpdir to oraasfs;
expdp oraasfs/oraasfs2301 directory=asmexpdir dumpfile=SBSR_EXP.dmp tables=TM_SFS_CUST_01 logfile=EXPDP_LOG:SBSR_EXP.log
SUCCESS MESSAGE
. . exported "ORAASFS"."TM_SFS_CUST_01" 387.2 MB 817684 rows
Master table "ORAASFS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for ORAASFS.SYS_EXPORT_TABLE_01 is:
+RECO/filtdb/expdp/sbsr_exp.dmp
Job "ORAASFS"."SYS_EXPORT_TABLE_01" successfully completed at 03:34:59
And I like to run this daily and delete after 14 days. but it show error, what can be the solution to run this script?
#!/bin/bash
#Script to Perform Datapump Export backup Every Day
################################################################
#Change History
[code]...
I have a table for exampl,
emp sal date
111 200 03-mar-2011
100 200 03-mar-2012
15 200 06-mar-2012
17 200 03-mar-2003
178 200 03-mar-2004
11 200 11-jun-2012
101 200 19-jun-2012
i need sql querry to get current year records upto sysdate.i.e for example this year 2012 so i need all the 2012 year records upto sysdate.
my output like this.....
emp sal date
100 200 03-mar-2012
15 200 06-mar-2012
11 200 11-jun-2012
101 200 19-jun-2012(upto sysdate)
CREATE TABLE table_a(f_table_type_id NUMBER, f_bet_amount NUMBER, f_win_amount NUMBER);
CREATE TABLE table_b(f_table_type_id NUMBER, f_bet_amount NUMBER, f_win_amount NUMBER);
CREATE TABLE table_c(f_table_type_id NUMBER, f_bet_amount NUMBER, f_win_amount NUMBER);
INSERT INTO table_c VALUES(3,100,100);
[code]...
for each of the is which I get in v_ids, if there are records in a,b,c then I should get all those records, but the above loop is not working.
My final result should be LIKE :
f_table_type_id f_bet_amount f_win_amount
1 200 200
2 100 100
3 100 100
CREATE TABLE SCHEDULE_DETAILS
(
SCHEDULE_ID NUMBER NOT NULL,
SCHEDULE_TYPE VARCHAR2(10 BYTE),
SCHEDULE_START_DATE DATE,
SCHEDULE_END_DATE DATE,
RUNTIME CHAR(8 BYTE),
TIMEZONE VARCHAR2(40 BYTE));
SET DEFINE OFF;
Insert into SCHEDULE_DETAILS
(SCHEDULE_ID, SCHEDULE_TYPE, SCHEDULE_START_DATE, SCHEDULE_END_DATE, RUNTIME, TIMEZONE)
Values
(1970, 'Daily', TO_DATE('07/26/2010 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
TO_DATE('01/26/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), '13:58 ', 'America/New_York');
[code]........
Taking Today date and timezone as EST, I need to run a select that shows all the rows , where sysdate falls in between
Start_date and End_date and RunTime, ( run time is basically the Local time of the TimeZone Column)
Basically we should Display rows by checking/Converting, Start_date||End_date||Runtime||timezone with Sysdate(est) then display.
From the Above Data these rows should be Displayed by that select, how to compare this data with sysdate and display.
SCHEDULE_IDSCHEDULE_TYPESCHEDULE_START_DATESCHEDULE_END_DATERUNTIMETIMEZONE
1970Daily7/26/20101/26/201113:58 America/New_York
2588Daily10/18/20104/18/201115:50 America/New_York
3567Daily12/8/20106/8/20118:40 America/New_York
3386Daily12/27/20106/27/20111:0 America/New_York
1973Daily8/3/20102/3/201111:25 America/New_York
2565Daily9/7/20103/7/20117:0 America/New_York
3580Daily12/20/20106/20/201117:0 America/Chicago
3167Daily11/30/20105/30/20111:0 US/Alaska
3390Daily12/30/20101/15/20117:00 Asia/Calcutta
For Example, Below rows shouldn't come, Since it's end date is less than Sysdate.
SCHEDULE_IDSCHEDULE_TYPESCHEDULE_START_DATESCHEDULE_END_DATERUNTIMETIMEZONE
2579Daily9/17/20109/18/201011:32 America/New_York
i got a prob in executing a query in my oracle.TRANSACTIONLIST is the table and my query is..
select * from TRANSACTIONLIST where USER_ID = '07751A1247'the table has 2 records with that user id.
But it is given result as "no data found".
I just want to know whether can i create a trigger where the sysdate can be automatically written into a table? for ex: if to_char(sysdate,'HH24:MI:SS') = 18:00:00 then it should be written in a table..?? is there anyother way instead of triggers??
View 8 Replies View RelatedI have a table with columns job_id, jan, feb, mar ... , and year
I need to create a select query that will get the data from 18 months ago based on sysdate.
So something like:
Select to_char(add_months(sysdate, -18),'MON') from table1 where job_id = 56947 and year = to_char(add_months(sysdate, -18),'YYYY');
However I need the result of to_char(add_months(sysdate, -18),'MON') to actually act as a column name, not a string result.
I have 2 tables
Table 1Name Item DateJon Apples 06/11/2013 00:30:00 hrsSam OrangesNish Apples
Table 2 - Net countName Item CountNish Apples 10Nish Oranges 17Nish BananaSam Apples 10Sam Oranges 1Sam Bananas 1Jon Apples 8
I need to create a job that checks Table 1 for new records added after last run and then add the count in Table 2 accordingly.how to achieve this using PL/SQl or something similar
I have table 'A' with column 'ID','NAME','IN_DATE','PHONE','EMAIL'
Now I have to create a trigger such that on every insert in the table 'A' the value of column 'IN_DATE' changes to sysdate.I m not good in PL/SQL
When I am loding the data in person table through sql loder runs successfully without errors but when i check the person table it shows me zero records. Following is the details about what i done.
here are the details of data files.
1 Ahmed Baraka 1000 1.87 1-1-2000
2 John Rice 5000 2.4 10-5-1998
3 Emme Rak 2500 2.34
4 King Size 2700
5 Small Size 3000 31-3-2001
And The control File.
OPTIONS ( ERRORS=0)
LOAD DATA
INFILE '/oraeng/app/oracle/product/10.2.0/dbs/persons.dat'
BADFILE '/oraeng/app/oracle/product/10.2.0/dbs/persons.bad'
DISCARDFILE '/oraeng/app/oracle/product/10.2.0/dbs/persons.dsc'
INTO TABLE "KAILAS"."PERSONS" REPLACE
FIELDS TERMINATED BY X'9' TRAILING NULLCOLS
[code]...
i want to retrieve records of empno from SCOTT.EMP talbe in form but without data block wizard using ..how .. and what logic will be in coding
DECLARE
TYPE EROC IS TABLE OF VARCHAR2(200) INDEX BY BINARY_INTEGER;
E1 EROC;
NO_EMPNOVARCHAR2(2000);
[code]....
just caling here EMPNO and :v_empno is fields on form...
I am trying to update records in the target table based on the records coming in from source. For instance, if the incoming record is present in the target table I would update them in the target else I would simply insert. I have over one million records in my source while my target has 46 million records. The target table is partitioned based on calendar key. I implement this whole logic using Informatica. Looking at the informatica session log I find that the informatica code is perfectly fine but its in the update part it takes long time (more than 5 days to update one million records). find the TARGET TABLE query and the UPDATE query as below.
TARGET TABLE:
CREATE TABLE OPERATIONS.DENIAL_REGRET_FACT
(
CALENDAR_KEY INTEGER NOT NULL,
DAY_TIME_KEY INTEGER NOT NULL,
SITE_KEY NUMBER NOT NULL,
RESERVATION_AGENT_KEY INTEGER NOT NULL,
LOSS_CODE VARCHAR2(30) NOT NULL,
PROP_ID VARCHAR2(5) NOT NULL,
[code].....
I am working for an agency which needs to synchronize(bi-directional) a master-master database configuration (Oracle <-> DB2) between sites hosted in Washington ,DC and Europe.
We are doing high level estimates right now of in-house development , Oracle Golden Gate and DBMOTO, MQ...etc with bi-directional synchronization with Oracle and DB2 in decentralized geographic locations?
Or production level experience(perf. issues, maintenance ..drawbacks) with any of the products mentioned.
When a user session commit he is waiting on 'log file sync' untill LGWR sends the message back to user session after writing log buffer content into redo log file.As far as i understand this is serialize operation(one at a time).
So how come i have 7 ms average 'log file sync' wait time and i can still perform 200 commits per sec ?
7 ms * 200 waits = 1400ms = 1.4 sec
I'm trying to work out how to synchronize a source and target database in an Oracle CDC implementation. We're in the architecture design stage of a near-real-time Operational Data Store style solution. Both the ODS and our pilot source system will be Oracle 10g. Our plan is to use Oracle Asynch Hotlog Change Data Capture to capture change data in near-real-time so that it can be applied to the ODS.
I understand the CDC apply process once the ODS and Source System are synchronized: DBMS_CDC_SUBSCRIBE.EXTEND_WINDOW to release the next window of change data, select from the publish views, then DBMS_CDC_SUBSCRIBE.PURGE_WINDOW to register the change data is no longer required.
But how do we do the initial synchronization if the source system is live and contains data and the ODS is new (and empty)? The easiest way would be to somehow flag EVERY row as change data. eg. Truncate every table and import. This would not be so good for existing CDC subscribers.
A more logical way would be to:
- Take a hot backup of the live prod database
- Activate CDC (DBMS_CDC_SUBSCRIBE.ACTIVATE_SUBSCRIPTION) on the source system to start tracking changes
- Manually build the ODS from the snapshot
- Start applying changes from CDC
if the source system is live, how can we GUARANTEE that the first 2 steps (snapshot and ACTIVATE_SUBSCRIPTION) are performed at EXACTLY the same time (ie. same SCN)?
If one of the redolog member corrupted and overcome this problem, I had removed the corrupted redolog member. Later I had added a new member to this group.
I would like to know, is the newly added log member will get sync with existing log member? How the newly added log member get sync with existing log member?
os version is solaris 10
db version 11.2.0.3
how to tune log file sysnc waits in 11gr2 what parameters are involving in tuning log file sync and what would be its optimal values.
We have siebel server on windows machine and db server on unix box. We have done some modification(added columns) in siebel tools level. Now we want to ddl synch through command line prompt. how ot do ddl sync?
View 4 Replies View RelatedWe are having 10gR2 DR setup,
We are planning for the cold backup of primary database on every sunday,
Does it going to affect DR sync if primary database is not availabe at the time of cold backup.
i have my oracle 10g database on my windows 7 and i install sql server 2005 on my windows server 2003 .now i want to sync my oracle 10g database with my sql 2005 means whenever i make any changes in my oracle table its automatically effect in my sql server also.
View 1 Replies View RelatedI am looking to get the maximum value for every 24 hour period for a month. So for example my date range can be defined by...
select to_date('&date','mm yyyy')-1 + level as DateRange
from dual
connect by level <= '&days'
...where I can provide the first date of the month and number of days in the month or a lesser value if less time is required. So, the results of the above query plus 24 for the range. I thought a some Googling would provide me what I needed, but my search came up empty.
I was hoping to do something like this...
select utctime, max(value) from table where utctime between.
CREATE OR REPLACE
PROCEDURE Set_Daily_Currencies
İS
incoming_data_ UTL_HTTP.html_pieces;
received_page_ NUMBER;
incoming_data_textclob_ CLOB;
[code]...
i want to run the salary posting procedure automatically at mid night every day. i have created a job in toad and defined the required information but it is not working.
View 13 Replies View RelatedI need to sum the below daily total to weekly with the week starting day of Saturday Jan 3 2009 for the 52 weeks in 2009. The below query provides me the weekly total with the week starting day MONDAY. But I need the week starting day to be SATURDAY instead of MONDAY.
select to_char(report_date, 'YYYYIW'), sum(total)
from report_table
where to_number(to_char(report_date,'YYYYIW')) >=
to_number(to_char(to_date( '&one_year_ago'),'YYYYIW'))
group by to_char(report_date, 'YYYYIW')
Here is a query I used to generate the Daily Sample Data:
SELECT DISTINCT A.PRODUCT, TO_CHAR(B.BEGIN_DT,'YYYY-MM-DD') as post_date,'RCSL', A.DEPTID, A.ACCOUNT, SUM( A.POSTED_TOTAL_AMT) as Amount_Posted
FROM A, B, C
AND B.BEGIN_DT BETWEEN TO_DATE('&StartDate','YYYY-MM-DD') AND TO_DATE('&EndDate','YYYY-MM-DD')
GROUP BY A.PRODUCT, TO_CHAR(B.BEGIN_DT,'YYYY-MM-DD'), A.DEPTID, A.ACCOUNT
For the below sample data I would need to add data starting 2010-01-02 - 2010-01-08 to get my weekly total.
7777,2010-01-01,RCSL,0170,331062,-85
7777,2010-01-02,RCSL,0170,331063,-190
7777,2010-01-03,RCSL,0170,341413,170.18
7777,2010-01-04,RCSL,0170,347103,-880
7777,2010-01-05,RCSL,0172,331050,-116
7777,2010-01-06,RCSL,0172,331053,-50
7777,2010-01-07,RCSL,0172,331061,-63
7777,2010-01-08,RCSL,0172,331061,-63