Skip to content

Oracle - 3. page

测试环境，安装EM12C AGENT后，登陆ASM时发现问题：
1.故障现象
[grid@AAAA grid]$ sqlplus / as sysasm
SQL*Plus: Release 11.2.0.4.0 Production on Wed May 11 14:54:39 2016
Copyright (c) 1982, 2013, Oracle. All rights reserved.
ERROR:
ORA-12547: TNS:lost contact
[root@AAAA ~]# crsctl stat res -t
——————————————————————————–
NAME TARGET STATE SERVER STATE_DETAILS
——————————————————————————–
Local Resources
——————————————————————————–
ora.ARCH.dg
ONLINE OFFLINE AAAA
ora.DATA.dg
ONLINE OFFLINE AAAA
ora.FRA.dg
ONLINE OFFLINE AAAA
ora.LISTENER.lsnr
ONLINE OFFLINE AAAA STARTING
ora.asm
ONLINE OFFLINE AAAA
ora.ons
OFFLINE OFFLINE AAAA
——————————————————————————–
Cluster Resources
——————————————————————————–
ora.cssd
1 ONLINE OFFLINE
ora.AAAA.db
1 ONLINE OFFLINE
ora.diskmon
1 OFFLINE OFFLINE
ora.evmd
1 ONLINE ONLINE AAAA

———————————-
2.排查过程
登陆ASM报错，首先排查ASM的日志：
Fri Apr 15 22:01:04 2016
NOTE: Advanced to new COD format for group ARCH
Wed May 11 14:11:51 2016
Errors in file /u01/app/grid/diag/asm/+asm/+ASM/trace/+ASM_ora_8967.trc (incident=5761):
ORA-00600: internal error code, arguments: [spstp: ORACLE_HOME uid does not match euid], [500], [507], [], [], [], [], [], [], [], [], []
Incident details in: /u01/app/grid/diag/asm/+asm/+ASM/incident/incdir_5761/+ASM_ora_8967_i5761.trc
—-ORACLE_HOME的UID不相符，从下面检查用户权限可以看到是将UID 507、500对象的是GRID/ORACLE用户。
[grid@AAAA grid]$ id grid
uid=507(grid) gid=501(oinstall) groups=501(oinstall),502(dba),504(asmadmin),505(asmdba),506(asmoper)
[grid@AAAA grid]$ id oracle
uid=500(oracle) gid=501(oinstall) groups=501(oinstall),502(dba),503(oper),504(asmadmin),505(asmdba)

如下验证可以发现GRID用户的安装权限都被误操作修改为OWNER为oracle。
[grid@AAAA +ASM]$ cd /u01/app/
[grid@AAAA app]$ ls -al
total 28
drwxrwxr-x. 7 oracle oinstall 4096 May 11 13:58 .
drwxrwxr-x. 3 oracle oinstall 4096 Apr 12 11:41 ..
drwxrwxr-x. 67 oracle oinstall 4096 Apr 15 11:49 11.2.0
drwxr-xr-x. 6 oracle oinstall 4096 May 11 14:03 agent12c
drwxrwxr-x. 9 oracle oinstall 4096 Apr 15 12:39 grid
drwxrwxr-x. 7 oracle oinstall 4096 Apr 15 13:38 oracle
drwxrwx—. 6 oracle oinstall 4096 May 11 14:24 oraInventory

临时解决方法：是重新使用命令:chown -R /u01/app/11.2.0 等方式修改目录权限后恢复正常，不过GRID软件中有一些文件的权限是root，此时暂时不管了。

–从运行稳定角度来看，建议是重新安装了。

扩展一下：
ORA-12547: TNS:lost contact在RAC中常见的还有oracle程序的权限不对，
ORA-12547 Errors
The error ORA-12547 indicates that the communication channel has been broken. It’s most often thrown because the other end of the process went away unexpectedly.

Note 1307075.1 Oracle Database Fails to Start with Error ORA-12547
Note 381566.1 connect / as sysdba Fails with Ora-12547 And Tns-12514
Note 744512.1 Ora-12547: Tns:Lost Contact Creating Database After Clean Installation

grid软件安装目录权限被修改引起登陆ASM出现ORA-12547 TNSlost contact

11.2.0.4 RAC，一个节点宕机，此时VIP FAILOVER到了另一节点。
此时存在大量客户端连接，客户端使用VIP连接到数据库服务器；
且一半客户端为连接节点1 VIP，另一半客户端为连接节点2 VIP。
此时为了快速恢复客户端到数据库连接，使用在服务器端修改监听相关配置，使存活的数据库实例注册到VIP1/VIP2。
客户端可以不进行修改的连接到数据库。

