oracle更新timesample类型,***Oracle恢复spfile等小问题处理

最新推荐文章于 2023-11-28 16:00:13 发布

暗香无名

最新推荐文章于 2023-11-28 16:00:13 发布

阅读量335

点赞数

文章标签： oracle更新timesample类型

没有任何文件的时候，只有备份的时候，如何把spfile恢复出来。比较简单，但就是很多人不会。

set ORACLE_SID=WWW

rman target /

startup force nomount;

SQL> exit

已断开连接

D:\work\scripts>rman target /

恢复管理器: Release 12.1.0.2.0 - Production on 星期三 10月 26 11:55:21

2016

rights reserved.

已连接到目标数据库 (未启动)

RMAN> startup force nomount

启动失败: ORA-01078: failure in processing system parameters

LRM-00109: ????????????????

'F:\APP\ORACLE12C\PRODUCT\12.1.0\DBHOME_1\DATABASE\INITTOTO.ORA'

在没有参数文件的情况下启动 Oracle 实例以检索 spfile

Oracle 实例已启动

系统全局区域总计 1073741824 字节

Fixed Size 3053880 字节

Variable Size 293603016 字节

Database Buffers 771751936 字节

Redo Buffers 5332992 字节

RMAN>

恢复命令如下：

restore spfile to

'D:\oracle\product\10.2.0\db_2\database\SPFILEWWW.ORA' from

'D:\oracle\WWWdbbk\20130718\WWW_SPFILE_20130718_4224_1.BAK';

启动 restore 于 19-7月 -13 使用目标数据库控制文件替代恢复目录分配的通道: ORA_DISK_1

通道 ORA_DISK_1: sid=36 devtype=DISK 通道

ORA_DISK_1: 已找到的自动备份: D:\oracle\jmcdbbk\20130718\xxxxxxxxSPFILE_201

30718_4224_1.BAK 通道 ORA_DISK_1: 从自动备份复原 SPFILE 已完成完成 restore 于

19-7月 -13 END;

注意 windows记得添加并启动服务，dim一下：

C:\Documents and

Settings\Administrator>RMAN target /

恢复管理器: Release 10.2.0.1.0 - Production on 星期五 7月 19

15:40:57 2013

rights reserved.

RMAN-00571:

====================================

RMAN-00569: =============== ERROR MESSAGE STACK

FOLLOWS

RMAN-00571:

=====================================

RMAN-00554: 内部恢复管理器程序包初始化失败

RMAN-04005: 目标数据库中存在错误:

Oracle SQL ： with as 语法

WITH

subquery_name1

( the aggregation SQL statement1 ) ,

subquery_name2

( the aggregation SQL statement2 )

.....

SELECT

(query naming subquery_name);

Example :

create table stores

(quantity number ,

money_amount number ,

store_brand varchar(20) ,

store_id number

)；

SQL> set linesize 160

SQL> col AVG_money_per_store format

99999,999.99

SQL> col AVG_PRICE format 99999,999.99

SQL>

SQL> WITH

2 sum_sales AS

3 (

4 select

5 sum(quantity) all_sales ,sum(money_amount) all_moneys ,

store_brand from stores group by store_brand ) ,

6 number_stores AS

7 (

8 select

9 count(* ) num_stores ,store_brand from stores group by

store_brand )

16 SELECT

17 DISTINCT stores.store_brand ,num_stores, all_sales

,all_moneys ,all_moneys / num_stores AVG_money_per_store,

18 all_moneys / all_sales AVG_PRICE

19 FROM stores, sum_sales, number_stores

20 where

21 stores.store_brand = sum_sales.store_brand

22 and stores.store_brand = number_stores.store_brand

23 ;

STORE_BRAND NUM_STORES ALL_SALES ALL_MONEYS AVG_MONEY_PER_STORE

AVG_PRICE

---------------------------------------- ---------- ----------

---------- --------------------

DELL

1100

4800

2,400.00

4.36

GUCCI

5750

1,916.67

958.33

KFC

250

250.00

125.00

SQL>

Note the use of the Oracle undocumented “materialize” hint in

the “WITH clause”. The Oracle materialize hint is used to ensure

that the Oracle cost-based optimizer materializes the temporary

tables that are created inside the “WITH” clause. This is not

