SPFILE的使用

Transcription

1 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

2 (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 :35 2,048 PWDeyglen.ORA 1 2, ,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)

3 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

4 . 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 bytes Fixed Size bytes Variable Size bytes Database Buffers bytes Redo Buffers bytes SQL> show parameter log_archive_start TYPE VALUE log_archive_start boolean TRUE SQL> show parameter spfile TYPE VALUE

5 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 bytes Fixed Size Variable Size Database Buffers Redo Buffers bytes bytes bytes bytes SQL> show parameter spfile TYPE VALUE spfile string E:\Oracle\Ora9iR2\database\SPF ILEEYGLEN.ORA SQL> show parameter log_archive_start TYPE

6 VALUE log_archive_start boolean FALSE ALTER SYSTEM SPFILE. SQL> alter system set log_archive_start=false scope=spfile;

7 . 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 bytes Fixed Size bytes

8 Variable Size bytes Database Buffers bytes Redo Buffers 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 bytes Fixed Size bytes

9 Variable Size bytes Database Buffers bytes Redo Buffers 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

10 TRUE SQL> shutdown immediate ORACLE SQL> startup ORACLE Total System Global Area bytes Fixed Size Variable Size Database Buffers Redo Buffers bytes bytes bytes 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 *

11 ERROR 1 : ORA-02095: SQL> ALTER SYSTEM SET sql_trace=false SCOPE=SPFILE; 4 shutdown spfile, SQL> show sga Total System Global Area bytes Fixed Size Variable Size Database Buffers Redo Buffers bytes bytes bytes bytes SQL> shutdown immediate ORACLE SQL> create pfile from spfile; SQL> create spfile from pfile; SQL>

12 . 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(*)

13 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

14 . SPFILE Oracle Spfile Rman (autoback) Oracle ( ) Spfile : a. : [oracle@jumper oracle]$ rman target / Recovery Manager: Release Production Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved. connected to target database: HSJF (DBID= ) 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 Production on Sat Jan 17 01:08: Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Connected to: Oracle9i Enterprise Edition Release Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release Production

15 SQL> select * from v$rman_configuration; CONF# VALUE 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: Starting control autobackup Control autobackup written to DISK device handle '/opt/oracle/product/9.2.0/dbs/c ' 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;

16 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= input archive log thread=1 sequence=64 recid=169 stamp= input archive log thread=1 sequence=65 recid=170 stamp= input archive log thread=1 sequence=66 recid=171 stamp= channel ch1: starting piece 1 at 02-DEC-03 channel ch1: finished piece 1 at 02-DEC-03 piece handle=e:\oracle\orabak\penny 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= archive log filename=e:\oracle\oradata\penny\archive\1_64.dbf recid=169 stamp= archive log filename=e:\oracle\oradata\penny\archive\1_65.dbf recid=170 stamp= archive log filename=e:\oracle\oradata\penny\archive\1_66.dbf recid=171 stamp= Finished backup at 02-DEC-03 Starting Control File and SPFILE Autobackup at 02-DEC-03 piece handle=e:\oracle\ora92\database\c 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 FF 16

17 c. spfile bdump]$ rman target / Recovery Manager: Release Production Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved. connected to target database: HSJF (DBID= ) 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: channel ORA_DISK_1: autobackup found: c 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 oracle dba :34 /tmp/spfileeygle.ora : [oracle@jumper bdump]$ rman target / Recovery Manager: Release Production Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved. connected to target database: HSJF (DBID= ) RMAN> restore controlfile to '/tmp/control01.ctl' from autobackup;

18 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: channel ORA_DISK_1: autobackup found: c channel ORA_DISK_1: controlfile restore from autobackup complete Finished restore at 17-JAN-04 RMAN> exit Recovery Manager complete. bdump]$ ls -l /tmp/control* -rw-r oracle dba :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 bytes Fixed Size bytes

19 Variable Size bytes Database Buffers bytes Redo Buffers bytes Database mounted. Database opened. SQL> show parameter event TYPE VALUE event string trace name context forever Oracle9i JDBC Thin Driver alert.log : Wed Jan 7 17:17: 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: 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: 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: 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: 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:

20 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: 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: 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: Errors in file /opt/oracle/admin/phsdb/udump/phsdb_ora_1785.trc: ORA-00600: internal error code, arguments: [ttcgcshnd-1], [0], [], [], [], [], [], [] bug: ORA Metalink. SPFILE SPFILE SPFILE SQL> create pfile='e:\initeyglen.ora' from spfile; SQL> shutdown immediate ORACLE Initeyglen.ora *.aq_tm_processes=1

21 *.background_dump_dest='e:\oracle\admin\eyglen\bdump' *.compatible=' ' *.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= *.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= *.job_queue_processes=10 *.large_pool_size= *.open_cursors=300 *.pga_aggregate_target= *.processes=150 *.query_rewrite_enabled='false' *.remote_login_passwordfile='exclusive' *.shared_pool_size= *.sort_area_size= *.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

