DG-dispatcher ‘D000’ encountered error getting listening address

DG 的备库一直报错:

ound dead dispatcher 'D000', pid = (29, 89)
Thu May 04 10:15:26 2017
dispatcher 'D000' encountered error getting listening address
found dead dispatcher 'D000', pid = (29, 90)
Thu May 04 10:15:29 2017
dispatcher 'D000' encountered error getting listening address
found dead dispatcher 'D000', pid = (29, 91)
Thu May 04 10:15:29 2017
dispatcher 'D000' encountered error getting listening address
found dead dispatcher 'D000', pid = (29, 92)
Thu May 04 10:15:29 2017
dispatcher 'D000' encountered error getting listening address
found dead dispatcher 'D000', pid = (29, 93)
Thu May 04 10:15:29 2017
dispatcher 'D000' encountered error getting listening address

其实这个是在网络解析上出现问题

[oracle@lps admin]$ cat /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
172.16.94.181 test primary
172.16.94.184 test standby

这里我是利用172.16.94.184 test standby 这套老数据库进行克隆的,所以解析不了。

[oracle@lps admin]$ cat /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
172.16.94.181 test primary
172.16.94.187 lps standby

修改后重启数据库无报错

ORA-00600 [kfioTranslateIO03][17090]

今天一同事打psu补丁后,数据库起不来。我试了一下单节点启动

SQL> startup
ORACLE instance started.

Total System Global Area 1.1758E+10 bytes
Fixed Size                  2239056 bytes
Variable Size            2013267376 bytes
Database Buffers         9663676416 bytes
Redo Buffers               79237120 bytes
ORA-03113: end-of-file on communication channel
Process ID: 9559
Session ID: 177 Serial number: 1

查看alert 日志:

ORA-00600: internal error code, arguments: [kfioTranslateIO03], [], [], [], [], [], [], [], [], [], [], []
Incident details in: /oracle/app/oracle/diag/rdbms/corebank_jy/corebank1/incident/incdir_48250/corebank1_ckpt_9890_i48250.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Errors in file /oracle/app/oracle/diag/rdbms/corebank_jy/corebank1/trace/corebank1_ckpt_9890.trc  (incident=48251):
ORA-00600: internal error code, arguments: [17090], [], [], [], [], [], [], [], [], [], [], []
Incident details in: /oracle/app/oracle/diag/rdbms/corebank_jy/corebank1/incident/incdir_48251/corebank1_ckpt_9890_i48251.trc
Dumping diagnostic data in directory=[cdmp_20170407113759], requested by (instance=1, osid=9890 (CKPT)), summary=[incident=48250].
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
ERROR: unrecoverable error ORA-600 raised in ASM I/O path; terminating process 9890 
Dumping diagnostic data in directory=[cdmp_20170407113759], requested by (instance=1, osid=9890 (CKPT)), summary=[incident=48251].
PMON (ospid: 9819): terminating the instance due to error 469
System state dump requested by (instance=1, osid=9819 (PMON)), summary=[abnormal instance termination].
System State dumped to trace file /oracle/app/oracle/diag/rdbms/corebank_jy/corebank1/trace/corebank1_diag_9831.trc
Dumping diagnostic data in directory=[cdmp_20170407113800], requested by (instance=1, osid=9819 (PMON)), summary=[abnormal instance termination].
Instance terminated by PMON, pid = 9819

一般遇到连接不上oracle ,我会检查bin/oracle权限
检查:oracle
ls -l oracle
grid:
-rwsr-s–x 1 grid oinstall 204400823 Nov 20 16:29 oracle

oracle:
-rwsr-s–x 1 oracle oinstall 232845330 Apr 7 10:56 oracle

权限都对。根据MOS ORA-00600 [kfioTranslateIO03] [17090] (文档 ID 1336846.1)

Solution #1] Group information for $RDBMS_HOME/bin/oracle should be changed to the group that can read/write to ASM devices. 

Please execute the following action plan from note 1084186.1.

$ su - grid
$ cd <Grid Home>/bin
$ ./setasmgidwrap o=<11.2 RDBMS Home>/bin/oracle

Solution #2]  Reinstall correct ASMlib that matches up to Linux version on the server.

Solution #3]  Change config.c entry to "asmadmin" and "relink all" as grid user after unlocking GRID home
                      See note 1536057.1 on how to unlock GRID home for relink all.

执行:
./setasmgidwrap o=/oracle/app/oracle/product/11.2.0/dbhome_1/bin/oracle

[grid@rifcdbs1 bin]$ srvctl start database -d corebank 
[grid@rifcdbs1 bin]$ crsctl status res -t
--------------------------------------------------------------------------------
NAME           TARGET  STATE        SERVER                   STATE_DETAILS       
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATA.dg
               ONLINE  ONLINE       rifcdbs1                                     
               ONLINE  ONLINE       rifcdbs2                                     
ora.DATADG.dg
               OFFLINE OFFLINE      rifcdbs1                                     
               OFFLINE OFFLINE      rifcdbs2                                     
ora.LISTENER.lsnr
               ONLINE  ONLINE       rifcdbs1                                     
               ONLINE  ONLINE       rifcdbs2                                     
ora.OCR.dg
               ONLINE  ONLINE       rifcdbs1                                     
               ONLINE  ONLINE       rifcdbs2                                     
ora.asm
               ONLINE  ONLINE       rifcdbs1                 Started             
               ONLINE  ONLINE       rifcdbs2                 Started             
ora.gsd
               OFFLINE OFFLINE      rifcdbs1                                     
               OFFLINE OFFLINE      rifcdbs2                                     