necessary in Oracle10g, but it helps ensure that the tables are

only created one time.

It should be noted that the “WITH clause” does not yet

fully-functional within Oracle SQL and it does not yet support the

use of “WITH clause” replacement for “CONNECT BY” when performing

recursive queries.

To see how the “WITH clause” is used in ANSI SQL-99 syntax, here

is an excerpt from Jonathan Gennick’s great work “Understanding the

WITH Clause” showing the use of the SQL-99 “WITH clause” to

traverse a recursive bill-of-materials hierarchy

The SQL-99 “WITH clause” is very confusing at first because the

SQL statement does not begin with the word SELECT. Instead, we use

the “WITH clause” to start our SQL query, defining the

aggregations, which can then be named in the main query as if they

were “real” tables:

timestamp类型的列直接兼容date类型，无需转化

select * from t where timest1 <=

sysdate - 10 /144 and timest1 >= sysdate - 11 /144

;

已选择 64 行。

已用时间: 00: 00: 00.14

执行计划

----------------------------------------------------------

Plan hash value: 1322348184

---------------------------------------------------------------------------

Operation

Name

Rows

Bytes

Cost (%CPU)

Time

---------------------------------------------------------------------------

SELECT STATEMENT

192

4800

729 (3)

00:00:01

* 1

FILTER

* 2

TABLE ACCESS FULL

192

4800

729 (3)

00:00:01

---------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 -

filter(SYSDATE@!-.0763888888888888888888888888888888888889<=SYSDA

TE@!-.0694444444444444444444444444444444444444)

2 -

filter("TIMEST1">=SYSDATE@!-.076388888888888888888888888888888888

8889 AND

"TIMEST1"<=SYSDATE@!-.0694444444444444444444444444444444444444)

统计信息

----------------------------------------------------------

0 recursive calls

0 db block gets

1897 consistent gets

0 physical reads

0 redo size

1918 bytes sent via SQL*Net to client

596 bytes received via SQL*Net from client

6 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

64 rows processed

SQL> set autot trace exp stat

SQL> select * from t where

timest1 <= sysdate - 10 /144 and timest1

>= sysdate - 11 /144 ;

已选择 64 行。

已用时间: 00: 00: 00.01

执行计划

----------------------------------------------------------

Plan hash value: 3371165309

-------------------------------------------------------------------------------------------------

Operation

Name

Rows

Bytes

Cost (%CPU)

Time

-------------------------------------------------------------------------------------------------

SELECT STATEMENT

192

4800

199 (0)

00:00:01

* 1

FILTER

TABLE ACCESS BY INDEX ROWID BATCHED

192

4800

199 (0)

00:00:01

* 3

INDEX RANGE SCAN

T_INDEX1

196

3 (0)

00:00:01

-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 -

filter(SYSDATE@!-.0763888888888888888888888888888888888889<=SYSDATE@!-.06944444444

44444444444444444444444444444)

3 -

access("TIMEST1">=SYSDATE@!-.0763888888888888888888888888888888888889

AND

"TIMEST1"<=SYSDATE@!-.0694444444444444444444444444444444444444)

统计信息

----------------------------------------------------------

0 recursive calls

0 db block gets

72 consistent gets

0 physical reads

0 redo size

1918 bytes sent via SQL*Net to client

596 bytes received via SQL*Net from client

6 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

64 rows processed

ORACLE

12c 在windows 7 上安装报错

[INS-30131] 执行安装程序验证所需的初始设置失败。

原因 - 无法访问临时位置。

操作 - 请确保当前用户具有访问临时位置所需的权限。

附加信息:

- 所有节点上的框架设置检查都失败

- 原因: 问题的原因不可用

- 操作: 用户操作不可用失败节点概要 pc-20130618muam

- 无法从节点 "pc-20130618muam" 检索 exectask 的版本

- 原因: 问题的原因不可用

- 操作: 用户操作不可用

解决办法：

在cmd窗口，用admin用户执行

C:\Users\Administrator>

net share C$=C:\

C$ 共享成功。

windows 上安装Oracle

12c另外一个不同：

必须新建一个新的用户，且设置密码，该用户没有admin权限，才可以安装。

Lob字段的create materialize

viewora-22992

SQL> drop MATERIALIZED VIEW

pub_alertregistry;

Materialized view dropped

