spparameter的显得上涨的幅度

[20171106]修改show spparameter的展现宽度.txt

[20171106]修改show spparameter的展现宽度.txt

[20171110]_allow_read_only_corruption参数.txt

[20171110]_allow_read_only_corruption参数.txt

–//很经年累月前做的,修改show parameter的显得宽度.
–//链接: [20121023]转移show
parameter的体现宽度.txt=>

–//很经年累月前做的,修改show parameter的来得宽度.
–//链接: [20121023]转移show
parameter的展现宽度.txt=>

–//前天在修改查询隐含参数脚本时意识一个参数_allow_read_only_corruption,认为应该能够在十二分关闭的动静下以read
only打开.
–//本身测试看看.

–//昨日在修改查询隐含参数脚本时意识1个参数_allow_read_only_corruption,以为应该能够在足够关闭的事态下以read
only展开.
–//本身测试看看.

–//明日意识show
spparameter展现上涨的幅度也不是很有理,修改看看,并做二个记下:

–//明日意识show
spparameter呈现上升的幅度也不是很合理,修改看看,并做三个记录:

1.环境:

1.环境:

1.环境:
SYS@book> @ &r/ver1
PORT_STRING                    VERSION        BANNER

1.环境:
SYS@book> @ &r/ver1
PORT_STRING                    VERSION        BANNER

SYS@book> @ &r/ver1
PORT_STRING                    VERSION        BANNER

SYS@book> @ &r/ver1
PORT_STRING                    VERSION        BANNER









x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g
Enterprise Edition Release 11.2.0.4.0 – 64bit Production

x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g
Enterprise Edition Release 11.2.0.4.0 – 64bit Production

x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g
Enterprise Edition Release 11.2.0.4.0 – 64bit Production

x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g
Enterprise Edition Release 11.2.0.4.0 – 64bit Production

SYS@book> show spparameter control_files
SID      NAME                         
TYPE                                     VALUE

SYS@book> show spparameter control_files
SID      NAME                         
TYPE                                     VALUE

SYS@book> @ &r/hide _allow_read_only_corruption
NAME                       
DESCRIPTION                                      DEFAULT_VALUE

SYS@book> @ &r/hide _allow_read_only_corruption
NAME                       
DESCRIPTION                                      DEFAULT_VALUE



SESSION_VALUE SYSTEM_VALUE



_allow_read_only_corruption allow read-only open even if database is
corrupt TRUE          FALSE         FALSE

2.测试前筹算:

SYS@book> create pfile=’/tmp/init@.ora’ from spfile ;
File created.

–//修改 /tmp/initbook.ora文件,参预如下内容:
*._allow_read_only_corruption=true

–//做一个相当关闭数据库.
SYS@book> shutdown abort ;
ORACLE instance shut down.

SYS@book> startup mount pfile=’/tmp/init@.ora’
ORACLE instance started.
Total System Global Area  634732544 bytes
Fixed Size                  2255792 bytes
Variable Size             197133392 bytes
Database Buffers          427819008 bytes
Redo Buffers                7524352 bytes
Database mounted.

SYS@book> show parameter allow
NAME                        TYPE    VALUE


_allow_read_only_corruption boolean TRUE

–//表明只要配置了参数在参数文件中,隐含参数实际上也足以选拔show
parameter.不过像前边带3个下划线参数,show parameter依然无
–//法查询.比如:
SYS@book> show parameter __java_pool_size
SYS@book> @ &r/hide __java_pool_size
NAME              DESCRIPTION                       DEFAULT_VALUE
SESSION_VALUE SYSTEM_VALUE



__java_997755.com澳门葡京,pool_size  Actual size in bytes of java pool FALSE        
4194304       4194304

SYS@book> alter database open read only;
alter database open read only
*
ERROR at line 1:
ORA-16005: database requires recovery

–//能够开采不行.可能须要_allow_resetlogs_corruption参数协作.

3.持续测试:
–//修改 /tmp/initbook.ora文件,参加如下内容:
*._allow_resetlogs_corruption=true

SYS@book> @ &r/logfile
GROUP# STATUS     TYPE       MEMBER                           IS_
GROUP# THREAD# SEQUENCE#       BYTES BLOCKSIZE MEMBERS ARC STATUS    
FIRST_CHANGE# FIRST_TIME          NEXT_CHANGE# NEXT_TIME




     1            ONLINE     /mnt/ramdisk/book/redo01.log     NO      
1       1       728    52428800       512       1 NO  CURRENT     
13277659048 2017-11-09 16:18:09 2.814750E+14
     2            ONLINE     /mnt/ramdisk/book/redo02.log     NO      