ora.net1.network
               ONLINE  ONLINE       rifcdbs1                                     
               ONLINE  ONLINE       rifcdbs2                                     
ora.ons
               ONLINE  ONLINE       rifcdbs1                                     
               ONLINE  ONLINE       rifcdbs2                                     
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
      1        ONLINE  ONLINE       rifcdbs1                                     
ora.corebank.db
      1        ONLINE  ONLINE       rifcdbs1                 Open,Readonly       
      2        ONLINE  ONLINE       rifcdbs2                 Open,Readonly       
ora.cvu
      1        ONLINE  ONLINE       rifcdbs1                                     
ora.oc4j
      1        ONLINE  ONLINE       rifcdbs1                                     
ora.rifcdbs1.vip
      1        ONLINE  ONLINE       rifcdbs1                                     
ora.rifcdbs2.vip
      1        ONLINE  ONLINE       rifcdbs2                                     
ora.scan1.vip
      1        ONLINE  ONLINE       rifcdbs1  

pandas学习之Series

利用Python进行数据分析

pandas基础: Series和DataFrame的简单介绍

pandas 是什么

pandas 是基于 NumPy 的一个 Python 数据分析包,主要目的是为了数据分析。它提供了大量高级的数据结构和对数据处理的方法。
pandas 有两个主要的数据结构:Series 和 DataFrame。

Series

Series 是一个一维数组对象 ,类似于 NumPy 的一维 array。它除了包含一组数据还包含一组索引,所以可以把它理解为一组带索引的数组。

1)将 Python 数组转换成 Series 对象:




import  pandas as pd

obj = Series([1,3,5,6,9,-19])
Traceback (most recent call last):
  File "C:\Users\cl\Anaconda2\lib\site-packages\IPython\core\interactiveshell.py", line 2881, in run_code
    exec(code_obj, self.user_global_ns, self.user_ns)
  File "<ipython-input-3-ae872dcd031c>", line 1, in <module>
    obj = Series([1,3,5,6,9,-19])
NameError: name 'Series' is not defined

这里没有将Series 进行声明,所以执行报错,现在声明下:


from pandas import Series, DataFrame

obj = Series([1,3,5,6,9,-19])
obj
Out[6]: 
0     1

2)将 Python 字典转换成 Series 对象:


dic={'a':1,'b':2,'c':3,'d':4}
obj3=Series(dic)
obj3
Out[9]: 
a    1
b    2
c    3
d    4
dtype: int64

也可以通过 index 参数显示指定索引:


obj2 = Series([1,3,5,6,9,-10],index=['a','b','c','d','e','f'])
obj2
Out[11]: 
a     1
b     3
c     5
d     6
e     9
f   -10
dtype: int64

对于 Series 对象里的单个数据来说,和普通数组一样,根据索引获取对应的数据或重新赋值;


obj2['a']
Out[12]: 
1

obj2[['a','b']]
Out[14]: 
a    1
b    3
dtype: int64

想要单独获取 Series 对象的索引或者数组内容的时候,可以使用 index 和 values 属性,例如:


obj2.index
Out[15]: 
Index([u'a', u'b', u'c', u'd', u'e', u'f'], dtype='object')
obj2.values
Out[16]: 
array([  1,   3,   5,   6,   9, -10], dtype=int64)

对 Series 对象的运算(索引不变):


obj2
Out[17]: 
a     1
b     3
c     5
d     6
e     9
f   -10
dtype: int64
obj2*2
Out[18]: 
a     2
b     6
c    10
d    12
e    18
f   -20
dtype: int64
obj2+1
Out[19]: 
a     2
b     4
c     6
d     7
e    10
f    -9
dtype: int64
obj2 <=10
Out[20]: 
a    True
b    True
c    True
d    True
e    True
f    True
dtype: bool
obj2 [obj2<=10]
Out[21]: 
a     1
b     3
c     5
d     6
e     9
f   -10
dtype: int64
obj2 [obj2>8]
Out[22]: 
e    9
dtype: int64

明天继续学习DataFrame

python 数据分析-电影分析

最近学习python 数据分析,将书中的案例学习演示一遍,温故知新
电影数据分析

users.dat文件格式



1::F::1::10::48067
2::M::56::16::70072
3::M::25::15::55117

ratings.dat文件格式
1::1193::5::978300760
1::661::3::978302109
1::914::3::978301968

movies.dat文件格式


1::Toy Story (1995)::Animation|Children's|Comedy
2::Jumanji (1995)::Adventure|Children's|Fantasy
3::Grumpier Old Men (1995)::Comedy|Romance


通过pandas.read_table将各个表分别读到pandas DataFrame对象中:
DataFrame 是一个表格型的数据结构。它提供有序的列和不同类型的列值。

import pandas as pd
import os
path='C:\python\movielens'
os.chdir(path)

unames = ['user_id', 'gender', 'age', 'occupation', 'zip']
users = pd.read_table('users.dat', sep='::', header=None,names=unames)
rnames = ['user_id', 'movie_id', 'rating', 'timestamp']
ratings = pd.read_table('ratings.dat',sep='::', header=None,names=rnames)
mnames = ['movie_id', 'title', 'genres']
movies = pd.read_table('movies.dat', sep='::', header=None,names=mnames)

执行报错:

C:/Users/cl/PycharmProjects/untitled/name.py:7: ParserWarning: Falling back to the 'python' engine because the 'c' engine does not support regex separators (separators > 1 char and different from '\s+' are interpreted as regex); you can avoid this warning by specifying engine='python'.
  users = pd.read_table('users.dat', sep='::', header=None,names=unames)