—————-需求比较扯，最好还是用11G的SCAN IP。

1.查看FAILOVER后存活节点上的IP状态信息：
[grid@bys1 admin]$ ifconfig
eth0 Link encap:Ethernet HWaddr 08:00:27:29:4B:B4
inet addr:192.168.57.215 Bcast:192.168.57.255 Mask:255.255.255.0
inet6 addr: fe80::a00:27ff:fe29:4bb4/64 Scope:Link
UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1
RX packets:3383 errors:0 dropped:0 overruns:0 frame:0
TX packets:3107 errors:0 dropped:0 overruns:0 carrier:0
collisions:0 txqueuelen:1000
RX bytes:307995 (300.7 KiB) TX bytes:388155 (379.0 KiB)

eth0:1 Link encap:Ethernet HWaddr 08:00:27:29:4B:B4
inet addr:192.168.57.219 Bcast:192.168.57.255 Mask:255.255.255.0
UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1

eth0:2 Link encap:Ethernet HWaddr 08:00:27:29:4B:B4
inet addr:192.168.57.218 Bcast:192.168.57.255 Mask:255.255.255.0
UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1

eth0:3 Link encap:Ethernet HWaddr 08:00:27:29:4B:B4
inet addr:192.168.57.216 Bcast:192.168.57.255 Mask:255.255.255.0
UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1

eth1 Link encap:Ethernet HWaddr 08:00:27:64:B0:1C
inet addr:192.168.58.1 Bcast:192.168.58.255 Mask:255.255.255.0
inet6 addr: fe80::a00:27ff:fe64:b01c/64 Scope:Link
UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1
RX packets:364 errors:0 dropped:0 overruns:0 frame:0
TX packets:79 errors:0 dropped:0 overruns:0 carrier:0
collisions:0 txqueuelen:1000
RX bytes:37826 (36.9 KiB) TX bytes:12624 (12.3 KiB)

eth1:1 Link encap:Ethernet HWaddr 08:00:27:64:B0:1C
inet addr:169.254.167.252 Bcast:169.254.255.255 Mask:255.255.0.0
UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1

lo Link encap:Local Loopback
inet addr:127.0.0.1 Mask:255.0.0.0
inet6 addr: ::1/128 Scope:Host
UP LOOPBACK RUNNING MTU:65536 Metric:1
RX packets:21148 errors:0 dropped:0 overruns:0 frame:0
TX packets:21148 errors:0 dropped:0 overruns:0 carrier:0
collisions:0 txqueuelen:0
RX bytes:12245612 (11.6 MiB) TX bytes:12245612 (11.6 MiB)

[grid@bys1 admin]$ cat /etc/hosts
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.57.215 bys1.bys.com bys1
192.168.57.216 bys1-vip.bys.com bys1-vip
192.168.57.217 bys2.bys.com bys2
192.168.57.218 bys2-vip.bys.com bys2-vip
192.168.58.1 bys1-priv.bys.com bys1-priv
192.168.58.2 bys2-priv.bys.com bys2-priv
192.168.57.219 bysrac-scan.bys.com bysrac-scan

2.修改GRID下监听配置文件：

[grid@bys1 admin]$ cat listener.ora
#LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))) # line added by Agent
###ADD BY DBA
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.57.215)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.57.216)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.57.218)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
###
LISTENER_SCAN1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1)))) # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1=ON # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON # line added by Agent

3.修改数据库local_listener参数
11.2.0.4 local_listener 默认是注册到本节点的VIP上。
SQL> show parameter local

NAME TYPE VALUE
———————————— ———– ——————————
local_listener string (ADDRESS=(PROTOCOL=TCP)(HOST=
192.168.57.216)(PORT=1521))
SQL> alter system set local_listener=”;

System altered.

修改为空值，则向本主机上所有IP地址注册。
SQL> show parameter local

NAME TYPE VALUE
———————————— ———– ——————————
local_listener string
log_archive_local_first boolean TRUE
parallel_force_local boolean FALSE

4.登陆测试：使用本机公网IP、VIP、宕机节点的VIP均可以连接–192.168.57.215/216/218
[oracle@bys1 ~]$ sqlplus system/oracle@192.168.57.215:1521/bysrac
SQL*Plus: Release 11.2.0.4.0 Production on Thu Nov 17 14:46:19 2016
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,
OLAP, Data Mining, Oracle Database Vault and Real Application Testing options

SQL> exit