2       1       726    52428800       512       1 YES INACTIVE    
13277630208 2017-11-09 09:54:47  13277632611 2017-11-09 10:18:14
     3            ONLINE     /mnt/ramdisk/book/redo03.log     NO      
3       1       727    52428800       512       1 YES INACTIVE    
13277632611 2017-11-09 10:18:14  13277659048 2017-11-09 16:18:09
     4            STANDBY    /mnt/ramdisk/book/redostb01.log  NO
     5            STANDBY    /mnt/ramdisk/book/redostb02.log  NO
     6            STANDBY    /mnt/ramdisk/book/redostb03.log  NO
     7            STANDBY    /mnt/ramdisk/book/redostb04.log  NO
7 rows selected.

$ mv /mnt/ramdisk/book/redo01.log /mnt/ramdisk/book/redo01.log_xxx

–//那样制止找到redo文件.或然假象redo0壹.log文件损坏了.
SYS@book> shutdown abort ;
ORACLE instance shut down.

SYS@book> startup mount pfile=’/tmp/init@.ora’
ORACLE instance started.
Total System Global Area    634732544 bytes
Fixed Size                    2255792 bytes
Variable Size               197133392 bytes
Database Buffers            427819008 bytes
Redo Buffers                  7524352 bytes
Database mounted.

SYS@book> @ &r/hide allow_r%corrupt%
NAME                       
DESCRIPTION                                      DEFAULT_VALUE

SESSION_VALUE SYSTEM_VALUE



_allow_read_only_corruption allow read-only open even if database is
corrupt TRUE          FALSE         FALSE

二.测试前计划:

SYS@book> create pfile=’/tmp/init@.ora’ from spfile ;
File created.

–//修改 /tmp/initbook.ora文件,参加如下内容:
*._allow_read_only_corruption=true

–//做2个那三个关闭数据库.
SYS@book> shutdown abort ;
ORACLE instance shut down.

SYS@book> startup mount pfile=’/tmp/init@.ora’
ORACLE instance started.
Total System Global Area  634732544 bytes
Fixed Size                  2255792 bytes
Variable Size             197133392 bytes
Database Buffers          427819008 bytes
Redo Buffers                7524352 bytes
Database mounted.

SYS@book> show parameter allow
NAME                        TYPE    VALUE


_allow_read_only_corruption boolean TRUE

–//表明只要配置了参数在参数文件中,隐含参数实际上也能够动用show
parameter.不过像前面带一个下划线参数,show parameter还是无
–//法查询.比如:
SYS@book> show parameter __java_pool_size
SYS@book> @ &r/hide __java_pool_size
NAME              DESCRIPTION                       DEFAULT_VALUE
SESSION_VALUE SYSTEM_VALUE



__java_pool_size  Actual size in bytes of java pool FALSE        
4194304       4194304

SYS@book> alter database open read only;
alter database open read only
*
ERROR at line 1:
ORA-16005: database requires recovery

–//可以窥见不行.只怕供给_allow_resetlogs_corruption参数同盟.

三.卫冕测试:
–//修改 /tmp/initbook.ora文件,参加如下内容:
*._allow_resetlogs_corruption=true

SYS@book> @ &r/logfile
GROUP# STATUS     TYPE       MEMBER                           IS_
GROUP# THREAD# SEQUENCE#       BYTES BLOCKSIZE MEMBERS ARC STATUS    
FIRST_CHANGE# FIRST_TIME          NEXT_CHANGE# NEXT_TIME




     1            ONLINE     /mnt/ramdisk/book/redo01.log     NO      
1       1       728    52428800       512       1 NO  CURRENT     
13277659048 2017-11-09 16:18:09 2.814750E+14
     2            ONLINE     /mnt/ramdisk/book/redo02.log     NO      
2       1       726    52428800       512       1 YES INACTIVE    
13277630208 2017-11-09 09:54:47  13277632611 2017-11-09 10:18:14
     3            ONLINE     /mnt/ramdisk/book/redo03.log     NO      
3       1       727    52428800       512       1 YES INACTIVE    
13277632611 2017-11-09 10:18:14  13277659048 2017-11-09 16:18:09
     4            STANDBY    /mnt/ramdisk/book/redostb01.log  NO
     5            STANDBY    /mnt/ramdisk/book/redostb02.log  NO
     6            STANDBY    /mnt/ramdisk/book/redostb03.log  NO
     7            STANDBY    /mnt/ramdisk/book/redostb04.log  NO
7 rows selected.

$ mv /mnt/ramdisk/book/redo01.log /mnt/ramdisk/book/redo01.log_xxx