C:/Users/cl/PycharmProjects/untitled/name.py:9: ParserWarning: Falling back to the 'python' engine because the 'c' engine does not support regex separators (separators > 1 char and different from '\s+' are interpreted as regex); you can avoid this warning by specifying engine='python'.
  ratings = pd.read_table('ratings.dat',sep='::', header=None,names=rnames)
C:/Users/cl/PycharmProjects/untitled/name.py:11: ParserWarning: Falling back to the 'python' engine because the 'c' engine does not support regex separators (separators > 1 char and different from '\s+' are interpreted as regex); you can avoid this warning by specifying engine='python'.
  movies = pd.read_table('movies.dat', sep='::', header=None,names=mnames)

添加:engine=’python’

import pandas as pd
import os
path='C:\python\movielens'
os.chdir(path)

unames = ['user_id', 'gender', 'age', 'occupation', 'zip']
users = pd.read_table('users.dat', sep='::', header=None,names=unames,engine='python')
rnames = ['user_id', 'movie_id', 'rating', 'timestamp']
ratings = pd.read_table('ratings.dat',sep='::', header=None,names=rnames,engine='python')
mnames = ['movie_id', 'title', 'genres']
movies = pd.read_table('movies.dat', sep='::', header=None,names=mnames,engine='python')

查看各个DataFrame对象:

users[:5]
Out[6]: 
   user_id gender  age  occupation    zip
0        1      F    1          10  48067
1        2      M   56          16  70072
2        3      M   25          15  55117
3        4      M   45           7  02460
4        5      M   25          20  55455


ratings[:5]
Out[7]: 
   user_id  movie_id  rating  timestamp
0        1      1193       5  978300760
1        1       661       3  978302109
2        1       914       3  978301968
3        1      3408       4  978300275
4        1      2355       5  978824291
movies[:5]
Out[8]: 
   movie_id                               title                        genres
0         1                    Toy Story (1995)   Animation|Children's|Comedy
1         2                      Jumanji (1995)  Adventure|Children's|Fantasy
2         3             Grumpier Old Men (1995)                Comedy|Romance
3         4            Waiting to Exhale (1995)                  Comedy|Drama
4         5  Father of the Bride Part II (1995)                        Comedy

接下来尝试分析散布在三个表中的数据。假设我们想根据性别和年龄计算某部电影的平均得分,如果将所有数据合并到一个表的话问题就简单多了。我们先用pandas的merge函数将ratings跟users 合并到一起,然后再将movies野合并进去。pandas会根据列明的重叠情况推断出哪些是合并(或连接)键:

pandas.merge可根据一个或者多个不同DataFrame中的行连接起来。

data = pd.merge(pd.merge(ratings,users),movies)
data[:5]
Out[10]: 
   user_id  movie_id  rating  timestamp gender  age  occupation    zip  \
0        1      1193       5  978300760      F    1          10  48067   
1        2      1193       5  978298413      M   56          16  70072   
2       12      1193       4  978220179      M   25          12  32793   
3       15      1193       4  978199279      M   25           7  22903   
4       17      1193       5  978158471      M   50           1  95350   
                                    title genres  
0  One Flew Over the Cuckoo's Nest (1975)  Drama  
1  One Flew Over the Cuckoo's Nest (1975)  Drama  
2  One Flew Over the Cuckoo's Nest (1975)  Drama  
3  One Flew Over the Cuckoo's Nest (1975)  Drama  
4  One Flew Over the Cuckoo's Nest (1975)  Drama  


接下来就可以根据任意个用户或者电影属性对评分数据进行聚合操作。按性别计算每部电影的平均分,可以使用pivot_table方法:

mean_ratings = data.pivot_table('rating',index='title',columns='gender',aggfunc='mean')
mean_ratings[:5]
Out[12]: 
gender                                F         M
title                                            
$1,000,000 Duck (1971)         3.375000  2.761905
'Night Mother (1986)           3.388889  3.352941
'Til There Was You (1997)      2.675676  2.733333
'burbs, The (1989)             2.793478  2.962085
And Justice for All (1979)     3.828571  3.689024

这个操作产生了了另一个dataframe,大概是电影平均得分。比如电影名 (时间),男性占比,女性占比。

现在,过滤掉评分数据不够250条的电影。先对title进行分组,然后利用size()得到一个含有各电影分组大小的Series对象:

先对title进行分组,然后利用size()得到一个每个电影分组大小的serise对象(series是一种类似于一维数组的对象,它由一组数据(各种NumPy数据类型)以及一组与之相关的数据标签(即索引)组成)

ratings_by_title=data.groupby('title').size()

ratings_by_title[:10]
Out[12]: 
title
$1,000,000 Duck (1971)                37
'Night Mother (1986)                  70
'Til There Was You (1997)             52
'burbs, The (1989)                   303
And Justice for All (1979)        199
1-900 (1994)                           2
10 Things I Hate About You (1999)    700
101 Dalmatians (1961)                565
101 Dalmatians (1996)                364
12 Angry Men (1957)                  616
dtype: int64


现在过滤掉不足250条的电影