[oracle@bys1 ~]$ sqlplus system/oracle@192.168.57.216:1521/bysrac
SQL*Plus: Release 11.2.0.4.0 Production on Thu Nov 17 14:46:23 2016
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,
OLAP, Data Mining, Oracle Database Vault and Real Application Testing options

SQL> exit

[oracle@bys1 ~]$ sqlplus system/oracle@192.168.57.218:1521/bysrac
SQL*Plus: Release 11.2.0.4.0 Production on Thu Nov 17 14:46:28 2016
Copyright (c) 1982, 2013, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,
OLAP, Data Mining, Oracle Database Vault and Real Application Testing options

SQL> exit

 

11G RAC 一节点宕机后修改监听相关配置使通过宕机节点VIP连接数据库的客户端可以连接

物理DG主、备库从11.2.0.4升级到12.1.0.2方式：在升级过程中，需要DG备库停止应用日志，主库停止对外服务，即停止业务，所需停机时间即主库升级的时间;

–另一种停机短的方式：如果对停机时间要求很短则可考虑主库对应一物理备库一逻辑备库，通过逻辑备库方式进行升级，进行逻辑备库与主库的主备切换来实现升级，最后再同步到物理备库来实现整个DG架构的升级，测试充分的话这种停机时间应该10分钟左右就够。对硬件及逻辑、物理备库互转等测试会要求较多;其它的第三方同步软件方式就不说了。

当前方式优点是主库升级时DG备库不升级，状态不变，如升级失败，业务回退比较方便，适合于数据库量大、对回退时间要求严格的场景；当然如果一主多备库环境，可以直接升主库同时应用日志到一个备库，另一个备库不升级做回退用—所需停机时间即主库升级的时间。。
—-主要步骤
1.物理DG主、备库状态检查，取消备库的日志恢复应用，但是保留接收REDO日志
2.主库进行升级
—–>升级前检查及处理–主库：SQL> @dbupgdiag.sql –MOS文档：556610.1有提供，SQL> @preupgrd.sql，并根据输出进行相应的修改
—–>将连接DG备库的tnsnames.ora文件复制到新的12C RDBMS_HOME相应目录
—–>DBUA升级–图形界面，中间遇到问题进行相应处理； 注意如果是RAC，此时已经安装了12C的GI并正常运行，需要通过11G的RDBMS_HOME下srvctl工具将11G的数据库资源注册到集群并启动两节点数据库到OPEN–数据库资源的ORACLE_HOME需要是11G的RDBMS_HOME–不然DBUA界面无法正确选择待升级的RDBMS_HOME及DB版本。
—–>DBUA升级完成后的配置修改compatible=’12.1.0.2.0′–主库

3.备库开启日志恢复应用，通过应用日志完成升级
—–>首先备库的spfile修改compatible=’12.1.0.2.0’（主库升级期间备库MOUNT但是不RECOVER，后面可能遇到600错误，不影响）
—–>将备库的spfile、密码文件、连接到主库的tnsnames.ora文件复制到12C软件的$ORACLE_HOME的相应目录
—–>使用12C的软件，启动备库到MOUNT，日志中有设置compatible相关信息
—–>在12C软件下启用DG备库的日志恢复应用—注意监控alert日志
—–>恢复完成后，启动备库至OPEN READ ONLY状态，并开启日志应用
4.检查主、备库同步情况及版本信息
—–>检查DG主备库同步情况–通过观察主、备库的ALERT日志来监控
—–>主库版本信息检查：—备库同样命令检查，不重复贴了。
—–>注意主、备库使用12C的监听器
—–>如果主机上有多个数据库实例，升级后存在多个版本数据库，如果监听使用11G，升级后的12C数据库可能无法动态注册到11G监听，建议使用12C监听器，低版本数据库均可以注册到12C监听。

############################单实例升级–详细的过程介绍及部分命令示例：
1.物理DG主、备库状态检查，取消备库的日志恢复应用，但是保留接收日志
备库：SQL> alter database recover managed standby database cancel;

2.主库进行升级
—–>升级前检查及处理
主库：
SQL> @dbupgdiag.sql –MOS文档：556610.1有提供
[oracle@bys1 ~]$ cd /u01/app/oracle/product/12.1/dbhome_1
[oracle@bys1 admin]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Sat Mar 18 20:57:22 2017
SQL> startup
SQL> @preupgrd.sql
根据输出进行相应的修改
—–>DBUA升级–图形界面，中间遇到问题进行相应处理
—–>升级后的配置修改–主库
SQL> show parameter com
NAME TYPE VALUE
———————————— ———– ——————————
compatible string 11.2.0.4.0
SQL> alter system set compatible=’12.1.0.2.0′ scope=spfile;