–//那样制止找到redo文件.或然假象redo01.log文件损坏了.
SYS@book> shutdown abort ;
ORACLE instance shut down.

SYS@book> startup mount pfile=’/tmp/init@.ora’
ORACLE instance started.
Total System Global Area    634732544 bytes
Fixed Size                    2255792 bytes
Variable Size               197133392 bytes
Database Buffers            427819008 bytes
Redo Buffers                  7524352 bytes
Database mounted.

SYS@book> @ &r/hide allow_r%corrupt%
NAME                       
DESCRIPTION                                      DEFAULT_VALUE



SESSION_VALUE SYSTEM_VALUE



_allow_read_only_corruption allow read-only open even if database is
corrupt FALSE         TRUE          TRUE
_allow_resetlogs_corruption allow resetlogs even if it will cause
corruption FALSE         TRUE          TRUE

SYS@book> alter database open read only;
alter database open read only
*
ERROR at line 1:
ORA-16005: database requires recovery

$ oerr ora 16005
16005, 00000, “database requires recovery”
// *Cause:  The database requires recovery, and therefore cannot be
opened for
//          read-only access by this instance.
// *Action: Perform the necessary recovery and reopen for read-only
access.
//

–//视乎与_allow_resetlogs_corruption无关,取消*._allow_resetlogs_corruption=true设置,重来..

SYS@book> shutdown abort ;
ORACLE instance shut down.
SYS@book> startup mount pfile=’/tmp/init@.ora’
ORACLE instance started.
Total System Global Area    634732544 bytes
Fixed Size                    2255792 bytes
Variable Size               197133392 bytes
Database Buffers            427819008 bytes
Redo Buffers                  7524352 bytes
Database mounted.
SYS@book> @ &r/hide allow_r%corrupt%
old  10:  and lower(a.ksppinm) like lower(‘%&1%’)
new  10:  and lower(a.ksppinm) like lower(‘%allow_r%corrupt%%’)
NAME                       
DESCRIPTION                                      DEFAULT_VALUE

SESSION_VALUE SYSTEM_VALUE



_allow_read_only_spparameter的显得上涨的幅度。corruption allow read-only open even if database is
corrupt FALSE         TRUE          TRUE
_allow_resetlogs_corruption allow resetlogs even if it will cause
corruption FALSE         TRUE          TRUE

SYS@book> alter database open read only;
alter database open read only
*
ERROR at line 1:
ORA-16005: database requires recovery

$ oerr ora 16005
16005, 00000, “database requires recovery”
// *Cause:  The database requires recovery, and therefore cannot be
opened for
//          read-only access by this instance.
// *Action: Perform the necessary recovery and reopen for read-only
access.
//

–//视乎与_allow_resetlogs_corruption无关,取消*._allow_resetlogs_corruption=true设置,重来..

SYS@book> shutdown abort ;
ORACLE instance shut down.
SYS@book> startup mount pfile=’/tmp/init@.ora’
ORACLE instance started.
Total System Global Area    634732544 bytes
Fixed Size                    2255792 bytes
Variable Size               197133392 bytes
Database Buffers            427819008 bytes
Redo Buffers                  7524352 bytes
Database mounted.
SYS@book> @ &r/hide allow_r%corrupt%
old  10:  and lower(a.ksppinm) like lower(‘%&1%’)
new  10:  and lower(a.ksppinm) like lower(‘%allow_r%corrupt%%’)
NAME                       
DESCRIPTION                                      DEFAULT_VALUE

*        control_files                
string                                   /mnt/ramdisk/book/control01.
                                                                               
ctl
*        control_files                
string                                   /mnt/ramdisk/book/control02.
                                                                               
ctl

*        control_files                
string                                   /mnt/ramdisk/book/control01.
                                                                               
ctl
*        control_files                
string                                   /mnt/ramdisk/book/control02.
                                                                               
ctl

SESSION_VALUE SYSTEM_VALUE



_allow_read_only_corruption allow read-only open even if database is
corrupt FALSE         TRUE          TRUE
_allow_resetlogs_corruption allow resetlogs even if it will cause
corruption TRUE          FALSE         FALSE

SYS@book> recover database until cancel;
ORA-00279: change 13277663682 generated at 11/10/2017 09:14:12 needed
for thread 1
ORA-00289: suggestion :
/u01/app/oracle/archivelog/book/1_728_896605872.dbf
ORA-00280: change 13277663682 for thread 1 is in sequence #728
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00308: cannot open archived log
‘/u01/app/oracle/archivelog/book/1_728_896605872.dbf’
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

ORA-00308: cannot open archived log
‘/u01/app/oracle/archivelog/book/1_728_896605872.dbf’
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error
below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: ‘/mnt/ramdisk/book/system01.dbf’