active_tiles = ratings_by_title.index[ratings_by_title >= 250]
active_tiles
Out[14]: 
Index([u''burbs, The (1989)', u'10 Things I Hate About You (1999)',
       u'101 Dalmatians (1961)', u'101 Dalmatians (1996)',
       u'12 Angry Men (1957)', u'13th Warrior, The (1999)',
       u'2 Days in the Valley (1996)', u'20,000 Leagues Under the Sea (1954)',
       u'2001: A Space Odyssey (1968)', u'2010 (1984)',
       ...
       u'X-Men (2000)', u'Year of Living Dangerously (1982)',
       u'Yellow Submarine (1968)', u'You've Got Mail (1998)',
       u'Young Frankenstein (1974)', u'Young Guns (1988)',
       u'Young Guns II (1990)', u'Young Sherlock Holmes (1985)',
       u'Zero Effect (1998)', u'eXistenZ (1999)'],
      dtype='object', name=u'title', length=1216)


该索引中含有评分数据大于250条的电影名称,然后就可以据此从前面的mean_ratings中选取所需的行了:

mean_ratings = mean_ratings.ix[active_tiles]
mean_ratings
Out[17]: 
gender                                                     F         M
title                                                                 
'burbs, The (1989)                                  2.793478  2.962085
10 Things I Hate About You (1999)                   3.646552  3.311966
101 Dalmatians (1961)                               3.791444  3.500000
101 Dalmatians (1996)                               3.240000  2.911215
12 Angry Men (1957)                                 4.184397  4.328421
13th Warrior, The (1999)                            3.112000  3.168000
2 Days in the Valley (1996)                         3.488889  3.244813
20,000 Leagues Under the Sea (1954)                 3.670103  3.709205
2001: A Space Odyssey (1968)                        3.825581  4.129738
2010 (1984)                                         3.446809  3.413712
28 Days (2000)                                      3.209424  2.977707
39 Steps, The (1935)                                3.965517  4.107692
54 (1998)                                           2.701754  2.782178
7th Voyage of Sinbad, The (1958)                    3.409091  3.658879
8MM (1999)                                          2.906250  2.850962
About Last Night... (1986)                          3.188679  3.140909
Absent Minded Professor, The (1961)                 3.469388  3.446809
Absolute Power (1997)                               3.469136  3.327759
Abyss, The (1989)                                   3.659236  3.689507
Ace Ventura: Pet Detective (1994)                   3.000000  3.197917
Ace Ventura: When Nature Calls (1995)               2.269663  2.543333
Addams Family Values (1993)                         3.000000  2.878531
Addams Family, The (1991)                           3.186170  3.163498
Adventures in Babysitting (1987)                    3.455782  3.208122
Adventures of Buckaroo Bonzai Across the 8th Di...  3.308511  3.402321
Adventures of Priscilla, Queen of the Desert, T...  3.989071  3.688811
Adventures of Robin Hood, The (1938)                4.166667  3.918367
African Queen, The (1951)                           4.324232  4.223822
Age of Innocence, The (1993)                        3.827068  3.339506
Agnes of God (1985)                                 3.534884  3.244898
                                                      ...       ...
White Men Can't Jump (1992)                         3.028777  3.231061
Who Framed Roger Rabbit? (1988)                     3.569378  3.713251
Who's Afraid of Virginia Woolf? (1966)              4.029703  4.096939
Whole Nine Yards, The (2000)                        3.296552  3.404814
Wild Bunch, The (1969)                              3.636364  4.128099
Wild Things (1998)                                  3.392000  3.459082
Wild Wild West (1999)                               2.275449  2.131973
William Shakespeare's Romeo and Juliet (1996)       3.532609  3.318644
Willow (1988)                                       3.658683  3.453543
Willy Wonka and the Chocolate Factory (1971)        4.063953  3.789474
Witness (1985)                                      4.115854  3.941504
Wizard of Oz, The (1939)                            4.355030  4.203138
Wolf (1994)                                         3.074074  2.899083
Women on the Verge of a Nervous Breakdown (1988)    3.934307  3.865741
Wonder Boys (2000)                                  4.043796  3.913649
Working Girl (1988)                                 3.606742  3.312500
World Is Not Enough, The (1999)                     3.337500  3.388889
Wrong Trousers, The (1993)                          4.588235  4.478261
Wyatt Earp (1994)                                   3.147059  3.283898
X-Files: Fight the Future, The (1998)               3.489474  3.493797
X-Men (2000)                                        3.682310  3.851702
Year of Living Dangerously (1982)                   3.951220  3.869403
Yellow Submarine (1968)                             3.714286  3.689286
You've Got Mail (1998)                              3.542424  3.275591
Young Frankenstein (1974)                           4.289963  4.239177
Young Guns (1988)                                   3.371795  3.425620
Young Guns II (1990)                                2.934783  2.904025
Young Sherlock Holmes (1985)                        3.514706  3.363344
Zero Effect (1998)                                  3.864407  3.723140
eXistenZ (1999)                                     3.098592  3.289086
[1216 rows x 2 columns]


为了了解女性观众最喜欢的电影,可以对F列降序排列:

