分类目录归档：数据库

rman中关于archivelog操作

发表于 2011 年 9 月 26 日由惜分飞

RMAN> list backup of archivelog all;
列出所有archive log 备份

RMAN> list backup of archivelog from logseq 100 until logseq 120;
列出archive log 从100到120

RMAN> list backup of archivelog sequence between 100 and 110;
列出archive log 从100到120
–说明:between……and只能使用sequence，而不能使用logseq

RMAN> list backup of archivelog from logseq 100;
列出seq大于等于100的archive log

RMAN> list backup of archivelog low logseq 120;
列出seq大于等于120的archive log

RMAN> list backup of archivelog sequence 100;
列出seq为100的archive log
–说明:在对于rman中关于archivelog的操作中logseq与sequence作用相同，但是建议尽量使用sequence

RMAN> list backup of archivelog logseq 85;
列出seq为85的archive log

RMAN> list backup of archivelog until logseq 85;
列出seq小于等于85的archive log

RMAN> list backup of archivelog high logseq 40;
列出seq小于等于40的archive log

RMAN> list backup of archivelog from time ‘sysdate-7′;
列出7天以前的archive log

RMAN> run {
2> set archivelog destination to ‘/opt/oracle/oradata/test/newlog’;
3> restore archivelog low logseq 40;
4> }
从seq为40开始，恢复到/opt/oracle/oradata/test/newlog中
–说明:list backup of archivelog中限定日志的位置也适合restore archivelog

RMAN> backup archivelog sequence between 100 and 110 format ‘/tmp/text_test.rman’ delete input;
备份seq为100至110的archive log
–说明:list backup of archivelog中限定日志的位置也适合backup archivelog

RMAN>DELETE ARCHIVELOG ALL COMPLETED BEFORE ‘SYSDATE-7′;
删除7天前archive log

RMAN>DELETE ARCHIVELOG low logseq 40;
删除seq大于等于40的archive log

RMAN> crosscheck archivelog all;
RMAN> delete expired archivelog all;
删除无效archive log
–说明:DELETE ARCHIVELOG中限定日志的位置也适合restore archivelog

发表在 Oracle备份恢复 | 评论关闭

升级到Oracle 10.2.0.4

发表于 2011 年 9 月 25 日由惜分飞

一、单实例升级先决条件：

1.表空间需求
确保system表空间至少有10M空间可用

2.系统参数：
确保参数SHARED_POOL_SIZE 和 JAVA_POOL_SIZE大于150MB以上，为加快升级速度，在系统内存可用的情况下，可临时调大这２个参数
SQL> SHOW PARAMETER SHARED_POOL_SIZE
SQL> SHOW PARAMETER JAVA_POOL_SIZE
SQL> ALTER SYSTEM SET SHARED_POOL_SIZE=’200M’ SCOPE=spfile;
SQL> ALTER SYSTEM SET JAVA_POOL_SIZE=’200M’ SCOPE=spfile;

二、实施升级

1.关闭需要升级的实例
停止实例
SQL> shutdown immediate
停止与该实例相关的所有后台进程
$ emctl stop dbconsole
$ isqlplusctl stop
$ lsnrctl stop

2.备份Oracle Home 目录及数据库
tar -cvf $ORACLE_BASE /orabak/ –确保Oracle相关的所有配置都位于$ORACLE_BASE目录，如监听等
cp *.dbf con*.ora redo*.log /orabak/ –对数据库实施冷备

3.升级软件
./runIstanller –>oracle 账户
root.sh –>root 账户

4.更新数据字典
SQL> startup upgrade
SQL> spool patch.log
SQL> @?/rdbms/admin/catupgrd.sql –注9i 使用catpatch.sql
SQL> spool off
SQL> !egrep “ORA-|Error” patch.log -i

5.重编译失效对象：
sql>shutdown immediate
sql>startup
SQL>@?/rdbms/admin/utlrp.sql

6.修改兼容性参数

SQL> alter system set compatible=’10.2.0.4.0′ scope=spfile;

7.如果使用了恢复目录，则执行下面的命令
$ rman catalog username/password@alias
RMAN> UPGRADE CATALOG;

8.如果升级回退：
SQL> STARTUP DOWNGRADE

SQL> SPOOL downgrade.log

SQL> @catdwgrd.sql（10.2.10运行的是这个，而10.1降级用的是d92000.sql，即dold_release.sql）

SQL>spool off

SQL>shutdown immediate

9.重新启动数据库:
SQL> SHUTDOWN
SQL> STARTUP
$ lsnrctl start
$ isqlplusctl start
$ emctl start dbconsole

10.检查升级后的情况

SQL> col comp_name for a35
SQL> col comp_name for a30
SQL> col version for a10
SQL> select comp_name,version,status from sys.dba_registry;

COMP_NAME VERSION STATUS
———————————– ———- ———————-
Oracle Database Catalog Views 10.2.0.4.0 VALID
Oracle Database Packages and Types 10.2.0.4.0 VALID
Oracle Workspace Manager 10.2.0.4.3 VALID
JServer JAVA Virtual Machine 10.2.0.4.0 VALID
Oracle XDK 10.2.0.4.0 VALID
Oracle Database Java Packages 10.2.0.4.0 VALID
Oracle Expression Filter 10.2.0.4.0 VALID
Oracle Data Mining 10.2.0.4.0 VALID
Oracle Text 10.2.0.4.0 VALID
Oracle XML Database 10.2.0.4.0 VALID
Oracle Rule Manager 10.2.0.4.0 VALID

