9i SPFILE Oracle9i spfile Oracle9i Oracle rman Oracle spfile spfile Oracle Oracle9i -spfile,spfile 9i Oracle pfile spfile ALTER SYSTEM ALTER SESSION spfile spfile SPFILE RMAN Oracle PFILE PFILE SPFILE, PFILE,ORACLE spfile. SPFILE,ORACLE PFILE SPFILE PFILE SPFILE CREATE SPFILE SYSDBA SYSOPER CREATE SPFILE[= SPFILE- ] FROM PFILE[= PFILE- ] SQL> create spfile from pfile; spfile
(Unix: $ORACLE_HOME/dbs; NT: $ORACLE_HOME\database) SPFILE SQL> create spfile from pfile; create spfile from pfile * ERROR 1 : ORA-32002: SPFILE SPFILE Oracle SQL> host rename SPFILEEYGLEN.ORA SPFILEEYGLEN.ORA.BAK SQL> alter system set db_cache_size=24m scope=both; SQL> host dir *.ora E Doc 980C-8EFF E:\Oracle\Ora9iR2\database 2003-02-10 14:35 2,048 PWDeyglen.ORA 1 2,048 0 150,347,776 SQL> alter system set db_cache_size=24m scope=spfile; alter system set db_cache_size=24m scope=spfile * ERROR 1 : ORA-27041: OSD-04002: O/S-Error: (OS 2)
SQL> host rename SPFILEEYGLEN.ORA.BAK SPFILEEYGLEN.ORA SQL> alter system set db_cache_size=24m scope=spfile; SQL> Oracle. SPFILE startup Oralce a. UNIX: ${ORACLE_HOME}/dbs/ NT: ${ORACLE_HOME}\database b. UNIX: ${ORACLE_HOME}/dbs/ NT: ${ORACLE_HOME}\database c. UNIX: ${ORACLE_HOME}/dbs/ NT: ${ORACLE_HOME}\database or ${ORACLE_HOME}\admin\db_name\pfile\ spfile,oracle spfile
. pfile/spfile pfile pfile spfile. SQL> startup pfile='e:\oracle\admin\eyglen\pfile\init.ora'; spfile. spfile spfile pfile SPFILE Oracle9i IFILE SPFILE spfile PFILE SPFILE, PFILE PFILE, : SPFILE SQL> startup ORACLE Total System Global Area 135338868 bytes Fixed Size 453492 bytes Variable Size 109051904 bytes Database Buffers 25165824 bytes Redo Buffers 667648 bytes SQL> show parameter log_archive_start TYPE ------------------------------------ ---------------------- VALUE ------------------------------ log_archive_start boolean TRUE SQL> show parameter spfile TYPE ------------------------------------ ---------------------- VALUE
------------------------------ spfile string %ORACLE_HOME%\DATABASE\SPFILE% ORACLE_SID%.ORA SQL> PFILE log_archive_start SPFILE SQL> startup pfile='e:\initeyglen.ora' ORACLE Total System Global Area 135338868 bytes Fixed Size Variable Size Database Buffers Redo Buffers 453492 bytes 109051904 bytes 25165824 bytes 667648 bytes SQL> show parameter spfile TYPE ------------------------------------ ---------------------- VALUE ------------------------------ spfile string E:\Oracle\Ora9iR2\database\SPF ILEEYGLEN.ORA SQL> show parameter log_archive_start TYPE
------------------------------------ ---------------------- VALUE ------------------------------ log_archive_start boolean FALSE ALTER SYSTEM SPFILE. SQL> alter system set log_archive_start=false scope=spfile;
. ALTER SYSTEM SPFILE ALTER SYSTEM SCOPE SCOPE MEMORY,SPFILE, BOTH MEMORY: SPFILE: SPFILE BOTH: SPFILE 1. SCOPE=MEMORY SQL> show parameter timed_statistics TYPE ------------------------------------ ---------------------- VALUE ------------------------------ timed_statistics boolean TRUE SQL> ALTER SYSTEM SET timed_statistics=false SCOPE=MEMORY; SQL> show parameter timed_statistics TYPE ------------------------------------ ---------------------- VALUE ------------------------------ timed_statistics boolean FALSE SQL> shutdown immediate ORACLE SQL> startup ORACLE Total System Global Area 135338868 bytes Fixed Size 453492 bytes
Variable Size 109051904 bytes Database Buffers 25165824 bytes Redo Buffers 667648 bytes SQL> show parameter timed_statistics TYPE ------------------------------------ ---------------------- VALUE ------------------------------ timed_statistics boolean TRUE 2. SCOPE=SPFILE SQL> show parameter timed_statistics TYPE ------------------------------------ ---------------------- VALUE ------------------------------ timed_statistics boolean TRUE SQL> ALTER SYSTEM SET timed_statistics=false SCOPE=SPFILE; SQL> show parameter timed_statistics TYPE ------------------------------------ ---------------------- VALUE ------------------------------ timed_statistics boolean TRUE SQL> shutdown immediate ORACLE SQL> startup ORACLE Total System Global Area 135338868 bytes Fixed Size 453492 bytes
Variable Size 109051904 bytes Database Buffers 25165824 bytes Redo Buffers 667648 bytes SQL> show parameter timed_statistics TYPE ------------------------------------ ---------------------- VALUE ------------------------------ timed_statistics boolean FALSE SQL> 3 SCOPE = BOTH BOTH ALTER SYSTEM SPFILE SQL> show parameter timed_statistics TYPE ------------------------------------ ---------------------- VALUE ------------------------------ timed_statistics boolean FALSE SQL> ALTER SYSTEM SET timed_statistics=true SCOPE=BOTH; SQL> show parameter timed_statistics TYPE ------------------------------------ ---------------------- VALUE ------------------------------ timed_statistics boolean
TRUE SQL> shutdown immediate ORACLE SQL> startup ORACLE Total System Global Area 135338868 bytes Fixed Size Variable Size Database Buffers Redo Buffers 453492 bytes 109051904 bytes 25165824 bytes 667648 bytes SQL> show parameter timed_statistics TYPE ------------------------------------ ---------------------- VALUE ------------------------------ timed_statistics boolean TRUE SQL> ALTER SYSTEM SET sql_trace=false SCOPE=BOTH; ALTER SYSTEM SET sql_trace=false SCOPE=BOTH *
ERROR 1 : ORA-02095: SQL> ALTER SYSTEM SET sql_trace=false SCOPE=SPFILE; 4 shutdown spfile, SQL> show sga Total System Global Area 135338868 bytes Fixed Size Variable Size Database Buffers Redo Buffers 453492 bytes 109051904 bytes 25165824 bytes 667648 bytes SQL> shutdown immediate ORACLE SQL> create pfile from spfile; SQL> create spfile from pfile; SQL>
. spfile SPFILE 1 v$parameter pfile. SQL> SELECT name,value FROM v$parameter WHERE name='spfile'; ------------------------------------------------------------------ VALUE ------------------------------------------------------------------ spfile %ORACLE_HOME%\DATABASE\SPFILE%ORACLE_SID%.ORA 2 SHOW value pfile: SQL> SHOW PARAMETER spfile TYPE ------------------------------------ ---------------------- VALUE ------------------------------ spfile string %ORACLE_HOME%\DATABASE\SPFILE% ORACLE_SID%.ORA 3 v$spparameter 0 pfile, spfile: SQL> SELECT COUNT(*) FROM v$spparameter WHERE value IS NOT NULL; COUNT(*)
---------- 32 true 0 spfile. SQL> select isspecified, count(*) from v$spparameter group 2 by isspecified; ISSPECIFIED COUNT(*) ------------ ---------- FALSE 226 TRUE 33 SQL> select decode(count(*), 1, 'spfile', 'pfile' ) USED 2 from v$spparameter 3 where rownum=1 and isspecified='true' 4 / USED ------------ spfile
. SPFILE Oracle Spfile Rman (autoback) Oracle ( ) Spfile : a. : [oracle@jumper oracle]$ rman target / Recovery Manager: Release 9.2.0.3.0 - Production Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved. connected to target database: HSJF (DBID=1052178311) RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON; using target database controlfile instead of recovery catalog old RMAN configuration parameters: CONFIGURE CONTROLFILE AUTOBACKUP OFF; new RMAN configuration parameters: CONFIGURE CONTROLFILE AUTOBACKUP ON; new RMAN configuration parameters are successfully stored RMAN> exit : [oracle@jumper bdump]$ sqlplus "/ as sysdba" SQL*Plus: Release 9.2.0.3.0 - Production on Sat Jan 17 01:08:05 2004 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Connected to: Oracle9i Enterprise Edition Release 9.2.0.3.0 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.3.0 - Production
SQL> select * from v$rman_configuration; CONF# VALUE ---------- ------------------------- ---------- 1 CONTROLFILE AUTOBACKUP ON b. SQL> create tablespace eygle 2 datafile '/data1/oracle/oradata/eygle01.dbf' 3 size 5M; Tablespace created. alert<sid>.log : Sat Jan 17 00:55:57 2004 Starting control autobackup Control autobackup written to DISK device handle '/opt/oracle/product/9.2.0/dbs/c-1052178311-20040117-00' Completed: create tablespace eygle datafile '/data1/oracle/oradata/eygle01.dbf rman : RMAN> configure controlfile autobackup on; old RMAN configuration parameters: CONFIGURE CONTROLFILE AUTOBACKUP OFF; new RMAN configuration parameters: CONFIGURE CONTROLFILE AUTOBACKUP ON; new RMAN configuration parameters are successfully stored RMAN> run 2> { 3> allocate channel ch1 type disk format='e:\oracle\orabak\penny%t.arc'; 4> backup archivelog all delete all input;
5> release channel ch1; 6> } allocated channel: ch1 channel ch1: sid=13 devtype=disk Starting backup at 02-DEC-03 current log archived channel ch1: starting archive log backupset channel ch1: specifying archive log(s) in backup set input archive log thread=1 sequence=63 recid=168 stamp=511712617 input archive log thread=1 sequence=64 recid=169 stamp=511712620 input archive log thread=1 sequence=65 recid=170 stamp=511712626 input archive log thread=1 sequence=66 recid=171 stamp=511712690 channel ch1: starting piece 1 at 02-DEC-03 channel ch1: finished piece 1 at 02-DEC-03 piece handle=e:\oracle\orabak\penny511712693.arc comment=none channel ch1: backup set complete, elapsed time: 00:00:03 channel ch1: deleting archive log(s) archive log filename=e:\oracle\oradata\penny\archive\1_63.dbf recid=168 stamp=511712617 archive log filename=e:\oracle\oradata\penny\archive\1_64.dbf recid=169 stamp=511712620 archive log filename=e:\oracle\oradata\penny\archive\1_65.dbf recid=170 stamp=511712626 archive log filename=e:\oracle\oradata\penny\archive\1_66.dbf recid=171 stamp=511712690 Finished backup at 02-DEC-03 Starting Control File and SPFILE Autobackup at 02-DEC-03 piece handle=e:\oracle\ora92\database\c-3627775766-20031202-01 comment=none Finished Control File and SPFILE Autobackup at 02-DEC-03 released channel: ch1 spfile : c-iiiiiiiiii-yyyymmdd-qq c ------------------------ IIIIIIIIII---------DBID YYYYMMDD------------ QQ---------------------- 00-FF 16
c. spfile [oracle@jumper bdump]$ rman target / Recovery Manager: Release 9.2.0.3.0 - Production Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved. connected to target database: HSJF (DBID=1052178311) RMAN> restore spfile to '/tmp/spfileeygle.ora' from autobackup; Starting restore at 17-JAN-04 using target database controlfile instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=18 devtype=disk channel ORA_DISK_1: looking for autobackup on day: 20040117 channel ORA_DISK_1: autobackup found: c-1052178311-20040117-01 channel ORA_DISK_1: SPFILE restore from autobackup complete Finished restore at 17-JAN-04 RMAN> exit Recovery Manager complete. [oracle@jumper bdump]$ ls -l /tmp/spfileeygle.ora -rw-r----- 1 oracle dba 3584 1 17 09:34 /tmp/spfileeygle.ora : [oracle@jumper bdump]$ rman target / Recovery Manager: Release 9.2.0.3.0 - Production Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved. connected to target database: HSJF (DBID=1052178311) RMAN> restore controlfile to '/tmp/control01.ctl' from autobackup;
Starting restore at 17-JAN-04 using target database controlfile instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=10 devtype=disk channel ORA_DISK_1: looking for autobackup on day: 20040117 channel ORA_DISK_1: autobackup found: c-1052178311-20040117-02 channel ORA_DISK_1: controlfile restore from autobackup complete Finished restore at 17-JAN-04 RMAN> exit Recovery Manager complete. [oracle@jumper bdump]$ ls -l /tmp/control* -rw-r----- 1 oracle dba 1892352 1 17 09:44 /tmp/control01.ctl Oracle9i,,,.,,.. Events Events Oracle Events Oracle spfile Events 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 bytes Fixed Size 451436 bytes
Variable Size 75497472 bytes Database Buffers 25165824 bytes Redo Buffers 667648 bytes Database mounted. Database opened. SQL> show parameter event TYPE VALUE ------------------------------------ ----------- ------------------------------ event string 10841 trace name context forever 10841 Oracle9i JDBC Thin Driver alert.log : Wed Jan 7 17:17:08 2004 Errors 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 2004 Errors 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:24 2004 Errors 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 2004 Errors 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 2004 Errors 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 2004
Errors 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:52 2004 Errors in file /opt/oracle/admin/phsdb/udump/phsdb_ora_1787.trc: ORA-00600: internal error code, arguments: [ttcgcshnd-1], [0], [], [], [], [], [], [] Wed Jan 7 17:18:11 2004 Errors 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 2004 Errors in file /opt/oracle/admin/phsdb/udump/phsdb_ora_1785.trc: ORA-00600: internal error code, arguments: [ttcgcshnd-1], [0], [], [], [], [], [], [] bug: 1725012 ORA-00600. Metalink. SPFILE SPFILE SPFILE SQL> create pfile='e:\initeyglen.ora' from spfile; SQL> shutdown immediate ORACLE Initeyglen.ora *.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_enabled=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' pfile pfile *.log_archive_start=true PFILE
SQL> startup pfile='e:\initeyglen.ora' ORACLE Total System Global Area 135338868 bytes Fixed Size 453492 bytes Variable Size 109051904 bytes Database Buffers 25165824 bytes Redo Buffers 667648 bytes SQL> show parameter log_archive_start TYPE ------------------------------------ ---------------------- VALUE ------------------------------ log_archive_start boolean TRUE SQL> PFILE SPFILE SQL> create spfile from pfile='e:\initeyglen.ora'; SPFILE
. 920 PFILE $ORACLE_BASE\admin\db_name\spfile [init.ora.192003215317] Oracle920, spfile spfile pfile SQL> create spfile='e:\oracle\ora10g\database\spfilesunny.ora' FROM pfile='e:\oracle\admin\sunny\scripts\init.ora'; pfile spfile NT $ORACLE_HOME\database Oracle spfile create pfile from spfile pfile E:\Oracle\Ora9iR2\database>dir *.ora E Doc 980C-8EFF E:\Oracle\Ora9iR2\database 2003-02-26 10:49 1,028 INITeyglen.ORA 2003-02-10 14:35 2,048 PWDeyglen.ORA 2003-02-26 11:05 3,584 SPFILEEYGLEN.ORA 3 6,660 0 937,455,616 E:\Oracle\Ora9iR2\database>sqlplus /nolog SQL*Plus: Release 9.2.0.1.0 - Production on 2 26 11:16:29 2003 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. SQL> connect / as sysdba SQL> startup ORACLE
Total System Global Area 135338868 bytes Fixed Size 453492 bytes Variable Size 109051904 bytes Database Buffers 25165824 bytes Redo Buffers 667648 bytes SQL> show parameter spfile TYPE ------------------------------------ ---------------------- VALUE ------------------------------ spfile string %ORACLE_HOME%\DATABASE\SPFILE% ORACLE_SID%.ORA SQL> shutdown immediate ORACLE SQL> host rename SPFILEEYGLEN.ORA SPFILEEYGLEN.ORA.bak SQL> host dir *.ora E Doc 980C-8EFF E:\Oracle\Ora9iR2\database 2003-02-26 10:49 1,028 INITeyglen.ORA 2003-02-10 14:35 2,048 PWDeyglen.ORA 2 3,076 0 937,435,136 SQL> startup ORACLE Total System Global Area 135338868 bytes Fixed Size 453492 bytes Variable Size 109051904 bytes Database Buffers 25165824 bytes Redo Buffers 667648 bytes
SQL> show parameter spfile TYPE ------------------------------------ ---------------------- VALUE ------------------------------ spfile string SQL> SQL> host rename SPFILEEYGLEN.ORA.bak SPFILEEYGLEN.ORA SQL> shutdown immediate ORACLE SQL> startup ORACLE Total System Global Area 135338868 bytes Fixed Size 453492 bytes Variable Size 109051904 bytes Database Buffers 25165824 bytes Redo Buffers 667648 bytes SQL> show parameter spfile TYPE ------------------------------------ ---------------------- VALUE ------------------------------ spfile string %ORACLE_HOME%\DATABASE\SPFILE% ORACLE_SID%.ORA SQL>. spfile. : SUN Solaris8 :9203
. UNDO. : 1. alert.log SunOS 5.8 login: root Password: Last login: Thu Apr 1 11:39:16 from 10.123.7.162 Sun Microsystems Inc. SunOS 5.8 Generic Patch October 2001 You have new mail. # su - oracle bash-2.03$ cd $ORACLE_BASE/admin/*/bdump bash-2.03$ vi *.log "alert_gzhs.log" 7438 lines, 283262 characters Sat Feb 7 20:30:06 2004 Starting ORACLE instance (normal) LICENSE_MAX_SESSION = 0 LICENSE_SESSIONS_WARNING = 0 SCN scheme 3 Using log_archive_dest parameter default value LICENSE_MAX_USERS = 0 SYS auditing is disabled Starting up ORACLE RDBMS Version: 9.2.0.3.0. System parameters with non-default values: processes = 150 timed_statistics = TRUE shared_pool_size = 1157627904 large_pool_size = 16777216 java_pool_size = 637534208 control_files = /u01/oradata/gzhs/control01.ctl, /u02/oradata/gzhs/control02.ctl, /u03/oradata/gzhs/control03.ctl db_block_size = 8192 db_cache_size = 2516582400 compatible = 9.2.0.0.0 log_archive_start = TRUE log_archive_dest_1 = LOCATION=/u06/oradata/gzhs/arch log_archive_format = %t_%s.dbf db_file_multiblock_read_count= 16 fast_start_mttr_target = 300 undo_management = AUTO
undo_tablespace = UNDOTBS1 undo_retention = 10800 remote_login_passwordfile= EXCLUSIVE db_domain = instance_name = gzhs dispatchers = (PROTOCOL=TCP)(SERVICE=gzhsXDB) job_queue_processes = 10 hash_join_enabled = TRUE background_dump_dest = /oracle/admin/gzhs/bdump user_dump_dest = /oracle/admin/gzhs/udump core_dump_dest = /oracle/admin/gzhs/cdump sort_area_size = 524288 db_name = gzhs open_cursors = 300 star_transformation_enabled= FALSE query_rewrite_enabled = FALSE pga_aggregate_target = 838860800 aq_tm_processes = 1 PMON started with pid=2 DBW0 started with pid=3 LGWR started with pid=4 CKPT started with pid=5 SMON started with pid=6 "alert_gzhs.log" 7438 lines, 283262 characters USER: terminating instance due to error 30012 Instance terminated by USER, pid = 26433 ORA-1092 signalled during: ALTER DATABASE OPEN... Thu Apr 1 11:11:08 2004 Starting ORACLE instance (normal) LICENSE_MAX_SESSION = 0 LICENSE_SESSIONS_WARNING = 0 SCN scheme 3 Using log_archive_dest parameter default value LICENSE_MAX_USERS = 0 SYS auditing is disabled Starting up ORACLE RDBMS Version: 9.2.0.3.0. System parameters with non-default values: processes = 150 timed_statistics = TRUE shared_pool_size = 1157627904 large_pool_size = 16777216 java_pool_size = 637534208 control_files = /u01/oradata/gzhs/control01.ctl, /u02/oradata/gzhs/control02.ctl, /u03/oradata/gzhs/control03.ctl
db_block_size = 8192 db_cache_size = 2516582400 compatible = 9.2.0.0.0 log_archive_start = TRUE log_archive_dest_1 = LOCATION=/u06/oradata/gzhs/arch log_archive_format = %t_%s.dbf db_file_multiblock_read_count= 16 fast_start_mttr_target = 300 undo_management = AUTO undo_tablespace = UNDOTBS1 undo_retention = 10800 remote_login_passwordfile= EXCLUSIVE db_domain = instance_name = gzhs dispatchers = (PROTOCOL=TCP)(SERVICE=gzhsXDB) job_queue_processes = 10 hash_join_enabled = TRUE background_dump_dest = /oracle/admin/gzhs/bdump user_dump_dest = /oracle/admin/gzhs/udump core_dump_dest = /oracle/admin/gzhs/cdump sort_area_size = 524288 db_name = gzhs open_cursors = 300 star_transformation_enabled= FALSE query_rewrite_enabled = FALSE pga_aggregate_target = 838860800 aq_tm_processes = 1 PMON started with pid=2 DBW0 started with pid=3 LGWR started with pid=4 CKPT started with pid=5 SMON started with pid=6 RECO started with pid=7 CJQ0 started with pid=8 Thu Apr 1 11:11:13 2004 starting up 1 shared server(s)... QMN0 started with pid=9 Thu Apr 1 11:11:13 2004 starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'... ARCH: STARTING ARCH PROCESSES ARC0 started with pid=12 ARC0: Archival started ARC1 started with pid=13
Thu Apr 1 11:11:13 2004 ARCH: STARTING ARCH PROCESSES COMPLETE Thu Apr 1 11:11:13 2004 ARC0: Thread not mounted Thu Apr 1 11:11:13 2004 ARC1: Archival started ARC1: Thread not mounted Thu Apr 1 11:11:14 2004 ALTER DATABASE MOUNT Thu Apr 1 11:11:18 2004 Successful mount of redo thread 1, with mount id 1088380178. Thu Apr 1 11:11:18 2004 Database mounted in Exclusive Mode. Completed: ALTER DATABASE MOUNT Thu Apr 1 11:11:27 2004 alter database open Thu Apr 1 11:11:27 2004 Beginning crash recovery of 1 threads Thu Apr 1 11:11:27 2004 Started first pass scan Thu Apr 1 11:11:28 2004 Completed first pass scan 1 redo blocks read, 0 data blocks need recovery Thu Apr 1 11:11:28 2004 Started recovery at Thread 1: logseq 177, block 2, scn 0.33104793 Recovery of Online Redo Log: Thread 1 Group 3 Seq 177 Reading mem 0 Mem# 0 errs 0: /u01/oradata/gzhs/redo03.log Thu Apr 1 11:11:28 2004 Completed redo application Thu Apr 1 11:11:28 2004 Ended recovery at Thread 1: logseq 177, block 3, scn 0.33124794 0 data blocks read, 0 data blocks written, 1 redo blocks read Crash recovery completed successfully Thu Apr 1 11:11:28 2004 LGWR: Primary database is in CLUSTER CONSISTENT mode Thread 1 advanced to log sequence 178 Thread 1 opened at log sequence 178 Current log# 1 seq# 178 mem# 0: /u01/oradata/gzhs/redo01.log Successful open of redo thread 1. Thu Apr 1 11:11:28 2004 ARC0: Evaluating archive log 3 thread 1 sequence 177 Thu Apr 1 11:11:28 2004
ARC0: Beginning to archive log 3 thread 1 sequence 177 Creating archive destination LOG_ARCHIVE_DEST_1: '/u06/oradata/gzhs/arch/1_177.dbf' Thu Apr 1 11:11:28 2004 SMON: enabling cache recovery ARC0: Completed archiving log 3 thread 1 sequence 177 Thu Apr 1 11:11:28 2004 Errors in file /oracle/admin/gzhs/udump/gzhs_ora_27781.trc: ORA-30012: \263\267\317\373\261\355\277\325\274\344 'UNDOTBS1' \262\273\264\346\324\332\273\362\300\340\320\315\262\273\325\375\310\ 267 Thu Apr 1 11:11:28 2004 Error 30012 happened during db open, shutting down database USER: terminating instance due to error 30012 Instance terminated by USER, pid = 27781 ORA-1092 signalled during: alter database open... :q Open. 2. bash-2.03$ sqlplus "/ as sysdba" SQL*Plus: Release 9.2.0.3.0 - Production on 4 1 11:43:52 2004 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. SQL> startup ORACLE Total System Global Area 4364148184 bytes Fixed Size 736728 bytes Variable Size 1845493760 bytes Database Buffers 2516582400 bytes Redo Buffers 1335296 bytes ORA-01092: ORACLE.
3. bash-2.03$ cd /u01/ oradata/gzhs bash-2.03$ ls -l total 55702458 -rw-r----- 1 oracle dba 1073750016 Apr 1 11:44 UNDOTBS2.dbf -rw-r----- 1 oracle dba 1073750016 Apr 1 11:44 WAP12_BILLINGDETAIL.dbf -rw-r----- 1 oracle dba 1073750016 Apr 1 11:44 WAP12_MAIN.dbf -rw-r----- 1 oracle dba 2097160192 Apr 1 11:44 WAP12_MAIN10.dbf -rw-r----- 1 oracle dba 2097160192 Apr 1 11:44 WAP12_MAIN11.dbf -rw-r----- 1 oracle dba 2097160192 Apr 1 11:44 WAP12_MAIN2.dbf -rw-r----- 1 oracle dba 2097160192 Apr 1 11:44 WAP12_MAIN3.dbf -rw-r----- 1 oracle dba 2097160192 Apr 1 11:44 WAP12_MAIN4.dbf -rw-r----- 1 oracle dba 2097160192 Apr 1 11:44 WAP12_MAIN5.dbf -rw-r----- 1 oracle dba 2097160192 Apr 1 11:44 WAP12_MAIN6.dbf -rw-r----- 1 oracle dba 2097160192 Apr 1 11:44 WAP12_MAIN7.dbf -rw-r----- 1 oracle dba 2097160192 Apr 1 11:44 WAP12_MAIN8.dbf -rw-r----- 1 oracle dba 2097160192 Apr 1 11:44 WAP12_MAIN9.dbf -rw-r----- 1 oracle dba 1073750016 Apr 1 11:44 WAP12_MVIEW.dbf -rw-r----- 1 oracle dba 1073750016 Mar 24 17:15 WAP12_TEMP1.dbf -rw-r----- 1 oracle dba 1810432 Apr 1 11:44 control01.ctl -rw-r----- 1 oracle dba 104865792 Apr 1 11:44 cwmlite01.dbf -rw-r----- 1 oracle dba 104865792 Apr 1 11:44 drsys01.dbf -rw-r----- 1 oracle dba 144842752 Apr 1 11:44 example01.dbf -rw-r----- 1 oracle dba 104865792 Apr 1 11:44 indx01.dbf -rw-r--r-- 1 oracle dba 1113 Mar 31 22:51 mkqio.dat -rw-r----- 1 oracle dba 104865792 Apr 1 11:44 odm01.dbf -rw-r----- 1 oracle dba 268435968 Apr 1 11:44 redo01.log -rw-r----- 1 oracle dba 268435968 Apr 1 11:44 redo02.log -rw-r----- 1 oracle dba 268435968 Apr 1 11:44 redo03.log -rw-r----- 1 oracle dba 524296192 Apr 1 11:44 system01.dbf -rw-r----- 1 oracle dba 1048584192 Feb 7 21:10 temp01.dbf -rw-r----- 1 oracle dba 104865792 Apr 1 11:44 tools01.dbf -rw-r----- 1 oracle dba 209723392 Apr 1 11:44 users01.dbf -rw-r----- 1 oracle dba 209723392 Apr 1 11:44 xdb01.dbf UNDOTBS2.dbf 4. mount bash-2.03$ sqlplus "/ as sysdba" SQL*Plus: Release 9.2.0.3.0 - Production on 4 1 11:46:20 2004 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
SQL> SQL> SQL> startup mount; ORACLE Total System Global Area 4364148184 bytes Fixed Size 736728 bytes Variable Size 1845493760 bytes Database Buffers 2516582400 bytes Redo Buffers 1335296 bytes SQL> select name from v$datafile; -------------------------------------------------------------------------------- /u01/oradata/gzhs/system01.dbf /u01/oradata/gzhs/cwmlite01.dbf /u01/oradata/gzhs/drsys01.dbf /u01/oradata/gzhs/example01.dbf /u01/oradata/gzhs/indx01.dbf /u01/oradata/gzhs/odm01.dbf /u01/oradata/gzhs/tools01.dbf /u01/oradata/gzhs/users01.dbf /u01/oradata/gzhs/xdb01.dbf /u01/oradata/gzhs/wap12_main.dbf /u01/oradata/gzhs/wap12_billingdetail.dbf -------------------------------------------------------------------------------- /u01/oradata/gzhs/wap12_mview.dbf /u01/oradata/gzhs/wap12_main2.dbf /u01/oradata/gzhs/wap12_main3.dbf /u01/oradata/gzhs/wap12_main4.dbf /u01/oradata/gzhs/wap12_main5.dbf /u01/oradata/gzhs/wap12_main6.dbf /u01/oradata/gzhs/wap12_main7.dbf /u01/oradata/gzhs/wap12_main8.dbf /u01/oradata/gzhs/wap12_main9.dbf /u01/oradata/gzhs/wap12_main10.dbf /u01/oradata/gzhs/wap12_main11.dbf
-------------------------------------------------------------------------------- /u01/oradata/gzhs/undotbs2.dbf 23 SQL> SQL> show parameter undo TYPE VALUE ------------------------------------ ----------- ------------------------------ undo_management string AUTO undo_retention integer 10800 undo_suppress_errors boolean FALSE undo_tablespace string UNDOTBS1 SQL> show parameter spfile TYPE VALUE ------------------------------------ ----------- ------------------------------ spfile string spfile, undo UNDOTBS1 5. bash-2.03$ cd $ORACLE_HOME/dbs bash-2.03$ ls init.ora initgzhs.ora initgzhs.ora.old orapwgzhs initdw.ora initgzhs.ora.hurray lkgzhs snapcf_gzhs.f bash-2.03$ vi initgzhs.ora "initgzhs.ora" [Incomplete last line] 105 lines, 3087 characters ################################### # Copyright (c) 1991, 2001, 2002 by Oracle Corporation ################################### # Archive log_archive_dest_1='location=/u06/oradata/gzhs/arch' log_archive_format=%t_%s.dbf log_archive_start=true # Cache and I/O
db_block_size=8192 db_cache_size=2516582400 db_file_multiblock_read_count=16 # Cursors and Library Cache open_cursors=300 # Database Identification db_domain="" db_name=gzhs # Diagnostics and Statistics background_dump_dest=/oracle/admin/gzhs/bdump core_dump_dest=/oracle/admin/gzhs/cdump timed_statistics=true user_dump_dest=/oracle/admin/gzhs/udump # File Configuration control_files=("/u01/oradata/gzhs/control01.ctl", "/u03/oradata/gzhs/control03.ctl") "/u02/oradata/gzhs/control02.ctl", # Instance Identification instance_name=gzhs # Job Queues job_queue_processes=10 # MTS dispatchers="(protocol=tcp) (SERVICE=gzhsXDB)"
# Miscellaneous aq_tm_processes=1 compatible=9.2.0.0.0 # Optimizer hash_join_enabled=true query_rewrite_enabled=false star_transformation_enabled=false # Pools java_pool_size=629145600 large_pool_size=8388608 shared_pool_size=1153433600 # Processes and Sessions processes=150 # Redo Log and Recovery fast_start_mttr_target=300 # Security and Auditing remote_login_passwordfile=exclusive # Sort, Hash Joins, Bitmap Indexes pga_aggregate_target=838860800 sort_area_size=524288 # System Managed Undo and Rollback Segments
undo_management=auto undo_retention=10800 undo_tablespace=undotbs1 :q!. 6. alert UNDO : Sat Feb 7 20:30:12 2004 CREATE DATABASE gzhs MAXINSTANCES 1 MAXLOGHISTORY 1 MAXLOGFILES 5 MAXLOGMEMBERS 3 MAXDATAFILES 100 DATAFILE '/u01/oradata/gzhs/system01.dbf' SIZE 500M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE '/u01/oradata/gzhs/temp01.dbf' SIZE 1000M REUSE AUTOEXTEND ON NEXT 250M MAXSIZE UNLIMITED UNDO TABLESPACE "UNDOTBS1" DATAFILE '/u01/oradata/gzhs/undotbs01.dbf' SIZE 1000M REUSE AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED CHARACTER SET ZHS16GBK NATIONAL CHARACTER SET AL16UTF16 LOGFILE GROUP 1 ('/u01/oradata/gzhs/redo01.log') SIZE 256M, GROUP 2 ('/u01/oradata/gzhs/redo02.log') SIZE 256M, GROUP 3 ('/u01/oradata/gzhs/redo03.log') SIZE 256M, UNDOTBS2 : Wed Mar 24 20:20:58 2004 /* OracleOEM */ CREATE UNDO TABLESPACE "UNDOTBS2" DATAFILE '/u01/oradata/gzhs/undotbs2.dbf' SIZE 1024M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED Wed Mar 24 20:22:37 2004 Created Undo Segment _SYSSMU11$ Created Undo Segment _SYSSMU12$ Created Undo Segment _SYSSMU13$
Created Undo Segment _SYSSMU14$ Created Undo Segment _SYSSMU15$ Created Undo Segment _SYSSMU16$ Created Undo Segment _SYSSMU17$ Created Undo Segment _SYSSMU18$ Created Undo Segment _SYSSMU19$ Created Undo Segment _SYSSMU20$ Completed: /* OracleOEM */ CREATE UNDO TABLESPACE "UNDOTBS2" Wed Mar 24 20:24:25 2004 Undo Segment 11 Onlined Undo Segment 12 Onlined Undo Segment 13 Onlined Undo Segment 14 Onlined Undo Segment 15 Onlined Undo Segment 16 Onlined Undo Segment 17 Onlined Undo Segment 18 Onlined Undo Segment 19 Onlined Undo Segment 20 Onlined Successfully onlined Undo Tablespace 15. Undo Segment 1 Offlined Undo Segment 2 Offlined Undo Segment 3 Offlined Undo Segment 4 Offlined Undo Segment 5 Offlined Undo Segment 6 Offlined Undo Segment 7 Offlined Undo Segment 8 Offlined Undo Segment 9 Offlined Undo Segment 10 Offlined Undo Tablespace 1 successfully switched out. UNDO Wed Mar 24 20:24:25 2004 ALTER SYSTEM SET undo_tablespace='undotbs2' SCOPE=MEMORY;, UNDO pfile pfile. spfile both, spfile.
UNDOTBS1 Wed Mar 24 20:25:01 2004 /* OracleOEM */ DROP TABLESPACE "UNDOTBS1" INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS Wed Mar 24 20:25:03 2004 Deleted file /u01/oradata/gzhs/undotbs01.dbf Completed: /* OracleOEM */ DROP TABLESPACE "UNDOTBS1" INCLUDI pfile UNDOTBS1 7. pfile bash-2.03$ vi initgzhs.ora "initgzhs.ora" [Incomplete last line] 105 lines, 3087 characters ################################### # Copyright (c) 1991, 2001, 2002 by Oracle Corporation ################################### # Archive log_archive_dest_1='location=/u06/oradata/gzhs/arch' log_archive_format=%t_%s.dbf log_archive_start=true # Cache and I/O db_block_size=8192 db_cache_size=2516582400 db_file_multiblock_read_count=16 # Cursors and Library Cache open_cursors=300 # Database Identification db_domain="" db_name=gzhs
# Diagnostics and Statistics background_dump_dest=/oracle/admin/gzhs/bdump core_dump_dest=/oracle/admin/gzhs/cdump timed_statistics=true user_dump_dest=/oracle/admin/gzhs/udump # File Configuration control_files=("/u01/oradata/gzhs/control01.ctl", "/u03/oradata/gzhs/control03.ctl") "/u02/oradata/gzhs/control02.ctl", # Instance Identification instance_name=gzhs "initgzhs.ora" [Incomplete last line] 105 lines, 3087 characters # Redo Log and Recovery fast_start_mttr_target=300 # Security and Auditing remote_login_passwordfile=exclusive # Sort, Hash Joins, Bitmap Indexes pga_aggregate_target=838860800 sort_area_size=524288 # System Managed Undo and Rollback Segments undo_management=auto undo_retention=10800 undo_tablespace=undotbs2
~ ~ ~ ~ "initgzhs.ora" 105 lines, 3088 characters 8. bash-2.03$ sqlplus "/ as sysdba" SQL*Plus: Release 9.2.0.3.0 - Production on 4 1 11:55:11 2004 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. : Oracle9i Enterprise Edition Release 9.2.0.3.0-64bit Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.3.0 - Production SQL> select * from v$version; BANNER ---------------------------------------------------------------- Oracle9i Enterprise Edition Release 9.2.0.3.0-64bit Production PL/SQL Release 9.2.0.3.0 - Production CORE 9.2.0.3.0 Production TNS for Solaris: Version 9.2.0.3.0 - Production NLSRTL Version 9.2.0.3.0 - Production SQL> exit Oracle9i Enterprise Edition Release 9.2.0.3.0-64bit Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.3.0 - Production bash-2.03$ spfile pfile