22 SQL> startup pfile='e:\initeyglen.ora' ORACLE Total System Global Area bytes Fixed Size bytes Variable Size bytes Database Buffers bytes Redo Buffers 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

23

24 . 920 PFILE $ORACLE_BASE\admin\db_name\spfile [init.ora ] 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 :49 1,028 INITeyglen.ORA :35 2,048 PWDeyglen.ORA :05 3,584 SPFILEEYGLEN.ORA 3 6, ,455,616 E:\Oracle\Ora9iR2\database>sqlplus /nolog SQL*Plus: Release Production on :16: Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. SQL> connect / as sysdba SQL> startup ORACLE

25 Total System Global Area bytes Fixed Size bytes Variable Size bytes Database Buffers bytes Redo Buffers 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 :49 1,028 INITeyglen.ORA :35 2,048 PWDeyglen.ORA 2 3, ,435,136 SQL> startup ORACLE Total System Global Area bytes Fixed Size bytes Variable Size bytes Database Buffers bytes Redo Buffers bytes

26 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 bytes Fixed Size bytes Variable Size bytes Database Buffers bytes Redo Buffers bytes SQL> show parameter spfile TYPE VALUE spfile string %ORACLE_HOME%\DATABASE\SPFILE% ORACLE_SID%.ORA SQL>. spfile. : SUN Solaris8 :9203

27 . UNDO. : 1. alert.log SunOS 5.8 login: root Password: Last login: Thu Apr 1 11:39:16 from 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, characters Sat Feb 7 20:30: 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: System parameters with non-default values: processes = 150 timed_statistics = TRUE shared_pool_size = large_pool_size = java_pool_size = control_files = /u01/oradata/gzhs/control01.ctl, /u02/oradata/gzhs/control02.ctl, /u03/oradata/gzhs/control03.ctl db_block_size = 8192 db_cache_size = compatible = 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

28 undo_tablespace = UNDOTBS1 undo_retention = 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 = db_name = gzhs open_cursors = 300 star_transformation_enabled= FALSE query_rewrite_enabled = FALSE pga_aggregate_target = 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, characters USER: terminating instance due to error Instance terminated by USER, pid = ORA-1092 signalled during: ALTER DATABASE OPEN... Thu Apr 1 11:11: 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: System parameters with non-default values: processes = 150 timed_statistics = TRUE shared_pool_size = large_pool_size = java_pool_size = control_files = /u01/oradata/gzhs/control01.ctl, /u02/oradata/gzhs/control02.ctl, /u03/oradata/gzhs/control03.ctl

29 db_block_size = 8192 db_cache_size = compatible = 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 = 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 = db_name = gzhs open_cursors = 300 star_transformation_enabled= FALSE query_rewrite_enabled = FALSE pga_aggregate_target = 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: starting up 1 shared server(s)... QMN0 started with pid=9 Thu Apr 1 11:11: 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

30 Thu Apr 1 11:11: ARCH: STARTING ARCH PROCESSES COMPLETE Thu Apr 1 11:11: ARC0: Thread not mounted Thu Apr 1 11:11: ARC1: Archival started ARC1: Thread not mounted Thu Apr 1 11:11: ALTER DATABASE MOUNT Thu Apr 1 11:11: Successful mount of redo thread 1, with mount id Thu Apr 1 11:11: Database mounted in Exclusive Mode. Completed: ALTER DATABASE MOUNT Thu Apr 1 11:11: alter database open Thu Apr 1 11:11: Beginning crash recovery of 1 threads Thu Apr 1 11:11: Started first pass scan Thu Apr 1 11:11: Completed first pass scan 1 redo blocks read, 0 data blocks need recovery Thu Apr 1 11:11: Started recovery at Thread 1: logseq 177, block 2, scn 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: Completed redo application Thu Apr 1 11:11: Ended recovery at Thread 1: logseq 177, block 3, scn data blocks read, 0 data blocks written, 1 redo blocks read Crash recovery completed successfully Thu Apr 1 11:11: 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: ARC0: Evaluating archive log 3 thread 1 sequence 177 Thu Apr 1 11:11:

31 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: SMON: enabling cache recovery ARC0: Completed archiving log 3 thread 1 sequence 177 Thu Apr 1 11:11: 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: Error happened during db open, shutting down database USER: terminating instance due to error Instance terminated by USER, pid = ORA-1092 signalled during: alter database open... :q Open. 2. bash-2.03$ sqlplus "/ as sysdba" SQL*Plus: Release Production on :43: Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. SQL> startup ORACLE Total System Global Area bytes Fixed Size bytes Variable Size bytes Database Buffers bytes Redo Buffers bytes ORA-01092: ORACLE.