3.备库开启日志恢复应用，通过应用日志完成升级
—–>首先备库的spfile修改compatible=’12.1.0.2.0′
—–>将备库的spfile、密码文件复制到12C软件的$ORACLE_HOME/dbs目录
—–>使用12C的软件，启动备库到MOUNT
—–>启用DG备库的日志恢复应用—注意监控alert日志
[oracle@bys1 dbs]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Sun Mar 19 19:44:24 2017

Copyright (c) 1982, 2014, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup mount;
ORACLE instance started.

Total System Global Area 536870912 bytes
Fixed Size 2926472 bytes
Variable Size 213911672 bytes
Database Buffers 314572800 bytes
Redo Buffers 5459968 bytes
Database mounted.
SQL> alter database recover managed standby database using current logfile disconnect from session;

Database altered.

—–>恢复完成后，启动备库至OPEN READ ONLY状态，并开启日志应用

4.检查主、备库同步情况及版本信息
—–>检查DG主备库同步情况–通过观察主、备库的ALERT日志来监控
—–>主库版本信息检查：—备库同样命令检查，不重复贴了。
SQL> select comp_name,version,status from dba_registry;

COMP_NAME VERSION STATUS
———————————– ————— ——-
Oracle Application Express 4.2.5.00.08 VALID
OWB 11.2.0.4.0 VALID
OLAP Catalog 11.2.0.4.0 OPTION
OFF
Spatial 12.1.0.2.0 VALID
Oracle Multimedia 12.1.0.2.0 VALID
Oracle XML Database 12.1.0.2.0 VALID
Oracle Text 12.1.0.2.0 VALID
Oracle Workspace Manager 12.1.0.2.0 VALID
Oracle Database Catalog Views 12.1.0.2.0 VALID
Oracle Database Packages and Types 12.1.0.2.0 VALID
JServer JAVA Virtual Machine 12.1.0.2.0 VALID
Oracle XDK 12.1.0.2.0 VALID
Oracle Database Java Packages 12.1.0.2.0 VALID
OLAP Analytic Workspace 12.1.0.2.0 VALID
Oracle OLAP API 12.1.0.2.0 VALID

15 rows selected.

SQL> select action_time,action,id,version,comments from dba_registry_history;

ACTION_TIME ACTION ID VERSION COMMENTS
—————————— ————— ———- ————— ——————————
24-AUG-13 12.03.45.119862 PM APPLY 0 11.2.0.4 Patchset 11.2.0.2.0
13-JUL-16 12.27.19.064373 AM APPLY 0 11.2.0.4 Patchset 11.2.0.2.0
18-MAR-17 10.31.36.080528 PM VIEW INVALIDATE 8289601 view invalidation

11204单实例DG升级到12102版本-有停机-包含升级12cRAC注意事项

在ORACLE 10G/11G版本时，如果要将数据库的数据文件，移动到不同的磁盘目录（即修改磁盘路径），如果数据库在OPEN状态需要将数据文件OFFLINE，或者在数据库MOUNT状态下进行修补。

在12C及以上版本时，可以在线移动一个正在被访问的数据文件；即是system表空间中的数据文件也可以。
可以在线移动数据文件，表示当用户正在访问系统的时候，很多维护操作可以在线执行；例如，将数据文件移动到其他存储设备或者移动到Oracle ASM。这确保了服务的连续性，并且满足正常运行时的服务水平协议（SLA）。

在 12C，当数据文件处于在线状态并且正在被访问的时候，可以执行下面的操作：

1. 重命名在线数据库文件
2. 迁移在线数据库文件
3. 拷贝在线数据文件
4. 迁移在线数据文件并且覆盖现有文件
5. 迁移在线数据文件到 oracle ASM

如下为对这些功能的测试案例：

1.在线移动数据文件-文件系统-ASM

 

 

–可用于数据文件重命令、从一个目录移动到另一目录(可能涉及不同的磁盘或分区、LV)

–直接使用MOVE DATAFILE参数相当于是移动或者重命令，不会保留原数据文件

–keep关键字，可以实现拷贝数据文件到新位置，原文件保留

移动PDB中数据文件需要在指定 的PDB中进行操作：

 

show con_name

 

CON_NAME

——————————

CDBPDB2

C##Luda@cdbpdb2>select name from v$dbfile;

 

NAME

————————————————————————————————————————————————————————————

/u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/LudaCDB/4FDB7936712F5A06E053D339A8C06FAC/datafile/o1_mf_sysaux_dkx3hppr_.dbf

/u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/LudaCDB/4FDB7936712F5A06E053D339A8C06FAC/datafile/o1_mf_undotbs1_dkx3hppt_.dbf

