I used 'delete input' to delete all the archives backed up.
I am storing archives in dest1 and dest2...i.e, in two locatiions in production.
Although archive in dest1 is getting deleted I am unable to delete those in dest2. how to delete archives in dest2 too.
I have Oracle 10gR2 database on a windows server, I have scheduled rman backup for this database from Solaris server
set echo onrun {
crosscheck archivelog all;
backup check logical database plus archivelog;
delete noprompt obsolete device type DISK;
}
exit;
It is deleting the obsolete backup pieces but it is not deleting old archive logs (obsolete archive logs) and it is not even showing old archives as obsolete when I check thru report obsolete
I am trying to delete the backupset using Delete Obsolete command, but i am unable to get the success. As per the oracle Doc after completion of Retention Period oracle automatically deletes the backupset. I have set the retention period of 8 days.
View 7 Replies View Relatedi have a database (test1 10gr2 ) on rhel 5.5 and am using ASM (raw devices). for this database i have arch location as
log_archive_dest_1='location=+DG_ARC/arch'
My backup location is +DG_bak/test1.
Now when i run rman backup (nocatalog mode).. backup database plus archivelog delete input; it completes successfully but deletes the arch directory from +DG_ARC diskgroup and when i switch logfile it hangs ... alert log says unable to find archive destination. this thing is driving me nuts not sure why arch directory is being deleted. but when i get into asmcmd and manually create the arch directory logswitch takes place now.
I am trying to backup my entire database's files :
[oracle@testing-oracle-1 ~]$ rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Mon Jun 24 16:09:54 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1341263457)
RMAN> list backup;
using target database control file instead of recovery catalog specification does not match any backup in the repository
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT
FOR DEVICE TYPE DISK TO '/u01/app/oracle/backupsets/control_files/cf_%F.BCKP';2>
[code]....
I see that a backup set was created to "/u01/app/oracle/flash_recovery_area/ORCL/backupset/2013_06_24/" , in spite of my specification.
So I asked RMAN to list all the backups I have:
RMAN> list backup;
List of Backup Sets
===================
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
1 41.14M DISK 00:00:00 24-JUN-13
BP Key: 1 Status: AVAILABLE Compressed: NO Tag: TAG20130624T161025
Piece Name: /u01/app/oracle/backupsets/818957425_1_%r.BCKP
[code]....
Here I see that 4 backup sets have been created.The backup sets 1 & 3 containing the archived redo logs (818957425_1_%r.BCKP and 818957451_3_%r.BCKP) are placed as I wanted in /u01/app/oracle/backupsets/ dir.
The file containing the Control File and Spfile (cf_c-1341263457-20130624-00.BCKP) is placed as I wanted in /u01/app/oracle/backupsets/control_files/ dir.However, The backup set file containing the data files(o1_mf_nnnd0_TAG20130624T161026_8wjkb2tg_.bkp) is placed under the directory /u01/app/oracle/flash_recovery_area/ORCL/backupset/2013_06_24/ ,and I don't understand why.
When I backup my database without the archived redo logs - it is saved correctly:
RMAN>
RMAN> delete backup;
using channel ORA_DISK_1
List of Backup Pieces
BP Key BS Key Pc# Cp# Status Device Type Piece Name
[code]....
1. Why aren't my data files backed up to the location I specified when I backup using PLUS ARCHIVELOG syntax?
2. Why are my redo logs saved to two different backup sets and not just one?
3. Is there a way to combine backup of all files (data files, control file, spfile, redo logs & arch redo's) in one backup set, one file?
I want to delete archivelogs from ASM and want to schedule a script for same in crontab.
Also i removed archives manually from asmcmd.But when i runned below command,i didn't observed any change in the free_space.
select total_mb,free_mb,(total_mb-free_mb) used from v$asm_diskgroup;
i have attended an interview there i got a question like--->There are 100 archives generated in standby in that a series of five archives are missing and those are not available in the primary then how will you do.....
View 2 Replies View RelatedI have a problem with my Oracle istance and so far I couldn't fix it, I have and Oracle 11g XE istance running on windows server 2003.A couple of days ago I received the error message "ORA-00257: archiver error".I found tips in this forum and wanted to apply but sqlplus doesn't recognize my SYS user/password. I type the correct password but no chance to log in.
If I try to connect using a client like Toad, I receive the message ORA-12170: TNS:Connect timeout occurred. I checked the firewall rules, but it's ok, moreover I was logging in in the past and any change has been performed on it. I learned it's possible to change SYS password.
If I connect as "/ as sysdba" on sqlplus I read "Connected to an idle istance".In order to connect as "/" I had to update sqlnet.ora and restart the service. The parameter SQLNET.AUTHENTICATION_SERVICES
was set as NONE ad I set as NTS. I should probably have to set it as ALL? I found out the orapwd utility to change SYS password throught password file for remote login as sysdba.
I tried to create that file, and when I ran the command it asked me the SYS password, I typed it and it created the file, so I guess the password I type is correct (I had the question to type it wrong). I didn't go to the next step as I wasn't sure to make the right thing and I don't want to make this issue bigger.
As long as I can't log in as sysdba I can't perform the actions mentioned in tips to fix the error 257, but I'm also wondering if the archive error can have a side effect and deny the action of update the SYS password.
In my case , the archives are getting generating in excess count, how to get sql text/sid information.
View 2 Replies View RelatedSuppose I take a multi stream backup, say 3 streams.
So if a control file is backed up in say backup set 1, so is the whole control file is backed up in one backup piece or it may span multiple pieces?
A backup set may have multiple backup pieces.
The controlfiles were corrupted due to some issue. So, I had to restore the recent backed up control file and then restore the database.
The weird thing which I have noticed is that. I tried backing up Yesterday's Control file. So, I would say that the backup pieces it should pick should be from yesterday. however, the backup pieces are getting restored from last week's backup.
I have a doubt in RMAN. The scenario is: We take online backup on all days and offline backup on Sunday only. My question is do i need to copy the archives also in offline backup?
What happens when i shutdown the database (the database is in archivelog mode) and alter the database to be in noarchivelog and open the database to do offline backup.
I have a table which has a trigger defined as such on the Main table.
before INSERT OR UPDATE OF USER_NM,COmpany_ID,UPDATED_BY_USER
OR DELETE ON MAIN TABLE OLD AS OLD NEW AS NEW
FOR EACH ROW
When I insert the 1st row (by user Scott)
MAIN TABLE
USER_NM CompnyID UPDATED_BY_USER
----------------------------------------------------------------
ABC1Scott
In the audit table I have this information:
USER_NM CompnyID UPDATED_BY_USER OPERATION_PERFORMED
----------------------------------------------------------------
ABC 1 ScottInsert
When an ADMIN user (other than Scott deletes the above row)
MAIN TABLE:
USER_NM CompnyID UPDATED_BY_USER
----------------------------------------------------------------row deleted....
In the audit table i have 2nd entry as:
USER_NM CompnyID UPDATED_BY_USER OPERATION_PERFORMED
----------------------------------------------------------------
ABC 1 ScottInsert
ABC 1 ScottDELETE
Here the updated_by_user is Scott and not ADMIN, even though I am doing the following in the trigger.
UPDATED_BY_USER := sys_context('USERENV','OS_USER')
I want to show the user who has deleted the record which should be ADMIN.
Since the user deleting may not be the samae as the one who inserted.Any clue?
I am trying to delete the one month data from the table , which contains the end customer sales data.The total data count in the table is 30 crores. And the data of one month is nearly 10 Crores.I am using oracle 10g , The date field to be used in the condition of delete is indexed.
best way to delete such huge data count.
After creating all the tables and the constraints, and inputting data to the table.. i want to delete everything. i try using drop table but it doesn't get rid of the constraints.
View 10 Replies View Relatedlooking at my oracle DB schema I see that there are considerable number of tables that appear to be backup(!!) of other tables and don't ask me the reason why would some one create backup/temp tables in production.
All the tables almost occupy 7GB of space and I would like to get ride of them. Before I get ride of them I would like to see when the table was last accessed for any purpose like any DDL/DML statements and any Select statements performed on the temp/backup tables as well. Is this kind of information readily available in any SYS tables or should I write a trigger to get the details going forward.
I have a table and it's having duplicate records.
for one particular employee, he is having multiple records with the same data in the table
EMPNO ENAME JOB SAL DOB
-------------------------------------------------------
1 A X 100 1956
2 B Y 200 1974
1 A X 100 1956
3 C Z 300 1920
[Code]....
like this am having multiple times the duplicates.
I have written the below query to delete the duplicate records. But it is deleting only one record (if we have 5 duplicates it is deleting only 1 ). But I am looking to delete if we have 5 duplicates need to delete 4 duplicates and keep 1 record in the table.
query which am using to delete the duplicates is
DELETE FROM Table1 a
WHERE ROWID IN (SELECT MAX(ROWID)
FROM Table2 b
WHERE a.ID = b.ID);
it is deleting only one row but I want to delete 4 records out of 5 and keep one record.
create or replace
Procedure ReadingsPurge
As
v_sql varchar2(500);
v_date date;
p_count NUMBER;
[Code]...
-- Code below drops partitions that are older than the NoOfDays Parameter
OPEN c1;
LOOP
FETCH c1 INTO v_partition_name, v_high_value;
EXIT WHEN c1%NOTFOUND;
[Code]....
Above code is compiling successfully.
After I added the lines makred in the red font, when I tried to execute the stored procedure, I got an error
Error starting at line 1 in command:
execute ReadingsPurge
Error report:
ORA-00933: SQL command not properly ended
ORA-06512: at "CDC_USER.READINGSPURGE", line 30
ORA-06512: at line 1
00933. 00000 - "SQL command not properly ended"
*Cause:
*Action:
I have been using MSaccess to do some updating and deleting of data.I have 2 tables, 1 with data, 1 with the criteria to change, 2 colums (FINDWORD and REPLACEWORD).
The main table has lots of data, and the SQL query im using in access looks like this:
UPDATE TABLE1, tblFindReplaceWords SET CleanList.COLUMN-NAME = Replace([TABLE1].[COLUMN-NAME],[tblFindReplaceWords].[FINDWORD],[tblFindReplaceWords].[REPLACEWORD]);
how would I go about doing the same thing in orcale?
Consider tables A,B,C,D,E,F. all are having 100000++ records Tables B,C,D are dependent on table A (with foreign key constraint). When I am deleting records from all tables, table B,C,D are taking max 30-40 seconds while table A is taking 30-40 mins. All tables are having indexes.
Method I have used:
1. Created Temp table
2. then deleted all records from B,C,D,E,F for all records in temp table for limit of 500.
delete from B where exists (select 1 from temp where b.col1=temp.col1);
3. why it is taking too much time for deleting records in table A.
I'm testing a procedure which loads data into my database, and after each test I want to empty some of the tables and reset the sequences. I have this script to do that...
DELETE FROM COM_MERGE;
COMMIT;
DELETE FROM COM_TITLE;
COMMIT;
DELETE FROM COM_ISSUE;
COMMIT;
DELETE FROM COM_PAGE_ELEMENT;
COMMIT;
DELETE FROM COM_ELEMENT;
COMMIT;
DELETE FROM COM_STORY_TITLE;
COMMIT;
BEGIN
COM_RESET_SEQUENCES;
END;
Today I added the call to the sequences procedure to my script, but I have been using the script to delete from tables for a number of days without problem.However today I am finding that when I run the script it works ok the first couple of times, but when I try running it for a third time, it hangs after the second delete (in other words it stops when it gets to the delete from COM_ISSUE). After this happened the first couple of times I stopped the db and restarted it, then the script was ok twice, but again I'm finding that the script hangs. There is no error message, but the script fails to complete.
I didn't know if it was because originally I had one commit at the end of the script, so I added commits after each delete but that didn't solve it.I am using SQL Developer but I have found the same problem when running the script from SQL Plus.This is the definition of the COM_ISSUE table (just in case the table is the source of the problem).There is only one row in COM_ISSUE.
CREATE TABLE "BILL"."COM_ISSUE"
(
"CI_ID" NUMBER NOT NULL ENABLE,
"CI_TITLE" NUMBER NOT NULL ENABLE,
"CI_DATE" NUMBER NOT NULL ENABLE,
"CI_PRICE" NUMBER NOT NULL ENABLE,
"CI_PUBLISHER" NUMBER NOT NULL ENABLE,
CONSTRAINT "COM_ISSUE_PK" PRIMARY KEY ("CI_ID") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE(INITIAL 65536
[code]....
I have a small question to be clarified. Is there any way to find out the "Applied Archive log files" in DR and "Deleting them through .
View 1 Replies View RelatedI want to create one alert while deleting the record from the custom form. So i have written the below code in the when-remove-record trigger.
DECLARE
alert_button NUMBER;
BEGIN
IF :XXWIPRELFRM.PRINTED = 'Yes' THEN
alert_button := SHOW_ALERT ('DELETE_ALERT');
[Code]..
When i click on the delete(X) symbol in the form, it is showing alert but when i click on "No" also its removing the record..what i need to write.
I want to delete records from block in the form.could you explain where(in which trigger) should i write set_block_property.
View 9 Replies View RelatedI want to delete Master / Detail Data through cursor between date 01-02-2010 till 10-02-2010. Problem is in Detail I dont have date column in detail. But I have to delete Master and Detail record with desire date. I have made a cursor but it delete only detail record i want to delete master record too.
Master Table
M_NO CHAR (12) NOT NULL,
REMARKS VARCHAR2 (200),
CANCEL_YN CHAR (1) NOT NULL,
M_DATE DATE NOT NULL,
PRIMARY KEY ( M_NO ) ) ;
Detail Structure
M_SNO NUMBER NOT NULL,
ACCOUNT_CODE CHAR (19) NOT NULL,
CANCEL_YN CHAR (1) NOT NULL,
M_DESC VARCHAR2 (200),
DB_AMT NUMBER,
CR_AMT NUMBER,
M_NO CHAR (12) NOT NULL,
PRIMARY KEY ( M_SNO, M_NO ) ) ;
create or replace procedure test as
cursor md_cur is
select m_No from master where m_Date
between '01-02-2010' and '10-02-2010';
[code]./....
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
PL/SQL Release 11.2.0.3.0 - Production
"CORE 11.2.0.3.0 Production"
TNS for 32-bit Windows: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
We are in the process of setting up our backup policy. After the Archived Logs have been backed up, we need to delete them after 7 days. Also the actual files on disk.
RMAN does not delete the Archived Logs from disk.
I have a table, a superclass and several derived classes.
the table definition looks like this:
/*-------------------------------------------------------------------------------------*/
/* table to store ObjectData for the current session/transaction */
/*-------------------------------------------------------------------------------------*/
create table SessionObjectRegistry(
oid number,
transactid varchar2(200),
sessionid number,
hash number,
[Code]....
ERROR at line 1:
ORA-00600: internal error code, arguments: [kkbnftn2], [], [], [], [], [], [], [], [], [], [], []The db is
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing optionsThe error occurs on 11.2.0.3.0 - 64bit as well.
As a workaround i'm not deleting the rows, but setting the stored objects to null. At the end of the routine i can truncate the table with the objects since truncating works. I'm not the administrator of the database, therefore i may not change any settings.
Unfortunately i wanted to be able to share Objects between Sessions, which is possible with this workaround (simply don't truncate at the end), but leaves a lot of crap lying around.
How can I delete the duplicate combination of records from the below table.
CREATE TABLE test
(
gidNUMBER(10),
pidNUMBER(10)
);
INSERT INTO test VALUES (10,20);
INSERT INTO test VALUES (20,10);
INSERT INTO test VALUES (25,46);
[code]....
The condition is if GID = PID and PID = GID then only one combination of these records should be retained. For example Out of 10-20 and 20-10 only one record should be retained.
Expected result after deletion
GID PID
---------- ----------
10 20
25 46
89 64
15 16
19 26
I have a requirement where i need to retain latest 3 records based on creation date for each customer_id and delete the older records. The customer_ id or contract_number data in the test table are not unique.
Sample Table Script:
CREATE TABLE TEST
(
CUSTOMER_ID VARCHAR2(120 BYTE) NOT NULL,
CONTRACT_NUMBER VARCHAR2(120 BYTE) NOT NULL,
CREATION_DATE DATE NOT NULL
);
[code]...
when 'where current clause' is used in updating or deleting in loop.... is it required to write commit in the loop or it will auto commit?
View 2 Replies View Related