SQL> CREATE MATERIALIZED VIEW pub_alertregistry

build deferred REFRESH FAST WITH PRIMARY KEY ON DEMAND AS SELECT *

FROM pub_alertregistry@DBLINK

;

CREATE MATERIALIZED VIEW pub_alertregistry build deferred

REFRESH FAST WITH PRIMARY KEY ON DEMAND AS SELECT * FROM

pub_alertregistry@DBLINK

ORA-22992: cannot use LOB locators selected from remote

tables

SQL>

SQL> CREATE MATERIALIZED VIEW

pub_alertregistry

REFRESH FAST WITH PRIMARY KEY ON DEMAND AS

SELECT * FROM pub_alertregistry@DBLINK;

ORA-06512: at "SYS.KUPV$FT_INT",

line

oracle@ZTGXPADDB1:/gg/ogg/dirrpt$ expdp

XXXXX/XXXXX@22.188.131.27:1521/oraXPAD DIRECTORY=DATA_PUMP_DIR

DUMPFILE=XXXXX_20160125.dmp LOGFILE=XXXXX_20160125.log

FLASHBACK_SCN=12242466617347

Export: Release 10.2.0.5.0 - 64bit Production on Saturday, 02

January, 2021 15:42:47

Connected to: Oracle Database 10g Enterprise Edition Release

10.2.0.5.0 - 64bit Production

With the Partitioning, Real Application Clusters, Data Mining

and Real Application Testing options

ORA-31626: job does not exist

ORA-31638: cannot attach to job SYS_EXPORT_SCHEMA_01 for user

XXXXX

ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95

ORA-06512: at "SYS.KUPV$FT_INT",

line 389

ORA-39077: unable to subscribe agent KUPC$A_2_20210102154238 to

queue "KUPC$C_2_20210102154237"

ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95

ORA-06512: at "SYS.KUPC$QUE_INT", line 249

ORA-06502: PL/SQL: numeric or value error: character string

buffer too small

SELECT *

FROM DBA_SEQUENCES D

WHERE D.sequence_name IN

('AQ$_KUPC$DATAPUMP_QUETAB_N',

'AQ$_KUPC$DATAPUMP_QUETAB_1_N');

SQL> SELECT AQ$_KUPC$DATAPUMP_QUETAB_N.NEXTVAL

FROM DUAL;

NEXTVAL

----------

1194988

SQL> DROP SEQUENCE

AQ$_KUPC$DATAPUMP_QUETAB_N;

Sequence dropped.

SQL> CREATE SEQUENCE AQ$_KUPC$DATAPUMP_QUETAB_N

MINVALUE 1 MAXVALUE 999999 START WITH 1 INCREMENT BY 1 CACHE 20

CYCLE;

Sequence created.

SQL> SELECT AQ$_KUPC$DATAPUMP_QUETAB_N.NEXTVAL

FROM DUAL;

然后就是

重新expdp

history记录里面找events对应的SQL

select count(sql_id),sql_id,sql_text from (

select session_id,

session_serial#,

to_char(sq.sql_text) as sql_text,

s.sql_id,

to_char(s.sample_time, 'yyyy-mm-dd hh24:mi:ss') as

sample_time

from dba_hist_active_sess_history s, dba_hist_sqltext sq

where s.sql_id = sq.sql_id

and event = 'enq: TX - row lock contention'

and to_char(s.sample_time, 'yyyy-mm-dd hh24:mi:ss')

'2016-02-02 16:46:00'

and to_char(s.sample_time, 'yyyy-mm-dd hh24:mi:ss')

'2016-02-02 16:49:00'

-- and s.session_id in ('100','200')

-- and s.sql_id = '084ysjn4s0dt5'

order by s.sql_id, s.session_id, s.session_serial#

) group by sql_id, sql_text order by 1

;

COUNT(SQL_ID) SQL_ID SQL_TEXT

------------- -------------

--------------------------------------------------------------------------------

17 gc79zkrdac4q2 update kh_khdd set pnr_lr=:1 where ddbh=:2

18 9y301qyhuvqg5 SELECT * FROM KH_KHDD WHERE DDBH = :B1 FOR

UPDATE

18 885tqxp134xdp SELECT * FROM KH_KHDD WHERE DDBH=:B1 AND

PNR_ZT<>'XX' FOR UPDATE