/u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/LudaCDB/4FDB7936712F5A06E053D339A8C06FAC/datafile/o1_mf_pdb2_dkx9pq4r_.dbf

/u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/system01.dbf

C##Luda@cdbpdb2>alter database  MOVE DATAFILE ‘/u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/LudaCDB/4FDB7936712F5A06E053D339A8C06FAC/datafile/o1_mf_sysaux_dkx3hppr_.dbf’ to ‘/u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/sysaux01.dbf’;

 

Database altered.

 

C##Luda@cdbpdb2>!ls /u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/LudaCDB/4FDB7936712F5A06E053D339A8C06FAC/datafile/o1_mf_sysaux_dkx3hppr_.dbf

ls: cannot access /u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/LudaCDB/4FDB7936712F5A06E053D339A8C06FAC/datafile/o1_mf_sysaux_dkx3hppr_.dbf: No such file or directory

 

C##Luda@cdbpdb2>!ls /u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/sysaux01.dbf

/u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/sysaux01.dbf

C##Luda@cdbpdb2>select file_name, status, online_status from dba_data_files;

 

FILE_NAME                                                                        STATUS    ONLINE_

——————————————————————————– ——— ——-

/u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/sysaux01.dbf                           AVAILABLE ONLINE

/u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/LudaCDB/4FDB7936712F5A06E053D339A8C06FA AVAILABLE ONLINE

C/datafile/o1_mf_undotbs1_dkx3hppt_.dbf

 

/u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/LudaCDB/4FDB7936712F5A06E053D339A8C06FA AVAILABLE ONLINE

C/datafile/o1_mf_pdb2_dkx9pq4r_.dbf

 

/u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/system01.dbf                           AVAILABLE SYSTEM

观察此时的ALERT日志：

2017-05-23T14:43:17.122383+08:00

CDBPDB2(4):alter database  MOVE DATAFILE ‘/u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/LudaCDB/4FDB7936712F5A06E053D339A8C06FAC/datafile/o1_mf_sysaux_dkx3hppr_.dbf’ to ‘/u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/sysaux01.dbf’

2017-05-23T14:43:17.146362+08:00

Moving datafile /u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/LudaCDB/4FDB7936712F5A06E053D339A8C06FAC/datafile/o1_mf_sysaux_dkx3hppr_.dbf (22) to /u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/sysaux01.dbf

2017-05-23T14:43:55.724994+08:00

Move operation committed for file /u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/sysaux01.dbf

2017-05-23T14:43:58.166391+08:00

CDBPDB2(4):Completed: alter database  MOVE DATAFILE ‘/u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/LudaCDB/4FDB7936712F5A06E053D339A8C06FAC/datafile/o1_mf_sysaux_dkx3hppr_.dbf’ to ‘/u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/sysaux01.dbf’

使用KEEP关键字的测试：–OMF管理的不会保留原文件，

C##Luda@cdbpdb2>alter database  MOVE DATAFILE ‘/u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/LudaCDB/4FDB7936712F5A06E053D339A8C06FAC/datafile/o1_mf_pdb2_dkx9pq4r_.dbf’ to ‘/u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/pdb201.dbf’ keep;

 

Database altered.

 

C##Luda@cdbpdb2>select file_name, status, online_status from dba_data_files;

 

FILE_NAME                                                                        STATUS    ONLINE_

——————————————————————————– ——— ——-

/u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/sysaux01.dbf                           AVAILABLE ONLINE

/u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/pdb201.dbf                             AVAILABLE ONLINE

/u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/LudaCDB/4FDB7936712F5A06E053D339A8C06FA AVAILABLE ONLINE

C/datafile/o1_mf_undotbs1_dkx3hppt_.dbf

 

/u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/system01.dbf                           AVAILABLE SYSTEM

 

C##Luda@cdbpdb2>!ls /u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/LudaCDB/4FDB7936712F5A06E053D339A8C06FAC/datafile/o1_mf_pdb2_dkx9pq4r_.dbf

ls: cannot access /u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/LudaCDB/4FDB7936712F5A06E053D339A8C06FAC/datafile/o1_mf_pdb2_dkx9pq4r_.dbf: No such file or directory

 

C##Luda@cdbpdb2>!ls /u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/pdb201.dbf

/u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/pdb201.dbf

 

 

C##Luda@cdbpdb2>alter database  MOVE DATAFILE ‘/u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/pdb201.dbf’ to ‘/u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/LudaCDB/pdb201.dbf’ keep;

 

Database altered.

 

 

C##Luda@cdbpdb2>!ls /u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/pdb201.dbf

