I am using oracle 10g R2. Some how control file is corrupted and database is not open. and there is no backup of control file. Now i need to open the database without recreating the database.
View 5 RepliesSQL> select block_size from v$controlfile;
BLOCK_SIZE
----------
16384
SQL> show parameter db_block_size;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_block_size integer 8192
the 2 can have difference block size?
how will you find your db is Corrupted or crashed. how will u recover it.
View 2 Replies View RelatedI am trying to create a new controlfile. I used "alter database backup controlfile to trace;" and then I copied and pasted the contents of the trace file into the .sql file. My sql script is
CREATE CONTROLFILE SET DATABASE "FIVESTAR" RESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
[code]....
Actually, the name of my database was 'STAR' and I changed it to "FIVESTAR" int this .sql script. Now when I run this script with SQL> @ <path of my sql script>, it gives me the following errors:
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-01504: database name 'FIVESTAR' does not match parameter db_name 'STAR'
I can not run the control file treace file,why?
[oracle@hxl oradata_bak]$ more run_sql.sql
CREATE CONTROLFILE REUSE DATABASE "ORACL" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
[code]...
I was doing some R&D on my test machine trying to understand how controlfile works. I started up my db and then deleted (renamed)the controlfiles at OS level. I was expecting the db to shutdown. But it dint. Moreover when i queried select name from v$controlfile; it was still reflecting the old controlfile names. To check if it was still functioning, i created a new tablespace with one file and it also got created without any error.
What i dint understand is how could the db still be running when the control file was'nt there and new tablespace and datafile get created? As i understand, whenever a new file is created, an entry is made in the control file. But when control file dint exist where was the data about new tablespace and datafile written?
I am not able to create control file from scrip . it showing following error
ORA-01081: cannot start already-running ORACLE - shut it down first
CHARACTER SET WE8ISO8859P1
*
ERROR at line 19:
ORA-02236: invalid file name
SQL> select checkpoint_change#,controlfile_change# from v$database;
CHECKPOINT_CHANGE# CONTROLFILE_CHANGE#
------------------ -------------------
203454 204955
what's the difference between checkpoint_change# and controlfile_change#.
what's the checkpoint_change# use for ? does it use for recover ?
what's the controlfile_change# use for ?
when the controlfile_change# will be increase ?
SQL> select controlfile_sequence# from v$database;
CONTROLFILE_SEQUENCE#
---------------------
293
Qs.) what is controlfile_sequence# ?
What's the maximum size of the control file in one database ?
i calculated it according to the following steps:
SQL>SELECT (BLOCK_SIZE/1024/1024)*20000 MB FROM V$CONTROLFILE WHERE ROWNUM = 1;
MB
------
312.5
The maximum number of the data block in one control file is 20000.
1)How a single control file is enough to have all the transaction's SCN no for older transactions.
Eg - A transaction has the SCN 01 and after a month the new transaction's SCN is 502. Is the control file holding all those (01 to 502) SCN in it or just will have the latest SCN no (ie - 502).
I am using oracle 10gXE just for basic practice but its showing following Errors
SQL*Plus: Release 10.2.0.1.0 - Production on Fri Mar 8 11:46:34 2
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> conn sys as sysdba
Enter password:
Connected to an idle instance.
SQL>
SQL>
SQL>
SQL> startup;
ORA-01081: cannot start already-running ORACLE - shut it down fir
SQL>
SQL>
SQL>
SQL> select * from dept;
select * from dept
[code]....
Oracle Enterprise Manager 11g Database Control Release 11.2.0.1.0 Agent is running, dbconsole too. to the database through SQL Developer and SQL Plus connects. when you go to localhost:1158/em opens a BLANK PAGE that contains 2 "squiggles" view"|[ ]]" tried in different browsers, identical. a feeling that most web interface is physically not in folders.
View 0 Replies View RelatedI am facing a strange issue on 11gR2 (OEL 5.4) standby readonly with apply database.It's throwing 16000: database open for read-only access during SELECT's .
Here is snapshot of errors.
ORA-00604: error occurred at recursive SQL level 1
ORA-16000: database open for read-only access
SQL> l
1* SELECT t0.airportID, t0.archived, t0.assetTag, t0.bluetoothID, t0.cmBundle, t0.createdDate,
t0.currentProductTaskID, t0.ethernetID, t0.failOrReworkCount, t0.highestCompletedTaskTypeID, t0.lastModDate, t0.lastStationID,
t0.modCount, t0.modelID, t0.oemSerialNumber, t0.orgSerialNumber, t0.pdmVersion, t0.preburnComplete,
t0.productID, t0.reworked, t0.secondaryEthernetID,
t0.serialNumber, t0.shipped, t0.specialBuildTypeID,
[code]....
I have a table enrty in dba_tables:
SQL> select OWNER,TABLE_NAME from dba_tables where TABLE_NAME='Viep_Porteur_Etudiant';
OWNER TABLE_NAME
------------------------------ ------------------------------
IEP_PESSAC Viep_Porteur_Etudiant
But this tables doesn't exist then I can't drop this table:
SQL> desc IEP_PESSAC.Viep_Porteur_Etudiant;
ERROR:
ORA-04043: objet IEP_PESSAC.Viep_Porteur_Etudiant inexistant
SQL> drop table IEP_PESSAC.Viep_Porteur_Etudiant;
drop table IEP_PESSAC.Viep_Porteur_Etudiant
*
ERREUR à la ligne 1 :
ORA-00942: Table ou vue inexistante
How can I clean dba_table? I suppose that that's happened because the user tablespace was full when he tried to create this table...
i found a temporary tablespace was corrupted,what happens to the database level, how to recover that tablespace? If need any recovery?
View 3 Replies View Relatedhow to create control file and how to load the data through command window in our database using sql * loader.i am having structure in my database and .csv file in my desktop.
View 20 Replies View RelatedThe following control file updates multiple rows in database table.
LOAD DATA
INFILE *
replace
INTO TABLE temp_tab
FIELDS TERMINATED BY ","
(Data LOBFILE(CONSTANT cadd_pass.xml) terminated by eof
There are 21 lines in the xml. So 21 rows are updated in table.update only one row?
I am having 3 oracle database instances running on 3 seperate Linux Node(RHEL Node).
Instance -1 named - DS
Instance -2 named - MIS
Instance -3 named - OAS
Among of these 3 nodes, we are facing Block Corruption issues with sysaux tablespace.Error in Instance name DS is
Errors in file /u01/home/dba/oracle/diag/rdbms/ds4db/DS/trace/DS_j000_655388.trc (incident=300847):
ORA-01578: ORACLE data block corrupted (file # 2, block # 38428)
ORA-01110: data file 2: '/DSdb1/dssysaux.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option
GATHER_STATS_JOB encountered errors. Check the trace file.
[code]....
for this I googled and found some solution as oracle doc [430230.1] related to sysaux couruption.After this again we are facing the same issue in sysaux tablespace.
how to repair the blocks that gets corrupted found in dbverify
View 8 Replies View RelatedWhen I am creating service for oracle using oradim Utility ,following messsage appearing
F:Users>oradim -new -sid stby -startmode manual
DIM-00014: Cannot open the Windows NT Service Control Manager.
O/S-Error: (OS 5) Access is denied.
I am trying to have sqlldr running against a file:
C:oratest20080318
Is it possible I get SQL*Loader-500: Unable to open file and 503 file not found just because the file name does not have an extension?
I can see that any file name I try it looks after whateverFileName.dat! Is there a way to have sqlldr working with files that do not have extensions?
How one should know whether RMAN is using target database control file or using separate catalog database. Also what one should do if he dont have catalog users credentials.
View 3 Replies View RelatedThe documentation for v$database says: QUOTE V$DATABASE displays information about the database from the control file. Is it safe to assume that there will only ever be one row queried from this view?
View 2 Replies View RelatedI am storing customer's snaps in a table ( column's data type as LONG RAW) using oracle forms Webutil. Now there are 250 snaps in the table. The file type of these snaps is JPG with the average size 30KB.
I made a backup using export utility before storing these snaps and the exported DMP file's size was 36MB. Now after storing these just 250 snaps of 30KB the DMP file's size is gone over 300MB.
i need to change column's datatype? or some where in oracle forms's image item. Because on window's file system the size of these files is just 8MB.
I was surprised when I shutdown our production dB. I got this error:
$ . oraenv
ORACLE_SID = [abcd] ?
SQL> shutdown
ORA-01116: error in opening database file 26
ORA-01110: data file 26: '/VOL03/oradata/qxyz/data/qxyz_data01.dbf'
ORA-27041: unable to open file
SVR4 Error: 2: No such file or directory
Additional information: 3
SQL> shutdown abort
ORACLE instance shut down.
2 days ago i rename this file QXYZ_DATA01.dbf from it's original name of QXYZ01.dbf without knowing that I was in the 'abcd' instance. When I backup a controlfile I noticed that it was part of the 'abcd' data files, so I just delete it (from OS level).
Is there any risk involved in the 'abcd' instance? How do I get rid of that file 'qxyz_data01.dbf' in the 'abcd' instance.
How can we add another control file to DBOG database? I need to clarify all the steps during this process.
View 7 Replies View RelatedCan we restore database password file in ASM for 11gR2 version.
View 4 Replies View RelatedI'm new into Oracle, consider the below scenario and respond it accordingly.
I want to add 500GB data file at primary database side, but doesn't have space at standby database side. My STANDBY_FILE_MANAGEMENT is set to Auto.
I am trying to get a readable version of a .trc file generated by Oracle
10.2.0.4.0 with tkprof, but I still have been unable to get this done...and this is what I have already tried:
tkprof /u00/app/oracle/admin/DB/udump/*BITN1234.trc /batch/salidas/student/BITNTRACE.txt
And whether I run this from my PL/SQL process (PRO*C) or from a command line, it returns:
TKPROF: Release 10.2.0.4.0 - Production on Thu Jan 6 11:04:38 2011
Copyright (c) 1982, 2007, Oracle. All rights reserved.
could not open trace file /u00/app/oracle/admin/DB/udump/BITN1234.trc
I have already (from my PRO*C code and from command line)...
- made sure that the file is in the directory.
- run this from the udump directory, where the .trc file is...didn't work.
- run this from the udump directory, and specifying explicitely the
complete path anyway in the tkprof line (redundant...I know)...didn't work.
- tried to copy the file to another directory in order to run the tkprof,
and it returns:
cp: BITN1234.trc: The file access permissions do not allow the specified
action.
- tried changing privileges with:
chmod a+r BITN1234.trc
and it returns:
chmod: BITN1234.trc: Operation not permitted.
- tried to run tkprof01 instead of tkprof and it returns:
We trust you have received the usual lecture from the local System Administrator. It usually boils down to these two things:
#1) Respect the privacy of others.
#2) Think before you type.
Which is the best possible way of configuring the Physical file structure in case of Data Warehouseing application?
I mean to ask you Multiplexing of the data file or control file is required. Or any best possible way to configure Physical file structure.