标签云
asm恢复 bbed bootstrap$ dul kcbzib_kcrsds_1 kccpb_sanity_check_2 kcratr_nab_less_than_odr MySQL恢复 obet ORA-00312 ORA-00704 ORA-00742 ORA-01110 ORA-01200 ORA-01555 ORA-01578 ORA-01595 ORA-600 2662 ORA-600 2663 ORA-600 3020 ORA-600 4000 ORA-600 4137 ORA-600 4193 ORA-600 4194 ORA-600 16703 ORA-600 kcbzib_kcrsds_1 ORA-600 KCLCHKBLK_4 ORA-600 kcratr_nab_less_than_odr ORA-15042 ORA-15196 ORACLE 12C oracle dul ORACLE PATCH Oracle Recovery Tools oracle加密恢复 oracle勒索 oracle勒索恢复 oracle异常恢复 ORACLE恢复 Oracle 恢复 ORACLE数据库恢复 oracle碎片 OSD-04016 YOUR FILES ARE ENCRYPTED 比特币加密文章分类
- MongoDB (1)
- MongoDB恢复 (1)
- Others (2)
- 中间件 (2)
- WebLogic (2)
- 操作系统 (112)
- 数据库 (1,885)
- DB2 (22)
- MySQL (82)
- Oracle (1,710)
- Data Guard (53)
- EXADATA (8)
- GoldenGate (24)
- ORA-xxxxx (168)
- ORACLE 12C (72)
- ORACLE 18C (6)
- ORACLE 19C (15)
- ORACLE 21C (3)
- Oracle 23ai (8)
- Oracle ASM (73)
- Oracle Bug (8)
- Oracle RAC (56)
- Oracle 安全 (6)
- Oracle 开发 (28)
- Oracle 监听 (29)
- Oracle备份恢复 (663)
- Oracle安装升级 (108)
- Oracle性能优化 (62)
- 专题索引 (5)
- 勒索恢复 (91)
- PostgreSQL (37)
- pdu工具 (7)
- PostgreSQL恢复 (13)
- SQL Server (34)
- SQL Server恢复 (14)
- TimesTen (7)
- 达梦数据库 (5)
- 达梦恢复 (3)
- 生活娱乐 (2)
- 至理名言 (11)
- 虚拟化 (2)
- VMware (2)
- 软件开发 (51)
- Asp.Net (9)
- JavaScript (12)
- PHP (2)
- 小工具 (34)
- MongoDB (1)
-
最近发表
- 恢复案例分享:win丢失数据文件恢复
- 记录19c ASM磁盘掉线触发ORA-59048故障处理
- aix平台Oracle数据库由于断电无法启动故障处理
- 断电引起ORA-600 3005故障处理
- 记录一次云存储资源到期之后引起的数据库恢复
- Firebird 损坏数据库数据抢救工具—firebirdext
- Oracle 19c 202610补丁(RUs+OJVM)-19.33
- 由于con$.con#值达到极限,触发数据库增加约束报ORA-600 12807错误
- MongoDB 离线提取工具—mongoext
- .bres勒索病毒加密数据库dmp文件可恢复
- ORA-600 krsm_mrp_rebuild_lf_list.lno_mismatch
- 记录一次0丢失的ORA-00354: 损坏重做日志块标头故障恢复
- 不当数据库恢复操作导致一个月数据丢失
- obet快速修复oracle 位图损坏块
- 不太常见的10.2.0.1的oracle redo损坏恢复
- obet forcecopy功能抢救硬件故障中的数据文件
- kcratr_nab_less_than_odr和system坏块故障处理
- 通过obet 恢复system坏块,打开数据库
- obet dbv功能完整说明
- 分享一例运行在aix上的sap系统数据库恢复过程
月归档:十二月 2011
修改oracle数据库字符集
现在有个需求,需要数据库字符集从ZHS16GBK修改为AL32UTF8,因为他们没有子集的关系,所以在转换前,需要先检测库中的数据内容是否全库可以转换为AL32UTF8字符集,检测使用oracle提供的csscan工具实现
一、csscan使用
1.安装csscan相关数据字典
SQL> @?/rdbms/admin/csminst.sql
2.csscan使用说明
[oracle@node1 ~]$ csscan help=y
Character Set Scanner v2.2 : Release 11.2.0.3.0 - Production on Wed Dec 21 12:30:42 2011
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
You can let Scanner prompt you for parameters by entering the CSSCAN
command followed by your username/password:
Example: CSSCAN \"SYSTEM/MANAGER AS SYSDBA\"
Or, you can control how Scanner runs by entering the CSSCAN command
followed by various parameters. To specify parameters, you use keywords:
Example:
CSSCAN \"SYSTEM/MANAGER AS SYSDBA\" FULL=y TOCHAR=utf8 ARRAY=1024000 PROCESS=3
Keyword Default Prompt Description
---------- ------- ------ -------------------------------------------------
USERID yes username/password
FULL N yes scan entire database
USER yes owner of tables to be scanned
TABLE yes list of tables to scan
COLUMN yes list of columns to scan
EXCLUDE list of tables to exclude from scan
TOCHAR yes new database character set name
FROMCHAR current database character set name
TONCHAR new national character set name
FROMNCHAR current national character set name
ARRAY 1024000 yes size of array fetch buffer
PROCESS 1 yes number of concurrent scan process
MAXBLOCKS split table if block size exceed MAXBLOCKS
CAPTURE N capture convertible data
SUPPRESS maximum number of exceptions logged for each table
FEEDBACK report progress every N rows
BOUNDARIES list of column size boundaries for summary report
LASTRPT N generate report of the last database scan
LOG scan base file name of report files
PARFILE parameter file name
PRESERVE N preserve existing scan results
LCSD N no enable language and character set detection
LCSDDATA LOSSY no define the scope of the detection
HELP N show help screen (this screen)
QUERY N select clause to scan subset of tables or columns
---------- ------- ------ -------------------------------------------------
Scanner terminated successfully.
3.使用csscan检测当前数据库
[oracle@node1 ~]$ csscan userid="'"sys/xifenfei as sysdba"'" full=y \ fromchar=ZHS16GBK tochar=AL32UTF8 log=/tmp/check.log capture=y array=1000000 process=4 Character Set Scanner v2.2 : Release 11.2.0.3.0 - Production on Wed Dec 21 12:36:37 2011 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, Oracle Label Security, OLAP, Data Mining, Oracle Database Vault and Real Application Testing options Enumerating tables to scan... . process 1 scanning SYS.SOURCE$[AAAADgAABAAAAXgAAA] . process 2 scanning XFF.T1[AAATjjAAEAAAC7AAAA] . process 3 scanning TEST.T_XFF[AAAT+6AAEAAAAIQAAA] . process 2 scanning SYS.METHOD$[AAAAHvAABAAAA0QAAA] . process 4 scanning SYS.ATTRIBUTE$[AAAAHvAABAAAA0QAAA] ………………………………………… . process 4 scanning CTXSYS.DR$INDEX_CDI_COLUMN . process 2 scanning CTXSYS.DR$SDATA_UPDATE . process 1 scanning EXFSYS.RLM$RULESETSTCODE . process 3 scanning EXFSYS.RLM$RULESET Creating Database Scan Summary Report... Creating Individual Exception Report... Scanner terminated successfully.
4.查看csscan检查日志
[oracle@node1 ~]$ ll /tmp/check.log.*
-rw-r--r-- 1 oracle oinstall 14526 12-21 12:37 /tmp/check.log.err
-rw-r--r-- 1 oracle oinstall 100235 12-21 12:37 /tmp/check.log.out
-rw-r--r-- 1 oracle oinstall 8265 12-21 12:37 /tmp/check.log.txt
--err是错误日志
--out是执行过程
--txt是执行结果汇总
[oracle@node1 tmp]$ cat /tmp/check.log.txt
Database Scan Summary Report
Time Started : 2011-12-21 12:36:37
Time Completed: 2011-12-21 12:37:13
Process ID Time Started Time Completed
---------- -------------------- --------------------
1 2011-12-21 12:36:43 2011-12-21 12:37:12
2 2011-12-21 12:36:43 2011-12-21 12:37:12
3 2011-12-21 12:36:43 2011-12-21 12:37:12
4 2011-12-21 12:36:43 2011-12-21 12:37:12
---------- -------------------- --------------------
[Database Size]
Tablespace Used Free Total Expansion
------------------------- --------------- --------------- --------------- ---------------
SYSTEM 723.63M 6.38M 730.00M .00K
SYSAUX 686.56M 103.44M 790.00M .00K
UNDOTBS1 18.31M 126.69M 145.00M .00K
TEMP .00K .00K .00K .00K
USERS 138.69M 22.56M 161.25M .00K
EXAMPLE 310.13M 35.50M 345.63M .00K
------------------------- --------------- --------------- --------------- ---------------
Total 1,877.31M 294.56M 2,171.88M .00K
[Database Scan Parameters]
Parameter Value
------------------------------ ------------------------------------------------
CSSCAN Version v2.1
Instance Name ora11g
Database Version 11.2.0.3.0
Scan type Full database
Scan CHAR data? YES
Database character set ZHS16GBK
FROMCHAR ZHS16GBK
TOCHAR AL32UTF8
Scan NCHAR data? NO
Array fetch buffer size 1000000
Number of processes 4
Capture convertible data? YES
------------------------------ ------------------------------------------------
[Scan Summary]
All character type data in the data dictionary are convertible to the new character set
All character type application data remain the same in the new character set
[Data Dictionary Conversion Summary]
Data Dictionary Tables:
Datatype Changeless Convertible Truncation Lossy
--------------------- ---------------- ---------------- ---------------- ----------------
VARCHAR2 6,061,403 152 0 0
CHAR 4,988 0 0 0
LONG 252,530 0 0 0
VARRAY 50,812 0 0 0
--------------------- ---------------- ---------------- ---------------- ----------------
Total 6,369,733 152 0 0
Total in percentage 99.998% 0.002% 0.000% 0.000%
The data dictionary can not be safely migrated using the CSALTER script
XML CSX Dictionary Tables:
Datatype Changeless Convertible Truncation Lossy
--------------------- ---------------- ---------------- ---------------- ----------------
VARCHAR2 711 0 0 0
CHAR 0 0 0 0
LONG 0 0 0 0
VARRAY 0 0 0 0
--------------------- ---------------- ---------------- ---------------- ----------------
Total 711 0 0 0
Total in percentage 100.000% 0.000% 0.000% 0.000%
[Application Data Conversion Summary]
Datatype Changeless Convertible Truncation Lossy
--------------------- ---------------- ---------------- ---------------- ----------------
VARCHAR2 8,142,707 0 0 0
CHAR 63,085 0 0 0
LONG 0 0 0 0
VARRAY 583 0 0 0
--------------------- ---------------- ---------------- ---------------- ----------------
Total 8,206,375 0 0 0
Total in percentage 100.000% 0.000% 0.000% 0.000%
[Distribution of Convertible, Truncated and Lossy Data by Table]
Data Dictionary Tables:
USER.TABLE Convertible Truncation Lossy
-------------------------------------------------- ---------------- ---------------- ----------------
CTXSYS.DR$INDEX_VALUE 76 0 0
CTXSYS.DR$STOPWORD 76 0 0
-------------------------------------------------- ---------------- ---------------- ----------------
XML CSX Dictionary Tables:
USER.TABLE Convertible Truncation Lossy
-------------------------------------------------- ---------------- ---------------- ----------------
-------------------------------------------------- ---------------- ---------------- ----------------
Application Data:
USER.TABLE Convertible Truncation Lossy
-------------------------------------------------- ---------------- ---------------- ----------------
-------------------------------------------------- ---------------- ---------------- ----------------
[Distribution of Convertible, Truncated and Lossy Data by Column]
Data Dictionary Tables:
USER.TABLE|COLUMN Convertible Truncation Lossy
-------------------------------------------------- ---------------- ---------------- ----------------
CTXSYS.DR$INDEX_VALUE|IXV_VALUE 76 0 0
CTXSYS.DR$STOPWORD|SPW_WORD 76 0 0
-------------------------------------------------- ---------------- ---------------- ----------------
XML CSX Dictionary Tables:
USER.TABLE|COLUMN Convertible Truncation Lossy
-------------------------------------------------- ---------------- ---------------- ----------------
-------------------------------------------------- ---------------- ---------------- ----------------
Application Data:
USER.TABLE|COLUMN Convertible Truncation Lossy
-------------------------------------------------- ---------------- ---------------- ----------------
-------------------------------------------------- ---------------- ---------------- ----------------
[Indexes to be Rebuilt]
USER.INDEX on USER.TABLE(COLUMN)
-----------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------
--因为检测结果没有Truncation(截断数据)/Lossy(丢失数据)都不存在记录,所以不用查看错误日志
--如果发现不为0,需要检查err日志,然后先处理丢这些记录,然后再转换
二、修改数据库字符集
[oracle@node1 tmp]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.3.0 Production on Wed Dec 21 12:59:55 2011 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, Oracle Label Security, OLAP, Data Mining, Oracle Database Vault and Real Application Testing options SQL> select value$ from props$ where name='NLS_CHARACTERSET'; VALUE$ ------------------------------------------------------ ZHS16GBK SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount ORACLE instance started. Total System Global Area 2137886720 bytes Fixed Size 2230072 bytes Variable Size 1493174472 bytes Database Buffers 637534208 bytes Redo Buffers 4947968 bytes Database mounted. SQL> alter system enable restricted session; System altered. SQL> alter system set job_queue_processes=0 scope=memory; System altered. SQL> alter system set aq_tm_processes=0 scope=memory; System altered. SQL> alter database open; Database altered. SQL> alter database character set internal_use AL32UTF8; Database altered. SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup; ORACLE instance started. Total System Global Area 2137886720 bytes Fixed Size 2230072 bytes Variable Size 1493174472 bytes Database Buffers 637534208 bytes Redo Buffers 4947968 bytes Database mounted. Database opened. SQL> select value$ from props$ where name='NLS_CHARACTERSET'; VALUE$ -------------------------------------------------------- AL32UTF8
发表在 Oracle
评论关闭
DBMS_SCHEDULER常规操作
1.create job
BEGIN DBMS_SCHEDULER.CREATE_JOB( job_name =>'xifenfei_job', job_type =>'STORED_PROCEDURE', job_action =>'p_schedule', repeat_interval =>'FREQ=DAILY; BYHOUR=18,20,22', enabled => true, comments => 'XIFENFEI'); END; --每天18/20/22点执行p_schedule过程
2.disable job
BEGIN
DBMS_SCHEDULER.DISABLE(name => 'xifenfei_job');
END;
3.enable job
BEGIN
DBMS_SCHEDULER.ENABLE(name => 'xifenfei_job');
END;
4.select job
select * from USER_SCHEDULER_JOBS;
5.query logs
SELECT * FROM USER_SCHEDULER_JOB_RUN_DETAILS WHERE job_name='XIFENFEI_JOB';
6.delete logs
--ALL_SCHEDULER_JOB_RUN_DETAILS视图
CREATE OR REPLACE VIEW ALL_SCHEDULER_JOB_RUN_DETAILS
(log_id, log_date, owner, job_name, job_subname, status, error#,
req_start_date, actual_start_date, run_duration, instance_id,
session_id, slave_pid, cpu_used, credential_owner, credential_name,
destination_owner, destination, additional_info)
AS
(SELECT
j.LOG_ID, j.LOG_DATE, e.OWNER,
DECODE(instr(e.NAME,'"'),0, e.NAME,substr(e.NAME,1,instr(e.NAME,'"')-1)),
DECODE(instr(e.NAME,'"'),0,NULL,substr(e.NAME,instr(e.NAME,'"')+1)),
e.STATUS, j.ERROR#, j.REQ_START_DATE, j.START_DATE, j.RUN_DURATION,
j.INSTANCE_ID, j.SESSION_ID, j.SLAVE_PID, j.CPU_USED,
decode(e.credential, NULL, NULL,
substr(e.credential, 1, instr(e.credential, '"')-1)),
decode(e.credential, NULL, NULL,
substr(e.credential, instr(e.credential, '"')+1,
length(e.credential) - instr(e.credential, '"'))),
decode(bitand(e.flags, 1), 0, NULL,
substr(e.destination, 1, instr(e.destination, '"')-1)),
decode(bitand(e.flags, 1), 0, e.destination,
substr(e.destination, instr(e.destination, '"')+1,
length(e.destination) - instr(e.destination, '"'))),
j.ADDITIONAL_INFO
FROM scheduler$_job_run_details j, scheduler$_event_log e
WHERE j.log_id = e.log_id
AND e.type# = 66 and e.dbid is null
AND ( e.owner = SYS_CONTEXT('USERENV','CURRENT_SCHEMA')
or /* user has object privileges */
( select jo.obj# from obj$ jo, user$ ju where
DECODE(instr(e.NAME,'"'),0, e.NAME,substr(e.NAME,1,instr(e.NAME,'"')-1))
= jo.name and e.owner = ju.name and jo.owner# = ju.user#
and jo.subname is null and jo.type# = 66
) in
( select oa.obj#
from sys.objauth$ oa
where grantee# in ( select kzsrorol from x$kzsro )
)
or /* user has system privileges */
(exists ( select null from v$enabledprivs
where priv_number = -265 /* CREATE ANY JOB */
)
and e.owner!='SYS')
)
);
--从这个视图中可以发现,日志有存在SCHEDULER$_JOB_RUN_DETAILS和
--SCHEDULER$_EVENT_LOG两张表中,所以要删除日志,就需要处理这两张表
######################删除日志操作#################
--删除SYS.SCHEDULER$_JOB_RUN_DETAILS中数据
DELETE FROM SYS.SCHEDULER$_JOB_RUN_DETAILS A
WHERE EXISTS (SELECT 1
FROM SYS.SCHEDULER$_EVENT_LOG B
WHERE B.NAME = 'XIFENFEI_JOB'
AND A.LOG_ID = B.LOG_ID);
--删除SYS.SCHEDULER$_EVENT_LOG中数据
DELETE FROM SYS.SCHEDULER$_EVENT_LOG B
WHERE B.NAME = 'XIFENFEI_JOB';
--提交
COMMIT;
#####################################################
7.delete jobs
BEGIN
DBMS_SCHEDULER.DROP_JOB(job_name => 'xifenfei_job');
END;
参考:http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_sched.htm
绕过win中服务开启oracle数据库
在win系统中,如果oracle数据库的服务没有启动,使用sqlplus登录,会报ORA-12560错误,数据库无法启动
C:\Users\XIFENFEI>sqlplus / as sysdba SQL*Plus: Release 11.2.0.1.0 Production on 星期一 12月 19 20:33:27 2011 Copyright (c) 1982, 2010, Oracle. All rights reserved. ERROR: ORA-12560: TNS: 协议适配器错误 请输入用户名:
绕过服务启动数据库具体操作步骤如下:
1.当前oracle数据库服务OracleServiceXFF处于关闭状态

2.右键OracleServiceXFF服务属性
从这里我们可以看到”可执行文件的路径”,然后我们直接在dos(管理员权限,或者说有权限运行oracle的用户下),运行该命令

3.在dos中运行命令
这里提示”Press CTRL-C to exit server:”在你不想oracle被abort之前,请不要执行CTRL-C或者直接关闭该窗口

4.开启oracle数据库

至此在win服务器中绕过服务,直接启动oracle操作完成,这个多半是在因为不能正常开启服务,通过这种方式来排错。在生产环境中,请勿模仿,如出现问题,后果自负(数据库被abort)
发表在 Oracle
评论关闭

加我微信(17813235971)
加我QQ(107644445)