/u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/pdb201.dbf

 

C##Luda@cdbpdb2>!ls /u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/LudaCDB/pdb201.dbf

/u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/LudaCDB/pdb201.dbf

 

 

使用REUSE关键字覆盖

C##Luda@cdbpdb2>!ls /u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/pdb201.dbf

/u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/pdb201.dbf

 

C##Luda@cdbpdb2>!ls /u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/LudaCDB/pdb201.dbf

/u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/LudaCDB/pdb201.dbf

 

C##Luda@cdbpdb2>alter database  MOVE DATAFILE ‘/u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/LudaCDB/pdb201.dbf’ to ‘/u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/pdb201.dbf’ keep;

alter database  MOVE DATAFILE ‘/u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/LudaCDB/pdb201.dbf’ to ‘/u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/pdb201.dbf’ keep

*

ERROR at line 1:

ORA-01119: error in creating database file ‘/u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/pdb201.dbf’

ORA-27038: created file already exists

Additional information: 1

 

 

C##Luda@cdbpdb2>alter database  MOVE DATAFILE ‘/u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/LudaCDB/pdb201.dbf’ to ‘/u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/pdb201.dbf’ reuse;

C##Luda@cdbpdb2>!ls /u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/LudaCDB/pdb201.dbf

ls: cannot access /u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/LudaCDB/pdb201.dbf: No such file or directory

 

C##Luda@cdbpdb2>!ls /u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/pdb201.dbf

/u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/pdb201.dbf

 

Oracle12C版本开始支持的在线移动数据文件功能测试

临时表空间使用率高问题的分析解释

当你监控temporary tablespace 的空闲空间，发现空闲空间并没有随着sort的结束而增加，甚至当前数据库没有任何客户端连接也是如此：仍然有大量的temporary类型的extent存在于temporary tablespace，有时候用户会在运行事务的时候遇到ora-1652（在temp表空间上），这个错误表明没有足够的空间，但是当用户的查询失败后，smon并没有去清理临时段。

Scope & Application
===================

如果TEMPORARY TABLESPACE的类型是TEMPORARY，TEMPORARY TABLESPACE里的使用过的空间是不会被释放的，除非shutdown。
如果是PERMANENT，SMON会在process不再使用临时段之后去做清理。

如果使用TEMPORARY类型的临时表空间，数据库刚刚startup后，第一个使用TEMPORARY tablespace进行排序的statement会创一个建sort segment，这个segment不会被释放，除非数据库restart，可以用V$SORT_SEGMENT察看当前的已分配了的sort segments地使用情况。

如果是用PERMANENT tablespace作排序，由smon负责在statement结束之后删除被创建的temporary segments，这样空间可以被其他对象使用。

使用如下查询：
select OWNER,
SEGMENT_NAME,
SEGMENT_TYPE ,
TABLESPACE_NAME
from DBA_SEGMENTS
where SEGMENT_TYPE = ‘TEMPORARY’;

to give results similar to:

OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME
—– ———— ———— —————
SYS 4.2 TEMPORARY TEMP

Note: segment_name and tablepace_name are likely to be different.

tablespace的类型可以用如下查询：
select TABLESPACE_NAME,
CONTENTS
from DBA_TABLESPACES
where TABLESPACE_NAME in ( );

to give results similar to:

TABLESPACE_NAME CONTENTS
—————————— ———
TEMP TEMPORARY
TEMP1 PERMANENT

解释
=====

1)
对于使用了TEMPORARY类型的TEMPORARY TABLESPACE，临时段被使用后不被回收是正常的行为（7.3以后），这时如果有ora-1652发生，说明临时表空间是真得不够.

系统中的临时段在oracle startup之后被创建，并只有在oracle shutdown的时候被释放，如果存在大量的extent，一个可能的原因是你的storage子句的设置有问题。

考虑到性能原因，当一个temporary extent被分配的时候，tablespace会做一个标记，操作结束之后这个extent不会被释放或回收，相应的，这个extent被简单的标志为free，对于后面的sort操作是available的，这样就省去了系统分配和回收temporary extent的负载。

2）
对于在PERMANENT的tablespace里面使用temporary segment，记得检查parameter file里面没有设置如下参数：
event=”10061 trace name context forever, level 10″
event=”10269 trace name context forever, level 10″

这两个参数禁止smon去做temporary segment的clean up和coalescing，在oraus.msg里面可以看到具体的定义：
10061, 00000, “disable SMON from cleaning temp segments
10269, 00000, “Don’t do coalesces of free space in SMON”

// *Cause: setting this event prevents SMON from doing free space coalesces

