当前位置: 数据库>其它
本页文章导读:
▪批量生成删除用户语句。 SELECT
'drop user '||t.username||' CASCADE;'
FROM dba_users t WHERE t.username like '%CSDN%'
作者：tanzugan 发表于2013-1-4 10:14:57 原文链接
阅读：0 评论：0 查看评论
.........
▪配置客户端连接到ASM实例 对于Oracle 网络配置，我们通常通过negmgr或者netca来完成客户端连接到数据库实例。而对于连接到ASM实例，同样可以实现从客户端来进行连接。不过Oracle并未为我们提供工具来完成配.........
▪保存误删除的存储或表等对象的创建脚本
在开发环境,由于多人共享一个库,而且权限管理不合理的话,很有可能出现你删除了我的存储,我删除了你的表的情况.
一般如果发现的及时,可以使用闪回技术马上闪回相应的表及数据,但是闪回.........
[1]批量生成删除用户语句。
来源: 互联网 发布时间: 2013-11-07
SELECT
'drop user '||t.username||' CASCADE;'
FROM dba_users t WHERE t.username like '%CSDN%'
作者：tanzugan 发表于2013-1-4 10:14:57 原文链接
阅读：0 评论：0 查看评论
[2]配置客户端连接到ASM实例
来源: 互联网 发布时间: 2013-11-07
对于Oracle 网络配置，我们通常通过negmgr或者netca来完成客户端连接到数据库实例。而对于连接到ASM实例，同样可以实现从客户端来进行连接。不过Oracle并未为我们提供工具来完成配置，我们可以通过手动配置监听以及客户端tnsnames来实现。本文对此给出描述与示例。
1、服务器端、客户端的环境
#服务器端环境，host信息
oracle@bo2dbp:~> cat /etc/hosts |grep vip
192.168.7.61 bo2dbp-vip.2gotrade.com bo2dbp-vip
192.168.7.62 bo2dbs-vip.2gotrade.com bo2dbs-vip
#操作系统及Oracle版本
oracle@bo2dbp:~> cat /etc/issue
Welcome to SUSE Linux Enterprise Server 10 SP3 (x86_64) - Kernel \r (\l).
oracle@bo2dbp:~> sqlplus -v
SQL*Plus: Release 10.2.0.3.0 - Production
#服务器端环境，集群信息
oracle@bo2dbp:~> ./crs_stat.sh
Resource name Target State
-------------- ------ -----
ora.GOBO4.GOBO4A.inst ONLINE ONLINE on bo2dbp
ora.GOBO4.GOBO4B.inst ONLINE ONLINE on bo2dbs
ora.GOBO4.db ONLINE ONLINE on bo2dbp
ora.bo2dbp.ASM1.asm ONLINE ONLINE on bo2dbp
ora.bo2dbp.LISTENER_BO2DBP.lsnr ONLINE ONLINE on bo2dbp
ora.bo2dbp.gsd ONLINE ONLINE on bo2dbp
ora.bo2dbp.ons ONLINE ONLINE on bo2dbp
ora.bo2dbp.vip ONLINE ONLINE on bo2dbp
ora.bo2dbs.ASM2.asm ONLINE ONLINE on bo2dbs
ora.bo2dbs.LISTENER_BO2DBS.lsnr ONLINE ONLINE on bo2dbs
ora.bo2dbs.gsd ONLINE ONLINE on bo2dbs
ora.bo2dbs.ons ONLINE ONLINE on bo2dbs
ora.bo2dbs.vip ONLINE ONLINE on bo2dbs
#客户端信息
C:\Users\robinson.cheng>systeminfo
Host Name: PC39
OS Name: Microsoft Windows 7 Professional
OS Version: 6.1.7600 N/A Build 7600
OS Manufacturer: Microsoft Corporation
System Type: x64-based PC
C:\Users\robinson.cheng>sqlplus -v
SQL*Plus: Release 10.2.0.3.0 - Production
2、监听器的状态
oracle@bo2dbs:~> lsnrctl status LISTENER_BO2DBS | grep ASM
Service "+ASM" has 1 instance(s). #可以看到ASM实例处于BLOCKED状态
Instance "+ASM2", status BLOCKED, has 1 handler(s) for this service...
Service "+ASM_XPT" has 1 instance(s).
Instance "+ASM2", status BLOCKED, has 1 handler(s) for this service...
3、分配ASM SID
#使用下面的ASM配置信息来修改监听器配置文件
Item Node1 Node2
------ -------- -----------
hostname bo2dbp bo2dbs
Oracle SID GOBO4A GOBO4B
ASM SID +ASM1 +ASM2
ASM Global DB Name (service name) +ASM +ASM
4、修改监听配置文件listener.ora
#对于监听器的配置，仅仅是增加子项SID_DESC
#下面是增加之后所看到的内容
oracle@bo2dbp:~> more $ORACLE_HOME/network/admin/listener.ora
# listener.ora.bo2dbp Network Configuration File: /u01/oracle/db/network/admin/listener.ora.bo2dbp
# Generated by Oracle configuration tools.
LISTENER_BO2DBP =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = bo2dbp-vip.2gotrade.com)(PORT = 1521)(IP = FIRST))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.7.51)(PORT = 1521)(IP = FIRST))
)
)
SID_LIST_LISTENER_BO2DBP =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /u01/oracle/db)
(PROGRAM = extproc)
(SID_DESC = #这整个SID_DESC项即为ASM1实例新增的条目
(SID_NAME = +ASM1)
(GLOBAL_DBNAME = +ASM)
(ORACLE_HOME = /u01/oracle/db)
)
)
oracle@bo2dbs:~> more $ORACLE_HOME/network/admin/listener.ora
# listener.ora.bo2dbs Network Configuration File: /u01/oracle/db/network/admin/listener.ora.bo2dbs
# Generated by Oracle configuration tools.
LISTENER_BO2DBS =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = bo2dbs-vip.2gotrade.com)(PORT = 1521)(IP = FIRST))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.7.52)(PORT = 1521)(IP = FIRST))
)
)
SID_LIST_LISTENER_BO2DBS =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /u01/oracle/db)
(PROGRAM = extproc)
)
(SID_DESC = #这整个SID_DESC项即为ASM2实例新增的条目
(SID_NAME = +ASM2)
(GLOBAL_DBNAME = +ASM)
(ORACLE_HOME = /u01/oracle/db)
)
)
5、重启监听器
oracle@bo2dbp:~> srvctl stop listener -n bo2dbp
oracle@bo2dbp:~> srvctl start listener -n bo2dbp
oracle@bo2dbp:~> srvctl stop listener -n bo2dbs
oracle@bo2dbp:~> srvctl start listener -n bo2dbs
#查看监听器的状态
oracle@bo2dbp:~> lsnrctl status LISTENER_BO2DBP | grep ASM
Service "+ASM" has 2 instance(s). #可以看到多处了一个为UNKNOWN状态，表明使用了静态方式注册
Instance "+ASM1", status UNKNOWN, has 1 handler(s) for this service...
Instance "+ASM1", status BLOCKED, has 1 handler(s) for this service...
Service "+ASM_XPT" has 1 instance(s).
Instance "+ASM1", status BLOCKED, has 1 handler(s) for this service...
oracle@bo2dbs:~> lsnrctl status LISTENER_BO2DBS | grep ASM
Service "+ASM" has 2 instance(s). #示例2上监听器状态也多出了一个为UNKNOWN状态的+ASM2实例
Instance "+ASM2", status UNKNOWN, has 1 handler(s) for this service...
Instance "+ASM2", status BLOCKED, has 1 handler(s) for this service...
Service "+ASM_XPT" has 1 instance(s).
Instance "+ASM2", status BLOCKED, has 1 handler(s) for this service...
#Author : Robinson
#Blog : http://blog.csdn.net/robinson_0612
6、配置客户端tnsnames
#Windons 客户端tnsnames.ora添加如下配置条目
GOBO4_ASM1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.7.61)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = +ASM)
)
)
GOBO4_ASM2 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.7.62)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = +ASM)
)
)
7、测试连接到ASM实例
C:\Users\robinson.cheng>sqlplus -S sys/oracle@GOBO4_ASM1 as sysdba @inst
INSTANCE_NAME HOST_NAME STATUS
---------------- ------------------------------ ------------
+ASM1 bo2dbp STARTED
C:\Users\robinson.cheng>sqlplus -S sys/oracle@GOBO4_ASM2 as sysdba @inst
INSTANCE_NAME HOST_NAME STATUS
---------------- ------------------------------ ------------
+ASM2 bo2dbs STARTED
更多参考
有关Oracle RAC请参考
使用crs_setperm修改RAC资源的所有者及权限
使用crs_profile管理RAC资源配置文件
RAC 数据库的启动与关闭
再说 Oracle RAC services
Services in Oracle Database 10g
Migrate datbase from single instance to Oracle RAC
Oracle RAC 连接到指定实例
Oracle RAC 负载均衡测试(结合服务器端与客户端)
Oracle RAC 服务器端连接负载均衡(Load Balance)
Oracle RAC 客户端连接负载均衡(Load Balance)
ORACLE RAC 下非缺省端口监听配置(listener.ora tnsnames.ora)
ORACLE RAC 监听配置 (listener.ora tnsnames.ora)
配置 RAC 负载均衡与故障转移
CRS-1006 , CRS-0215 故障一例
[3]保存误删除的存储或表等对象的创建脚本
来源: 互联网 发布时间: 2013-11-07
在开发环境,由于多人共享一个库,而且权限管理不合理的话,很有可能出现你删除了我的存储,我删除了你的表的情况.
一般如果发现的及时,可以使用闪回技术马上闪回相应的表及数据,但是闪回具有一定的局限性:受闪回区大小限制,如果删除对象的时间过长就很有可能闪回不了了。
所以本人写了一个存储和触发器来把DROP掉的对象全部以文件的方式保存起来(相当于做了一个备份),以方便误删除对象之后的恢复.
首先,需要创建目录以存放备份的文件
create directory DDIR as 'D:\drop_back';
然后,创建存储过程
CREATE OR REPLACE PROCEDURE PROC_OUTPUTDDL(pTYPE VARCHAR2,pNAME VARCHAR2,PMESSAGE VARCHAR2,POWNER VARCHAR2 DEFAULT '')
AUTHID CURRENT_USER --调用者权限
AS
/*
功能:输出存储、表、函数等对象的创建语句到文件
*/
V_FILE UTL_FILE.FILE_TYPE;
V_OWNER VARCHAR2(100);
BEGIN
--判断是否有确定用户,缺省为当前用户
IF POWNER IS NULL THEN
SELECT USER INTO V_OWNER FROM DUAL;
ELSE
V_OWNER:=POWNER;
END IF;
--如果文件存在就追加内容,不存在就创建
IF DBMS_LOB.FILEEXISTS(BFILENAME('DDIR','DDL'||TO_CHAR(SYSDATE,'YYYY_MM_DD')||'.LOG'))=1
THEN
V_FILE:=UTL_FILE.FOPEN('DDIR','DDL'||TO_CHAR(SYSDATE,'YYYY_MM_DD')||'.LOG','A');
ELSE
V_FILE:=UTL_FILE.FOPEN('DDIR','DDL'||TO_CHAR(SYSDATE,'YYYY_MM_DD')||'.LOG','W');
END IF;
UTL_FILE.NEW_LINE(V_FILE);
UTL_FILE.PUT_LINE(V_FILE,'***********STARTTIME['||TO_CHAR(SYSDATE,'YYYY_MM_DD HH24:MI:SS')||']********************');
UTL_FILE.PUT_LINE(V_FILE,PMESSAGE);
UTL_FILE.PUT_LINE(V_FILE,pTYPE||' '||pNAME||'的DDL语句为:');
for x in (SELECT DBMS_METADATA.GET_DDL(pTYPE,pNAME,V_OWNER) A FROM DUAL) LOOP
UTL_FILE.PUT_LINE(V_FILE,X.A);
END LOOP;
UTL_FILE.PUT_LINE(V_FILE,'*************ENDTIME['||TO_CHAR(SYSDATE,'YYYY_MM_DD HH24:MI:SS')||']********************');
UTL_FILE.FCLOSE(V_FILE);
EXCEPTION
WHEN OTHERS THEN
UTL_FILE.NEW_LINE(V_FILE);
UTL_FILE.PUT_LINE(V_FILE,FN_GETNAME||'出现错误：'||SQLCODE||'----'||SQLERRM);
UTL_FILE.FCLOSE(V_FILE);
END PROC_OUTPUTDDL;
最后创建系统触发器来捕捉DROP事件,注意操作需要SYSDBA权限
CREATE OR REPLACE TRIGGER DROP_DDL
AFTER DDL ON database
--将DROP掉的对象创建语句保存到文件
BEGIN
IF UPPER(ORA_SYSEVENT)='DROP' THEN
PROC_OUTPUTDDL(ora_dict_obj_type,ora_dict_obj_name,'终端'||userenv('terminal')||'['||ORA_CLIENT_IP_ADDRESS||']在数据库**'||ora_database_name||'** 上删除了对象'||ora_dict_obj_name,ORA_LOGIN_USER);
end if;
end;
作者：edcvf3 发表于2013-1-4 10:53:47 原文链接
阅读：22 评论：0 查看评论
最新技术文章: