MySQL SQL模式的探讨

MySQL服务器的sql模式对sql的执行会产生几个方面的影响,对于sql模式可以参考sql_mode

mysql> show variables like '%SQL_mode%';
+---------------+--------------------------------------------+
| Variable_name | Value                                      |
+---------------+--------------------------------------------+
| sql_mode      | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+---------------+--------------------------------------------+
1 row in set (0.00 sec)

1)STRICT_TRANS_TABLES和STRICT_ALL_TABLES,启用“严格”模式,在该模式下,如果一个值不能插入到一个事物表中,则中断当前的操作,对非事物表不做限制。
也即是更加严格对待接受的坏数据

2)TRADITIONAL,严格模式,当向mysql数据库插入数据时,进行数据的严格校验,保证错误数据不能插入,报error错误。用于事物时,会进行事物的回滚。

3)ANSI,宽松模式,对插入数据进行校验,如果不符合定义类型或长度,对数据类型调整或截断保存,报warning警告。

设置SQL 模式:

1.启动服务器时设置SQL模式,或者选项文件里设置

–sql-mode=”TRADITIONAL”
–sql-mode=”ANSI_QUOTES,PIPES_AS_CONCAT”

2.服务器运行中进行修改

SET sql_mode=’TRADITIONAL’

如果设置全局模式,则需要加上GLOBAL关键字
SET global sql_mode=’TRADITIONAL’

如果设置当前会话模式
set @@session.sql_mode=ANSI;


mysql> CREATE TABLE testtable(
    -> 
    ->   NAME VARCHAR(2),
    -> 
    ->   PASSWORD VARCHAR(2)
    -> 
    -> );
Query OK, 0 rows affected (0.09 sec)

mysql> 
mysql> 
mysql> desc testtables
    -> ;
ERROR 1146 (42S02): Table 'albert.testtables' doesn't exist
mysql> desc testtable;
+----------------+------------+------+-----+---------+-------+
| Field          | Type       | Null | Key | Default | Extra |
+----------------+------------+------+-----+---------+-------+
|   NAME       | varchar(2) | YES  |     | NULL    |       |
|   PASSWORD   | varchar(2) | YES  |     | NULL    |       |
+----------------+------------+------+-----+---------+-------+
2 rows in set (0.01 sec)



mysql> INSERT INTO testtable VALUES('11111111','222222222222');
ERROR 1406 (22001): Data too long for column '  NAME' at row 1



mysql> set @@session.sql_mode=ANSI;
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO testtable VALUES('11111111','222222222222');
Query OK, 1 row affected, 2 warnings (0.01 sec)


mysql> select * from testtable;
+------------+----------------+
|   NAME   |   PASSWORD   |
+------------+----------------+
| 11         | 22             |
+------------+----------------+
1 row in set (0.00 sec)

MySQL–数据目录知识点总结

在unix 系统里,
1)如果通过二进制或者源代码发行版安装MySQL,那么数据目录默认在/usr/local/mysql/data
2)如果通过RPM 包来安装,则默认目录为/var/lib/mysql

在windows 里,默认数据目录在c:\progarmData\MySQL 或者C:\document and settings\All Users\Application data\MySQL

查看:

1)

<code>[root@albert1 ~]# ps -ef | grep mysql
root       2052      1  0 Mar19 ?        00:00:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --pid-file=/var/lib/mysql/albert1.pid
mysql      2149   2052  0 Mar19 ?        00:00:25 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --log-error=albert1.err --pid-file=/var/lib/mysql/albert1.pid</code>

2)查看cnf文件

<code>[root@albert1 etc]# more /mysqldata/mysql/my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html

[mysqld]

# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M

# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin

# These are commonly set, remove the # and set as required.
# basedir = .....
 datadir = /mysqldata/mysql/
# port = .....
# server_id = .....
# socket = .....
pid-file= /mysqldata/mysql/albert1.pid

# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M 

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES </code>

3)mysqld 命令查看

# mysqld –verbose –help

4)数据库查看

<code>mysql> show variables like 'datadir';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| datadir       | /var/lib/mysql/ |
+---------------+-----------------+
1 row in set (0.01 sec)</code>

数据目录结构

mysql 数据目录一般情况下是一个树形结构:
1)每个数据库在数据目录下都对应有一个数据库目录
2)数据库里的表、视图和触发器都对应一个数据库目录的文件

例如:

<code>[root@albert1 etc]# cd /var/lib/mysql
[root@albert1 mysql]# ls -l
total 110680
drwx------. 2 mysql mysql     4096 Dec  5 09:38 albert
-rw-rw----. 1 mysql mysql    52659 Jan 17 13:19 albert1.err
-rw-rw----. 1 mysql mysql        5 Jan 17 13:19 albert1.pid
-rw-rw----. 1 mysql mysql       56 Nov 28 17:16 auto.cnf
-rw-rw----. 1 mysql mysql 12582912 Jan 17 13:19 ibdata1
-rw-rw----. 1 mysql mysql 50331648 Jan 17 13:19 ib_logfile0
-rw-rw----. 1 mysql mysql 50331648 Nov 28 17:16 ib_logfile1
drwx--x--x. 2 mysql mysql     4096 Nov 29 09:13 mysql
srwxrwxrwx. 1 mysql mysql        0 Jan 17 13:19 mysql.sock
drwx------. 2 mysql mysql     4096 Nov 29 09:13 performance_schema
-rw-r--r--. 1 root  root       111 Nov 29 09:13 RPM_UPGRADE_HISTORY
-rw-r--r--. 1 mysql mysql      111 Nov 29 09:13 RPM_UPGRADE_MARKER-LAST
drwxr-xr-x. 2 mysql mysql     4096 Nov 29 10:41 test

[root@albert1 mysql]# cd albert
[root@albert1 albert]# ls -l
total 120
-rw-rw----. 1 mysql mysql    65 Nov 29 09:43 db.opt
-rw-rw----. 1 mysql mysql    42 Dec  5 09:38 pseudohash_crc_ins.TRN
-rw-rw----. 1 mysql mysql  8620 Dec  5 09:23 pseudohash.frm
-rw-rw----. 1 mysql mysql 98304 Dec  5 09:47 pseudohash.ibd
-rw-rw----. 1 mysql mysql   310 Dec  5 09:38 pseudohash.TRG</code>

这里面好包含了日志文件,PID 文件等。

mysql 服务器提供的数据访问方式

当使用常见的客户端/服务端结构使用MySQL时,数据目录下的所有的数据库都会由一个实体mysqld 来管理。

当服务器启动时,他会打开请求它维护的日志文件,然后通过监听各种类型的网络连接,对外提供一个通向数据目录的网络接口。
为访问数据,客户端程序需要建立一个与服务器的连接,然后用SQL 语句传递请求

检查表的状态:

<code>mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| test               |
+--------------------+
2 rows in set (0.03 sec)</code>

<code>mysql> 
mysql> 
mysql> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| runoob_tbl     |
+----------------+
1 row in set (0.00 sec)

mysql> check table runoob_tbl;
+-----------------+-------+----------+----------+
| Table           | Op    | Msg_type | Msg_text |
+-----------------+-------+----------+----------+
| test.runoob_tbl | check | status   | OK       |
+-----------------+-------+----------+----------+
1 row in set (0.00 sec)</code>

数据库在文件系统里的表示:

MySQL 服务器管理的每个数据库都有其自己的数据库目录,数据库目录是以数据目录的子目录形式存在。
例如:datadir/db_name

show databases 可以看出数据目录里的目录名称
<code>mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| test               |
+--------------------+
2 rows in set (0.03 sec)</code>

create database db_name 语句后会创建一个db_name 的数据库目录,它还会在这个数据库目录里创建了一个db.opt的文件,它说明数据库的各种属性:

<code>[root@albert1 albert]# more db.opt
default-character-set=latin1
default-collation=latin1_swedish_ci</code>

drop database db_name 会删除数据目录里的db_name 目录,以及该目录里的所有表文件和数据库对象。