SYS@book> alter database open read only;
alter database open read only
*
ERROR at line 1:
ORA-16005: database requires recovery

–//照旧不行.失望!!

肆.看望符合规律关闭数据库丢失有些redo的情景吧?
$ mv /mnt/ramdisk/book/redo01.log_xxx /mnt/ramdisk/book/redo01.log

–//先苏醒到不荒谬状态.
SYS@book> shutdown abort ;
ORACLE instance shut down.
SYS@book> startup mount
ORACLE instance started.
Total System Global Area    634732544 bytes
Fixed Size                    2255792 bytes
Variable Size               197133392 bytes
Database Buffers            427819008 bytes
Redo Buffers                  7524352 bytes
Database mounted.

SYS@book> @ &r/hide allow_r%corrupt%
NAME                       
DESCRIPTION                                      DEFAULT_VALUE

SESSION_VALUE SYSTEM_VALUE



_allow_read_only_corruption allow read-only open even if database is
corrupt FALSE         TRUE          TRUE
_allow_resetlogs_corruption allow resetlogs even if it will cause
corruption TRUE          FALSE         FALSE

SYS@book> recover database until cancel;
ORA-00279: change 13277663682 generated at 11/10/2017 09:14:12 needed
for thread 1
ORA-00289: suggestion :
/u01/app/oracle/archivelog/book/1_728_896605872.dbf
ORA-00280: change 13277663682 for thread 1 is in sequence #728
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00308: cannot open archived log
‘/u01/app/oracle/archivelog/book/1_728_896605872.dbf’
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

ORA-00308: cannot open archived log
‘/u01/app/oracle/archivelog/book/1_728_896605872.dbf’
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error
below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: ‘/mnt/ramdisk/book/system01.dbf’

SYS@book> alter database open read only;
alter database open read only
*
ERROR at line 1:
ORA-16005: database requires recovery

–//依然不行.失望!!

4.探视不荒谬关闭数据库丢失有些redo的情状吧?
$ mv /mnt/ramdisk/book/redo01.log_xxx /mnt/ramdisk/book/redo01.log

–//先复苏到正常状态.
SYS@book> shutdown abort ;
ORACLE instance shut down.
SYS@book> startup mount
ORACLE instance started.
Total System Global Area    634732544 bytes
Fixed Size                    2255792 bytes
Variable Size               197133392 bytes
Database Buffers            427819008 bytes
Redo Buffers                  7524352 bytes
Database mounted.

SYS@book> @ &r/hide allow_r%corrupt%
NAME                       
DESCRIPTION                                      DEFAULT_VALUE

–//出现折市价况,突显不是很好.

–//出现折市价况,展现不是很好.

SESSION_VALUE SYSTEM_VALUE



_allow_read_only_corruption allow read-only open even if database is
corrupt FALSE         TRUE          TRUE
_allow_resetlogs_corruption allow resetlogs even if it will cause
corruption TRUE          FALSE         FALSE

SYS@book> recover database until cancel;
ORA-00279: change 13277663682 generated at 11/10/2017 09:14:12 needed
for thread 1
ORA-00289: suggestion :
/u01/app/oracle/archivelog/book/1_728_896605872.dbf
ORA-00280: change 13277663682 for thread 1 is in sequence #728
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/mnt/ramdisk/book/redo01.log
Log applied.
Media recovery complete.

SYS@book> alter database open ;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SYS@book> alter database open NORESETLOGS;
Database altered.

–//那种气象下是能够NORESETLOGS张开的,因为redo文件设置回来了.

SYS@book> select open_mode from v$database ;

SESSION_VALUE SYSTEM_VALUE



_allow_read_only_corruption allow read-only open even if database is
corrupt FALSE         TRUE          TRUE
_allow_resetlogs_corruption allow resetlogs even if it will cause
corruption TRUE          FALSE         FALSE

SYS@book> recover database until cancel;
ORA-00279: change 13277663682 generated at 11/10/2017 09:14:12 needed
for thread 1
ORA-00289: suggestion :
/u01/app/oracle/archivelog/book/1_728_896605872.dbf
ORA-00280: change 13277663682 for thread 1 is in sequence #728
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/mnt/ramdisk/book/redo01.log
Log applied.
Media recovery complete.

SYS@book> alter database open ;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SYS@book> alter database open NORESETLOGS;
Database altered.

–//那种境况下是足以NORESETLOGS张开的,因为redo文件设置回来了.

SYS@book> select open_mode from v$database ;