36 97w53q308y8ms UPDATE KH_KHDD SET DS_COMPID=:B12

,DS_DEPTID=:B11 ,DS_DPYID=:B6 ,DS_DATETIME=:B1

36 bvhd3dc8q7mjm update kh_khdd set

xguserid=:1,czfrom='出票控制台待出票打印' ,ddzt='5',dp_pid=:2,version=n

179 dawn77zmam101 SELECT DDBH FROM KH_KHDD WHERE DDBH=:B1 FOR

UPDATE

244 084ysjn4s0dt5 update kh_khdd set

ddzt='5',xguserid=:1,czfrom=:2,dp_pid=:3 where ddbh = :4

select ... for

update

Select…For

Update语句所加的锁与update语句所加的锁相同：一个行级别的EXCLUSIVE锁，TX锁。阻止其他会话update或select

... For Update.

ORA-02017:

要求整数值

想把会话改为2g个字节

SQL> alter session set

sort_area_size=2147483648;

alter session set sort_area_size=2147483648

第 1 行出现错误:

ORA-02017: 要求整数值

分析：

4字节的常规int类型范围： -2147483648－－2147483647

想把会话改为2g个字节，2g为2147483648 正好比这个多了1，hahaha.....

减少1就好了

SQL> alter session set

sort_area_size=2147483647;

会话已更改。

已用时间: 00: 00: 00.00

大到600G的单个索引如何建立

alter session set sort_area_size=2147483647;

drop index IX_1_P4_NOLOG;

drop index IX_1_P4_NOLOG_c1;

CREATE INDEX IX_1_P4_NOLOG_c1 ON TOAD1( OBJECT_NAME )

parallel 4 NOLOGGING compress 1 TABLESPACE ANDZEN1;

CREATE INDEX IX_1_P4_NOLOG ON TOAD1( OBJECT_NAME ) parallel

4 NOLOGGING TABLESPACE ANDZEN1;

什么参数都不加测试创建速度

SQL> create index id1_ind on toad1(id1)

tablespace t_toad1;

Index created.

Elapsed: 00:16:23.51

加上nologing参数

SQL> create index id1_ind on toad1(id1)tablespace

t_toad1 nologging;

Index created.

Elapsed: 00:16:40.20

再加上parallel参数

SQL> create index id1_ind on toad1(id1)tablespace

t_toad1 nologging parallel 4;

Index created.

Elapsed: 00:09:03.74

调整sort_area_size

SQL> alter session

setworkarea_size_policy=manual;

SQL> alter session

setsort_area_size=2000000000;

SQL> create index id1_ind on toad1(id1)tablespace

t_toad1 nologging parallel 4;

Index created.

Elapsed: 00:08:12.79

nologging效果不明显

parallel效果明显一点

sort_area_size有点效果

compress

索引的时候，应该选择组合索引，且字段的排列顺序有要求，选择率低的必须排在前面，如果有三个字段,前2个字段选择率(分辨率)低则可以用2：

CREATE INDEX IX_1_P4_NOLOG_c1 ON TOAD1( OBJECT_NAME,c1,c2 )

parallel 4 NOLOGGING compress2 TABLESPACE ANDZEN1

更多reference:

http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_5012.htm#SQLRF01209

SQL> conn NOPRIV/nopriv

访问 PRODUCT_USER_PROFILE 时出错

警告: 未加载产品用户概要文件信息！

您需要将 PUPBLD.SQL 作为 SYSTEM

运行

已连接。

这个错误信息已经很清楚了，除了没有说明pupbld.sql这个文件的路径外，已经给出了解决问题的其他所有信息。

其实这个文件的位置很好找，既然是和sqlplus相关的脚本，那么这个文件多半就存在于$ORACLE_HOME/sqlplus/admin目录下：

SQL> conn system

请输入口令:

已连接。

SQL> @?/sqlplus/admin/pupbld.sql

DROP SYNONYM PRODUCT_USER_PROFILE

...

同义词已创建。

会话已更改。

SQL> conn andzen/andzen

已连接。

SQL>

UTC时间，带时区时间，本地时间

select SYS_EXTRACT_UTC( SYSTIMESTAMP ) , SYSTIMESTAMP ,sysdate

from dual;

11gR2安装错误