（正常情况下，smon会负责定期做temporary segment的clean up和coalescing。具体作的方式metalink可以找到）

通过查询V$SORT_SEGMENT来看temp segment是free还是being used

For example:
select TABLESPACE_NAME,TOTAL_BLOCKS,USED_BLOCKS,FREE_BLOCKS from v$sort_segment;

>>> DURING the SORT you will see something like this:
TABLESPACE_NAME TOTAL_BLOCKS USED_BLOCKS FREE_BLOCKS
—————- ———— ———– ———–
TEMP 590 590 0

>>> AFTER the SORT you will see something like this:
TABLESPACE_NAME TOTAL_BLOCKS USED_BLOCKS FREE_BLOCKS
————— ———— ———— ———–
TEMP 590 0 590

通过这个查询可以看到在做sort的时候，segment标识为used，sort结束后，标志为free。实际上这时候相应的extents都被释放回SEP（sort extent pool）里面。（SEP是SGA里面存放temp extent的部分，具体描述在metalink也可以找到）

参考MOS文档：Temporary Tablespace, the Sort Extent Pool, and OPS (文档 ID 65973.1)
Temporary Segments Are Not Being De-Allocated After a Sort（Note:1039341.6）

如下为典型的临时表空间问题的排查步骤和命令脚本：

1.查看当前临时表空间配置情况–大小、数据文件个数、扩展情况等
2.哪些会话使用临时表空间较多，具体占用大小、临时段类型
3.找出使用临时表空间较多的SQL信息
4.找出使用临时表空间较多的SESSION信息
###############临时表空间使用过多的排查思路
1.查看当前临时表空间配置情况
–查看当前临时文件大小、是否支持自动扩展等
set linesize 180
col file_name for a50
col tablespace_name for a20
select file_name,file_id,tablespace_name,bytes/1024/1024/1024 gb,status,AUTOEXTENSIBLE,MAXBYTES from dba_temp_files;

set linesize 180
col name for a50
select * from V$TEMPFILE;
—查看临时表空间使用率
col tablespace_name for a30
select (s.tot_used_blocks/f.total_blocks)*100 as “percent used”
from
(select sum(used_blocks) tot_used_blocks from gv$sort_segment where tablespace_name=’TEMP’) s,
(select sum(blocks) total_blocks from dba_temp_files where tablespace_name=’TEMP’) f;
====
select f.TABLESPACE_NAME,f.total_MB,f.USED_MB,f.USED_MB/f.total_MB*100 as percent_used
from (select bb.TABLESPACE_NAME,bb.temp_MB total_MB,aa.used_blocks*P.VALUE/1024/1024 USED_MB
from (select a.TABLESPACE_NAME,sum(a.used_blocks) USED_blocks from gv$sort_segment a group by a.TABLESPACE_NAME) aa,
(select b.TABLESPACE_NAME,sum(b.BYTES/1024/1024) temp_MB from dba_temp_files b group by b.TABLESPACE_NAME) bb, SYS.V_$SYSTEM_PARAMETER P
where aa.TABLESPACE_NAME= bb.TABLESPACE_NAME and UPPER(P.NAME)=’DB_BLOCK_SIZE’) f;

###########################################################

2.查看当前哪个会话使用的临时段较大
—–找出使用临时空间最多的20个会话session_addr及其SQL_ID
set linesize 180
select * from
(select username,session_addr,sql_id,contents,segtype,blocks*8/1024 mb
from v$sort_usage order by blocks desc)
where rownum<11;

select * from v$temporary_lobs;

#####查出TEMP段大小以及进程相关信息
set linesize 180 pagesize 10000
col username for a10
col program for a20
col machine for a15
COL EVENT FOR A28
col sql_id for a15
col spid for a8
col TABLESPACE for a6

select * from (select s.username,b.spid,s.sid,s.program,s.machine,s.sql_id,s.event,to_char(s.LOGON_TIME,’yyyymmdd hh24:mi:ss’),u.tablespace, u.segtype, round(((u.blocks*P.VALUE)/1024/1024),2) MB
from v$session s, v$sort_usage u, SYS.V_$SYSTEM_PARAMETER P,v$process b
where s.saddr = u.session_addr
and s.paddr=b.addr
AND UPPER(P.NAME)=’DB_BLOCK_SIZE’
order by MB DESC) where rownum<31;