二.做2个追踪:
SYS@book> @ &r/10046on 12
old   1: alter session set events ‘10046 trace name context forever,
level &1’
new   1: alter session set events ‘10046 trace name context forever,
level 12’
Session altered.

2.做1个追踪:
SYS@book> @ &r/10046on 12
old   1: alter session set events ‘10046 trace name context forever,
level &1’
new   1: alter session set events ‘10046 trace name context forever,
level 12’
Session altered.

OPEN_MODE

READ WRITE

SYS@book> shutdown immediate ;
Database closed.
Database dismounted.
ORACLE instance shut down.

SYS@book> startup mount pfile=’/tmp/init@.ora’
ORACLE instance started.
Total System Global Area    634732544 bytes
Fixed Size                    2255792 bytes
Variable Size               197133392 bytes
Database Buffers            427819008 bytes
Redo Buffers                  7524352 bytes
Database mounted.
SYS@book> @ &r/logfile
GROUP# STATUS TYPE       MEMBER                           IS_ GROUP#
THREAD# SEQUENCE#       BYTES BLOCKSIZE MEMBERS ARC STATUS    
FIRST_CHANGE# FIRST_TIME          NEXT_CHANGE# NEXT_TIME




     1        ONLINE     /mnt/ramdisk/book/redo01.log     NO      
1       1       728    52428800       512       1 YES INACTIVE    
13277659048 2017-11-09 16:18:09  13277684336 2017-11-10 09:54:02
     2        ONLINE     /mnt/ramdisk/book/redo02.log     NO      
2       1       729    52428800       512       1 NO  CURRENT     
13277684336 2017-11-10 09:54:02 2.814750E+14
     3        ONLINE     /mnt/ramdisk/book/redo03.log     NO      
3       1       727    52428800       512       1 YES INACTIVE    
13277632611 2017-11-09 10:18:14  13277659048 2017-11-09 16:18:09
     4        STANDBY    /mnt/ramdisk/book/redostb01.log  NO
     5        STANDBY    /mnt/ramdisk/book/redostb02.log  NO
     6        STANDBY    /mnt/ramdisk/book/redostb03.log  NO
     7        STANDBY    /mnt/ramdisk/book/redostb04.log  NO
7 rows selected.

–//当前是/mnt/ramdisk/book/redo02.log.
$ mv /mnt/ramdisk/book/redo02.log /mnt/ramdisk/book/redo02.log_xxx

SYS@book> alter database open read only ;
Database altered.

SYS@book> @ &r/hide allow_r%corrupt%
NAME                       
DESCRIPTION                                      DEFAULT_VALUE

OPEN_MODE

READ WRITE

SYS@book> shutdown immediate ;
Database closed.
Database dismounted.
ORACLE instance shut down.

SYS@book> startup mount pfile=’/tmp/init@.ora’
ORACLE instance started.
Total System Global Area    634732544 bytes
Fixed Size                    2255792 bytes
Variable Size               197133392 bytes
Database Buffers            427819008 bytes
Redo Buffers                  7524352 bytes
Database mounted.
SYS@book> @ &r/logfile
GROUP# STATUS TYPE       MEMBER                           IS_ GROUP#
THREAD# SEQUENCE#       BYTES BLOCKSIZE MEMBERS ARC STATUS    
FIRST_CHANGE# FIRST_TIME          NEXT_CHANGE# NEXT_TIME




     1        ONLINE     /mnt/ramdisk/book/redo01.log     NO      
1       1       728    52428800       512       1 YES INACTIVE    
13277659048 2017-11-09 16:18:09  13277684336 2017-11-10 09:54:02
     2        ONLINE     /mnt/ramdisk/book/redo02.log     NO      
2       1       729    52428800       512       1 NO  CURRENT     
13277684336 2017-11-10 09:54:02 2.814750E+14
     3        ONLINE     /mnt/ramdisk/book/redo03.log     NO      
3       1       727    52428800       512       1 YES INACTIVE    
13277632611 2017-11-09 10:18:14  13277659048 2017-11-09 16:18:09
     4        STANDBY    /mnt/ramdisk/book/redostb01.log  NO
     5        STANDBY    /mnt/ramdisk/book/redostb02.log  NO
     6        STANDBY    /mnt/ramdisk/book/redostb03.log  NO
     7        STANDBY    /mnt/ramdisk/book/redostb04.log  NO
7 rows selected.

–//当前是/mnt/ramdisk/book/redo02.log.
$ mv /mnt/ramdisk/book/redo02.log /mnt/ramdisk/book/redo02.log_xxx

SYS@book> alter database open read only ;
Database altered.

SYS@book> @ &r/hide allow_r%corrupt%
NAME                       
DESCRIPTION                                      DEFAULT_VALUE