top_female_ratings = mean_ratings.sort_index(by='F',ascending=False)
C:\Program Files (x86)\JetBrains\PyCharm Community Edition 2017.1\helpers\pydev\pydevconsole.py:1: FutureWarning: by argument to sort_index is deprecated, pls use .sort_values(by=...)
  '''

这里是由于该解析器没by这个参数,替换为sort_values
笔记:
pandas有两个函数进行排序:
– sort_index():根据series的index进行排序,返回一个series
– sort_values():根据值进行排序(默认从小到大)

top_female_ratings = mean_ratings.sort_values(by='F',ascending=False)
top_female_ratings[:10]
Out[22]: 
gender                                                     F         M
title                                                                 
Close Shave, A (1995)                               4.644444  4.473795
Wrong Trousers, The (1993)                          4.588235  4.478261
Sunset Blvd. (a.k.a. Sunset Boulevard) (1950)       4.572650  4.464589
Wallace & Gromit: The Best of Aardman Animation...  4.563107  4.385075
Schindler's List (1993)                             4.562602  4.491415
Shawshank Redemption, The (1994)                    4.539075  4.560625
Grand Day Out, A (1992)                             4.537879  4.293255
To Kill a Mockingbird (1962)                        4.536667  4.372611
Creature Comforts (1990)                            4.513889  4.272277
Usual Suspects, The (1995)                          4.513317  4.518248


CASE:计算评分分歧
假设想要找出男性和女性观众分歧最大的电影。一个办法师给mean_ratings加上一个用于存放平均得分之差的列diff,并对其进行排序可得到分歧最大且女性观众更喜欢的电影:

mean_ratings['diff']=mean_ratings['M'] - mean_ratings['F']
sorted_by_diff = mean_ratings.sort_values(by='diff')
sorted_by_diff[:10]
Out[25]: 
gender                                        F         M      diff
title                                                              
Dirty Dancing (1987)                   3.790378  2.959596 -0.830782
Jumpin' Jack Flash (1986)              3.254717  2.578358 -0.676359
Grease (1978)                          3.975265  3.367041 -0.608224
Little Women (1994)                    3.870588  3.321739 -0.548849
Steel Magnolias (1989)                 3.901734  3.365957 -0.535777
Anastasia (1997)                       3.800000  3.281609 -0.518391
Rocky Horror Picture Show, The (1975)  3.673016  3.160131 -0.512885
Color Purple, The (1985)               4.158192  3.659341 -0.498851
Age of Innocence, The (1993)           3.827068  3.339506 -0.487561
Free Willy (1993)                      2.921348  2.438776 -0.482573


很明显,对排序结果反序并取前5行,得到的则是男性观众更喜爱的电影:

sorted_by_diff[::-1][:10]
Out[26]: 
gender                                         F         M      diff
title                                                               
Good, The Bad and The Ugly, The (1966)  3.494949  4.221300  0.726351
Kentucky Fried Movie, The (1977)        2.878788  3.555147  0.676359
Dumb & Dumber (1994)                    2.697987  3.336595  0.638608
Longest Day, The (1962)                 3.411765  4.031447  0.619682
Cable Guy, The (1996)                   2.250000  2.863787  0.613787
Evil Dead II (Dead By Dawn) (1987)      3.297297  3.909283  0.611985
Hidden, The (1987)                      3.137931  3.745098  0.607167
Rocky III (1982)                        2.361702  2.943503  0.581801
Caddyshack (1980)                       3.396135  3.969737  0.573602
For a Few Dollars More (1965)           3.409091  3.953795  0.544704

如果只想要找出分歧最大的电影(不考虑性别因素),则可以计算得分数据的方差或者标准差:

#分组后计算标准差
rating_std_by_title = data.groupby(‘title’)[‘rating’].std()
#筛选评分多于250条的
rating_std_by_title = rating_std_by_title.ix[active_titles]

根据值对Series 进行降序排序

rating_std_by_title.order(ascending=False)[:10]
C:\Program Files (x86)\JetBrains\PyCharm Community Edition 2017.1\helpers\pydev\pydevconsole.py:1: FutureWarning: order is deprecated, use sort_values(...)
  '''
Out[29]: 
title
Dumb & Dumber (1994)                     1.321333
Blair Witch Project, The (1999)          1.316368
Natural Born Killers (1994)              1.307198
Tank Girl (1995)                         1.277695
Rocky Horror Picture Show, The (1975)    1.260177
Eyes Wide Shut (1999)                    1.259624
Evita (1996)                             1.253631
Billy Madison (1995)                     1.249970
Fear and Loathing in Las Vegas (1998)    1.246408
Bicentennial Man (1999)                  1.245533
Name: rating, dtype: float64

AIX 设置oracle lager Page Memory

1.查看aix 大页大小

aibsdbs[/etc]vmo -L lgpg_size
NAME                      CUR    DEF    BOOT   MIN    MAX    UNIT           TYPE
     DEPENDENCIES
--------------------------------------------------------------------------------
lgpg_size                 0      0      0      0      16M    bytes             D
     lgpg_regions
--------------------------------------------------------------------------------

aibsdbs[/etc]pagesize -a
4096
65536
16777216
17179869184

Linux平台和AIX平台默认的页面大小都是4K,Linux平台的HugePage特性通常每个页面可以调整为2M;AIX平台的Large Page Memory使得单个页面最大可以调整为16M;

2.ORACLE启用大页的前提 
如果ORACLE要启用大页,那么ORACLE的user需要具有 CAP_BYPASS_RAC_VMM and CAP_PROPAGATE属性,如果是在RAC环境中,ROOT,GRID(11G)用户也要有CAP_BYPASS_RAC_VMM and CAP_PROPAGATE属性使用如下命令查看与修改

aibsdbs[/etc]lsuser -a capabilities oracle
oracle

aibsdbs[/etc]chuser capabilities=CAP_BYPASS_RAC_VMM,CAP_PROPAGATE oracle

aibsdbs[/etc]lsuser -a capabilities oracle
oracle capabilities=CAP_BYPASS_RAC_VMM,CAP_PROPAGATE

3 、AIX大页内存的参数查看
lgpg_regions控制大页的个数lgpg_size控制页的大小,使用如下命令查看与修改
查看大页内存的个数,一个大页的大小

aibsdbs[/etc]vmo -L lgpg_regions -L lgpg_size
NAME                      CUR    DEF    BOOT   MIN    MAX    UNIT           TYPE
     DEPENDENCIES