表在文件系统里的表示:
MySQL 可以支持多种存储引擎,例如innodb,myisam和memory。
对于每一个表,MySQL都会使用一个文件来表示。
例如:.frm 文件

<code>[root@albert1 albert]# ls -l
total 120
-rw-rw----. 1 mysql mysql    65 Nov 29 09:43 db.opt
-rw-rw----. 1 mysql mysql    42 Dec  5 09:38 pseudohash_crc_ins.TRN
-rw-rw----. 1 mysql mysql  8620 Dec  5 09:23 pseudohash.frm
-rw-rw----. 1 mysql mysql 98304 Dec  5 09:47 pseudohash.ibd
-rw-rw----. 1 mysql mysql   310 Dec  5 09:38 pseudohash.TRG

[root@albert1 albert]#  strings pseudohash.frm
PRIMARY
InnoDB
)                                        
url_crc
url_crc</code>

对于表结构的描述,MySQL会负责创建.frm 文件,而各个存储引擎会创建一些附件文件,用于保存数据行和索引信息。
这些文件会根据存储引擎的不同而有所差异

InnoDB 是默认的存储引擎,在数据库目录里,每个InnoDB 表都有一个.frm 文件,其中包含了表结构的定义。对于表的内容,InnoDB 有两种表示方式:
1)系统表空间。这种表空间由数据目录里的一个或者多个大文件构成。表空间的这些组成文件共同形成了一个在逻辑上连续不断的存储区域,其大小为各个组成文件大小之和。这种情况下,InnoDB 会将其表存储到这个系统表空间里。
2)独立表空间,通过配置Innod 可以实现每个表一个表空间的存储模式。在这种情况下,每个InnDB表在数据库目录里都会有两个与表特定相关的文件,即.frm 文件和包含表数据和索引的.ibd 文件

MyISAM 引擎会创建3个文件:
1)mytbl.frm,格式文件,其中包含对该表的结构的描述
2)mtbl.MYD,数据文件,其中存储了该表各行的内容
3)mytbl.MYI ,索引文件,其中包含该表的所有索引信息

MEMORY 表是一种内存表,只有.frm 文件,其余文件都构造在内存里面

视图和触发器在文件系统里的标示:
每个视图只有一个.frm 文件,其中包含了该视图的定义和其他相关属性,建议在命名时加上一个视图的name
触发器是存储在.TRG 文件里,其中包含了该触发器的定义和其他相关属性

ORA-00245: control file backup failed;

ORA-00245: control file backup failed; target is likely on a local file system

Archived Log entry 2601 added for thread 1 sequence 13546 ID 0xffffffffc99d3af3 dest 1:
Wed Jan 18 05:50:03 2017
Errors in file /oracle/app/oracle/diag/rdbms/sopdb_jy/SOPDB1/trace/SOPDB1_ora_9699380.trc:
ORA-00245: control file backup failed; target is likely on a local file system
Wed Jan 18 06:06:41 2017
RFS[4]: Selected log 13 for thread 1 sequence 13548 dbid -912433933 branch 843201395
Wed Jan 18 06:06:50 2017
Archived Log entry 2602 added for thread 1 sequence 13547 ID 0xffffffffc99d3af3 dest 1:
Wed Jan 18 06:09:14 2017
RFS[4]: Selected log 14 for thread 1 sequence 13549 dbid -912433933 branch 843201395
Wed Jan 18 06:09:23 2017
Archived Log entry 2603 added for thread 1 sequence 13548 ID 0xffffffffc99d3af3 dest 1:


出现ORA-00245: control file backup failed; target is likely on a local file system
从报错的新来看,是控制文件的备份出现了问题,原因可能是放置在了本地文件系统上。

acbsdbs1[/oracle]rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Wed Jan 18 10:17:51 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

connected to target database: SOPDB (DBID=3382533363, not open)

RMAN> show all;

using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name SOPDB_JY are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_SOPDB1.f'; # default

从RMAN 来看,确实SNAPSHOT CONTROLFILE放置在了本地文件系统中。