SYS@book> show spparameter control_files
SID      NAME                         
TYPE                                     VALUE

SYS@book> show spparameter control_files
SID      NAME                         
TYPE                                     VALUE

SESSION_VALUE SYSTEM_VALUE



_allow_read_only_corruption allow read-only open even if database is
corrupt FALSE         TRUE          TRUE
_allow_resetlogs_corruption allow resetlogs even if it will cause
corruption TRUE          FALSE         FALSE

–//^_^,这些参数意义相当小,要在例行关闭的情景下,redo文件损坏的情景下,可以选择它展开数据库.

5.探访使用正规参数运行状态如何?
SYS@book> shutdown immediate ;
Database closed.
Database dismounted.
ORACLE instance shut down.

SYS@book> startup
ORACLE instance started.
Total System Global Area    634732544 bytes
Fixed Size                    2255792 bytes
Variable Size               197133392 bytes
Database Buffers            427819008 bytes
Redo Buffers                  7524352 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 18301
Session ID: 274 Serial number: 3

–//因为/mnt/ramdisk/book/redo0二.log文件不可能找到,运转战败.修改回来:

$ mv /mnt/ramdisk/book/redo02.log_xxx /mnt/ramdisk/book/redo02.log

SYS@book> startup
ORACLE instance started.
Total System Global Area  634732544 bytes
Fixed Size                  2255792 bytes
Variable Size             197133392 bytes
Database Buffers          427819008 bytes
Redo Buffers                7524352 bytes
Database mounted.
Database opened.

SYS@book> @ &r/hide allow_r%corrupt%
old  10:  and lower(a.ksppinm) like lower(‘%&1%’)
new  10:  and lower(a.ksppinm) like lower(‘%allow_r%corrupt%%’)
NAME                       
DESCRIPTION                                      DEFAULT_VALUE

SESSION_VALUE SYSTEM_VALUE



_allow_read_only_corruption allow read-only open even if database is
corrupt FALSE         TRUE          TRUE
_allow_resetlogs_corruption allow resetlogs even if it will cause
corruption TRUE          FALSE         FALSE

–//^_^,这几个参数意义非常的小,要在健康关闭的情形下,redo文件损坏的情状下,可以动用它开发数据库.

5.看看使用正规参数运行状态怎么样?
SYS@book> shutdown immediate ;
Database closed.
Database dismounted.
ORACLE instance shut down.

SYS@book> startup
ORACLE instance started.
Total System Global Area    634732544 bytes
Fixed Size                    2255792 bytes
Variable Size               197133392 bytes
Database Buffers            427819008 bytes
Redo Buffers                  7524352 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 18301
Session ID: 274 Serial number: 3

–//因为/mnt/ramdisk/book/redo0二.log文件不恐怕找到,运营退步.修改回来:

$ mv /mnt/ramdisk/book/redo02.log_xxx /mnt/ramdisk/book/redo02.log

SYS@book> startup
ORACLE instance started.
Total System Global Area  634732544 bytes
Fixed Size                  2255792 bytes
Variable Size             197133392 bytes
Database Buffers          427819008 bytes
Redo Buffers                7524352 bytes
Database mounted.
Database opened.

SYS@book> @ &r/hide allow_r%corrupt%
old  10:  and lower(a.ksppinm) like lower(‘%&1%’)
new  10:  and lower(a.ksppinm) like lower(‘%allow_r%corrupt%%’)
NAME                       
DESCRIPTION                                      DEFAULT_VALUE



SESSION_VALUE SYSTEM_VALUE



_allow_read_only_corruption allow read-only open even if database is
corrupt TRUE          FALSE         FALSE
_allow_resetlogs_corruption allow resetlogs even if it will cause
corruption TRUE          FALSE         FALSE

总结:
1._allow_read_only_corruption参数意义相当小,要在健康关闭的境况下,有些redo文件不存在也许破坏的情事下目前open
read only张开.
二.举个例子数据库文件头与操纵文件当前scn区别等,供给复苏是心有余而力不足运用它,read
only张开的,怪不得很少见人提到那些参数.

–//补充测试redo损坏不是current的情状.
SYS@book> shutdown immediate ;
Database closed.
Database dismounted.
ORACLE instance shut down.

SYS@book> startup mount pfile=’/tmp/init@.ora’
ORACLE instance started.
Total System Global Area  634732544 bytes
Fixed Size                  2255792 bytes
Variable Size             197133392 bytes
Database Buffers          427819008 bytes
Redo Buffers                7524352 bytes
Database mounted.