--------------------------------------------------------------------------------
lgpg_regions              0      0      0      0      8E-1                     D
     lgpg_size
--------------------------------------------------------------------------------
lgpg_size                 0      0      0      0      16M    bytes             D
     lgpg_regions
--------------------------------------------------------------------------------

4.确定大页内存的个数:
可以参照这个公式:
Configure the AIX large page pool by calculating the number of large pages required for the SGA: 
num_of_large_pages = INT((total_SGA_size-1)/16MB)+1 

num_of_large_pages = INT((total_SGA_size-1)/16MB)+1

本机内存为120g,设sga为55g,即
num_of_large_pages = INT((55*1024-1)/16 mb)+1
num_of_large_pages = 3520

5、设置大页内存:

# vmo -p -o lgpg_regions=3520 -o lgpg_size=16777216

aibsdbs[/etc]vmo -L lgpg_regions -L lgpg_size
NAME                      CUR    DEF    BOOT   MIN    MAX    UNIT           TYPE
     DEPENDENCIES
--------------------------------------------------------------------------------
lgpg_regions              3520   0      3520   0      8E-1                     D
     lgpg_size
--------------------------------------------------------------------------------
lgpg_size                 16M    0      16M    0      16M    bytes             D
     lgpg_regions
--------------------------------------------------------------------------------

设置完大页内存后需要修改参数 lru_file_repage
Change lru_file_repage, the default is 1:
#vmo -o lru_file_repage=0

aibsdbs[/etc]vmstat 1 10

System configuration: lcpu=64 mem=122880MB

kthr    memory              page              faults        cpu    
----- ----------- ------------------------ ------------ -----------
 r  b   avm   fre  re  pi  po  fr   sr  cy  in   sy  cs us sy id wa
 2  0 16958247 17016   0   0   0 131 1022474 119  62  625 3610  0  7 93  0
 0  0 16958247 18284   0   0   0 125  124   0  43  642 1163  0  1 99  0
 3  0 16958247 18284   0   0   0   0 246383  58  62  290 1458  0  2 98  0
 0  0 16958247 18284   0   0   0   0    0   0  36  347 795  0  0 99  0
 2  0 16958247 18284   0   0   0   0 9075   1  59  351 940  0  0 99  0
 1  0 16958247 18796   0   0   0 512 379955  43  45  351 1742  0  4 96  0
 0  0 16958247 18796   0   0   0   0    0   0  64  520 817  0  0 99  0
 0  0 16958265 19694   0   0   0 913 48914   9  38 5383 4541  0  0 99  0
 0  0 16958265 19694   0   0   0   0    0   0  59  293 831  0  0 99  0
 0  0 16958265 19694   0   0   0   0    0   0  37  449 800  0  0 99  0

aibsdbs[/etc]vmo -L minperm% -L maxperm% -L maxclient% -L lru_file_repage
vmo: 1485-110 Invalid tunable name lru_file_repage
NAME                      CUR    DEF    BOOT   MIN    MAX    UNIT           TYPE
     DEPENDENCIES
--------------------------------------------------------------------------------
maxclient%                90     90     90     1      100    % memory          D
     maxperm%
     minperm%
--------------------------------------------------------------------------------
maxperm%                  90     90     90     1      100    % memory          D
     minperm%
     maxclient%
--------------------------------------------------------------------------------
minperm%                  3      3      3      1      100    % memory          D
--------------------------------------------------------------------------------

12c,pluggable 数据库 还原恢复

如果pluggable 数据库出现损坏怎么办?别急,下面有两种方式可以进行还原恢复
1)连接到root 容器,指定需要恢复的pluggable数据库进行还原恢复
2)直接连接到需要还原恢复的pluggable用户

比如:以root 容器为例
$ rman target /
RMAN> alter pluggable database salespdb close;
RMAN> restore pluggable database salespdb;
RMAN> recover pluggable database salespdb;
RMAN> alter pluggable database salespdb open;

比如:以pluggable 为例

$ rman target sys/foo@salespdb
RMAN> shutdown immediate;
RMAN> restore database;
RMAN> recover database;
RMAN> alter database open;

rman-restore

When you issue the RESTORE command, RMAN automatically decides how to extract the data files from any of the following available backups:
•  Full database backup
•  Incremental level-0 backup
•  Image copy backup generated by BACKUP AS COPY command

After the files are restored from a backup, you are required to apply redo to them via the RECOVER command. When you issue the RECOVER command, Oracle examines the SCNs in the affected data files and determines whether any of them need to be recovered. If the SCN in the data file is less than the corresponding SCN in the control file, then media recovery will be required.

利用备份进行restore 完成后,将利用recover 命令应用redo 日志,其目的为将需要进行恢复的数据文件头的scn信息进行更新,直到数据文件的SCN 不小于控制文件scn。如果数据文件中的SCN小于控件文件中的相应SCN,则将需要介质恢复。

Oracle retrieves the data file SCN and then looks for the corresponding SCN in the redo stream to establish where to start the recovery process. If the starting recovery SCN is in the online redo log files, the archived redo log files are not required for recovery.

上面表示,redo 中某scn 是可以判断那些是需要恢复的scn。

During a recovery, RMAN automatically determines how to apply redo. First, RMAN applies any incremental backups available that are greater than level 0, such as the incremental level 1. Next, any archived redo log files on disk are applied. If the archived redo log files do not exist on disk, RMAN attempts to retrieve them from a backup set

即:在recover 时,rman会自动决定如何运用在线redo,
1)rman 应用增量备份,比如1级增量
2)应用归档日志
3)如果本地没有归档日志,那么rman会尝试从备份集中恢复归档日志