error- PRVF-7532: Package "libaio-0.3.105 (i386)" is

missing on node "Localhost"

Check: Package existence for "libaio-0.3.105

(i386)"

Node Name Available Required Comment

------------ ------------------------

------------------------ ----------

rac2 missing libaio-0.3.105 (i386) failed

rac1 missing libaio-0.3.105 (i386)

failed

cd /etc/yum.repos.d

rm /etc/yum.repos.d/*

yum clean all

su Enter Password

# yum install

oracle-rdbms-server-11gR2-preinstall

注意在新版本的操作系统里面，i386的名字已经被i686替代了，所以请查看i686的包是否存在，i686存在的话就不要管上面的提示信息里面的i386信息了。

Oracle Universal Installer prints beginning with 11g R2

also warnings for optional requirements. Identify the errors and

decide whether they can be ignored or not. The next example shows

erros which can usually be ignored since OUI does not recognise

newer packages.

20101205_185530: Info: Grepping for PRVF- pattern in

/opt/oracle/oraInventory/logs/installActions2010-12-05_06-40-15PM.log

INFO: Error Message:PRVF-7532 : Package "libaio-0.3.105 (i386)"

is missing on node ""

INFO: Error Message:PRVF-7532 : Package

"compat-libstdc++-33-3.2.3 (i386)" is missing on node ""

INFO: Error Message:PRVF-7532 : Package "libaio-devel-0.3.105

(i386)" is missing on node ""

INFO: Error Message:PRVF-7532 : Package "libgcc-3.4.6 (i386)" is

missing on node ""

INFO: Error Message:PRVF-7532 : Package "libstdc++-3.4.6 (i386)"

is missing on node ""

INFO: Error Message:PRVF-7532 : Package "unixODBC-2.2.11 (i386)"

is missing on node ""

INFO: Error Message:PRVF-7532 : Package "unixODBC-devel-2.2.11

(i386)" is missing on node ""

INFO: Error Message:PRVF-7532 : Package "pdksh-5.2.14" is

missing on node "

在64位的Linux安装64位的oracle的时候，有时候会出现“libXext.so.6: cannot

open shared object file:”的错误。或者有时出现 libXp.so.6 is needed by

openmotif21-2.1.30-11.EL...错误。

这个原因主要是oracle在安装的时候需要安装一些32位的lib，可以采用如下的解决办法：

# yum list available|grep libXp

libXp.i686 1.0.0-15.1.el6 Server

libXp.x86_64 1.0.0-15.1.el6 Server

libXp-devel.i686 1.0.0-15.1.el6 Server

libXp-devel.x86_64 1.0.0-15.1.el6 Server

所以需要安装 libXp.i686 及

libXp.x86_64

# yum install libXext.i686

# yum install libXext.x86_64

再比如在查询 gdbm 包，发现只有x86_64 , 没有 32bit 的

# yum install gdbm.i686

来安装32bit的包。

Linux NUMA

的导致性能问题，禁用之

[root@localhost ~]# numactl --show

policy: default

preferred node: current

physcpubind: 0 1 2 3

cpubind: 0

nodebind: 0

membind: 0

[root@localhost ~]#

[root@localhost ~]# numactl --hardware

available: 1 nodes (0)

node 0 cpus: 0 1 2 3

node 0 size: 8191 MB

node 0 free: 6590 MB

node distances:

node 0

0: 10

关闭NUMA的几种方法：

1) BIOS中关闭NUMA设置

2) 在操作系统kernel层面关于numa

/etc/grub.conf的kernel行最后添加： numa=off

不要放错位置，会导致无法启动

3)Oracle数据库层面关闭：

_enable_NUMA_optimization=false

(11g中参数为_enable_NUMA_support)

第一种方法最好，最安全

暗香无名

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
oracle更新timesample类型,***Oracle恢复spfile等小问题处理

没有任何文件的时候，只有备份的时候，如何把spfile恢复出来。比较简单，但就是很多人不会。set ORACLE_SID=WWWrman target /startup force nomount;SQL> exit已断开连接D:\work\scripts>rman target /恢复管理器: Release 12.1.0.2.0 - Production on 星期三 10月 ...
复制链接

扫一扫

oracle更新timesample类型,***Oracle恢复spfile等小问题处理

“相关推荐”对你有帮助么？