SYS@book> @ &r/logfile
GROUP# STATUS     TYPE       MEMBER                          IS_
GROUP# THREAD# SEQUENCE#       BYTES BLOCKSIZE MEMBERS ARC STATUS    
FIRST_CHANGE# FIRST_TIME          NEXT_CHANGE# NEXT_TIME




     1            ONLINE     /mnt/ramdisk/book/redo01.log    NO      
1       1       728    52428800       512       1 YES INACTIVE    
13277659048 2017-11-09 16:18:09  13277684336 2017-11-10 09:54:02
     2            ONLINE     /mnt/ramdisk/book/redo02.log    NO      
2       1       729    52428800       512       1 NO  CURRENT     
13277684336 2017-11-10 09:54:02 2.814750E+14
     3            ONLINE     /mnt/ramdisk/book/redo03.log    NO      
3       1       727    52428800       512       1 YES INACTIVE    
13277632611 2017-11-09 10:18:14  13277659048 2017-11-09 16:18:09
     4            STANDBY    /mnt/ramdisk/book/redostb01.log NO
     5            STANDBY    /mnt/ramdisk/book/redostb02.log NO
     6            STANDBY    /mnt/ramdisk/book/redostb03.log NO
     7            STANDBY    /mnt/ramdisk/book/redostb04.log NO
7 rows selected.

$ mv /mnt/ramdisk/book/redo03.log /mnt/ramdisk/book/redo03.log_xxx

SYS@book> alter database open ;
alter database open
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 18497
Session ID: 274 Serial number: 3

–//无法张开数据库.

SYS@book> startup mount pfile=’/tmp/init@.ora’
ORACLE instance started.

Total System Global Area  634732544 bytes
Fixed Size                  2255792 bytes
Variable Size             197133392 bytes
Database Buffers          427819008 bytes
Redo Buffers                7524352 bytes
Database mounted.

SYS@book> alter database open read only ;
Database altered.

–//还原实地:

SYS@book> shutdown immediate ;
Database closed.
Database dismounted.
ORACLE instance shut down.

$ mv /mnt/ramdisk/book/redo03.log_xxx /mnt/ramdisk/book/redo03.log

SYS@book> startup
ORACLE instance started.
Total System Global Area  634732544 bytes
Fixed Size                  2255792 bytes
Variable Size             197133392 bytes
Database Buffers          427819008 bytes
Redo Buffers                7524352 bytes
Database mounted.
Database opened.

SESSION_VALUE SYSTEM_VALUE



_allow_read_only_corruption allow read-only open even if database is
corrupt TRUE          FALSE         FALSE
_allow_resetlogs_corruption allow resetlogs even if it will cause
corruption TRUE          FALSE         FALSE

总结:
1._allow_read_only_corruption参数意义非常的小,要在例行关闭的处境下,有些redo文件不设有也许损坏的状态下一时半刻open
read only张开.
二.1旦数据库文件头与调节文件当前scn不均等,须求还原是不可能选拔它,read
only展开的,怪不得很少见人涉及那个参数.

–//补充测试redo损坏不是current的情状.
SYS@book> shutdown immediate ;
Database closed.
Database dismounted.
ORACLE instance shut down.

SYS@book> startup mount pfile=’/tmp/init@.ora’
ORACLE instance started.
Total System Global Area  634732544 bytes
Fixed Size                  2255792 bytes
Variable Size             197133392 bytes
Database Buffers          427819008 bytes
Redo Buffers                7524352 bytes
Database mounted.

SYS@book> @ &r/logfile
GROUP# STATUS     TYPE       MEMBER                          IS_
GROUP# THREAD# SEQUENCE#       BYTES BLOCKSIZE MEMBERS ARC STATUS    
FIRST_CHANGE# FIRST_TIME          NEXT_CHANGE# NEXT_TIME




     1            ONLINE     /mnt/ramdisk/book/redo01.log    NO      
1       1       728    52428800       512       1 YES INACTIVE    
13277659048 2017-11-09 16:18:09  13277684336 2017-11-10 09:54:02
     2            ONLINE     /mnt/ramdisk/book/redo02.log    NO      
2       1       729    52428800       512       1 NO  CURRENT     
13277684336 2017-11-10 09:54:02 2.814750E+14
     3            ONLINE     /mnt/ramdisk/book/redo03.log    NO      
3       1       727    52428800       512       1 YES INACTIVE    
13277632611 2017-11-09 10:18:14  13277659048 2017-11-09 16:18:09
     4            STANDBY    /mnt/ramdisk/book/redostb01.log NO
     5            STANDBY    /mnt/ramdisk/book/redostb02.log NO
     6            STANDBY    /mnt/ramdisk/book/redostb03.log NO
     7            STANDBY    /mnt/ramdisk/book/redostb04.log NO