32 3. bash-2.03$ cd /u01/ oradata/gzhs bash-2.03$ ls -l total rw-r oracle dba Apr 1 11:44 UNDOTBS2.dbf -rw-r oracle dba Apr 1 11:44 WAP12_BILLINGDETAIL.dbf -rw-r oracle dba Apr 1 11:44 WAP12_MAIN.dbf -rw-r oracle dba Apr 1 11:44 WAP12_MAIN10.dbf -rw-r oracle dba Apr 1 11:44 WAP12_MAIN11.dbf -rw-r oracle dba Apr 1 11:44 WAP12_MAIN2.dbf -rw-r oracle dba Apr 1 11:44 WAP12_MAIN3.dbf -rw-r oracle dba Apr 1 11:44 WAP12_MAIN4.dbf -rw-r oracle dba Apr 1 11:44 WAP12_MAIN5.dbf -rw-r oracle dba Apr 1 11:44 WAP12_MAIN6.dbf -rw-r oracle dba Apr 1 11:44 WAP12_MAIN7.dbf -rw-r oracle dba Apr 1 11:44 WAP12_MAIN8.dbf -rw-r oracle dba Apr 1 11:44 WAP12_MAIN9.dbf -rw-r oracle dba Apr 1 11:44 WAP12_MVIEW.dbf -rw-r oracle dba Mar 24 17:15 WAP12_TEMP1.dbf -rw-r oracle dba Apr 1 11:44 control01.ctl -rw-r oracle dba Apr 1 11:44 cwmlite01.dbf -rw-r oracle dba Apr 1 11:44 drsys01.dbf -rw-r oracle dba Apr 1 11:44 example01.dbf -rw-r oracle dba Apr 1 11:44 indx01.dbf -rw-r--r-- 1 oracle dba 1113 Mar 31 22:51 mkqio.dat -rw-r oracle dba Apr 1 11:44 odm01.dbf -rw-r oracle dba Apr 1 11:44 redo01.log -rw-r oracle dba Apr 1 11:44 redo02.log -rw-r oracle dba Apr 1 11:44 redo03.log -rw-r oracle dba Apr 1 11:44 system01.dbf -rw-r oracle dba Feb 7 21:10 temp01.dbf -rw-r oracle dba Apr 1 11:44 tools01.dbf -rw-r oracle dba Apr 1 11:44 users01.dbf -rw-r oracle dba Apr 1 11:44 xdb01.dbf UNDOTBS2.dbf 4. mount bash-2.03$ sqlplus "/ as sysdba" SQL*Plus: Release Production on :46: Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

33 SQL> SQL> SQL> startup mount; ORACLE Total System Global Area bytes Fixed Size bytes Variable Size bytes Database Buffers bytes Redo Buffers 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

34 /u01/oradata/gzhs/undotbs2.dbf 23 SQL> SQL> show parameter undo TYPE VALUE undo_management string AUTO undo_retention integer 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

35 db_block_size=8192 db_cache_size= 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)"

36 # Miscellaneous aq_tm_processes=1 compatible= # Optimizer hash_join_enabled=true query_rewrite_enabled=false star_transformation_enabled=false # Pools java_pool_size= large_pool_size= shared_pool_size= # 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= sort_area_size= # System Managed Undo and Rollback Segments

37 undo_management=auto undo_retention=10800 undo_tablespace=undotbs1 :q!. 6. alert UNDO : Sat Feb 7 20:30: 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: /* OracleOEM */ CREATE UNDO TABLESPACE "UNDOTBS2" DATAFILE '/u01/oradata/gzhs/undotbs2.dbf' SIZE 1024M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED Wed Mar 24 20:22: Created Undo Segment _SYSSMU11$ Created Undo Segment _SYSSMU12$ Created Undo Segment _SYSSMU13$

38 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: 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: ALTER SYSTEM SET undo_tablespace='undotbs2' SCOPE=MEMORY;, UNDO pfile pfile. spfile both, spfile.

39 UNDOTBS1 Wed Mar 24 20:25: /* OracleOEM */ DROP TABLESPACE "UNDOTBS1" INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS Wed Mar 24 20:25: 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= db_file_multiblock_read_count=16 # Cursors and Library Cache open_cursors=300 # Database Identification db_domain="" db_name=gzhs

40 # 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= sort_area_size= # System Managed Undo and Rollback Segments undo_management=auto undo_retention=10800 undo_tablespace=undotbs2

41 ~ ~ ~ ~ "initgzhs.ora" 105 lines, 3088 characters 8. bash-2.03$ sqlplus "/ as sysdba" SQL*Plus: Release Production on :55: Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. : Oracle9i Enterprise Edition Release bit Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release Production SQL> select * from v$version; BANNER Oracle9i Enterprise Edition Release bit Production PL/SQL Release Production CORE Production TNS for Solaris: Version Production NLSRTL Version Production SQL> exit Oracle9i Enterprise Edition Release bit Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release Production bash-2.03$ spfile pfile