Oracle9i new features: SPFILE use
--How to set events with spfile and etc.
Last Updated:
Wednesday, 2004-10-27 0:39
Eygle
This paper is published in the ITPUB Technology Series "Oracle Database DBA Special Technology", which is strictly prohibited to reprint this article without permission.
Original source:
http://www.eygle.com/faq/oracle9i.new.feature.spfile.05.htm
7. Setting the Events Event Events Event is Oracle's important diagnostic tool and problem solution. Many times you need to block or change the behavior of Oracle via Events, let's take a look at how to modify spfile, add events event settings:
SQL> ALTER System Set Event = '10841 Trace Name Context Forever' Scope = SPFILE;
System altered.
SQL> Startup force; Oracle Instance Started.
Total System Global Area 101782380 bytesFixed Size 451436 bytesVariable Size 75497472 bytesDatabase Buffers 25165824 bytesRedo Buffers 667648 bytesDatabase mounted.Database opened.SQL> show parameter event
Name Type Value --------------------------------- ------------------------------ Event String 10841 Trace Name Context Forever
By the way, 10841 The event is a method for solving the JDBC Thin Driver problem in Oracle9i. If you have the following error tips in your alert.log file:
Wed Jan 7 17:17:08 2004errors in file /opt/oracle/admin/phsdb/udump/phsdb_ora_1775.trc:ora-00600: Internal Error Code, Arguments: [TTCGCSHND-1], [0], [], [] ], [], [], [], [] WED JAN 7 17:17:18 2004ERRORS IN FILE /OPT/OrCle/admin/phsdb/udump/phsdb_ora_1777.trc:ora-00600: Internal Error Code, Arguments: [ TtcgcShnd-1], [0], [], [], [], [], [], [] WED JAN 7 17:17:24 2004ERRORS IN FILE / OPT / ORACLE / Admin / PHSDB / UDUMP / PHSDB_ORA_1783. TRC: ORA-00600: Internal Error Code, Arguments: [TTCGCSHND-1], [0], [], [], [], [], [], [] WED JAN 7 17:17:31 2004ERRORS IN FILE /opt/oracle/admin/phsdb/udump/phsdb_ora_1785.trc:ora-00600: Internal Error Code, Arguments: [TTCGCSHND-1], [0], [], [], [], [], [], [] WED JAN 7 17:17:39 2004errors in file /opt/oracle/admin/phsdb/udump/phsdb_ora_1777.trc:ora-00600: Internal Error Code, Arguments: [TtcgcShnd-1], [0], [] , [], [], [], [], [] WED JAN 7 17:17:45 2004ERRORS IN FILE /OPT/OrCle/admin/phsdb/udump/phsdb_ora_1783.trc:ora-00600: Internal Error Code, Arguments : [TTCGCSHND -1], [0], [], [], [], [], [], [] WED JAN 7 17:17:52 2004ERRORS IN FILE /OPT/OrCle/admin/phsdb/udump/phsdb_ora_1787.trc : ORA-00600: Internal Error Code, Arguments: [TTCGCSHND-1], [0], [], [], [], [], [], [] WED JAN 7 17:18:11 2004ERRORS IN FILE / OPT / ORACLE / ADMIN / PHSDB / UDUMP / PHSDB_ORA_1791.TRC: ORA-00600: Internal Error Code, Arguments: [TTCGCSHND-1], [0], [], [], [], [], [], ] WED JAN 7 17:18:19 2004errors in file /opt/oracle/admin/phsdb/udump/phsdb_ora_1785.trc:ora-00600: Internal Error Code, Arguments: [TTCGCSHND-1], [0], [], [], [], [], [], [], Very unfortunate, you are likely to encounter BUG: 1725012 By setting up the above event, you can block and solve this ORA-00600 error. You can refer to MetAlink related documentation .
8. Export SPFILE file
SPFILE files can be exported to text files, use export, create a process you can add parameters to SPFILE.
SQL> CREATE PFILE = 'E: /INITEYGLEN.ORA' from spfile; files have been created.
SQL> Shutdown Immediate database has been turned off. The database has been uninstalled. Oracle routines have been closed.
The content of the INITEYGLEN.ORA file
* .aq_tm_processes = 1
* .BACKGROUND_DUMP_DEST = 'E: / Oracle / Admin / Eyglen / BDump'
* .compatible = '9.2.0.0.0'
* .control_files = 'e: /oracle/oradata/eyglen/control01.ctl',
'E: /oracle/oradata/eyglen/control02.ctl',
'E: /oracle/oradata/eyglen/control03.ctl'
* .CORE_DUMP_DEST = 'E: / Oracle / Admin / Eyglen / CDump'
* .db_block_size = 8192
* .db_cache_size = 25165824
* .db_domain = ''
* .db_file_multiblock_read_count = 16
* .db_name = 'EYGLEN'
* .dispatchers = '(protocol = tcp) (Service = EyglenxDB)
* .fast_start_mttr_target = 300
* .hash_join_ENABED = TRUE
* .instance_name = 'Eyglen'
* .java_pool_size = 33554432
* .job_queue_processes = 10
* .large_pool_size = 8388608
* .Open_cursors = 300
* .pga_aggregate_target = 25165824
* .processes = 150
* .query_rewrite_enabled = 'false'
* .remote_login_passwordfile = 'Exclusive'
* .shared_pool_size = 50331648
* .sort_area_size = 524288
* .SQL_TRACE = FALSE
* .STAR_TRANSFORMATION_ENABLED = 'false'
* .timed_statistics = true
* .undo_management = 'auto'
* .undo_retention = 10800
* .undo_tablespace = 'undotbs1'
* .user_dump_dest = 'E: / Oracle / Admin / Eyglen / Udump'
Then we can use this pfile or manually modify the parameters to start the database.
We modify this PFILE, add a line
* .log_archive_start = true
Use this PFILE to boot the database
SQL> Startup Pfile = 'E: /INITEYGLEN.ORA'Oracle routine has started.
Total System Global Area 135338868 Bytesfixed Size 453492 bytesvariable size 109051904 bytesdatabase buffers 25165824 BYTESREDO BUFFERS 667648 BYTES Database loaded. The database has been opened. SQL> Show parameter log_archive_startname Type ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------- Value ---------------------------- Log_archive_start BooleanTruesql>
Then we can create spfiles with new Pfile
SQL> CREATE SPFILE from Pfile = 'E: /INITEYGLEN. ORA';
The file has been created.
Restart the database, the new SPFile takes effect.
SQL> StartUporacle routines have been started.
Total System Global Area 135338868 Bytesfixed Size 453492 bytesvariable size 109051904 bytesdatabase buffers 25165824 BYTESREDO BUFFERS 667648 BYTES Database loaded. The database has been opened. SQL> Show Parameter SPFILE
Name Type ---------------------------------------------------------------------------------------------------- ---------- Value ------------------------------ SPFILE STRING% Oracle_Home% / Database / SPFile % Oracle_SID%. Orsql> show parameter log_archive_start
Name Type ------------------------------------------------------ ---------- Value ------------------------------ log_archive_start booleanTruesql>
Nine. About 920 Pfile
Under $ oracle_base / admin / db_name / spfile, you are likely to see a file like this [init.ora.192003215317] name, which is initializing the parameter file, just keeping up with timestamp. For Oracle920, the default is started using SPFILE, but this spfile is not shipped, but it is created according to this file, you can remove this long suffix, which is the standard pfile file.
If you manually create a database, you can see the following procedure:
SQL> CREATE SPFILE = 'E: /oracle/ora10g/database/spfilesunny.ora'
From pfile = 'e: /oracle/admin/sunny/scripts/init.ora';
The file has been created.
If you want to start the database with a PFile, you can rename the spfile, NT default, under $ oracle_home / database, so Oracle will not use this spfile. You should use the create pfile from spfile command to create a PFile, should also be in this directory
The following is a complete example:
E: / Oracle / ORA9IR2 / DATABASE> DIR *. The volume in the driver E is the serial number of the DOC volume is 980C-8EFF
E: / Oracle / Ora9ir2 / Database Directory 2003-02-26 10:49 1,028 initeyglen.ora2003-02-10 14:35 2,048 Pwdekeyglen.ora2003-02-26 11:05 3,584 spfileeyglen.ora3 file 6,660 bytes 0 Directory 937, 455, 616 available bytes
E: / Oracle / ORA9IR2 / DATABASE> SQLPLUS / NOLOG
SQL * Plus: Release 9.2.0.1.0 - Production On Wednesday February 26 11:16:29 2003
CopyRight (C) 1982, 2002, Oracle Corporation. All Rights Reserved.
SQL> Connect / as SYSDBA is connected to the idle routine. SQL> StartUporacle routines have been started.
Total System Global Area 135338868 Bytesfixed Size 453492 bytesvariable size 109051904 bytesdatabase buffers 25165824 BYTESREDO BUFFERS 667648 BYTES Database loaded. The database has been opened. SQL> Show Parameter SPFILE
Name Type ------------------------------------------------------ ---------- Value ------------------------------ SPFILE STRING% Oracle_Home% / Database / SPFile % Oracle_SID%. Orsql> Shutdown Immediate database has been closed. The database has been uninstalled. Oracle routines have been closed. SQL> host rename spfileeyglen.ora spfileeyglen.ora.bak
SQL> Host Dir *. The volume in the driver E is the serial number of the DOC volume is 980C-8EFF
E: / Oracle / Ora9ir2 / Database directory
2003-02-26 10:49 1,028 initeysglen.ora2003-02-10 14:35 2,048 pwdekeyglen.ora2 file 3,076 bytes 0 directory 937, 435, 136 available bytes
SQL> StartUporacle routines have been started.
Total System Global Area 135338868 Bytesfixed Size 453492 bytesvariable size 109051904 bytesdatabase buffers 25165824 BYTESREDO BUFFERS 667648 BYTES Database loaded. The database has been opened. SQL> Show Parameter SPFILE
Name Type ------------------------------------------------------ ---------- Value ------------------------------ SPFile string
SQL> SQL> Host Rename SPFILEEYGLEN. ORA.BAK SPFILEGLEN. O
SQL> Shutdown Immediate database has been turned off. The database has been uninstalled. Oracle routines have been closed. SQL> StartUporacle routines have been started.
Total System Global Area 135338868 Bytesfixed Size 453492 bytesvariable size 109051904 bytesdatabase buffers 25165824 BYTESREDO BUFFERS 667648 BYTES Database loaded. The database has been opened. SQL> Show Parameter SPFileName Type ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------- Value ---------------------------- SPFILE STRING% ORACLE_HOME% /DATABASE/SPFILE%Oracle_sid%.ORASQL>
<< Previous Next >>
Author: eygle, Oracle technology followers, Oracle technical forum itpub.www.eygle.com from China is the biggest author's personal site you may contact the author by Guoqiang.Gai@gmail.com welcome to explore technical exchanges and links. exchange.
Original source:
http://www.eygle.com/faq/oracle9i.new.feature.spfile.05.htm