To be able to perform a complete recovery, all the following conditions need to be true:
•  Your database is in archivelog mode.
•  You have a good baseline backup of your database.
•  You have any required redo that has been generated since the backup (archived redo log files, online redo log files, or incremental backups that RMAN can use for recovery instead of applying redo).
There are a wide variety of restore-and-recovery scenarios. How you restore and recover depends directly on your backup strategy and which files have been damaged. Listed next are the general steps to follow when facing a media failure:
1. Determine which files need to be restored.
2. Depending on the damage, set your database mode to nomount, mount, or open.
3. Use the RESTORE command to retrieve files from RMAN backups.
4. Use the RECOVER command for data files requiring recovery.
5. Open your database.
Your particular restore-and-recovery scenario may not require that all the previous steps be performed. For instance, you may just want to restore your spfile, which doesn’t require a recovery step.
The first step in the restore-and-recovery process is to determine which files have experienced media failure. You can usually determine which files need to be restored from the following sources:
•  Error messages displayed on your screen, either from RMAN or SQL*Plus
•  Alert.log file and corresponding trace files
 Data dictionary views
If you’re using Oracle 11g or higher, then in addition to the previously listed methods, you should consider the Data Recovery Advisor for obtaining information about the extent of a failure and corresponding corrective action.

DG-备库无法启动日志应用进程


ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE disconnect from session
Attempt to start background Managed Standby Recovery process (prpdb)
Fri Mar 10 13:25:50 2017
MRP0 started with pid=27, OS id=174461 
MRP0: Background Managed Standby Recovery process started (prpdb)
 started logmerger process
Fri Mar 10 13:25:55 2017
Managed Standby Recovery starting Real Time Apply
Parallel Media Recovery started with 32 slaves
Waiting for all non-current ORLs to be archived...
All non-current ORLs have been archived.
Media Recovery Log /arch/1_191625_856647413.dbf
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE disconnect from session
Fri Mar 10 13:25:58 2017
Errors in file /oracle/diag/rdbms/prpdb/prpdb/trace/prpdb_pr0i_174837.trc  (incident=45270):
ORA-00600: internal error code, arguments: [3020], [51], [1349380], [215258884], [], [], [], [], [], [], [], []
ORA-10567: Redo is inconsistent with data block (file# 51, block# 1349380, file offset is 2464186368 bytes)
ORA-10564: tablespace T74_TABLE
ORA-01110: data file 51: '/oradata/prpdb/t74tab_08_20G.dbf'
ORA-10560: block type '0'
Incident details in: /oracle/diag/rdbms/prpdb/prpdb/incident/incdir_45270/prpdb_pr0i_174837_i45270.trc
Use ADRCI or Support Workbench to package the incident.

报错内容来看,说明该数据文件存在损坏,导致redo 信息与主库不一致。

1)查看文件SCN


SQL> select to_char(CHECKPOINT_CHANGE#) from v$datafile_header;

TO_CHAR(CHECKPOINT_CHANGE#)
----------------------------------------
14942487749
14942487749
14942487749
14942487749
......

SQL> select to_char(CHECKPOINT_CHANGE#) from v$datafile;

TO_CHAR(CHECKPOINT_CHANGE#)
----------------------------------------
14942487749
14942487749
14942487749
14942487749
14942487749

.......

文件SCN 与控制文件一致

2)对象检查


SQL> SELECT SEGMENT_NAME FROM DBA_EXTENTS WHERE FILE_ID= 51  
  2  AND &BLOCK_NUMBER BETWEEN BLOCK_ID AND BLOCK_ID+BLOCKS-1;
Enter value for block_number: 1349380
old   2: AND &BLOCK_NUMBER BETWEEN BLOCK_ID AND BLOCK_ID+BLOCKS-1
new   2: AND 1349380 BETWEEN BLOCK_ID AND BLOCK_ID+BLOCKS-1


 select file#, status, error, recover from v$datafile_header;
SEGMENT_NAME
--------------------------------------------------------------------------------
T74_KJJS_17

SQL> SELECT OWNER, OBJECT_NAME, SUBOBJECT_NAME, OBJECT_TYPE 
  2  FROM DBA_OBJECTS 
  3  WHERE DATA_OBJECT_ID = &Object_number; 
Enter value for object_number: 1349380
old   3: WHERE DATA_OBJECT_ID = &Object_number
new   3: WHERE DATA_OBJECT_ID = 1349380

no rows selected



SQL> select file_id,file_name,tablespace_name from dba_data_files where file_name ='/oradata/prpdb/t74tab_08_20G.dbf';

   FILE_ID FILE_NAME                                                             TABLESPACE_NAME
---------- --------------------------------------------------------------------- ------------------------------
        51 /oradata/prpdb/t74tab_08_20G.dbf                                      T74_TABLE

生产:
SQL> select file_id,file_name,tablespace_name from dba_data_files where file_id=51;

   FILE_ID FILE_NAME                                                             TABLESPACE_NAME
---------- --------------------------------------------------------------------- ------------------------------
        51 /oradata/prpdb/t74tab_08_20G.dbf                                      T74_TABLE

3)坏块检查:


[oracle@rprpdbs admin]$ dbv file=/oradata/prpdb/t74tab_08_20G.dbf

DBVERIFY - Verification complete

Total Pages Examined         : 2621440
Total Pages Processed (Data) : 2523271
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 67127
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 13071
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 17971
Total Pages Marked Corrupt   : 127
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 1976922457 (3.1976922457)

坏块有127个挺多的。

4)主库备份文件到备库恢复


RMAN> backup datafile 51 format '/orabak/backup/t74tab_08_20G.dbf';

Starting backup at 10-MAR-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=136 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00051 name=/oradata/prpdb/t74tab_08_20G.dbf
channel ORA_DISK_1: starting piece 1 at 10-MAR-17

channel ORA_DISK_1: finished piece 1 at 10-MAR-17
piece handle=/orabak/backup/t74tab_08_20G.dbf tag=TAG20170310T152545 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:03:45
Finished backup at 10-MAR-17

Starting Control File and SPFILE Autobackup at 10-MAR-17
piece handle=/oracle/fast_recovery_area/PRPDB/autobackup/2017_03_10/o1_mf_s_938273370_dd4ogvc1_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 10-MAR-17

传输到备库:


RMAN> sql 'alter database datafile 51 offline drop';

sql statement: alter database datafile 51 offline drop

RMAN> restore datafile 51;

Starting restore at 10-MAR-17
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=581 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00051 to /oradata/prpdb/t74tab_08_20G.dbf
channel ORA_DISK_1: reading from backup piece /oradata/dgrestore/t74tab_08_20G.dbf
channel ORA_DISK_1: piece handle=/oradata/dgrestore/t74tab_08_20G.dbf tag=TAG20170310T152545
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:35
Finished restore at 10-MAR-17

5)重启日志应用ok

