I had newly installed oracle 11g database, for testing & learning purpose.
Don't know wht happen to it after successfull installation , I am not able to start database.
Following error is appearing, how to resolve this.
Ora-01078:failure in processing system parameters
I can do only login to idle instance.
Today, I've got an error:
$ sqlplus / as sysdba
SQL*Plus: Release 11.1.0.6.0 - Production on Wed Nov 17 09:45:50 2010
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to an idle instance.
idle> startup
ORA-01078: failure in processing system parameters
ORA-01565: error in identifying file '+DATA/DOISOAT/spfileDOISOAT.ora'
ORA-17503: ksfdopn:2 Failed to open file +DATA/DOISOAT/spfileDOISOAT.ora
ORA-15077: could not locate ASM instance serving a required diskgroup
idle> exit
SP2-0042: unknown command "exit" - rest of line ignored.
idle> exit
Disconnected
First time, I tried to recreate spfile from pfile, however, it - the errors continued to occur. And I searched in metalink, so, I've got ID 387217.1. I've been beging followed:
1- Check the REQUIRED_RESOURCES was listed by crs_stat -p command, wasn't it? Then, the REQUIRED_RESOURCES did not contain anything
2- Modify by Metalink comment solution:
srvctl modify instance -d <database name> -i <instance name> -s <ASM instance name>
Everything is done, however, I still did not startup database instance. The above error continued, and I force to stop all by crs_stop -all.
I am learning how to clone database using cold backup.
I have copied all datafile ,redofile and also created control file but I am getting error while inititialisation of my clone(CVM) instance.
SQL> startup nomount pfile=C:ORACLEPRODUCT10.1.0DB_1DATABASEINITCVM.ORA;
LRM-00109: could not open parameter file 'C:ORACLEPRODUCT10.1.0DB_1DATABASE
INITCVM.ORA'
ORA-01078: failure in processing system parameters
but I have copied this file to C:ORACLEPRODUCT10.1.0DB_1DATABASE location but still giving me error.
I have a small problem with creating the SPFILE,
These are the commands i had issued :-
1. startup nomount
2. create SPFILE from PFILE ;
Then i got an error :-
ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file 'C:ORACLEORA92DATABASEINITORCL.ORA'
Ever since this happened i am unable to connect to ORACLE under any schema !
I get the following error : -
SQL> conn scott/tiger
ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Warning: You are no longer connected to ORACLE.
WHY DOES THIS HAPPEN AND HOW CAN I OVERCOME IT ?
I successfully created a DB manually with PFILE.
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.
While configuring Data Guard as I have inserted following parameters in SPFILEPRMDB.ora (PRMDB is Primary DB).
*.db_unique_name='PRMDB'
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(PRMDB,STLDB)'
*.LOG_ARCHIVE_DEST_1='LOCATION=C:oracleproduct10.2.0flash_recovery_areaPRMDBARCHIVELOG VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=PRMDB'
*.LOG_ARCHIVE_DEST_2='SERVICE=STLDB LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=STLDB'
[code]....
when I SQL> shutdown immediate; and SQL> Startup;
Oracle gives me following Error
ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file 'C:ORACLEPRODUCT10.2.0DB_1DATABASE
INITPRMDB.ORA'
When I Restored old spfileprmdb.ora without these parameters then it Starts Up Normally.
I am trying to create standby database on second machine ie. not on my machine. For this i have made added the required parameters in the pfile of the primary database but when I am trying to startup the database using the new pfile I am receiving some errors.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup nomount pfile='E:oracleproduct10.2.0db_1databaseinitden1.ora'
LRM-00101: unknown parameter name 'L0G_ARCHIVE_DEST_2'
ORA-01078: failure in processing system parameters
SQL>
Below are the parameters of my pfile:
*.core_dump_dest='E:oracleproduct10.2.0/admin/den/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='den'
*.db_recovery_file_dest='E:oracleproduct10.2.0/flash_recovery_area'
[code]....
I installed Oracle 11gR2 withouth creating a database in my laptop. Later when I tried to crete the database using DBCA it shows error ORA-01078:failure in processing system parameters.I clicked on ignore error, then shows the error ORA-01034: Oracle not available.
View 4 Replies View Relateddear all
i installed oracle database 10g
and iconnectthrowsql and it workfine but when i connect from client it give me this error
TNS: packet checksum failure
i try tnsping on the database serverit gives me the same error
I'm getting the error messaage:
Database Connection failure. Reason: java.sql.SQLException: ORA-01034: ORACLE not available ORA-27101: shared memory realm does not exist SVR4 Error: 2: No such file or directory
I'm not an Oracle expert, but I have research a little and seems that the SID could be wrong or that the DB is down, in fact I have already checked trhe server but everything seems normal to me.
I am facing an issue while rebuilding a DR (Physical Standby) site using RMAN.Rman is failing to restore all the datafiles to the stanby site.
There are 403 datafiles that should be restored among which after restoring 50-60 datafiles the channel is abruptly getting terminated.Please note this exercise is not new I have done this many times (once a year after a DR testing) and the same script is being used.The rcv file is as follows
/u03/oracle/admin/mpr/scripts/dataguard/configure_dr.rcv
connect auxiliary sys/XXXX@YYY
run {
duplicate target database for standby nofilenamecheck dorecover;
}
exit
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET;
configure_dr.rcv: END
[code]....
The trace file says "krso_proc_add: Detected dead process 13828326; subsuming V$MANAGED_STANDBY slot
INFO: krsr_rfs_atc: RFS processes are temporarily disallowed"
I have also started RMAN debug tracing and the trace is also scanned as it is huge.I have seen the following in the trace error 3015 signalled during compilation..I do not find any specific oracle error to check on .
The only change that happened is that the Primary production server has been moved to a new datacentre and after its move all the connections seems to be working fine.
we have OS detail:
Microsoft Windows Server 2003
Standard Edition
Service Pack 2
We have upgraded our database from 9.2.0.8.0 to 11.2.0.1.0 From past 5 months database working fine but on day before yesterday we got error in alert log as
Errors in file f:appdiag
dbms rac rac race rac_ora_3732.trc (incident=41729):
ORA-04030: out of process memory when trying to allocate 524812 bytes (kxs-heap-w,kllcqgf:kllsltba)
Process startup failed, error stack:
Errors in file f:appdiag
dbms rac rac race rac_psp0_5120.trc:
ORA-27300: OS system dependent operation:CreateThread failed with status: 8
ORA-27301: OS failure message: Not enough storage is available to process this command.
ORA-27302: failure occurred at: ssthrddcr
[code].....
we have increases/decrease the process and sessions and parameter,but it doesn't work.Right now i have to submit the RCA(Root Cause Analysis) report for the same.
I m using Windows Vista Business 32 bit , Oracle 10.2.0.3
while running my setup so many time i got same error each and every time Error:12560 TNS Protocol Adapter error and it shows me i attached my screen shot with my thread , and remember i already installed earlier but at that time there was no error and successfully installed, i removed the total software before installing and i also removed my environment variable and after getting this Database Configuration Assistant failure there is no service is started regarding my database.. I also tried manually but there also i got some other problem if by Database Configuration Assistant it ll b solved then it ll be good for me.
I recently discovered that there was a difference in my QA and prod environments, which I have since rectified.
SQL> select DBMS_STATS.GET_PARAM('METHOD_OPT') from dual;
DBMS_STATS.GET_PARAM('METHOD_OPT')
--------------------------------------------------------------------------------
FOR ALL COLUMNS SIZE 1
SQL> select DBMS_STATS.GET_PARAM('METHOD_OPT') from dual;
DBMS_STATS.GET_PARAM('METHOD_OPT')
--------------------------------------------------------------------------------
FOR ALL COLUMNS SIZE AUTO
I found a script that allows me to compare values from v$parameter
set pagesize 1000
col name format a28
col local format a20
col remote format a20
select local.name, local.value local,
remote.value remote,
[code]....
Is there other SQL code or another methode out there that would find differences in my DB's such as the method_opt setting, which don't appear in v$parameter.
Today I see my system tablespace is 98%, 480 MB used. Why the system tablespace is so big?
View 5 Replies View RelatedIn my database ,the size of the system tablespace is 1024M.now i found its free space is 477.875M.
SQL> SELECT TABLESPACE_NAME,SUM(BYTES)/1024/1024 M FROM DBA_FREE_SPACE where tablespace_name = 'SYSTEM' GROUP BY TABLESPACE_NAME ;
TABLESPACE_NAME M
--------------- -------
SYSTEM 477.875
In system tablespace ,the max size segment is IDL_UB1$,it is 167M
SQL> SELECT SEGMENT_NAME, SX || 'M' "Size" FROM (SELECT SEGMENT_NAME,SUM(BYTES)/1024/1024 SX FROM DBA_SEGMENTS WHERE TABLESPACE_NAME = 'SYSTEM' GROUP BY SEGMENT_NAME) WHERE SX > 100 ORDER BY SX DESC;
SEGMENT_NAME Size
------------- -----
IDL_UB1$ 167M
QUESTION: what's the table IDL_UB1$ used for ?
I created a view with parameters l_id and l_name, how can i find them in oracle view?
Create Table tb_test
(
Id Number,
Name Varchar2(64)
);
Create Or Replace View vw_tb_test(l_id,l_name)
As
Select Id,Name From tb_test;
What is the function by specified the size_clause of parameters method_opt?
dbms_stats.gather_table_stats
method_opt := FOR ALL [INDEXED | HIDDEN] COLUMNS [size_clause]
FOR COLUMNS [size_clause]
column|attribute [size_clause]
[,column|attribute [size_clause] ... ]
size_clause := SIZE [integer | auto | skewonly | repeat],
where integer is between 1 and 254
I can not change the parameters RESOURCE_MANAGER_PLAN, at first, I set the parameters to DAYTIME,but when I restart my db,the parameters hold old values named MAXCAP_PLAN. Why?
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
[code]....
I am getting below in alert log:
Deprecated system parameters with specified values:
remote_os_authent
IS this parameter is supportable in 11G and also how can i remove it from SP file with out bouncing the instance.
that's what I did:
SQL> select profile from DBA_USERS where username = 'SYSTEM';
PROFILE
------------------------------
DEFAULT
SQL> alter profile DEFAULT limit password_life_time UNLIMITED;
Profile altered. But I get still the warning message every login. ORA-28002: the password will expire within 7 days.
$ sqlplus system/oracle111@XE
SQL*Plus: Release 11.2.0.2.0 Production on Mon Aug 27 10:29:03 2012
Copyright (c) 1982, 2011, Oracle. All rights reserved.
ERROR:
ORA-28002: the password will expire within 7 days
Connected to: Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
Can I use OFA and Raid system together for Oracle 11g in Windows 2008 Server? I am installing the OS in C drive. Where to install the oracle, location for Oracle_Base, number of logical and physical drive required, size of each etc.
I am planning to use
Raid 1 - Controlfile, redolog file and archive log file
Raid 1+0 - Datafiles
Also I want to multiplex virtual memory page also.
My system tablespace size is 800mb and it sends an alert saying that it has exceeded 80% of its usage.However its total max size is 30gb.
FYI
Its autoextensible is set to 'YES'.
2 Node RAC database.
File system is ASM
My question here is... is it advicable to resize a system tablespace to some size when autoextensible is set to 'YES'
or genrally can we resize a system tablespace when autoextensible is set to 'No' ?
SQL> col FILE_NAME format a40
SQL> /
FILE_NAME BYTES/1024/1024 AUT MAXBYTES/1024/1024
---------------------------------------- --------------- --- ------------------
+DATA/ge01114/datafile/system.556.2254454 800 YES 30000.9844
The SYSTEM tablespace refuses to autoextend (3 segments in SYSTEM tablespace unable to extend). It sits on its current size of 559MB (auto set to 2GB, increment 10M).
I guess there are no free extends that are that big as 10M.
There are some sys views can not found. why?
SQL> connect / as sysdba
Connected.
SQL> show user;
USER is "SYS"
SQL> desc dba_users;
ERROR:
ORA-04043: object dba_users does not exist
Here is an article to show a case when SYS password is changed by SYSTEM:
[URL]....
This behavior is not working on all the environments.
When I was going through the Enterprise Manager Grid Control, I found an error due to which I looked at the trace file and it said:
ORA-07445: exception encountered: core dump ACCESS_VIOLATION unable_to_trans_pc PC:0x7C81BD02 ADDR:0x49444E49 UNABLE_TO_READ]
I searched and found that it has something to do with the SGA parameters. I saw that the shared_pool_size and the sga_target paramters are set to 0...Also there are certain SQLs hanging at some point. I thought I should change the above mentioned parameters.
My question now is, can I use the Alter System statements from the SQL Plus to change these parameters, and do they change immediately or do I need to reboot the Oracle instance for those changes to take effect? I would like to do:
alter system set sga_target=400m;
alter system set shared_pool_size=200m;
would these work and take effect immediately?
A way to show session parameters? Such as
CONSTRAINTS
USE_STORED_OUTLINES
ROW ARCHIVE VISIBILITY
CURRENT_SCHEMA
I can detect the CURRENT_SCHEMA with a query against the user env context, but I can't find any of the others there. Could there be an issue with these values being stored in PGA, and therefore not visible though any regular views? I did find an article that showed a query against an x$ data structure which showed something for different settings of CONSTRAINTS, but I can't find it again.
I mistakenly added a datafile to a tablespace which is asm, however the datafile was created in a default location and not the asm location:
alter tablespace pdaiidata1 add datafile '<filename>' size 2048M;
What I should have done:
alter tablepsace <tablespace_name> add datafile '+DATA1' size 2048M;
Is there any way to move this filesystem datafile into the asm tablespace? In previous Oracle versions, I've taken a tablespace offline, moved a datafile, renamed it, then brought the tablespace back online. Can I do something similar here in this situation?
how to reduce the system tablespace size
my system01.dbf size is 6gb
i want reduce from 6gb to 2gb