COMP_NAME VERSION STATUS
———————————– ———- ———————-
Oracle interMedia 10.2.0.4.0 VALID
OLAP Analytic Workspace 10.2.0.4.0 VALID
Oracle OLAP API 10.2.0.4.0 VALID
OLAP Catalog 10.2.0.4.0 VALID
Spatial 10.2.0.4.0 VALID
Oracle Enterprise Manager 10.2.0.4.0 VALID

17 rows selected.

SQL> select * from utl_recomp_errors;

no rows selected

发表在 Oracle安装升级 | 评论关闭

10g新增列方式指定HINT

发表于 2011 年 9 月 23 日由惜分飞

在9i和以前的版本，索引提示的格式为/*+ index(table_alias) */或/*+ index(table_alias index_name) */，但是在10g中不仅可以通过索引名称来确定HINT的索引，还可以通过指定列名的方式，格式为/*+ index(table_alias (column_names)) */

SQL> create table test_hint   
  2  as
  3  select * from dba_objects;

Table created.

SQL> create index ind_hint on test_hint(owner,object_type);

Index created.

SQL>  exec dbms_stats.gather_table_stats(user, 'TEST_HINT', 
  2   method_opt => 'for all indexed columns size 100',cascade=>true);

PL/SQL procedure successfully completed.

SQL> set autot trace exp
SQL> select * from test_hint where owner = 'SYS';

Execution Plan
----------------------------------------------------------
Plan hash value: 11101196

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           | 23272 |  2113K|   161   (1)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| TEST_HINT | 23272 |  2113K|   161   (1)| 00:00:02 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("OWNER"='SYS')

SQL> select /*+index(a)*/ * from test_hint  a where owner = 'SYS';


Execution Plan
----------------------------------------------------------
Plan hash value: 890897193

-----------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           | 23272 |  2113K|  1122   (1)| 00:00:14 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST_HINT | 23272 |  2113K|  1122   (1)| 00:00:14 |
|*  2 |   INDEX RANGE SCAN          | IND_HINT  | 23272 |       |    84   (0)| 00:00:02 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OWNER"='SYS')

SQL> select /*+index(a ind_hint)*/ * from test_hint  a where owner = 'SYS';

Execution Plan
----------------------------------------------------------
Plan hash value: 890897193

-----------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           | 23272 |  2113K|  1122   (1)| 00:00:14 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST_HINT | 23272 |  2113K|  1122   (1)| 00:00:14 |
|*  2 |   INDEX RANGE SCAN          | IND_HINT  | 23272 |       |    84   (0)| 00:00:02 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OWNER"='SYS')

SQL> select /*+index(a (owner,object_type))*/ * from test_hint  a where owner = 'SYS';

Execution Plan
----------------------------------------------------------
Plan hash value: 890897193

-----------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           | 23272 |  2113K|  1122   (1)| 00:00:14 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST_HINT | 23272 |  2113K|  1122   (1)| 00:00:14 |
|*  2 |   INDEX RANGE SCAN          | IND_HINT  | 23272 |       |    84   (0)| 00:00:02 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OWNER"='SYS')
--指定和index完全一致的列，走index

SQL> select /*+index(a (owner))*/ * from test_hint  a where owner = 'SYS';

Execution Plan
----------------------------------------------------------
Plan hash value: 890897193

-----------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           | 23272 |  2113K|  1122   (1)| 00:00:14 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST_HINT | 23272 |  2113K|  1122   (1)| 00:00:14 |
|*  2 |   INDEX RANGE SCAN          | IND_HINT  | 23272 |       |    84   (0)| 00:00:02 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OWNER"='SYS')
--指定列和where条件一致，也可以使用该index

SQL> select /*+index(a (object_id))*/ * from test_hint  a where owner = 'SYS';

Execution Plan
----------------------------------------------------------
Plan hash value: 11101196

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           | 23272 |  2113K|   161   (1)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| TEST_HINT | 23272 |  2113K|   161   (1)| 00:00:02 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("OWNER"='SYS')
--虽然是index中的一个列，但是由于不是where条件中，所以不能被使用

SQL> select /*+index(a (owner))*/ * from test_hint  a where object_type = 'TABLE';

Execution Plan
----------------------------------------------------------
Plan hash value: 1755360976

-----------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |  1752 |   159K|   104   (0)| 00:00:02 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST_HINT |  1752 |   159K|   104   (0)| 00:00:02 |
|*  2 |   INDEX SKIP SCAN           | IND_HINT  |  1752 |       |    25   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OBJECT_TYPE"='TABLE')
       filter("OBJECT_TYPE"='TABLE')
--指定index的第一列，虽然不在where中，但是还是会使用index

--说明：使用/*+ index(table_alias (column_names)) */方式的hint，需要先测试，有可能不能达到预期效果

发表在 Oracle | 评论关闭

17813235971

QQ 咨询

分类目录归档：数据库

rman中关于archivelog操作

升级到Oracle 10.2.0.4

10g新增列方式指定HINT

文章分类

最近发表

分类目录归档：数据库

rman中关于archivelog操作

升级到Oracle 10.2.0.4

10g新增列方式指定HINT

标签云

文章分类

最近发表