snapshot 一点猜想

本来是一个很简单的道理,还是得测试一下。
如果dg备库在做snapshot 后,应用数据库表空间使用率较高,那么对表空间进行扩容会有什么影响呢?
猜想是没有影响的,主要担忧是dg 同步会发生什么问题。

SQL> select database_role,protection_mode,protection_level from v$database;

DATABASE_ROLE    PROTECTION_MODE      PROTECTION_LEVEL
---------------- -------------------- --------------------
SNAPSHOT STANDBY MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE



SQL>   SELECT total.tablespace_name,
  2           ROUND (total.MB, 2)                             AS Total_MB,
  3           ROUND (total.MB - free.MB, 2)                   AS Used_MB,
  4           ROUND ( (1 - free.MB / total.MB) * 100, 2) || '%' AS Used_Pct
  5      FROM (  SELECT tablespace_name, SUM (bytes) / 1024 / 1024 AS MB
  6                FROM dba_free_space
  7            GROUP BY tablespace_name) free,
  8           (  SELECT tablespace_name, SUM (bytes) / 1024 / 1024 AS MB
  9                FROM dba_data_files
 10            GROUP BY tablespace_name) total
 11     WHERE free.tablespace_name = total.tablespace_name
 12  ORDER BY used_pct DESC;

TABLESPACE_NAME                  TOTAL_MB    USED_MB USED_PCT
------------------------------ ---------- ---------- -----------------------------------------
SYSTEM                                690        684 99.13%
SYSAUX                                540     507.19 93.92%
EXAMPLE                               100      78.75 78.75%
USERS                                  60       4.19 6.98%
TEST                                   52      12.25 23.56%
UNDOTBS1                               90      18.75 20.83%
TEST_DB                                 5          1 20%


SQL> SELECT FILE_NAME,FILE_ID FROM DBA_DATA_FILES WHERE TABLESPACE_NAME='TEST';

FILE_NAME                                                       FILE_ID
------------------------------------------------------------ ----------
/opt/oracle/app/oradata/std/imptest.dbf                               7



SQL> alter tablespace TEST add datafile '/opt/oracle/app/oradata/std/imptest2.dbf' size 20m;

Tablespace altered.

SQL> SELECT FILE_NAME,FILE_ID FROM DBA_DATA_FILES WHERE TABLESPACE_NAME='TEST';

FILE_NAME                                                       FILE_ID
------------------------------------------------------------ ----------
/opt/oracle/app/oradata/std/imptest.dbf                               7
/opt/oracle/app/oradata/std/imptest2.dbf                              8



TABLESPACE_NAME                  TOTAL_MB    USED_MB USED_PCT
------------------------------ ---------- ---------- -----------------------------------------
SYSTEM                                690        684 99.13%
SYSAUX                                540     507.19 93.92%
EXAMPLE                               100      78.75 78.75%
USERS                                  60       4.19 6.98%
UNDOTBS1                               90      18.75 20.83%
TEST_DB                                 5          1 20%
TEST                                   72      13.25 18.4%


SQL> show parameter stand

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
standby_archive_dest                 string      ?/dbs/arch
standby_file_management              string      AUTO

SQL> create table  tablename (id  number)  tablespace  test;

Table created.

SQL> insert into tablename values(1);

1 row created.

SQL> /

1 row created.

SQL> /

1 row created.

SQL> /

1 row created.

SQL> /

1 row created.

SQL> /

1 row created.

SQL> select * from tablename;

        ID
----------
         1
         1
         1
         1
         1
         1

SQL> SELECT FILE_NAME,FILE_ID FROM DBA_DATA_FILES WHERE TABLESPACE_NAME='TEST';

FILE_NAME                                                       FILE_ID
------------------------------------------------------------ ----------
/opt/oracle/app/oradata/std/imptest.dbf                               7

SQL> select database_role,protection_mode,protection_level from v$database;

DATABASE_ROLE    PROTECTION_MODE      PROTECTION_LEVEL
---------------- -------------------- --------------------
PHYSICAL STANDBY MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE 

证明了备库启用snapshot 操作后,在其期间的操作都会被记录到闪回日志中,即使是新建的数据文件也会在闪回为physical standby 时删除掉,同时dg 同步也不会有影响