错误原因:
Oracle Database – Enterprise Edition – Version 11.2.0.1.0 and later
Information in this document applies to any platform.
This issue is only applicable to RAC database.
该问题会出现在11.2.0.1.0及之后的RAC环境中。

From 11gR2 onwards, the controlfile backup happens without holding the controlfile enqueue. For non-RAC database, this doesn’t change anything. But for RAC database, due to the changes made to the controlfile backup mechanism in 11gR2, any instance in the cluster may write to the snapshot controlfile. Due to this snapshot controlfile need to be visible to all instances.

11gR2之后,备份控制文件无需锁定controlfile enqueue。在非Rac中,没有任何改变。但是,在rac环境中,由于11gR2的控制文件备份原理发生了变化,集群中的每个实例都要能够写到快照控制文件。所以,快照控制文件要对所有实例可见。

The snapshot controlfile MUST be accessible by all nodes of a RAC database, if the snapshot controlfile does not reside on a shared device error will be raised at the time of RMAN backup while taking snapshot of controlfile.
在Rac中,所有节点都必须能够访问快照控制文件。如果快照控制文件没有放到共享设备上,当rman备份快照控制文件时,ORA-00245错误就会出现。

This applies to backing up controlfile using sqlplus / having autobackup of controlfile configured on nonshared location.
???

ORA-245 error message description
----------------------------------------
00245, 00000, "control file backup operation failed"
*Cause: Failed to create a control file backup because some process
signaled an error during backup creation.
*Action: Check alert files for further information. This usually happens
because some process could not access the backup file during
backup creation. Any process of any instance that starts a
read/write control file transaction must have an access to the
backup control file during backup creation.

下面操作都会引起ORA-245错误
1. In RAC environment controlfile autobackup fails with ora-0245

Autobackup of controlfile in RMAN is failing with error:
RMAN-571: ===========================================================
RMAN-569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-571: ===========================================================
RMAN-3009: failure of Control File and SPFILE Autobackup command on
ORA_DISK_1 channel at 10/27/2010 12:13:31
ORA-245: control file backup operation failed

2. In RAC environment, backup controlfile to non shared location fails

SQL> ALTER DATABASE BACKUP CONTROLFILE TO ‘/home/rac1122/test/control.bk’ REUSE
*
ERROR at line 1:
ORA-245: control file backup operation failed

3. In RAC environment backing up standby controlfile to non shared location fails
SQL> alter database create standby controlfile as ‘/home/oracle/renostdbycntrl.ctl’;

alter database create standby controlfile as
‘/home/oracle/renostdbycntrl.ctl’
*
ERROR at line 1:
ORA-245: control file backup operation failed

4. In RAC environment copy current controlfile to ‘${DB_BACKUP_DIR}/rac_tnctv_control.bak’;
channel ch1: starting datafile copy
copying current control file
RMAN-571: ===========================================================
RMAN-569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-571: ===========================================================
RMAN-3009: failure of backup command on 10/07/2011 11:36:42 channel at ch1
ORA-245: control file backup operation failed

5. In RAC environment, Rman backup fails if snapshot controlfile is not in shared location.

RMAN-00571: ========================================================
RMAN-00569: ============ ERROR MESSAGE STACK FOLLOWS =============
RMAN-00571: =========================================================
RMAN-03009: failure of resync command on default channel at 03/13/2012 10:19:41
ORA-00245: control file backup operation failed

解决办法:
This is a RAC specific ‘configuration’ issue and the correct configuration is as described below

It is changed behaviour which requires that the snapshot controlfile in a RAC environment, is on a shared location.
1. Check the snapshot controlfile location:
RMAN> show snapshot controlfile name;

2. Configure the snapshot controlfile to a shared disk:
RMAN> CONFIGURE SNAPSHOT CONTROLFILE NAME TO ‘/snapcf_.f’;

Or in case of ASM use
RMAN> CONFIGURE SNAPSHOT CONTROLFILE NAME TO ‘+/snapcf_.f’;

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;