7 rows selected.

$ mv /mnt/ramdisk/book/redo03.log /mnt/ramdisk/book/redo03.log_xxx

SYS@book> alter database open ;
alter database open
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 18497
Session ID: 274 Serial number: 3

–//不也许张开数据库.

SYS@book> startup mount pfile=’/tmp/init@.ora’
ORACLE instance started.

Total System Global Area  634732544 bytes
Fixed Size                  2255792 bytes
Variable Size             197133392 bytes
Database Buffers          427819008 bytes
Redo Buffers                7524352 bytes
Database mounted.

SYS@book> alter database open read only ;
Database altered.

–//还原实地:

SYS@book> shutdown immediate ;
Database closed.
Database dismounted.
ORACLE instance shut down.

$ mv /mnt/ramdisk/book/redo03.log_xxx /mnt/ramdisk/book/redo03.log

SYS@book> startup
ORACLE instance started.
Total System Global Area  634732544 bytes
Fixed Size                  2255792 bytes
Variable Size             197133392 bytes
Database Buffers          427819008 bytes
Redo Buffers                7524352 bytes
Database mounted.
Database opened.



*        control_files                
string                                   /mnt/ramdisk/book/control01.
                                                                               
ctl
*        control_files                
string                                   /mnt/ramdisk/book/control02.
                                                                               
ctl
SYS@book> @ &r/10046off
Session altered.

*        control_files                
string                                   /mnt/ramdisk/book/control01.
                                                                               
ctl
*        control_files                
string                                   /mnt/ramdisk/book/control02.
                                                                               
ctl
SYS@book> @ &r/10046off
Session altered.

–//检查转储文件能够窥见实际实施如下:

PARSING IN CURSOR #139831052073784 len=248 dep=0 uid=0 oct=3 lid=0
tim=1509954996964120 hv=3485984188 ad=’7c0b1308′ sqlid=’28vy07r7wgtdw’
SELECT SID SID_COL_PLUS_SHOW_SPPARAM, NAME
NAME_COL_PLUS_SHOW_SPPARAM, TYPE, DISPLAY_VALUE
VALUE_COL_PLUS_SHOW_SPPARAM FROM V$SPPARAMETER WHERE UPPER(NAME)
LIKE UPPER(:NMBIND_SHOW_OBJ) ORDER BY
NAME_COL_PLUS_SHOW_SPPARAM,VALUE_COL_PLUS_SHOW_SPPARAM
END OF STMT

–//很分明VLUES对应的展现字段是VALUE_COL_PLUS_SHOW_SPPARAM.

SYS@book> column value_col_plus_show_spparam format a100
SYS@book> show spparameter control_files
SID      NAME                         
TYPE                                     VALUE




*        control_files                
string                                  
/mnt/ramdisk/book/control01.ctl
*        control_files                
string                                  
/mnt/ramdisk/book/control02.ctl

–//OK,今后来得就不会油可是生折市价况了.修改$ORACLE_HOME/sqlplus/admin/glogin.sql.加入column
value_col_plus_show_spparamformat a十0那行就长久有效了.

–//检查转储文件能够开采其实实行如下:

PARSING IN CURSOR #139831052073784 len=248 dep=0 uid=0 oct=3 lid=0
tim=1509954996964120 hv=3485984188 ad=’7c0b1308′ sqlid=’28vy07r7wgtdw’
SELECT SID SID_COL_PLUS_SHOW_SPPARAM, NAME
NAME_COL_PLUS_SHOW_SPPARAM, TYPE, DISPLAY_VALUE
VALUE_COL_PLUS_SHOW_SPPARAM FROM V$SPPARAMETER WHERE UPPER(NAME)
LIKE UPPER(:NMBIND_SHOW_OBJ) ORDER BY
NAME_COL_PLUS_SHOW_SPPARAM,VALUE_COL_PLUS_SHOW_SPPARAM
END OF STMT

–//很显明VLUES对应的体现字段是VALUE_COL_PLUS_SHOW_SPPARAM.

SYS@book> column value_col_plus_show_spparam format a100
SYS@book> show spparameter control_files
SID      NAME                         
TYPE                                     VALUE




*        control_files                
string                                  
/mnt/ramdisk/book/control01.ctl
*        control_files                
string                                  
/mnt/ramdisk/book/control02.ctl

–//OK,今后展示就不会并发折市价况了.修改$ORACLE_HOME/sqlplus/admin/glogin.sql.加入column
value_col_plus_show_spparamformat a十0那行就永世有效了.

相关文章

发表评论

电子邮件地址不会被公开。 必填项已用*标注

*
*
Website