Suppose my Prod Database is up and accidentally my pfile deleted. what will happen to my prod database whether it wil fail the instance or any error . as per concept that i know pfile accessed when we nomount the database for initialization of database.(11g)
in case of SPFILE
Suppose my spfile deleted and previously my database was in up condition. what will happen to my instance if i am working in a OLTP/DSS environment.(i dont have backup also how to recover and work in 11g if instance failed)
I have a database started using pfile and spfile parameter shows NULL from command prompt as below:
****************************************************************** Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production With the Partitioning, OLAP and Data Mining options
SQL> show parameter spfile
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ spfile string SQL> *****************************************************************
Now when I want to change/switch database to start using SPFILE, can I do it with out restarting (shutdown and startup)the database.
I have issued to create pfile from spfile. When I run the create command it is done successfully but in my pfile content has only one parameter of *.SPfile='D:apporacleproduct11.2.0dbhome_1databaseSPFILEORCLE1.ORA'
I want to have a normal pfile with many parameters in the pfile that it can be changed.
I am having few confusion on dbname, sid & tnsname.
rman target sys/oracle@suman auxiliary /
In the above command, "suman" is SID or Tnsname or dbname?
Bimistake, in my test database, i tried to change the DBname to suman from sumandb in pfile. After changing, i tried to startup database in nomount pfile but oracle through error that, unable to locate control file. Then, i remove the control files and tried to start database with nomount. Oracle through error about the control file.
This database, i had installed through DBCA. I want to know, where are all places where DBname reside?
Can i start database through pfile even if it is created through DBCA?
what is missing in this pfile that would cause enterprise manager not to appear ?
In the Oracle Database 10g: Administration Workshop II The chapter 8 excercise calls for the database to be started using the pfile init_sgalab.ora . The purpose of this I suppose is to simulate a memory error.
When I get to the part where I need to use OEM to fix the problem, OEM does not come up. The contents of init_sgalab.ora are : -------------------------------------------- background_dump_dest='/u01/app/oracle/admin/orcl/bdump' compatible='10.2.0.1.0' control_files='/u01/app/oracle/oradata/orcl/control01.ctl','/u01/app/oracle/oradata/orcl/control02.ctl','/u01/app/oracle/oradata/orcl /control03.ctl' core_dump_dest='/u01/app/oracle/admin/orcl/cdump' db_block_size=8192 [code].......
I am not sure what is missing in this file to be able to bring up OEM.
Also, I looked at the logs and this is the only error message I found that might be relevant in
If I have to create a database on server which has already 5 databases running , how will I set the memory (pga,sga) values . AFAIK it depends on o/s is linux and database version is 10g and 11g .
1)free avaialble memory on server . 2)kernel parameters .
what else has to be considering while initialize memory parameters .
I am using oracle 10g. Is there any mechanism to / parameter to enable or disable archive log mode? can I enable arching directly from pfile without touching the startup process?
when setting up data guard, if the primary clustered database is already in archive log mode, is there a need to restart both cluster instances when we update the spfile parameters for data guard ? or we can simply add the data guard parameters to the spfile while both cluster instances are online ?
Database: 11gEBS: 12.1.3O/S: Solaris Sparc 64 bits
Problem Description: Cloning from Source instance A to target instance B and both are in ASM instances.RMAN backup in source instance A is completed, which has spfile configured in non-default location in ASM for example,see belowASMCMD
[+ASMDG003/TEST1 > ls -ltType Redund Striped Time Sys Name Y TEMPFILE/ Y ONLINELOG/ Y DATAFILE/ Y CONTROLFILE/ N spfiletest1.ora => +ASMDG003/TEST1/PARAMETERFILE/SPFILE.747.856347
Also, there is no SPFILE in $ORACLE_HOME/dbs for the TEST1 source instance. INITTEST1.ora file has the following entry:
Question: In the target instance to be cloned from RMAN backup fro source instance TEST1, on performing ' restore spfile from autobackup 'location';Will the spfile be restored in $ORACLE_HOME/dbs in the target instance TEST2?
I've pasted some of the results of an RMAN backup that I ran. It says that it's backing up both the Control File and the SPFILE, but it appears that only one file is being created. Is this correct? How does this work? Are both files combined into the C-3843121877-20120619-00 file?
Starting Control File and SPFILE Autobackup at 19-JUN-12 piece handle=C:ORACLEPRODUCT10.2.0DATABASEC-3843121877-20120619-00 comment=NONE Finished Control File and SPFILE Autobackup at 19-JUN-12
Also, regarding the below configuration of RMAN - I can change this directory to whatever I want, correct? In other words, is it just the directory where I am putting the backup?
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'C:ORACLEPRODUCT10.2.0DATABASESNCFPLDG.ORA'; # default
What is correct way to move the default oracle spfile to ASM diskgroup so i can successfully bring up the instance . After bring up the instance , it should point to ASM diskgroup. My oracle database 11.2.0.3 is siting on sun solaris 64 Bit.
I want the DB to startup with SPFILE at next login.So i fired the below command.
CREATE SPFILE='path/SPFILEPRAGAT.ORA' FROM PFILE='path/file.ora'.
But it shows the error as below:
ORA-01078: failure in processing system parameters ORA-27040: file create error, unable to create file OSD-04002: unable to open file O/S-Error: (os 123) The filename, directory name,or volume label syntax is incorrect.
i try to startup a database, and get that obsolete/deprecated parameter specified(ORA-32004: obsolete and/or deprecated parameter(s) specified)so i try to reset the parameter, but it seems it is already reset(not in spfile), so maybe this is in memory. SO now i try to reset with scope = memory or both, it gives this error(ORA-32029: resetting with SCOPE=MEMORY or SCOPE=BOTH is currently not supported).
what to do, because now i cannot reset with scope = memory and in spfile it is not there but still it comes in deprecated parameter.
I need to restore entire database(sp+control+datafile) to new host.I have started the instance with nomount( and it has created a dummy parameter file).Now, I need to fire the command to restore spfile.
RUN { ALLOCATE CHANNEL c1 DEVICE TYPE sbt PARMS='...'; RESTORE SPFILE TO PFILE '?/oradata/test/inittrgta.ora' FROM AUTOBACKUP; SHUTDOWN ABORT; }
My questions:-
1.How will RMAN find the location of backupset(Since there is controlfile for neither target or catalog database)?
2.How does RMAN reache the backupset in case of a)tape b)disk?
In my alert log I am getting the following error quite repeatedly; My application hangs, and have to restart the instance.
CODEORA-00604: error occurred at recursive SQL level 1 ORA-06521: PL/SQL: Error mapping function ORA-06512: at "SYS.OLAPIHISTORYRETENTION", line 1 ORA-06512: at line 6
im new to Database i have to count no of messages based on the minuties like 1-5, 6-15,16-30,>30 min how to write query
select count(*) from table1 where ( select ( (extract(day from column2)-extract(day from column1))*24*60 + (extract(hour from column2)-extract(hour from column1))*60 + extract(minute from column2)-extract(minute from column1)) as Processed_time from table1 ) IN (1,5)
In oracle 11.2.0.3 I am getting, PL/SQL:ORA-00904 TR_STATE_NAME invalid identifier
but the same code compiled without any compilation error (though column name specified was wrong).I corrected the column name and its working fine in 11.2.0.3 now. Is there any enhancement in Oracle Version 11.2.0.3 in terms of compilation error?
My task has more lines of code,so i tried to present here only few lines of code where i am getting the error.The following cursor needs to select data from two tables with some conditions that are included in cursor and place those in other table.I used BULK collect.
CURSOR c_arch_trk (p_run_date DATE, p_nbr_days_arch1 NUMBER, p_nbr_days_arch2 NUMBER ) IS SELECT a.SIS_PGM_START_DATE,a.SIS_PGM_END_DATE,a.PGM_MSTR_NBR,a.PGM_TRK_NBR,a.CNTL_LOCN,a.CMPNY_VNDR_NBR,a.AGRMNT_MSTR_NBR, a.SLS_CONT_NBR,b.PGM_NAME,b.PGM_BASIS,b.AGRMNT_CNTL_LOCN [code]....
Create table students ( Sid number(3), Sname varchar(20) not null, Email varchar(20) not null, Doj Timestamp default sysdate not null, Dob date not null,
[code]....
when i run the above code i m getting error like this...I dont understand wat error it is...Because i didnt reference any column in the check constraint...Thn why it says that i m referencing some column..
Error: SQL> SQL> Create table students 2 ( 3 Sid number(3), 4 Sname varchar(20) not null,
I just create a trigger to fire whenever the quantity on hand for a books table has been updated.when the quantity on hand become zero ,it should call 'insert_reorder' procedure and pass the ISBN for the books. This is my code
CREATE OR REPLACE TRIGGER books_qty_on_hand_trg AFTER UPDATE OF on_hand_quantity ON books FOR EACH ROW BEGIN IF :NEW.on_hand_quantity = 0 THEN insert_reorde_pp (:NEW.isbn) END IF; END;
But i m getting this error
LINE/COL ERROR 4/3 PLS-00103: Encountered the symbol "END" when expecting one of the following: := . ( % ; The symbol ";" was substituted for "EN D" to continue.
