I had a problem starting up my database but was able to get it up and running. The only difference in my two scenarios was that I added a "recover database" statement, which seemed to do the trick.
I was under the impression during startup Oracle implicity does a recover if needed. If that is the case, how come I needed to use the "recover database" step. why the first scenario did not work and the second did.
STARTUP DID NOT WORK
================
Total System Global Area 3775086592 bytes
Fixed Size 2159192 bytes
Variable Size 1694502312 bytes
Database Buffers 2013265920 bytes
Redo Buffers 65159168 bytes
Database mounted.
SQL> alter database open;
alter database open
[code]...
recover database until cancel using backup controlfile; and alter database recover automatic until cancel;
1. in user managed and server managed backups. What would be the location of the controlfile which is used in (recover database until cancel using backup controlfile;) .
2. why user first option and why use second option.
While trying to copy a database, i used the command: RECOVER DATABASE USING BACKUP CONTROLFILE until cancel; it gave:
ORA-00279: change 7866782806751 generated at 07/12/2010 19:39:23 needed for thread 1 ORA-00289: suggestion : /moteurs/oracle/r11110/R11110/archivelog/2010_07_13/o1_mf_1_1158_%u_.arc ORA-00280: change 7866782806751 for thread 1 is in sequence #1158
Specify log: {<RET>=suggested | filename | AUTO | CANCEL} CANCEL ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below ORA-01194: file 1 needs more recovery to be consistent ORA-01110: data file 1: '/data01/oracle/r11110/data/SYSTEM01.dbf'
ORA-01112: media recovery not started what can i do in this case of issue?
i took the backup database with dump file.. today morning database is crashed but i have only yesterdays backup dump file. How can i recover the full database?
I have an oracle 8i recovery scenario. I have database cold backups of MONDAY and WEDNESDAY evenings. backup contains control files, data files, redo files
Database runs in Archive log mode and all archives of the month are available. Hard disk crashed on SATURDAY
all archive logs are still available at different location. Control files are not backed up to trace or file name or automatically means no backup control file (except ones in cold backups described above)
restoring all files from cold backup of either MONDAY or WEDNESDAY (I recovered to Wednesday) leaves nothing to recover. Querying V$RECOVER_FILE returns nothing. All log sequences higher to the number nnn displayed as result of ARCHIVE LOG LIST are present at correct location in correct format
My question is how do i ROLL Forward the Database from WEDNESDAY to SATURDAY in the above scenario.
i have one question regarding archivelog and backup if i take just backup excluding archivelogs like = RMAN>backup database; (not plus archivelog ) now when even i recover database using backup , will it also recover changes that was in archives?
I create a test database which is duplicate of my production server and after every 15 or 20 days I need to synchronize test db with production db.So I clone it and it takes about a day.I have to remove all datafiles from test and move from production to there on test server and I applied restore and recover.So it is very time consuming process.
I want to know is there any process which makes my work less like I just need to apply all rest of the logs which are new after synchronize last time from production server that need restore and recover.
I have the following situation: a customer sent me a group of files, that he said to be a "backup" of an old database, that is no longer online (he doesn´t use our software anymore). However, he needs to recover the data in this database. The person at the company who generate these files doesn´t work there anymore, so we don´t have any of the following information:
- Database version, connection details, service names, user names neither tablespace names.
We were just told that these files were recorded in a DVD, and is que only source remaining. The files he sent are the following:
Currently my DB is running on 10g Archivelog mode. All the archive logs being generated at /u04/dbarch/.The server crashed yesterday. Now when I want to start my instance it throwing the error ORA-01110 and ORA-01113-- system01.dbf need more recovery.The below are the command I fired from sql prompt:-
SQL>STARTUP MOUNT; SQL>RECOVER DATABASE UNTIL CANCEL; But still oracle suggesting that system01.dbf need more recovery.
At a test database instance of Oracle 11g (11.2.0.3.0) at CentOS 6 i've imported a tablespace from another instance to test my backup strategy.
First i've done a fullbackup with RMAN: ---------- # rman target / RMAN> BACKUP DATABASE PLUS ARCHIVELOG DELETE INPUT; ---------- Then i thought to test the database i could delete something and restore and recover the database from the backup. So i deleted a table in the imported tablespace: ---------- # sqlplus / as sysdba SQL> drop table TESTTABLE; ---------- After that i would start the restore and recover process by restoring and recovering the tablespace in which I deleted the table with RMAN. At first i've set the tablespace offline: ---------- SQL> alter tablespace TESTTABLESPACE offline immediate; ---------- Then I go to RMAN and start the restoring: ---------- RMAN> restore tablespace TESTTABLESPACE; ---------- Now there is a problem when i execute the recover command. The recover command includes the recovering from the redolog files, right? As I dropped the table from the tablespace this operation was written in the redolog files. So when i make a : ---------- RMAN> recover tablespace TESTTABLESPACE; ---------- The dropped tablespace would be dropped also, because the last redolog file told RMAN to drop the table. So i think I have to restore and recover my tablespace from an older timestamp, so RMAN would ignore the dropping of the table in the redolog file. Is that right so?
And when it is right how can i restore a tablespace from an older date so RMAN would ignore the dropping?
I'm using Oracle Standard edition 11gR2, actually that's why I'm posting this thread because I can't use TRANSPORT TABLESPACE feature.
I need to restore and recover two tablespaces, backed up in source database in another database. The destination db has been duplicated from the source db a couple of days ago so the structure is identical.
I cannot use Data Pump on this specific tablespace, because it causes some strange behavior in our application. So I took a backup from tablespaces and now I'm looking for a solution to restore and recover it in another db.
Is there any command to restore a tablespace from a specific file? I googled a lot and no luck so far..
P.S: I use controlfile instead of recovery catalog.
We have lost some datafiles and some archivelogs. We have backup of whole database taken on Dec 20 and archivelogs until Dec 23.So we dont have 1 day worth of archivelogs which can lead to incomplete recovery.
Now question is do we need to restore all datafiles or only lost datafiles for incomplete recovery since we dont have all archivelogs ? lost datafiles belong to different tablespaces.
Datafiles which are still intact are with current SCN and we restore lost datafiles only and recover unitl our last available archivelog it will have different SCN. Will we be able to open database or we need to resotre whole database for this scenario?
multiple disk failure, the database was open at the time.We have managed to recover the data from the disk and have put it on another disk.We have got a new server with oracle 9 running.
A blank database has been created.I have copied the control files and redo files onto the new server and left the datafiles , (.dbf,.ora) on a seperate external disk F drive (this is because the original database resided on F drive)I can mount the database , however when I try to open it I get the following
ORACLE instance started.
Total System Global Area 135338868 bytes Fixed Size 453492 bytes Variable Size 109051904 bytes Database Buffers 25165824 bytes Redo Buffers 667648 bytes Database mounted. ORA-01122: database file 1 failed verification check ORA-01110: data file 1: 'F:ORACLEORADATALCMPROSYSTEM01.DBF' ORA-01207: file is more recent than controlfile - old controlfile
We have faced database(10.2.0) issue cause incomplete recovery and have performed open resetlogs. This DB is of 12Tb. In 10g opening database with reset logs do not invalidate previous backups. we have another replica(no reset state)of this database which we sync using archives.how we can apply those archives(reset database)to previous database?
I am doing some test, seeking your expert opinion.I have a database and have the following backup strategy.
1) Database running in archive log mode. 2) First I backup all the table space excluding one tablespace 'DP_TS_LOB'.
RMAN> CONFIGURE EXCLUDE FOR TABLESPACE 'DP_TS_LOBS'; RMAN> BACKUP DATABASE;
3) I take a separate Backup of DP_TS_LOB tablespace.
RMAN> BACKUP TABLESPACE DP_TS_LOB;
4) I backup all the archived redo logs.
RMAN> SQL 'ALTER SYSTEM ARCHIVE LOG CURRENT'; RMAN> backup archivelog all delete input;
Now I wanted to restore the database from the backup excluding the DP_TS_LOB and bring it up and running. DP_TS_LOB is huge and used only on certain work flows. I don't want the database to be down until we restore DP_TS_LOB. I wanted to restore all the other tablespace and bring the database up operational and restore and recover the DP_TS_LOB tablespace datafile's in background taking the datafiles of DP_TS_LOB tablespace offline.I tried the following but unsuccessful.
RMAN> restore controlfile to 'g:ctl_bckctl_01' from autobackup; RMAN> restore spfile to 'g:ctl_bckspfile' from autobackup; RMAN> sql "create PFILE = ''G:ctl_bckPFILE'' from SPFILE = ''G:ctl_bckSPFILE''";
Update the new parameter with new control file name. Copied the control file and parameter file to corresponding location.
First I assumed "Recover Database" will only recover online database files. But got the following error.
RMAN> recover database;
Starting recover at 27-JUL-11 using channel ORA_DISK_1 RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of recover command at 07/27/2011 18:23:03 RMAN-06094: datafile 8 must be restored
Then I tried recovering data file separately, but same error.
RMAN> recover datafile 1;
Starting recover at 27-JUL-11 using channel ORA_DISK_1
RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of recover command at 07/27/2011 18:24:30 RMAN-06067: RECOVER DATABASE required with a backup or created control file
If I like to identify the executed time of a particular SQL Statement, beside v$sql, is there any other dictionary or lookup table that have this piece of information?
Why v$sql is not sufficient, because this is a recurrsive update statement which is regularly called, and thus the last_load_time is overwritten.
My archivelog had been purged due to our scheduled backup.
Is there any other way to identify when the particular SQL statement is executed?
I have a table which contains data of all calcualted payements for a Land for a particular period i.e. for a Land 124 for the duration of Jan-2010 to Mar-2010 100 rupees rent is paid to the owner.Requirement here is that for a particular duration i.e is for Jan-2010 to Mar-2010 system calculation of rent for a land file multiple times where the table would contain history of each transaction i.e. a user can calculate for a Land 124 for duration fo Jan-2010 to Mar-2010 rent more than ten times and there would be ten records in the table but only one record out of these would be valid where the user cannot calcualte rent for Lnd 124 for Jan-2010 to Mar-2010 without updating the status of the last record in the table for the same land and duration to invlaid. I want to add this business validation through triggers as i am not able to do so through check constarinst.I want to ensure that only one record should be valid for a particular land for a particular duration since row level triggers do not allow us to query the table ion which the trigger is executing i am trying to use statement level trigger but i have a few doubts here: Incase of a Before Insert statement level trigger does the trigger have access to the data which is being inserted into that the table cause whenever a record is being inserted into a table the systen would check through the trigger that if there is a valid (payment_status is valid) payment record in the table for that same land file and same duration.If there is a valid record then the trigger would through an exception. I know you can use a row level trigger with an autonomous transaction should work wihtout giving the mutating table error issue but i wont to know if using a statement level trigger here is feasible!!
i'm working in an Oracle 10g database on an IBM AIX server.
I have 3 tables (tables A, B and C).
Table A has columns -- product, rate and expiration date.
Table B has columns -- product, rate and deductible.
Table C has columns -- product, rider, gender, age and rate.
I also have a Master table which is used to store the data from Tables A, B and C via the insert statement.
I'm trying to create a dynamic SQL insert statement using a shell script to insert data from the columns in Tables A, B and C into my Master table. Master table does contains all columns from Tables A, B and C, although a column name could be spelled differently. For example, Master table contains a column named "deduct", while Table B has the same column spelled as "deductible".
I build the dynamic query using a for loop in my shell script (see below).
The problem is that i can't get the correct columns in the Master table in the dynamic SQL for the insert because depending on the table i'm selection from, the columns are different. So how do i get the correct columns in the SQL for the Master table?
Example Shell Script
--Archive_Rates.txt contains: Table A, Table B, Table C (but the next time my process runs, Archive_Rates might contain Table D, Table E and Table F -- each which have different column...but all columns are still in the Master table)
for tbl in `more Archive_Rates.txt` do echo 'BEGIN WORK; ' > rc1.sql echo ' ' >> rc1.sql echo 'insert into Master' >> rc1.sql echo '(prod, rate, rate_exp) ' >> rc1.sql
I have the following statement dynamic sql statement in a database package.
begin EXECUTE IMMEDIATE 'SELECT REPLACE(SUBSTR('||V_WHERE_CLAUSE||',1,3, ''AND'', NULL) INTO V_WHERE_FILTER FROM DUAL'; EXCEPTION NULL; end;
When executed it gives following error.
ORA-20000: ORA-20000: ORA-00936: missing expression ### TRACE ### ORA-06512: at "NDSS.DUP_SRCH_PKG_MOD", line 931 ### END TRACE ### ### TRACE ### ORA-06512: at "NDSS.DUP_SRCH_PKG_MOD", line 935 ORA-06512: at "NDSS.DUP_SRCH_PKG_MOD", line 164 ### END TRACE ### ORA-06512: at "NDSS.DUP_SRCH_PKG_MOD", line 184 ORA-06512: at line 9
FYI,I have taken control file backup and DB backup also 6 hour before the control file get corrupted,then there are quite lot of DB structure change, so Now how to recover my control file with the upto time DB state , since the backup was taken for control file 6 hour before the file get corrupted, now how do I revery the DB/control file, step by step with syntax as well.
I need to recover a dropped table, I have a RMAN backup of midnight and the table was dropped at 7:00 PM today. I do not have flash back enabled on the database. I am thinking of restoring midnight backup and applying the archives just before the table was dropped and opening the database in resetlogs method.
Currently my Source Database capture state stream is "Waiting for Dictionary Redo FIRST SCN" some logfiles are missing. My problem now is how to recover those missing logfiles.
HOW to use variable P_TMPLID in following statement
TYPE typ_unrecon IS TABLE OF REC_' || P_TMPLID ||'_UNRECON%ROWTYPE index by binary_integer;
because its throwing error while compiling
and also in statement FORALL i IN unrecondata.FIRST .. unrecondata.LAST SAVE EXCEPTIONS --STRSQL := ''; --STRSQL := ' INSERT INTO REC_' || P_TMPLID ||'_UNRECON VALUES ' || unrecondata(i); -- EXECUTE IMMEDIATE STRSQL; INSERT INTO REC_' || P_TMPLID ||'_UNRECON VALUES unrecondata(i);---throwing error on this statement commit; --dbms_output.put_line(unrecondata(2).TRANSID); EXCEPTION
While taking a full export i came to know there was a block corruption in SYSAUX tablespace. I dont have any COLD/HOTBACKUPS/RMAN BACKUPS. As i have only the Exp backups and the database is in archive log mode. Whether is it possible to recover the BLOCK CORRUPTION with exp backups.