===prev_sql_id
========
select * from (select s.username,b.spid,s.sid,s.program,s.machine,s.prev_sql_id,to_char(s.LOGON_TIME,’yyyymmdd hh24:mi:ss’), u.segtype, round(((u.blocks*P.VALUE)/1024/1024),2) MB
from v$session s, v$sort_usage u, SYS.V_$SYSTEM_PARAMETER P,v$process b
where s.saddr = u.session_addr
and s.paddr=b.addr
AND UPPER(P.NAME)=’DB_BLOCK_SIZE’
order by MB DESC) where rownum<31;
===

—通过SQL_ID查SQL文本–如果SESSION已执行完排序SQL后并未有执行其它SQL，则可如下查：
select SQL_ID,sql_text from v$sqltext where sql_id in(’08yg5rb6upbra’,”) order by SQL_ID,piece;
###########################################################
3.找出使用临时表空间较多的SQL信息
v$sort_usage中的SQL_ID是会话前一条执行的SQL,–即对应V$SESSION中的字段PREV_SQL_ID.
–从11.2.0.2这个版本开始，v$sort_usage的基表x$ktsso中增加了一个字段ktssosqlid，表示该临时段真正关联的SQL。
以上述的测试结果为例，查询这个基表的结果如下：
select ktssosqlid,v$session.SADDR from x$ktsso, v$session where ktssoses = v$session.saddr
and ktssosno = v$session.serial#
and v$session.SADDR in(‘3231FE14′,’322DEA14′);

对于11.2.0.2之前版本的查询：
—通过第三步查出的session_addr关联SADDR查prev_sql_id–要求prev_sql_id执行后未执行新SQL
select sid,prev_sql_id, sql_id from v$session where saddr=’070000294AC0D050’;

#######################################
4.找出使用临时表空间较多的SESSION信息
–使用第三步查出的session_addr查出的使用临时表空间较多的SQL_ID及SESSION信息：
select a.sql_id sort_sql_id,b.sql_id,b.prev_sql_id, a.contents,a.segtype,a.blocks*8/1024 Mb,b.sid
from v$sort_usage a,v$session b
where a.session_addr=b.saddr and (b.sid=31 or b.saddr in(‘3231FE14′,’322DEA14′));
–根据session_addr查询会话的SID/终端信息等
col spid for a10
col machine for a30
select b.spid,a.sid,a.username,a.OSUSER,a.program,a.machine from v$session a,v$process b
where a.paddr=b.addr and a.type=’USER’ and a.SADDR in(‘3231FE14′,’322DEA14’);

################
适用11.2.0.2及以上，直接查出排序空间使用较多的SQL_ID
col username for a10
col osuser for a10
col tablespace for a15
select * from
(select k.inst_id “INST_ID”,
ktssoses “SADDR”,
sid,
ktssosno “SERIAL#”,
username “USERNAME”,
osuser “OSUSER”,
s.machine,
s.event,
ktssosqlid “SQL_ID”,
ktssotsn “TABLESPACE”,
decode(ktssocnt, 0, ‘PERMANENT’, 1, ‘TEMPORARY’) “CONTENTS”,
decode(ktssosegt, 1, ‘SORT’, 2, ‘HASH’, 3, ‘DATA’, 4, ‘INDEX’,
5, ‘LOB_DATA’, 6, ‘LOB_INDEX’ , ‘UNDEFINED’) “SEGTYPE”,
ktssofno “SEGFILE#”,
ktssobno “SEGBLK#”,
ktssoexts “EXTENTS”,
ktssoblks “BLOCKS”,
round(ktssoblks*p.value/1024/1024, 2) “SIZE_MB”,
ktssorfno “SEGRFNO#”
from x$ktsso k, v$session s,
(select value from v$parameter where name=’db_block_size’) p
where ktssoses = s.saddr
and ktssosno = s.serial#
order by size_mb)
where rownum<11;

====
select * from
(select
ktssoses “SADDR”,
s.sid,
username “USERNAME”,
s.machine,
s.event,
ktssosqlid “SQL_ID”,
ktssotsn “TABLESPACE”,
decode(ktssocnt, 0, ‘PERMANENT’, 1, ‘TEMPORARY’) “CONTENTS”,
decode(ktssosegt, 1, ‘SORT’, 2, ‘HASH’, 3, ‘DATA’, 4, ‘INDEX’,
5, ‘LOB_DATA’, 6, ‘LOB_INDEX’ , ‘UNDEFINED’) “SEGTYPE”,
round(ktssoblks*p.value/1024/1024, 2) “SIZE_MB”
from x$ktsso k, v$session s,
(select value from v$parameter where name=’db_block_size’) p
where ktssoses = s.saddr
and ktssosno = s.serial#
order by size_mb)
where rownum<11;

Oracle临时表空间使用率高问题的分析解释与排查脚本