iPod Nano 6入手初体验
经过长达两个多月的等待，终于收到了这份迟来的小礼物，iPod Nano 6 8GB。虽然来的有些迟，甚至因为新加坡那边儿投递的原因差点儿导致与这款小巧却功能强大的MP3失之交臂，但是收到快递包裹的那一刻，还是有一点儿小兴奋的。
话说这个免费礼物的由来，7月份的时候，接到365信息科技大讲堂的邮件邀请，于2011年7月20日参加了甲骨文网上讲座 “最高的数据库性能和快速 100% 的投资回报率”，会后成为众多参与者中的幸运儿。
初入手时，精致而又环保的包装，着实给用户一个完美的体验，尤其是那个设计别有用心的卡片式基本使用说明手册。拆解开来，银白色的机身，延续苹果产品的主打色系。据称，苹果的这款机器投入生产时白黑比是6:1，而市场反应是，消费者的喜好却是白黑比1:6，黑色系的机器颇受消费者的青睐。个人而言，白也好，黑也罢，好用就行，当然更不能颠倒黑白。
接下来，第一件事就是阅读手册，登录苹果官网，获取更多的用户指南。
第二，连接USB接口进行充电，在充电的过程中，顺便安装配置iTunes。这里出了一个小差错，充电大概10分钟左右，误操作断开了iPod Nano，导致充电结束。苹果官方申明初次充电要到2-3小时左右，担心会不会出什么问题，以后电池会不会都充不满？没辙呀，自己是个土包子，从未拥有过苹果的任何产品，使用经验更几乎为零。在此，向各位请教，为什么现在用USB连接线给Nano充电都充不满？是否需要购买iPod 专属充电器？
第三，使用iTunes同步数据，这个应用程序的功能比较强大，就是要注册用户的那个玩意儿比较繁琐，貌似要Visa信用卡号之类的，目前还未注册成功。
第四，Nano的收音机功能很好用，可以迅速搜索当地的所有无线电台。
第五，健身功能很好用，可以记录步行、跑步的数据，并且可以同步到Nike +。
第六，时钟不错，好用。
第七，MP3播放功能和效果自是不用说了，超牛逼。
第八，感谢365信息科技大讲堂，那次会议不仅给予我Oracle相关的知识,还赠送我这个可爱的小礼物。当然还要感谢自己对于Oracle数据库技术的热爱，看来选择从业Oracle DBA还是非常不错的，至少有Nano可以免费玩儿。欢迎Nano网友回复交流用户经验及Oracle数据库相关技术经验，谢谢！
最后，附上邮件确认函一封：
From: Oracle APAC Responses Team <response@oracleevent.com>
Date: Fri, 22 Jul 2011 07:44:40 +0100
Subject: 甲骨文网上讲座: 祝贺你, 您已成为甲骨文网上讲座的iPod Nano幸运儿
To: “3dian14@gmail.com” <3dian14@gmail.com>
Dear黄伟,
谢谢您出席2011 年 7 月 20 日, 星期三的甲骨文网上讲座 “最高的数据库性能和快速 100% 的投资回报率”
您已成为甲骨文网上讲座的iPod Nano幸运儿!
请您确认以下的地址:
厦门火炬高新区软件园二期望海路29#吉联研发大楼, 厦门, 福建 361006, China
(Xiamen Torch Hi-tech Software Park two WangHai 29 # Kyrgyzstan joint
R & D Building, Xiamen, Fujian 361006, China)
我们期待您的答复。
The Oracle Corporation
Test the new wordpress plugins
The purpose of this article is just for testing the new plugins installed on this blog…
记一次10g RAC 收缩表空间
这是公司一套10g RAC双节点数据库，近期业务量增长比较大，导致硬盘空间有些吃紧，在新的存储添加之前。我想到的是收缩表空间，缓解存储。做一简单记录如下：
① 操作系统版本：
[root@oracle-rac2 bin]# su - oracle [oracle@oracle-rac2 ~]$ uname -a Linux oracle-rac2.gillion.com.cn 2.6.18-194.0.0.0.3.el5 #1 SMP Mon Mar 29 18:14:09 EDT 2010 x86_64 x86_64 x86_64 GNU/Linux [oracle@oracle-rac2 ~]$ uname -r 2.6.18-194.0.0.0.3.el5
②数据库版本：
[oracle@oracle-rac2 ~]$ . oraenv ORACLE_SID = [glndb2] ? glndb [oracle@oracle-rac2 ~]$ export ORACLE_SID=glndb2 [oracle@oracle-rac2 ~]$ sqlplus SQL*Plus: Release 10.2.0.5.0 - Production on Wed May 25 09:32:03 2011 Copyright (c) 1982, 2010, Oracle. All Rights Reserved. SQL> conn / as sysdba; Connected. SQL> select * from v$version; BANNER ---------------------------------------------------------------- Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi PL/SQL Release 10.2.0.5.0 - Production CORE 10.2.0.5.0 Production TNS for Linux: Version 10.2.0.5.0 - Production NLSRTL Version 10.2.0.5.0 - Production SQL>
③ 采用ASM存储，收缩表空间之前，存储信息如下：
SQL> select name,block_size,state,type,total_mb,free_mb from v$asm_diskgroup; NAME BLOCK_SIZE STATE TYPE TOTAL_MB FREE_MB ------------------------------ ---------- ----------- ------ ---------- ---------- ORADATA 4096 MOUNTED EXTERN 262138 61364 SQL> select mount_status,header_status ,state,redundancy ,total_mb,free_mb ,name ,path from v$asm_disk; MOUNT_S HEADER_STATU STATE REDUNDA TOTAL_MB FREE_MB NAME PATH ------- ------------ -------- ------- ---------- ---------- ------------------------------ ---------- CACHED MEMBER NORMAL UNKNOWN 262138 61364 ORADATA_0000 /dev/oradata1
④ 表空间FR7_SA_DATA在收缩之前占用的空间如下：
SQL> select file_name,tablespace_name,bytes/1024/1024 M from dba_data_files where tablespace_name='FR7SA_DATA'; FILE_NAME ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ TABLESPACE_NAME M ------------------------------ ---------- +ORADATA/glndb/datafile/fr7sa_data.425.751556425 FR7SA_DATA 4996 接下来，采用如下命令resize该表空间下的数据文件： SQL> alter database datafile '+ORADATA/glndb/datafile/fr7sa_data.425.751556425' resize 3325M; Database altered. 收缩之后，该数据文件大小为3325M，大约释放了1670M的空间。 SQL> select file_name,tablespace_name,bytes/1024/1024 M from dba_data_files where tablespace_name='FR7SA_DATA'; FILE_NAME ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ TABLESPACE_NAME M ------------------------------ ---------- +ORADATA/glndb/datafile/fr7sa_data.425.751556425 FR7SA_DATA 3325 SQL>
⑤ 最后，再查看ASM磁盘、磁盘组的存储信息：
SQL>select name,block_size,state,type,total_mb,free_mb from v$asm_diskgroup; NAME BLOCK_SIZE STATE TYPE TOTAL_MB FREE_MB ------------------------------ ---------- ----------- ------ ---------- ---------- ORADATA 4096 MOUNTED EXTERN 262138 63035 SQL>select mount_status,header_status ,state,redundancy ,total_mb,free_mb ,name ,path from v$asm_disk; MOUNT_S HEADER_STATU STATE REDUNDA TOTAL_MB FREE_MB NAME PATH ------- ------------ -------- ------- ---------- ---------- ------------------------------ ---------- CACHED MEMBER NORMAL UNKNOWN 262138 63035 ORADATA_0000 /dev/oradata1
⑥ 至此，存储回收了大概1.6G。附上：收缩表空间的脚本。
col file# for 999
col name for a50
col resizecmd for a100
select a.file#,a.name,a.bytes/1024/1024 CurrentMB,
ceil(HWM * a.block_size)/1024/1024 ResizeTo,
(a.bytes - HWM * a.block_size)/1024/1024 ReleaseMB,
'alter database datafile '''||a.name||''' resize '||
ceil(HWM * a.block_size/1024/1024) || 'M;' ResizeCMD
from v$datafile a,
(select file_id,max(block_id+blocks-1) HWM
from dba_extents
group by file_id) b
where a.file# = b.file_id(+)
and (a.bytes - HWM *block_size)>0
order by 5
如果只是想对某个表个间的datafile resize,可采用:
select a.file#,a.name,a.bytes/1024/1024 CurrentMB,
ceil(HWM * a.block_size)/1024/1024 ResizeTo,
(a.bytes - HWM * a.block_size)/1024/1024 ReleaseMB,
'alter database datafile '''||a.name||''' resize '||
ceil(HWM * a.block_size/1024/1024) || 'M;' ResizeCMD
from v$datafile a,
(select file_id,max(block_id+blocks-1) HWM
from dba_extents where file_id in
(select b.file# From v$tablespace a ,v$datafile b
where a.ts#=b.ts# and a.name='MP2000')
group by file_id) b
where a.file# = b.file_id(+)
and (a.bytes - HWM *block_size)>0
order by 5
计划tempfile可以resize的空间.on apply that have only one tempfile
select 'alter database tempfile '''||a.name ||''' reize '||b.siz||'M;' from v$tempfile a,
(select tmsize.maxblk*bk.value/1024/1024 siz from
(select nvl(max(segblk#),128) maxblk from v$sort_usage) tmsize,
(select value From v$parameter where name = 'db_block_size') bk) b
生产环境，需慎重使用上述脚本。
ORA-00600: internal error code, arguments: [4194]
在修改_allow_resetlogs_corruption参数为TRUE，并强制打开数据库后，遭遇下述错误信息：
该错误信息来自alert告警日志文件：
Thu Feb 17 14:07:30 2011 Errors in file /u01/app/admin/orcl/bdump/orcl_smon_26850.trc: ORA-00600: internal error code, arguments: [4194], [41], [31], [], [], [], [], []
通常ORA-00600 4194级的错误是跟回滚段错误相关！
继续查看TRACE文件（/u01/app/admin/orcl/bdump/orcl_smon_26850.trc）
看到有下述相关信息：
Acq rbs _SYSSMU1$ KSL POST SENT postee=6 loc='ksasnd' id1=0 id2=0 name= type=0 Undo Segment 1 Onlined KSL POST SENT postee=6 loc='ksasnd' id1=0 id2=0 name= type=0 Acq rbs _SYSSMU2$ KSL POST SENT postee=6 loc='ksasnd' id1=0 id2=0 name= type=0 Undo Segment 2 Onlined KSL POST SENT postee=6 loc='ksasnd' id1=0 id2=0 name= type=0 Acq rbs _SYSSMU3$ KSL POST SENT postee=6 loc='ksasnd' id1=0 id2=0 name= type=0 Undo Segment 3 Onlined KSL POST SENT postee=6 loc='ksasnd' id1=0 id2=0 name= type=0 Acq rbs _SYSSMU4$ KSL POST SENT postee=6 loc='ksasnd' id1=0 id2=0 name= type=0 Undo Segment 4 Onlined KSL POST SENT postee=6 loc='ksasnd' id1=0 id2=0 name= type=0 Acq rbs _SYSSMU5$ KSL POST SENT postee=6 loc='ksasnd' id1=0 id2=0 name= type=0 Undo Segment 5 Onlined KSL POST SENT postee=6 loc='ksasnd' id1=0 id2=0 name= type=0 Acq rbs _SYSSMU6$ KSL POST SENT postee=6 loc='ksasnd' id1=0 id2=0 name= type=0 Undo Segment 6 Onlined KSL POST SENT postee=6 loc='ksasnd' id1=0 id2=0 name= type=0 Acq rbs _SYSSMU7$ KSL POST SENT postee=6 loc='ksasnd' id1=0 id2=0 name= type=0 Undo Segment 7 Onlined KSL POST SENT postee=6 loc='ksasnd' id1=0 id2=0 name= type=0 Acq rbs _SYSSMU8$ KSL POST SENT postee=6 loc='ksasnd' id1=0 id2=0 name= type=0 Undo Segment 8 Onlined KSL POST SENT postee=6 loc='ksasnd' id1=0 id2=0 name= type=0 Acq rbs _SYSSMU9$ KSL POST SENT postee=6 loc='ksasnd' id1=0 id2=0 name= type=0 Undo Segment 9 Onlined KSL POST SENT postee=6 loc='ksasnd' id1=0 id2=0 name= type=0 Acq rbs _SYSSMU10$ KSL POST SENT postee=6 loc='ksasnd' id1=0 id2=0 name= type=0 Undo Segment 10 Onlined
Google之，获取相关解决办法：
① 启动数据库，获取回滚段信息：
SYS@orcl > conn / as sysdba;
Connected to an idle instance.
SYS@orcl > startup
ORACLE instance started.
Total System Global Area 507510784 bytes
Fixed Size 1220240 bytes
Variable Size 171966832 bytes
Database Buffers 327155712 bytes
Redo Buffers 7168000 bytes
Database mounted.
ORA-00603: ORACLE server session terminated by fatal error
SYS@orcl > select * from v$rollname;
ERROR:
ORA-03114: not connected to ORACLE
SYS@orcl > conn / as sysdba;
Connected.
SYS@orcl > startup mount;
ORA-01081: cannot start already-running ORACLE - shut it down first
SYS@orcl > conn / as sysdba;
Connected.
SYS@orcl > select * from V$rollname;
USN NAME
---------- ------------------------------
0 SYSTEM
1 _SYSSMU1$
2 _SYSSMU2$
3 _SYSSMU3$
4 _SYSSMU4$
5 _SYSSMU5$
6 _SYSSMU6$
7 _SYSSMU7$
8 _SYSSMU8$
9 _SYSSMU9$
10 _SYSSMU10$
11 rows selected.
SYS@orcl >
此时，数据库虽可以使用，但是还是有问题的，过一段儿时间，莫名其妙的宕机！！！再次尝试启动数据库的时候，却非常正常，并未报错！！！
SYS@orcl > startup ORACLE instance started. Total System Global Area 507510784 bytes Fixed Size 1220240 bytes Variable Size 171966832 bytes Database Buffers 327155712 bytes Redo Buffers 7168000 bytes Database mounted. Database opened. SYS@orcl > select segment_name from dba_rollback_segs; SEGMENT_NAME ------------------------------ SYSTEM _SYSSMU1$ _SYSSMU2$ _SYSSMU3$ _SYSSMU4$ _SYSSMU5$ _SYSSMU6$ _SYSSMU7$ _SYSSMU8$ _SYSSMU9$ _SYSSMU10$ SEGMENT_NAME ------------------------------ _SYSSMU11$ 12 rows selected. SYS@orcl > select segment_name from dba_rollback_segs; select segment_name from dba_rollback_segs * ERROR at line 1: ORA-03135: connection lost contact SYS@orcl >
② 开始通过调整参数的方式，来修复数据库：
SYS@orcl > conn / as sysdba; Connected to an idle instance. SYS@orcl > create pfile from spfile; File created. SYS@orcl >
修改pfile，在该文件中，添加如下信息：
[oracle@rhel10g dbs]$ tail -2 /u01/app/oracle/dbs/initorcl.ora *.undo_management='MANUAL' _corrupted_rollback_segments='_SYSSMU1$','_SYSSMU2$','_SYSSMU3$',
'_SYSSMU4$','_SYSSMU5$','_SYSSMU6$','_SYSSMU7$','_SYSSMU8$',
'_SYSSMU9$','_SYSSMU10$','_SYSSMU11$'
这两个参数表示，回滚段手工管理，并设置_corrupted_rollback_segments后面的那11个回滚段为损坏状态！
③ 然后，用该PFILE来启动数据库：
SYS@orcl > startup pfile=/u01/app/oracle/dbs/initorcl.ora
ORACLE instance started.
Total System Global Area 507510784 bytes
Fixed Size 1220240 bytes
Variable Size 171966832 bytes
Database Buffers 327155712 bytes
Redo Buffers 7168000 bytes
Database mounted.
Database opened.
SYS@orcl > select * from V$rollname;
USN NAME
---------- ------------------------------
0 SYSTEM
SYS@orcl > select segment_name from dba_rollback_segs;
SEGMENT_NAME
------------------------------
SYSTEM
_SYSSMU1$
_SYSSMU2$
_SYSSMU3$
_SYSSMU4$
_SYSSMU5$
_SYSSMU6$
_SYSSMU7$
_SYSSMU8$
_SYSSMU9$
_SYSSMU10$
SEGMENT_NAME
------------------------------
_SYSSMU11$
12 rows selected.
数据库正常启动，并未像刚才的那样，莫名其妙的“死”掉！！！但是，数据看出现下述症状，普通用户在数据库内无法做事务操作，而SYS用户却可以。
SYS@orcl > select count(*) from m;
COUNT(*)
----------
176
SYS@orcl > insert into m select * from m;
176 rows created.
SYS@orcl >
上述看到，SYS用户正常做操作。而HR用户执行事务就报错：ORA-01552！
SYS@orcl > conn hr/hr;
Connected.
HR@orcl > select count(*) from t;
COUNT(*)
----------
214
HR@orcl > insert into t select * from t;
insert into t select * from t
*
ERROR at line 1:
ORA-01552: cannot use system rollback segment for non-system tablespace 'USERS'
HR@orcl >
④ 删除，并重建UNDO表空间：
SYS@orcl > conn / as sysdba; Connected. SYS@orcl > show parameter undo NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ undo_management string MANUAL undo_retention integer 900 undo_tablespace string UNDOTBS1 SYS@orcl > col file_name for a40 SYS@orcl > col tablespace_name for a15 SYS@orcl > set line 120 SYS@orcl > select file_name,tablespace_name from dba_data_files; FILE_NAME TABLESPACE_NAME ---------------------------------------- --------------- /u01/app/oradata/orcl/users01.dbf USERS /u01/app/oradata/orcl/sysaux01.dbf SYSAUX /u01/app/oradata/orcl/undotbs1.dbf UNDOTBS1 /u01/app/oradata/orcl/system01.dbf SYSTEM /u01/app/oradata/orcl/example01.dbf EXAMPLE SYS@orcl > drop tablespace undotbs1 including contents and datafiles; Tablespace dropped. SYS@orcl > select file_name,tablespace_name from dba_data_files; FILE_NAME TABLESPACE_NAME ---------------------------------------- --------------- /u01/app/oradata/orcl/users01.dbf USERS /u01/app/oradata/orcl/sysaux01.dbf SYSAUX /u01/app/oradata/orcl/system01.dbf SYSTEM /u01/app/oradata/orcl/example01.dbf EXAMPLE SYS@orcl > create undo tablespace undotbs1 datafile '/u01/app/oradata/orcl/undotbs1.dbf' size 50m; Tablespace created. SYS@orcl >
⑤ 关闭实例，生成新的参数文件：
SYS@orcl > shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SYS@orcl >
⑥ 修改PFILE，这次要去掉_corrupted_rollback_segments参数的设置，并将undo_management改回为AUTO：
[oracle@rhel10g ~]$ tail -3 /u01/app/oracle/dbs/initorcl.ora *.undo_tablespace='UNDOTBS1' *.user_dump_dest='/u01/app/admin/orcl/udump' *.undo_management='auto'
⑦ 然后创建SPFILE：
SYS@orcl > create spfile from pfile; File created. SYS@orcl > !strings /u01/app/oracle/dbs/spfileorcle.ora | grep undo strings: '/u01/app/oracle/dbs/spfileorcle.ora': No such file SYS@orcl > !strings /u01/app/oracle/dbs/spfileorcl.ora | grep undo *.undo_management='auto' *.undo_tablespace='UNDOTBS1' SYS@orcl >
⑧ 用新SPFILE启动数据库，并做校验：
SYS@orcl > startup
ORACLE instance started.
Total System Global Area 507510784 bytes
Fixed Size 1220240 bytes
Variable Size 171966832 bytes
Database Buffers 327155712 bytes
Redo Buffers 7168000 bytes
Database mounted.
Database opened.
SYS@orcl > select count(*) from m;
COUNT(*)
----------
352
SYS@orcl > select * from v$rollname;
USN NAME
---------- ------------------------------
0 SYSTEM
1 _SYSSMU1$
2 _SYSSMU2$
3 _SYSSMU3$
4 _SYSSMU4$
5 _SYSSMU5$
6 _SYSSMU6$
7 _SYSSMU7$
8 _SYSSMU8$
9 _SYSSMU9$
10 _SYSSMU10$
11 rows selected.
SYS@orcl > select segment_name from dba_rollback_segs;
SEGMENT_NAME
------------------------------
SYSTEM
_SYSSMU1$
_SYSSMU2$
_SYSSMU3$
_SYSSMU4$
_SYSSMU5$
_SYSSMU6$
_SYSSMU7$
_SYSSMU8$
_SYSSMU9$
_SYSSMU10$
11 rows selected.
SYS@orcl > select count(*) from m;
COUNT(*)
----------
352
SYS@orcl > conn hr/hr;
Connected.
HR@orcl > select count(*) from t;
COUNT(*)
----------
214
HR@orcl > insert into t select * from t;
214 rows created.
HR@orcl > commit;
Commit complete.
HR@orcl > select count(*) from t;
COUNT(*)
----------
428
HR@orcl >
运行一段时间，发现数据库并无其它异常。至此，解决ORA-00600 4194的错误！
Oracle _disable_logging and _allow_resetlogs_corruption
本文简单研究oracle隐含参数_disable_logging与_allow_resetlogs_corruption两个参数。
仅作学习测试用，实际情况需慎重，做好数据库的备份，以防丢失数据。
1 启动数据库，获取相关信息：
SYS@orcl > startup
ORACLE instance started.
Total System Global Area 507510784 bytes
Fixed Size 1220240 bytes
Variable Size 167772528 bytes
Database Buffers 331350016 bytes
Redo Buffers 7168000 bytes
Database mounted.
Database opened.
SYS@orcl > show parameter spfile;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /u01/app/oracle/dbs/spfileorcl
.ora
SYS@orcl > show parameter _disable
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
_disable_logging boolean FALSE
SYS@orcl > select count(*) from m;
COUNT(*)
----------
44
SYS@orcl > select * from V$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------------
1 1 0 52428800 1 YES UNUSED 0
2 1 0 52428800 1 YES UNUSED 0
3 1 1 52428800 1 NO CURRENT 1098823 17-FEB-11
可以看到，当前数据库使用SPFILE参数文件，_disable_logging参数为FALSE，测试表m有44条记录，当前日志组为第3组。
2修改隐含参数_disable_logging为TRUE，并重启数据库使之生效：
SYS@orcl > alter system set "_disable_logging"=true scope=spfile;
System altered.
SYS@orcl > shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@orcl > startup;
ORACLE instance started.
Total System Global Area 507510784 bytes
Fixed Size 1220240 bytes
Variable Size 167772528 bytes
Database Buffers 331350016 bytes
Redo Buffers 7168000 bytes
Database mounted.
Database opened.
SYS@orcl > show parameter _disable
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
_disable_logging boolean TRUE
SYS@orcl > select * from V$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------------
1 1 0 52428800 1 YES UNUSED 0
2 1 0 52428800 1 YES UNUSED 0
3 1 1 52428800 1 NO CURRENT 1098823 17-FEB-11
SYS@orcl > select count(*) from m;
COUNT(*)
----------
44
SYS@orcl > insert into m select * from m;
44 rows created.
SYS@orcl > commit;
Commit complete.
SYS@orcl > select * from V$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------------
1 1 0 52428800 1 YES UNUSED 0
2 1 0 52428800 1 YES UNUSED 0
3 1 1 52428800 1 NO CURRENT 1098823 17-FEB-11
插入44条记录，这44条记录写入当前组第3组日志文件中的。
SYS@orcl > alter system switch logfile;
System altered.
SYS@orcl > select * from V$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------------
1 1 2 52428800 1 NO CURRENT 1101521 17-FEB-11
2 1 0 52428800 1 YES UNUSED 0
3 1 1 52428800 1 NO ACTIVE 1098823 17-FEB-11
SYS@orcl > select count(*) from m;
COUNT(*)
----------
88
SYS@orcl > insert into m select * from m;
88 rows created.
SYS@orcl > commit;
Commit complete.
SYS@orcl > select count(*) from m;
COUNT(*)
----------
176
SYS@orcl > select * from V$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------------
1 1 2 52428800 1 NO CURRENT 1101521 17-FEB-11
2 1 0 52428800 1 YES UNUSED 0
3 1 1 52428800 1 NO ACTIVE 1098823 17-FEB-11
SYS@orcl > alter system checkpoint;
System altered.
SYS@orcl > select * from V$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------------
1 1 2 52428800 1 NO CURRENT 1101521 17-FEB-11
2 1 0 52428800 1 YES UNUSED 0
3 1 1 52428800 1 NO INACTIVE 1098823 17-FEB-11
SYS@orcl >
向m表插入记录（此时共有176条记录），做完日志切换，并做检查点，看到第三组状态有ACTIVE变为INACTIVE，说明检查点完成。
3 强制启动数据库FORCE：
SYS@orcl > startup force;
ORACLE instance started.
Total System Global Area 507510784 bytes
Fixed Size 1220240 bytes
Variable Size 167772528 bytes
Database Buffers 331350016 bytes
Redo Buffers 7168000 bytes
Database mounted.
Database opened.
SYS@orcl > select count(*) from m;
COUNT(*)
----------
176
SYS@orcl > select * from V$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------------
1 1 2 52428800 1 NO INACTIVE 1101521 17-FEB-11
2 1 3 52428800 1 NO CURRENT 1121523 17-FEB-11
3 1 1 52428800 1 NO INACTIVE 1098823 17-FEB-11
看到数据库一切正常，且M表中的176条记录完好。
4 继续插入测试数据，又插入176条记录，这176条记录对应的日志是在第2组的。
SYS@orcl > insert into m select * from m;
176 rows created.
SYS@orcl > alter system switch logfile;
System altered.
SYS@orcl > select * from V$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------------
1 1 2 52428800 1 NO INACTIVE 1101521 17-FEB-11
2 1 3 52428800 1 NO ACTIVE 1121523 17-FEB-11
3 1 4 52428800 1 NO CURRENT 1121706 17-FEB-11
SYS@orcl > select count(*) from m;
COUNT(*)
----------
352
SYS@orcl > insert into m select * from m;
352 rows created.
SYS@orcl > select * from V$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------------
1 1 2 52428800 1 NO INACTIVE 1101521 17-FEB-11
2 1 3 52428800 1 NO ACTIVE 1121523 17-FEB-11
3 1 4 52428800 1 NO CURRENT 1121706 17-FEB-11
SYS@orcl > startup force;
ORACLE instance started.
Total System Global Area 507510784 bytes
Fixed Size 1220240 bytes
Variable Size 167772528 bytes
Database Buffers 331350016 bytes
Redo Buffers 7168000 bytes
Database mounted.
ORA-00354: corrupt redo log block header
ORA-00353: log corruption near block 3 change 1121523 time 02/17/2011 11:45:37
ORA-00312: online log 2 thread 1: '/u01/app/oradata/orcl/redo02.log'
SYS@orcl > select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------------
1 1 2 52428800 1 NO INACTIVE 1101521 17-FEB-11
3 1 4 52428800 1 NO CURRENT 1121706 17-FEB-11
2 1 3 52428800 1 NO ACTIVE 1121523 17-FEB-11
不做检查点，FORCE强制启动数据库，发现报错！！！第2个日志组需要恢复。为什么呢，该怎么恢复呢？
5 修改另外一个隐含参数_allow_resetlogs_corruption为TRUE，并做恢复：
SYS@orcl > show parameter _allow
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
_allow_resetlogs_corruption boolean FALSE
SYS@orcl > alter system set "_allow_resetlogs_corruption"=true scope=spfile;
System altered.
SYS@orcl > startup force;
ORACLE instance started.
Total System Global Area 507510784 bytes
Fixed Size 1220240 bytes
Variable Size 167772528 bytes
Database Buffers 331350016 bytes
Redo Buffers 7168000 bytes
Database mounted.
ORA-00354: corrupt redo log block header
ORA-00353: log corruption near block 3 change 1121523 time 02/17/2011 11:45:37
ORA-00312: online log 2 thread 1: '/u01/app/oradata/orcl/redo02.log'
SYS@orcl > alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01139: RESETLOGS option only valid after an incomplete database recovery
SYS@orcl > recover database using backup controlfile until cancel;
ORA-00279: change 1121524 generated at 02/17/2011 11:45:37 needed for thread 1
ORA-00289: suggestion : /u01/app/flash_recovery_area/ORCL/
archivelog/2011_02_17/o1_mf_1_3_%u_.arc
ORA-00280: change 1121524 for thread 1 is in sequence #3
Specify log: {=suggested | filename | AUTO | CANCEL}
ORA-00308: cannot open archived log '/u01/app/flash_recovery_area/ORCL/archivelog/2011_02_17/o1_mf_1_3_%u_.arc'
ORA-27037: unable to obtain file status
Linux 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: '/u01/app/oradata/orcl/system01.dbf'
SYS@orcl > alter database open resetlogs;
Database altered.
SYS@orcl > select count(*) from m;
COUNT(*)
----------
176
SYS@orcl >
6 结果，数据库不完全恢复成功，但是丢失数据了。那么又丢失了哪些数据呢？
结论：①_disable_logging参数是禁止数据库产生日志的。可以看到，在日志组为ACTIVE状态下（检查点未完成），startup force时，该日志组需要恢复。也就是为什么做过检查点（alter system checkpoint）后，startup force时，数据还在，而未做检查点时，数据丢失；
②_allow_resetlogs_corruption参数允许在特定情况下将数据库强制打开，不做校验；
③本测试环境：数据库非归档模式，未做任何备份；
SYS@orcl > !uname -a Linux rhel10g 2.6.18-164.el5xen #1 SMP Tue Aug 18 16:06:30 EDT 2009 i686 athlon i386 GNU/Linux SYS@orcl > select * from v$version; BANNER ---------------------------------------------------------------- Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod PL/SQL Release 10.2.0.1.0 - Production CORE 10.2.0.1.0 Production TNS for Linux: Version 10.2.0.1.0 - Production NLSRTL Version 10.2.0.1.0 - Production SYS@orcl >
asmcmd 报错解决
在 RHEL 5.4 服务器上运行的一套 oracle 10g ASM的数据库，在调用asmcmd的时候遇到如下错误：
[oracle@rhel10g ~]$ asmcmd install_driver(Oracle) failed: Can't load '/u01/app/oracle/perl/lib/site_perl/5.8.3/i686-linux-thread-multi/auto/DBD/Oracle/Oracle.so' for module DBD::Oracle: /u01/app/oracle/lib/libnnz10.so: cannot restore segment prot after reloc: Permission denied at /u01/app/oracle/perl/lib/5.8.3/i686-linux-thread-multi/DynaLoader.pm line 229. at (eval 2) line 3Compilation failed in require at (eval 2) line 3. Perhaps a required shared library or dll isn't installed where expected at /u01/app/oracle/bin/asmcmdcore line 5953 [oracle@rhel10g ~]$
Google 之，给出解决方法：
关闭SELinux：
[oracle@rhel10g ~]$ su - root Password: [root@rhel10g ~]# setenforce 0 [root@rhel10g ~]# exit logout [oracle@rhel10g ~]$ export ORACLE_SID=+ASM [oracle@rhel10g ~]$ asmcmd ASMCMD>
问题基本解决，具体原因有待进一步探究！！！
Oracle time_zone
1. How to check the session time zone ?
=======================================
Answer
——
The new SESSIONTIMEZONE built-in SQL function returns the value of the current session’s time zone.
SQL> SELECT SESSIONTIMEZONE FROM DUAL;
SESSIONTIMEZONE
—————
+01:00
2. How to set the session time zone ?
=====================================
Answer
——
The session time zone can be set to:
- O/S local time zone
- Database time zone
- An absolute offset
- A named region
1. The first method consists to use one of the following ALTER SESSION SET TIME_ZONE statements:
SQL> ALTER SESSION SET TIME_ZONE = local;
SQL> ALTER SESSION SET TIME_ZONE = dbtimezone;
SQL> ALTER SESSION SET TIME_ZONE = ‘-05:00′;
SQL> ALTER SESSION SET TIME_ZONE = ‘Europe/London’;
2. As an alternative, the operating system environment variable ORA_SDTZ can also be used to set the session time zone:
ORA_SDTZ = ‘OS_TZ’ | ‘DB_TZ’ | ‘[+ | -] hh:mm’ | ‘timezone_region’
Example:
$ ORA_SDTZ=’OS_TZ’
$ export ORA_SDTZ
$ ORA_SDTZ=’-05:00′
$ export ORA_SDTZ
3. How to check the database time zone ?
========================================
Answer
——
The DBTIMEZONE SQL function returns the value of the database time zone.
SQL> SELECT DBTIMEZONE FROM DUAL;
DBTIMEZONE
————–
Europe/Lisbon
Note that the database timezone is also included in both database_properties and props$ views:
SQL> SELECT property_name, property_value
FROM database_properties
WHERE property_name=’DBTIMEZONE’;
and
SQL> SELECT name, value$
FROM props$
WHERE name=’DBTIMEZONE’;
Be aware that you should not rely on these views because in case of db time zone change, these views reflect the new db time zone too early: they should reflect
it only after database shutdown and restart.
4. How to set the database time zone ?
======================================
Answer
——
Note that the database time zone is only relevant for TIMESTAMP WITH LOCAL TIME ZONE columns.
1. At creation time
SQL> CREATE DATABASE …
SET TIME_ZONE=’Europe/London’;
If not specified with the CREATE DATABASE statement, the database time zone defaults to the server抯 O/S timezone offset.
2. After database creation, use the ALTER DATABASE SET TIME_ZONE statement and then shut down and restart the database.
SQL> ALTER DATABASE SET TIME_ZONE = ‘-05:00′;
SQL> ALTER DATABASE SET TIME_ZONE = ‘Europe/Lisbon’;
The change will not take effect until the database is bounced.
5. How to list the valid time zone regions ?
============================================
Answer
——
SQL> SELECT * FROM v$timezone_names;
TZNAME TZABBREV
—————————— ———-
Pacific/Tahiti LMT
Poland LMT
US/Pacific PST
Europe/Zurich CET
…
The result will depend on which timezone file is currently in use.
See Q&A 7-8 for further details.
6. How to retrieve the time zone offset corresponding to a time zone region ?
======================================================
The new 9i TZ_OFFSET() SQL function returns the time zone offset displacement to the input time zone region.
SQL> SELECT TZ_OFFSET(’Europe/London’) FROM DUAL;
TZ_OFFS
——-
+01:00
The returned offset depends on the date this statement is executed. For example, in the ‘US/Pacific’ time zone, it may return ‘-07:00′ or ‘-08:00′ whether daylight saving is in effect or not.
SQL> SELECT TZ_OFFSET(SESSIONTIMEZONE), TZ_OFFSET(DBTIMEZONE) FROM DUAL;
returns the time zone offset corresponding to the time zone set for session and database.
TZ_OFFS TZ_OFFS
——- ——-
+01:00 +00:00
7. How to use a larger set of defined time zones ?
==================================================
2 different time zone files contain for each zone the offset from UTC,the transition times for daylight savings and abbreviation :
- ORACLE_HOME/oracore/zoneinfo/timezone.dat
This is the default and contains the most commonly used time zones.
This is the smallest file.
- ORACLE_HOME/oracore/zoneinfo/timezlrg.dat
This file contains the larger set of defined time zones and can be used
if you require time zones that are not defined in the default time zone file.
To enable the use of this larger time zone data file :
1. Shutdown the database
2.1 On UNIX platforms :
set the ORA_TZFILE environment variable to the full pathname of
the location for the timezlrg.dat. By default, this should be
$ORACLE_HOME/oracore/zoneinfo/timezlrg.dat:
$ setenv ORA_TZFILE $ORACLE_HOME/oracore/zoneinfo/timezlrg.dat
2.2 On Windows systems :
You should add an ORA_TZFILE registry subkey for each of your Oracle Homes
with the Windows Registry Editor:
Start -> Run…
Type “regedit”, and click “ok”
Add or edit the following registry entry:
HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOMEID\ORA_TZFILE
where ID is the unique number identifying the Oracle home.
Set the ORA_TZFILE to the full pathname of the location for the timezlrg.dat: By default, the value should be %ORACLE_HOME%\oracore\zoneinfo\timezlrg.dat
3. Restart the database
Once the larger timezlrg.dat is used, it must continue to be used unless the user is sure that none of the non-default zones are used for data that is stored in the database.
Also, all databases that share information should use the same time zone data file.
Be aware that you can neither create nore alter these timezone definition files !
8. Which timezone-set is currently used ?
=========================================
Beside checking the UNIX ORA_TZFILE parameter or the Windows registry subkey,you can easily check whether you are using the large or the default timezone file with the following query:
SQL> SELECT COUNT(*) FROM v$timezone_names;
If the default-smallest timezone region file is in use, it will return:
COUNT(*)
———-
616 (in 9.0.1.x and 9.2.x)
or
COUNT(*)
———-
1250 (in 9.0.1.x and 9.2.x)
if the largest file is in use.
9. Can customers rely on Oracle time zones ?
============================================
Timezone.dat and timezlrg.dat are based on information maintained by the U.S. Navy Observatory.