ora-01775 问题与expdp

sql> shutdown immediate;
sql> startup nomount;
$ rman nocatalog
RMAN>connect target
RMAN>restore controlfile to '+ASM2/orcl/controlfile/newfile.name' from '+ASM/orcl/controlfile/controlfile.name';
sql> alter system set controlfiles='+ASM/orcl/controlfile.name',
'+ASM2/orcl/controlfile/newfile.name' scope=spfile;
sql> alter database mount;
sql> alter database open;
Subject: How to duplicate a controlfile when ASM is involved
Doc ID: Note:345180.1 Type: HOWTO
Last Revision Date: 16-DEC-2006 Status: PUBLISHED
In this Document
Goal
Solution
Applies to:
Oracle Server - Enterprise Edition - Version: 10.1.0.0 to 10.2.0.0
Information in this document applies to any platform.
Goal
This document presents different options to duplicate a controlfile in environments using ASM. The procedure applies either to duplicate a controlfile into ASM using a controlfile stored in file system or to duplicate a controlfile into ASM using a controlfile already stored in ASM.
Solution
Duplicating a controlfile into ASM when original controlfile is stored on a file system
On the database instance:
1. Identify the location of the current controlfile:
SQL> select name from v$controfile;
NAME
--------------------------------------------------------------------------------
/oradata2/102b/oradata/P10R2/control01.ctl'

2. Shutdown the database and start the instance:
SQL> shutdown normal
SQL> startup nomount
3. Use RMAN to duplicate the controlfile:
$ rman nocatalog
RMAN>connect target
RMAN>restore controlfile to '<DISKGROUP_NAME>' from '<OLD_PATH>';
RMAN> restore controlfile to '+DG1' from '/oradata2/102b/oradata/P10R2/control01.ctl';
Starting restore at 23-DEC-05
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
channel ORA_DISK_1: copied control file copy
Finished restore at 23-DEC-05
We are only specifying the name of the diskgroup, so Oracle will create an OMF (Oracle Managed File). Use ASMCMD or sqlplus to identify the name assigned to the controlfile
4. On the ASM instance, identify the name of the controlfile:
Using ASMCMD:
$ asmcmd
ASMCMD> cd <DISKGROUP_NAME>
ASMCMD> find -t controlfile . *
Changing the current directory to the diskgroup where the controlfile was created will speed the search.
Output:
ASMCMD> find -t controlfile . *
+DG1/P10R2/CONTROLFILE/backup.308.577785757
ASMCMD>

Note the name assigned to the controlfile. Although the name starts with the backup word, that does not indicate is a backup of the file. This just the name assigned for the identical copy of the current controlfile.
5. On the database side:
* Modify init.ora or spfile, adding the new path to parameter control_files.
* if using init<SID>.ora, just modify the control_files parameter and restart the database.
* If using spfile,
1) startup nomount the database instance
2) alter system set control_files='+DG1/P10R2/CONTROLFILE/backup.308.577785757','/oradata2/102b/oradata/P10R2/control01.ctl' scope=spfile;
For RAC instance:
alter system set control_files='+DG1/P10R2/CONTROLFILE/backup.308.577785757','/oradata2/102b/oradata/P10R2/control01.ctl' scope=spfile sid='*';
3) shutdown immediate
* start the instance.
Verify that new control file has been recognized. If the new controlfile was not used, the complete procedure needs to be repeated.
Duplicating a controlfile into ASM using a specific name
It is also possible to duplicate the controlfile using a specific name for the new controlfile. In the following example, the controlfile is duplicated into a new diskgroup where controlfiles have not been created before.
On the ASM instance:
A. Create the directory to store the new controlfile.
SQL> alter diskgroup <DISKGROUP_NAME> add directory '+<DG_NAME>/<DB_NAME>/CONTROLFILE';
Note that ASM uses directories to store the files and those are created automatically when using OMF files. (just specifying the diskgroup name). Asumming that other OMF files were created on the diskgroup, the first directory (DB_NAME) already exist, so it is only required to create the directory for the controlfile.

SQL> alter diskgroup DG1 add directory '+DG1/P10R2/CONTROLFILE';
ASMCMD can also be used
ASMCMD>cd dg1
ASMCMD>mkdir controlfile
On the database instance:
B. Edit init.ora or spifile and modify parameter control_file:
control_files='+DG1/P10R2/CONTROLFILE/control02.ctl','/oradata2/102b/oradata/P10R2/control01.ctl'
C. Identify the location of the current controlfile:

SQL> select name from v$controfile;
NAME
--------------------------------------------------------------------------------
/oradata2/102b/oradata/P10R2/control01.ctl'
D. Shutdown the database and start the instance:
SQL> shutdown normal
SQL> startup nomount
E. Use RMAN to duplicate the controlfile:
$ rman nocatalog
RMAN>connect target
RMAN>restore controlfile to '<FULL PATH>' from '<OLD_PATH>';
RMAN> restore controlfile to '+DG1/PROD/controlfile/control02.ctl' from '/oradata2/102b/oradata/P10R2/control01.ctl';
Starting restore at 23-DEC-05
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
channel ORA_DISK_1: copied control file copy
Finished restore at 23-DEC-05
F. Start the database:
SQL> alter database mount;
SQL> alter database open;
Now, using ASMCMD to search for information for the controlfiles, the find -t contrlfile command will return two records. That does not indicate there were created two controlfiles. The name specified is an alias name and is only an entry in the ASM metadata (V$ASM_ALIAS). Oracle will create the alias and the OMF entry when user specifies the file name.
Duplicating a controlfile into ASM when original controlfile is stored on ASM
1) Edit init.ora and add new disk group name or same disk group name for mirroring controlfiles.
Example:
control_files=('+GROUP1','+GROUP2')
(2) Mount the instance
(3) Execute restore command, to duplicate the controlfile using the original location. Presuming, your current controlfile location DISK path is '+data/V10G/controlfile/Current.260.605208993' , execute:
RMAN> restore controlfile from '+data/V10G/controlfile/Current.260.605208993';
Starting restore at 29-APR-05
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=317 devtype=DISK
channel ORA_DISK_1: copied controlfile copy
output filename=+GROUP2/v10g/controlfile/backup.268.7
output filename=+GROUP2/v10g/controlfile/backup.260.5
Finished restore at 29-APR-05
(4) Mount and open the database:
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
RMAN> alter database open;
database opened
RMAN> exit
(5) Verify new mirrored controlfiles via sqlplus
SQL> show parameter control_files
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string +GROUP2/v10g/controlfile/backup.268.7, +GROUP2/v10g/controlfile/backup.260.5
(6) Modify pfile and create new spfile.
First modify the init.ora file and include the full path of the new controlfiles, so next time the database is restarted, it will use the new controlfiles. If using spfile, recreate the new spfile.

今天看到一个应用，发现索引简直是乱建，许多表上每个字段都有索引，写一个sql，查询相似的sql：

查询相似的索引：

SELECT TABLE_OWNER, TABLE_NAME, INDEX_NAME, COLUMN_NAME
FROM ALL_IND_COLUMNS
WHERE TABLE_OWNER = UPPER (':OWNER')
AND (TABLE_OWNER, TABLE_NAME, COLUMN_NAME) IN
(SELECT TABLE_OWNER, TABLE_NAME, COLUMN_NAME
FROM (SELECT TABLE_OWNER, TABLE_NAME, COLUMN_NAME,
COUNT (*) TCOUNT
FROM ALL_IND_COLUMNS
WHERE COLUMN_POSITION = 1
AND TABLE_OWNER NOT IN ('SYS','SYSTEM','OUTLN','DBSNMP')
HAVING COUNT (*) > 1
GROUP BY TABLE_OWNER, TABLE_NAME, COLUMN_NAME))
ORDER BY TABLE_OWNER, TABLE_NAME, COLUMN_NAME, INDEX_NAME

http://www.chinalinuxpub.com/read.php?wid=1890

很多人都有这样的了解，为了防止热备期间的分裂块问题,Oracle在热备过程中会对修改块进行全块的Redo记录。
请大家注意：此外还存在一个隐含参数可以控制这个行为:
SQL> SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc describ
2 FROM SYS.x$ksppi x, SYS.x$ksppcv y
3 WHERE x.inst_id = USERENV ('Instance')
4 AND y.inst_id = USERENV ('Instance')
5 AND x.indx = y.indx
6 AND x.ksppinm LIKE '%&par%'
7 /
Enter value for par: blocks
old 6: AND x.ksppinm LIKE '%&par%'
new 6: AND x.ksppinm LIKE '%blocks%'
NAME VALUE DESCRIB
------------------------------
_log_blocks_during_backup TRUE log
block images when changed during backup
注释：此参数的缺省值是TRUE，设置在热备份期间允许在redo中记录数据块信息，如果数据库块大小等于操作系统块大小，则可以设置该参数为False，用以减少热备期间数据库的负担。

后记：不知道是否有用，没测试过。

http://www.itpub.net/showthread.php?threadid=822248&pagenumber=

export ORACLE_SID=+ASM1
sqlplus sys as sysdba
SQL*Plus: Release 10.2.0.2.0 - Production on Thu Jul 26 15:44:34 2007
Copyright (c) 1982, 2005, Oracle. All Rights Reserved.
Enter password:
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options
SQL> show parameter control
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 7
control_files string /u01/app/oracle/product/10.2.0
/rac_db/dbs/cntrl+ASM1.dbf
SQL>
但是我检查这个路径，这个文件并不存在。

今天一个用户报告无法登录系统，检查发现系统用户已经尝试太多，出现locked，导致无法进入。因为这个用户属于熟练用户，不应该出现尝试太多无法登录的问题，询问他密码才知道口令中包含@,导致无法连接数据库。

跟踪一下程序发现，用户修改自己的口令时，执行如下：
alter user xxxx identified by "a@" ;

注意在程序中使用了引号，如果没有引号是无法正常执行的，可以肯定口令已经修改为a@,通过orabf破解软件也可以确定（注意必须使用
-c 5 参数）。

在sqlplus测试如下：

如果使用：
sqlplus scott／"a@"@orcl 是无法登录的。

修改为：
set local=orcl
set ORACLE_SID=orcl
sqlplus scott／"a@" 同上。

但是如果使用如下：
sqlplus scott@orcl
进入后输入"a@"(注意要打双引号，是可以登录的）。

关键字:rac ocfs2 ORA-39083 ORA-01115 impdp
前一阵子，帮别人解决了一个问题，就是建立好ocfs2文件系统后，使用impdp导入大量数据出错，报告如下：

ORA-39083: Object type INDEX failed to create with error:
ORA-01115: IO error reading block from file 24 (block # 29334)
ORA-01110: data file 24: '/data/orcl/XXX.dbf'
ORA-27091: unable to queue I/O
ORA-27072: File I/O error

google找到如下链接：
http://oss.oracle.com/projects/ocfs2/dist/documentation/ocfs2_faq.html
http://bbs.loveunix.net/viewthread.php?tid=70357&extra=page%3D6

从第二个连接看似乎已经导入完成，但是问题也是在建立索引的时候存在问题，在仔细看第1个连接的文档，发现存在如下内容：

ORACLE RAC
# Any special flags to run Oracle RAC?
OCFS2 volumes containing the Voting diskfile (CRS), Cluster registry (OCR), Data files, Redo logs, Archive logs and Control files must be mounted with the datavolume and nointr mount options. The datavolume option ensures that the Oracle processes opens these files with the o_direct flag. The nointr option ensures that the ios are not interrupted by signals.
# mount -o datavolume,nointr -t ocfs2 /dev/sda1 /u01/db
# What about the volume containing Oracle home?
Oracle home volume should be mounted normally, that is, without the datavolume and nointr mount options. These mount options are only relevant for Oracle files listed above.
# mount -t ocfs2 /dev/sdb1 /software/orahome
Also as OCFS2 does not currently support shared writeable mmap, the health check (GIMH) file $ORACLE_HOME/dbs/hc_ORACLESID.dat and the ASM file $ASM_HOME/dbs/ab_ORACLESID.dat should be symlinked to local filesystem. We expect to support shared writeable mmap in the OCFS2 1.4 release.

修改/etc/fstat参数，加入nointr后，在使用impdp导入正常：
/dev/sdb1 /xxx ocfs2 _netdev,datavolume,nointr 0 0

补充说明一点，就是按照第一个链接，建立ocfs2
mkfs.ocfs2 -b 4K -C 256K -N 4 -L /oradata /dev/sdb1
测试问题一样，在加入nointr参数后正常。

http://www.itpub.net/showthread.php?threadid=492053&pagenumber=

以前我使用smaba试过不行，接着测试nfs，我发现当时按照一些介绍是参数不对，就没有仔细研究。

今天发现一个链接，记录下来：

mount -t nfs -o rw,hard,nointr,tcp,noac,vers=3,timeo=600,rsize=32768,wsize=32768

关键字：ORA-00979 ORA-06512

今天分析一个表，遇到一个ora-00979 错误。

BEGIN
SYS.DBMS_STATS.GATHER_TABLE_STATS (
OwnName => 'XXXX'
,TabName => 'II_INMAININFO'
,Method_Opt => 'FOR ALL INDEXED COLUMNS SIZE SKEWONLY '
,Degree => 4
,Cascade => TRUE
,No_Invalidate => FALSE);
END;
Runtime error occurred: 979 (ORA-00979: 不是 GROUP BY 表达式
ORA-06512: 在"SYS.DBMS_STATS", line 10502
ORA-06512: 在"SYS.DBMS_STATS", line 10516
ORA-06512: 在line 2)

我使用的oracle版本如下：
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
PL/SQL Release 9.2.0.8.0 - Production
CORE 9.2.0.8.0 Production
TNS for 32-bit Windows: Version 9.2.0.8.0 - Production
NLSRTL Version 9.2.0.8.0 - Production

5 rows selected.

执行如下：

ALTER SESSION SET TRACEFILE_IDENTIFIER=i979trace;
ALTER SESSION SET SQL_TRACE=TRUE;
ALTER session SET EVENTS='979 TRACE NAME ERRORSTACK FOREVER, LEVEL 12';

从跟踪文件取出错误结果，如下：
ksedmp: internal or fatal error
ORA-00979: 不是 GROUP BY 表达式
Current SQL statement for this session:
SELECT /*+ cursor_sharing_exact dynamic_sampling(0) no_monitoring */ substrb(dump(val, 16, 0, 32), 1, 120) ep, cnt
FROM (
SELECT /*+ noparallel(t) noparallel_index(t) cursor_sharing_exact dynamic_sampling(0) no_monitoring */ TRUNC("OPER_DTIME") val, count(*) cnt
FROM "xxxx"."II_INMAININFO" t
WHERE TRUNC("OPER_DTIME") is not null
group by TRUNC("OPER_DTIME"))
order by val

我在表上建立了TRUNC("OPER_DTIME")的函数索引。如果删除trunc,执行如下sql语句是可以成功的：
SELECT /*+ cursor_sharing_exact dynamic_sampling(0) no_monitoring */ substrb(dump(val, 16, 0, 32), 1, 120) ep, cnt
FROM (
SELECT /*+ noparallel(t) noparallel_index(t) cursor_sharing_exact dynamic_sampling(0) no_monitoring */ "OPER_DTIME" val, count(*) cnt
FROM "xxxx"."II_INMAININFO" t
WHERE "OPER_DTIME" is not null
group by "OPER_DTIME")
order by val

我在8i下执行以上sql语句是可以的，看来这个又是9i下的BUG。

http://tolywang.itpub.net/post/48/287344

在oracle 10g中安装rac,在配置裸设备时,要修改/etc/sysconfig/rawdevices文件,但是配置好重新启动后,ls -l /dev/raw/raw* , 发现文件的属性是root:disk, 一般以前为了正常
的使用裸设备.要修改/etc/rc.d/rc.local文件,加入如下:
chown root:oinstall /dev/raw/raw1
chown root:oinstall /dev/raw/raw2
chown root:oinstall /dev/raw/raw3
...
chmod 660 /dev/raw/raw1
chmod 660 /dev/raw/raw2
chmod 660 /dev/raw/raw3
...

实际上还有更简单的方法就是,修改/etc/udev/permissions.d/50-udev.permissions文件:
raw/*:root:disk:0660
为
raw/*:root:oinstall:0660

这样就可以了!

http://spaces.msn.com/roujiaweize/blog/cns!9745F14B4AEB3B72!232.entry?vv=400

# touch /opt/rmoracle.sh
# vi /opt/rmoracle.sh
rm -rf /opt/oracle
rm -rf /opt/ORCLfmap
rm -f /etc/oraInst.loc
rm -f /etc/oratab
rm -f /usr/local/bin/oraenv
rm -f /usr/local/bin/coraenv
rm -f /usr/local/bin/dbhome

# chmod 755 /opt/rmoracle.sh
# /opt/rmoracle.sh

上面实际上是删除oracle home 目录.比较容易忘记的是/opt/ORCLfmap.

http://www.itpub.net/showthread.php?threadid=864080&pagenumber=
今天遇到一个死锁问题,在windows下,杀异常线程使用:

ALTER SYSTEM KILL SESSION 'sid,serial#';

我发现kill后,查询:
select * from v$session where sid=28

STATUS='KILLED' , 什么那时候才消失.难道以后删除线程,要使用
orakill sid thread比较好.不过现在执行:

SELECT s.username, s.osuser, s.sid, s.serial#, p.spid
FROM v$session s, v$process p
WHERE s.paddr = p.addr
AND s.username is not null
AND sid = 28
是无法获得结果的.

google后,找到eygle写的文章,里面很好的描述了如何解决这个问题.
http://www.eygle.com/faq/Kill_Session.htm

主要是由于查询spid，在操作系统级来kill这些进程.但是由于此时v$session.paddr已经改变，
我们无法通过v$session和v$process关联来获得spid.

记录一下需要执行的sql语句:
SELECT s.username, s.status, x.ADDR, x.KSLLAPSC, x.KSLLAPSN, x.KSLLASPO, x.KSLLID1R, x.KSLLRTYP, decode(bitand (x.ksuprflg, 2), 0, null, 1)
FROM x$ksupr x, v$session s
WHERE s.paddr(+) = x.addr
AND bitand(ksspaflg, 1) != 0;

简单一点:
SELECT p.addr
FROM v$process p
WHERE pid <> 1 minus
SELECT s.paddr
FROM v$session s;

SELECT *
FROM v$process
WHERE addr IN (SELECT p.addr
FROM v$process p
WHERE pid <> 1
MINUS
SELECT s.paddr
FROM v$session s);

SELECT s.username, s.osuser, s.SID, s.serial#, p.spid
FROM v$session s, v$process p
WHERE s.paddr = p.addr AND s.username IS NOT NULL

然后使用orakill 来删除线程.

http://www.itpub.net/showthread.php?threadid=853638&pagenumber=

今天,无意中发现shared pool很大,占到4G,不知道是什么问题?

show parameter shared

hi_shared_memory_address integer 0
max_shared_servers integer 20
shared_memory_address integer 0
shared_pool_reserved_size integer 4194304
shared_pool_size integer 83886080
shared_server_sessions integer 181
shared_servers integer 1

shared_pool_size大小仅仅80M.

select * from v$version

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
PL/SQL Release 9.2.0.8.0 - Production
CORE 9.2.0.8.0 Production
TNS for 32-bit Windows: Version 9.2.0.8.0 - Production
NLSRTL Version 9.2.0.8.0 - Production

5 rows selected.

SELECT *
FROM (SELECT *
FROM v$sgastat
WHERE pool = 'shared pool'
ORDER BY BYTES DESC)
WHERE ROWNUM <= 5

POOL NAME BYTES
----------- -------------------------- ----------
shared pool session param values 4293530440
shared pool free memory 67550256
shared pool miscellaneous 27355660
shared pool XDB Schema Cac 3889744
shared pool library cache 3709176

5 rows selected.

发现shared pool 的session param values占用很大,目前系统运行正常,是遇到什么BUG吗?

今天看执行计划,无意中发现过滤的时候使用一个不知道的函数lnnvl,google发现如下链接,记录如下:
http://www.adp-gmbh.ch/ora/sql/lnnvl.html

create table lnnvl_ex (
a number,
b number
);

insert into lnnvl_ex values (null, null);
insert into lnnvl_ex values (null, 1);
insert into lnnvl_ex values ( 2, 2);
insert into lnnvl_ex values ( 3, null);
insert into lnnvl_ex values ( 4, 5);

select * from lnnvl_ex where a=b;

A B
---------- ----------
2 2

select * from lnnvl_ex where lnnvl(a=b);

A B
---------- ----------

1
3
4 5

参考链接:

http://www.itpub.net/showthread.php?threadid=415889&pagenumber=

alter system switch logfile ;
alter system archive log current ;
alter system archive log all ;

Oracle8i Bible上是这样说的
To have Oracle archive the current
redo log files, issue this command:

ALTER SYSTEM ARCHIVE LOG CURRENT;

This command causes Oracle to switch to a new log file. Oracle then archives all redo log files that have not yet been archived. Another approach to this same task is to issue the following two commands:

ALTER SYSTEM SWITCH LOGFILE;
ALTER SYSTEM ARCHIVE LOG ALL

The first command forces the log switch, and the second causes Oracle to archive all the redo log files that are full but that haven't been archived yet.

http://grassbell.itpub.net/post/26/18902

补充一点：

改名之前在standby上先执行 ALTER SYSTEM SET standby_file_management='MANUAL' SCOPE=BOTH;。

需要注意的是：
如果你想让primary 和standby 上的数据文件结构保持一致的话，在primary 上rename a datafile后，即使STANDBY_FILE_MANAGEMENT = auto,也需要在standby上手工执行相同的操作。当然，如果不想保持一致，standby上可以不作任何动作。

-------------------------修改相应的primary数据文件路径-----------------------
1.Offline 表空间:
Alter tablespace tools offline NORMAL；

2.Mv dbfile:
Mv /data4/oradata/crmtemp/tools02.dbf /disk3/oradata/crmtemp/

3.Rename：
Alter tablespace tools
rename datafile '/data4/oradata/crmtemp/tools02.dbf'
To '/disk3/oradata/crmtemp/tools02.dbf';

4.Online：
Alter tablespace tools online；

-------------------------修改相应的standby 数据文件路径-----------------------
1．Recover
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE
2> DISCONNECT FROM SESSION;
确定已经所有的log都已经应用了。
（或用脚本sh /home/oracle/admin/recover_stby.sh）
2．Cancel RECOVER：
SQL> SELECT NAME, SEQUENCE#, ARCHIVED, APPLIED
2> FROM V$ARCHIVED_LOG;
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

3．Shutdown
SQL> SHUTDOWN;

4．Mv dbfile:
Mv /data4/oradata/crmtemp/tools02.dbf /disk3/oradata/crmtemp/

5．mount
SQL> STARTUP NOMOUNT;
SQL> ALTER DATABASE MOUNT STANDBY DATABASE;

6．Rename：
ALTER DATABASE RENAME FILE
'/data4/oradata/crmtemp/tools02.dbf'
To '/disk3/oradata/crmtemp/tools02.dbf';

7．Recover：
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE
2> DISCONNECT FROM SESSION;
（或用脚本sh /home/oracle/admin/recover_stby.sh）

前几天安装一台oracle数据库，重新启动后发现无法启动，才知道以前安装单个实例的需要修改/etc/oratab文件，将最后一个参数改为Y。
orcl:/u01/app/oracle/product/10.2.0/db_1:Y

参考了其他一些文档，启动脚本如下：

 查看全文

今天上午，一台rac机器重启，发现机器无法正常启动数据库，手工执行：
sqlplus sys as sysdba
>startup 提示如下：

ORA-00119: invalid specification for system parameter REMOTE_LISTENER
ORA-00132: syntax error or unresolved network name 'LISTENERS_ORCL'

在另外一台机器检查发现：

show parameter remote_listener

显示正常，设置也正确。再检查有问题的机器的tnsnames.ora文件，也没有发现什么错误。

在另外一台机器执行(有问题的是rac2)：

alter system reset remote_listener scope=spfile sid='*' ;

再启动另外的实例，发现有问题的实例可以启动，但是这样有一个问题，负载均衡是不能正常工作的，
仔细检查发现，用户全部连接到先前正常的机器，根本不能实现负载均衡。

在仔细检查发现，有问题的tnsnames.ora文件，发现第2个配置中，orcl1 缺少一个等号'='

ORCL1
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = XXX)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
(INSTANCE_NAME = orcl1)
)
)

不知道谁删除了文件的等号，正是缺少这个等号，导致tnsnames.ora文件破坏。而且导致下面的配置参数LISTENERS_ORCL不正确！！！

简单的检测方法就是执行
tnsping orcl1
tnsping LISTENERS_ORCL

提示出现：
TNS-03505: Failed to resolve name

我很奇怪的是如果紧连着的是orcl2的配置，tnsping orcl2 是没有问题的。实际上如果alert*.log文件，也能发现ORA-12154的错误。加入等号后问题解决！使用net manager管理工具能发现tnsnames.ora无法配置。

http://dev.csdn.net/article/67/67101.shtm

简介：

SRVCTL是ORACLE9i RAC集群配置管理的工具。本文是对SRVCTL的所有命令进行详细说明的一篇参考文档。

读者对象：ORACLE9i RAC数据库集群管理员。

注：

RAC: Real Application Clusters

SRVM: Server Management

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

l SRVCTL Add

添加数据库或实例的配置信息。在增加实例中，与-i一起指定的名字应该与INSTANCE_NAME 和 ORACLE_SID参数匹配。

srvctl add database -d database_name [-m domain_name] -o oracle_home [-s spfile]

srvctl add instance -d database_name -i instance_name -n node_name

表4-5 SRVCTL Add命令详细选项

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

命令 选项

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

-m 数据库域名 格式如"us.mydomain.com"

-m 选项指定的数据库域名必须匹配数据库INIT.ORA或者SPFILE中DB_DOMAIN 和DB_NAME参数。在增加数据库时，-d指定的数据库名必须与DB_NAME参数匹配

-n 实例节点名

-o $ORACLE_HOME(用来确定lsnrctl和Oracle等命令路径)

-s SPFILE 文件名

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

实例：添加一个新的数据库

srvctl add database -d mydb -o /ora/ora9

实例：向数据库添加实例

srvctl add instance -d mydb -i mydb01 -n gm01

srvctl add instance -d mydb -i mydb02 -n gm02

srvctl add instance -d mydb -i mydb03 -n gm03

l SRVCTL Config

显示保存在SRVM配置文件中的配置信息

srvctl config database

显示数据库配置列表

srvctl config database -d database_name

数据库配置信息显示的格式：

nodename1 instancename1 oraclehome

nodename2 instancename2 oraclehome

实例：显示数据库配置信息

srvctl config database -d mydb

l SRVCTL Getenv

getenv操作用来从SRVM配置文件中获取与显示环境变量

srvctl getenv database -d database_name [-t name[,name,...]]

srvctl getenv instance -d database_name -i instance_name [-t name[,name,...]]

实例：列出数据库的所有环境变量

srvctl getenv database -d mydb

l SRVCTL Modify

修改实例的节点配置信息。这些修改会在程序下次重新启动后生效。修改后的信息将永久保存。

srvctl modify instance -d database_name -i instance_name -n node_name

实例：修改实例到另一个节点

srvctl modify instance -d mydb -n my_new_node

l SRVCTL Remove

这是用来删除SRVM库中配置信息的命令。对象相关的环境设置也同样删除。如果你未使用强制标志(-f)，ORACLE将提示你确认是否删除。

使用强制选项(-f)，删除操作将不进行提示

srvctl remove database -d database_name [-f]

srvctl remove instance -d database_name -i instance_name [-f]

SRVCTL Remove命令详细选项

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

-f 强制删除应用时不进行确认提示

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

实例：删除数据库

srvctl remove database -d mydb

实例：删除数据库实例

srvctl remove instance -d mydb -i mydb01

srvctl remove instance -d mydb -i mydb02

srvctl remove instance -d mydb -i mydb03

l SRVCTL Setenv

设置SRVM配置文件中的环境变量值。

srvctl setenv database -d database_name -t name=value [,name=value,...]

srvctl setenv instance -d database_name [-i instance_name] -t name=value [,name=value,...]

实例：设置数据库环境变量

srvctl setenv database -d mydb -t LANG=en

l SRVCTL Start

启动数据库，所有实例或指定的实例，及启动所有相关未启动的监听。

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

注：

对于start命令和其它一些可以使用连接字符串的操作，如果你不提供连接字符串，那么ORACLE会使用"/ as sysdba"在实例上执行相关的操作。另外，要执行类似的操作，你必须是OSDBA组的成员。关于更多的OSDBA组权限信息，请参考ORACLE9i的安装指南。

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

srvctl start database -d database_name [-o start_options] [-c connect_string]

srvctl start instance -d database_name -i instance_name [,instance_name-list] [-o start_options] [-c connect_string]

表4-6 SRVCTL Start命令详细选项

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

命令 选项

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

-o 在SQL*Plus直接传递的startup命令选项，可以包括PFILE

-c 使用SQL*Plus连接数据库实例的连接字符串

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

实例：启动数据库及所有的实例

srvctl start database -d mydb

实例：启动指定的实例

srvctl start instance -d mydb -i mydb1,mydb4

l SRVCTL Status

显示指定数据库的当前状态

srvctl status database -d database_name

srvctl status instance -d database_name -i instance_name [,instance_name-list]

实例：显示数据和所有实例状态

srvctl status database -d mydb

实例：显示指定实例的状态

srvctl status instance -d mydb -i mydb1,mydb2

l SRVCTL Stop

停止数据库所有实例可者指定实例

srvctl stop database -d database_name [-o stop_options] [-c connect_string]

srvctl stop instance -d database_name -i instance_name [,instance_name_list] [-o stop_options][-c connect_string]

表4-7 SRVCTL Stop命令详细选项

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

命令 选项

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

-c 使用SQL*Plus连接数据库实例的连接字符串

-o 在SQL*Plus直接传递的shutdown命令选项

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

实例：停止数据库所有的实例

srvctl stop database -d mydb

实例：停止指定实例

srvctl stop instance -d mydb -i mydb1

l SRVCTL Unsetenv

取消SRVM配置文件中环境变量定义值

srvctl unsetenv database -d database_name-t name[,name,...]

srvctl unsetenv instance -d database_name[-i instance_name] -t name[,name,...]

实例：取消环境变量，回到缺省值

srvctl unsetenv database -d mydb -t CLASSPATH

l 使用SRVCONFIG导入和导出RAW设备配置信息

你可使用SRVCONFIG导入和导出RAW设备配置信息，不管配置文件是在集群文件系统上还是在RAW设备上。你可以使用这种方法来备份与恢复SRVM配置信息。

例如：

下面的命令用来导出配置信息的内容到你指定文件名的文本文件中。

srvconfig -exp file_name

下面的命令用来从指定文本文件中导入配置信息到到你运行命令的RAC环境配置信息库。

srvconfig -imp file_name

http://www.itpub.net/showthread.php?threadid=822248&pagenumber=

export ORACLE_SID=+ASM1

sqlplus sys as sysdba

SQL*Plus: Release 10.2.0.2.0 - Production on Thu Jul 26 15:44:34 2007

Copyright (c) 1982, 2005, Oracle. All Rights Reserved.

Enter password:

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options

SQL> show parameter control

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 7
control_files string /u01/app/oracle/product/10.2.0
/rac_db/dbs/cntrl+ASM1.dbf
SQL>

但是我检查这个路径，这个文件并不存在。

http://www.newbooks.com.cn/info/136239.html

一、 环境配置

primary： IP：192.168.0.120
CPU：2个Intel(R) Xeon(TM) CPU 2.80GHz （HT）
Mem：2G Swap：4G
Disk：130G DB：

Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - Prod OS：Linux oracle 2.4.21-20.ELsmp #1 SMP
standby： IP：192.168.0.101 Cup：2个Intel(R) Xeon(TM) CPU 2.40GHz （HT） Mem：2G Swap：2G Disk：66G DB：
Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - Prod OS：Linux boss-3 2.4.21-15.ELsmp #1 SMP Primary

为正在使用的生产数据库，standby安装oracle软件，但不建立数据库。正在装载数据……

二、 建立物理备用数据库

1． 准备主库的oracle环境：编辑oracle用户的/HOME/.bash_profile文件，oracle相关环境变量如下：

ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE ORACLE_SID=BOSS;
export ORACLE_SID ORACLE_HOME=$ORACLE_BASE/product/10.1.0/Db_1;
export ORACLE_HOME
export PATH=$ORACLE_HOME/bin:$PATH:
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib DISPLAY=10.1.9.59:0.0;
export DISPLAY NLS_LANG=american_america.ZHS16CGB231280; export NLS_LANG

2． 修改主库为归档模式建立归档目录：

mkdir -p /u02/oradata/BOSS/arch

修改归档模式：

archive log list; create pfile from spfile;

编辑$ORACLE_HOME/dbs/initBOSS.ora 添加下面一行 log_archive_dest_1='location=/u02/oradata/BOSS/arch'

sqlplus /nolog conn sys as sysdba
shutdown immediate;
create spfile from pfile;
startup nomount; alter database mount;
alter database archivelog;
alter database open;

3. 对主数据库做一次完整热备份，获得备用数据库数据 RMAN>connect target

RMAN> backup database format='/home/oracle/%U_%s.bak';
RMAN> sql "Alter System Archive Log Current";
RMAN> Backup filesperset 10 ArchiveLog all format='/home/oracle/%U_%s.bak';

cd /home/oracle scp *.bak 192.168.0.101:/home/oracle/

4. 在standby服务器准备环境与primary相同编辑oracle用户的$HOME/.bash_profile文件，oracle相关环境变量如下：
ORACLE_BASE=/u01/app/oracle;
export ORACLE_BASE ORACLE_SID=BOSS;
export ORACLE_SID ORACLE_HOME=$ORACLE_BASE/product/10.1.0/Db_1;
export ORACLE_HOME export PATH=$ORACLE_HOME/bin:$PATH:
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib DISPLAY=10.1.9.59:0.0;
export DISPLAY NLS_LANG=american_america.ZHS16CGB231280;

export NLS_LANG

5. 准备相应目录，如日志文件路径，归档路径，参数文件路径，数据文件准备存放路径等
$mkdir -p /u02/oradata/BOSS
$mkdir -p /u02/oradata/BOSS/arch
$mkdir -p $ORACLE_BASE/admin/BOSS
$mkdir -p $ORACLE_BASE/admin/BOSS/bdump
$mkdir -p $ORACLE_BASE/admin/BOSS/cdump
$mkdir -p $ORACLE_BASE/admin/BOSS/udump

6. 建立备用数据库参数文件主库的参数如下：
BOSS.__db_cache_size=339738624
BOSS.__java_pool_size=33554432
BOSS.__large_pool_size=4194304
BOSS.__shared_pool_size=218103808
*.background_dump_dest='/u01/app/oracle/admin/BOSS/bdump'
*.compatible='10.1.0.2.0'
*.control_files='/u02/oradata/BOSS/control01.ctl','/u02/oradata/BOSS/control02.ctl','/u02/oradata/BOSS/control03.ctl'
*.core_dump_dest='/u01/app/oracle/admin/BOSS/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='BOSS'
*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.db_writer_processes=4
*.dispatchers='(PROTOCOL=TCP) (SERVICE=BOSSXDB)'
*.global_names=FALSE
*.java_pool_size=32M
*.job_queue_processes=10
*.license_max_users=250
*.log_archive_dest_1='location=/u02/oradata/BOSS/arch'
*.log_archive_dest_2='SERVICE=dbstandby LGWR'
*.open_cursors=300
*.pga_aggregate_target=199229440
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=598736896
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/u01/app/oracle/admin/BOSS/udump'
*.utl_file_dir='/u01/app/oracle/admin/BOSS/bdump'

与主数据库不一样的参数如下：

#standby database parameter
standby_file_management=AUTO
remote_archive_enable=TRUE
standby_archive_dest='/u02/oradata/BOSS/arch'
fal_server='DBPRIMARY'
fal_client='DBSTANDBY'

7. 从主服务器拷贝口令文件到备用服务器

$cd $ORACLE_HOME/dbs/
$scp orapwBOSS 192.168.0.101: /u01/app/oracle/product/10.1.0/Db_1/dbs

8. 配置网络连接修改主服务名：
/u01/app/oracle/product/10.1.0/Db_1/network/admin/listener.ora文件如下：
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC)) )
(ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = oracle)(PORT = 1521)) ) ) )

SID_LIST_LISTENER =
(SID_LIST = (SID_DESC = (SID_NAME = PLSExtProc) (ORACLE_HOME = /u01/app/oracle/product/10.1.0/Db_1)
(PROGRAM = extproc) )
(SID_DESC = (GLOBAL_DBNAME = BOSS) (ORACLE_HOME = /u01/app/oracle/product/10.1.0/Db_1) (SID_NAME = BOSS) ) )

LISTENERDB = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.120)(PORT = 1522)) ) ) )

SID_LIST_LISTENERDB = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = BOSS)
(ORACLE_HOME = /u01/app/oracle/product/10.1.0/Db_1) (SID_NAME = BOSS) ) )

$lsnrctl start
$lsnrctl status
查看监听状态. 修改主服务器的/u01/app/oracle/product/10.1.0/Db_1/network/admin/tnsnames.ora文件如下：
BOSS = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = oracle)(PORT = 1521)) (
CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = BOSS) ) )

EXTPROC_CONNECTION_DATA = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC)) )
(CONNECT_DATA = (SID = PLSExtProc) (divSENTATION = RO) ) )

DBPRIMARY = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.120)(PORT = 1521)) )
(CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = BOSS) ) )

DBSTANDBY = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.101)(PORT = 1522)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = BOSS) ) )

修改备用服务器的 /u01/app/oracle/product/10.1.0/Db_1/network/admin/listener.ora文件如下：

LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.101)(PORT = 1521)) ) ) )
SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = BOSS) (ORACLE_HOME = /u01/app/oracle/product/10.1.0/Db_1) (SID_NAME = BOSS) ) )
LISTENERDB = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.101)(PORT = 1522)) ) ) )
SID_LIST_LISTENERDB = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = BOSS) (ORACLE_HOME = /u01/app/oracle/product/10.1.0/Db_1) (SID_NAME = BOSS) ) )

修改备用服务器的 /u01/app/oracle/product/10.1.0/Db_1/network/admin/tnsnames.ora文件如下：
DBPRIMARY = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.120)(PORT = 1521)) )
(CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = BOSS) ) )

DBSTANDBY = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.101)(PORT = 1522))
(CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = BOSS) ) )

在这里配置两个监听，一个用于主服务器到备用服务器的连接，端口是1522，另外一个用于日后的切换需要，默认端口1521。启动1522 的端口

$lsnrctl start listenerdb
$lsnrctl status listenerdb
查看1522 端口上监听的状态.
测试: 在主和备用机上分别执行 tnsping dbprimary tnsping dbstandby

9. 在主数据库创建备用服务器控制文件
alter database create standby controlfile as '/home/oracle/standby.ctl';

创建后将控制文件cp(rcp or scp)到备用数据库所在的控制文件目录下。
如$ scp control01.ctl 192.168.0.101:/u02/oradata/BOSS/
cp /u02/oradata/BOSS/control01.ctl /u02/oradata/BOSS/control02.ctl cp /u02/oradata/BOSS/control01.ctl /u02/oradata/BOSS/control03.ctl

10. 启动备用数据库
conn sys as sysdba

create spfile from pfile;
startup nomount;
alter database mount standby database;

恢复数据库:
RMAN> connect target;
RMAN> restore database;
RMAN> restore archivelog all;
如果有恢复的日志并想手工恢复，可以运行如下命令

SQL>recover automatic standby database;
如果过程中出现如下类似错误，则可以忽略 ORA-00279: change 50775 generated at 06/08/2004 21:57:21 needed for thread 1 ORA-00289:

suggestion : /u01/oracle/oradata/tbdb/archive/1_5.dbf

ORA-00280: change 50775 for thread 1 is in sequence #5
ORA-00278: log file '/u01/oracle/oradata/tbdb/archive/1_5.dbf' no longer needed for this recovery
ORA-00308: cannot open archived log '/u01/oracle/oradata/tbdb/archive/1_5.dbf'
ORA-27037: unable to obtain file status Linux Error: 2: No such file or directory Additional information:

3 因为最后需要的日志根本没有从主数据库送过来 进入到后台管理恢复状态

SQL>alter database recover managed standby database disconnect from session;

三、采用Lgwr进程传递联日志机的最大性能模式

1. 在备用数据库上创建备用日志

alter database recover managed standby database cancel;
alter database add standby LOGFILE GROUP 5 ('/u02/oradata/BOSS/stdy_redo05.log') size 10m;
alter database add standby LOGFILE GROUP 6 ('/u02/oradata/BOSS/stdy_redo06.log') size 10m;
alter database add standby LOGFILE GROUP 7 ('/u02/oradata/BOSS/stdy_redo07.log') size 10m;
alter database add standby LOGFILE GROUP 8 ('/u02/oradata/BOSS/stdy_redo08.log') size 10m;
alter database recover managed standby database disconnect from session;

2. 修改主库的归档路径
alter system set LOG_ARCHIVE_DEST_2='SERVICE=dbstandby LGWR' scope=both;
另外，如果考虑到以后该库可能被切换到备用数据库，也可以创建同样的备用日志组:

alter database add standby LOGFILE GROUP 5 ('/u02/oradata/BOSS/stdy_redo05.log') size 10m;
alter database add standby LOGFILE GROUP 6 ('/u02/oradata/BOSS/stdy_redo06.log') size 10m;
alter database add standby LOGFILE GROUP 7 ('/u02/oradata/BOSS/stdy_redo07.log') size 10m;
alter database add standby LOGFILE GROUP 8 ('/u02/oradata/BOSS/stdy_redo08.log') size 10m;

四、验证备用服务器是否工作在主库上：
create user test identified by ftp123;
grant connect,resource to test;
conn test/ftp123@primary;
create table test(name varchar2(20));
insert into test values('hi, Data Guard');
commit;
conn / as sysdba;
alter system switch logfile;

查看从库日志以只读方式打开从库查看 insert into test values('hi, Data Guard'); 已经生效。
conn / as sysdba;
alter database recover managed standby database cancel;
alter database open read only;
conn test/ftp123
select * from test;

再次设置从库在恢复模式：
alter database recover managed standby database disconnect from session;

五、日常管理
1. 备用服务器的管理模式与只读模式
（1）启动到管理模式
SQL>shutdown immediate;
SQL>startup nomount;
SQL>alter database mount standby database;
SQL>alter database recover managed standby database disconnect from session;

（2）启动到只读方式
SQL>shutdown immediate;
SQL>startup nomount;
SQL>alter database mount standby database;
SQL>alter database open read only;

（3）如果在管理恢复模式下到只读模式
SQL>recover managed standby database cancel;
SQL>alter database open read only;

这个时候，可以给数据库增加临时数据文件（这个在热备份的时候是没有备份过来的）如
alter tablespace temp add tempfile '/u02/oradata/BOSS/temp01.dbf' size 100M;
（4）从只读方式到管理恢复方式
SQL>recover managed standby database disconnect from session;

2. 备用服务器日志删除备用服务器的日志删除也必须小心，因为如果有些日志还没有被备用服务器应用而该日志被删除的话，
将引起备用数据库无法往下应用新的日志。删除备用服务器的日志的脚本为：
#!/bin/sh
# set env
cd $HOME .
.bash_profile
# start remove cd
$HOME/dbbat grep "Media Recovery Log" $ORACLE_BASE/admin/$ORACLE_SID/bdump/alert_${ORACLE_SID}.log | awk '{print $4}'|sed -e 's/^/rm /' > rmarch log.sh
chmod +x ./rmarchlog.sh
./rmarchlog.sh

cd $ORACLE_BASE/admin/$ORACLE_SID/bdump
cat alert_${ORACLE_SID}.log >>alert_${ORACLE_SID}.log.bak
echo ''>alert_${ORACLE_SID}.log rm -f ./rmarchlog.sh

3. 日志延迟检查备用服务器可能有这样的情况发生，因为日志块逻辑损坏，所以必须对日志应用进行检查，防止日志应用被停止，防患于未然，
当然我们可以手工检查，但是以下脚本则可以实现自动检查（放到cron中）
#!/bin/bash
# set env
cd $HOME .
.bash_profile
# start check
DATE=`date +%Y-%m-%d:%H:%M:%S`
filepath=/u02/oradata/$ORACLE_SID/arch/
logpath=$ORACLE_BASE/admin/$ORACLE_SID/bdump
remotefile=`ssh oracle@192.168.0.120 "ls -t /u02/oradata/BOSS/arch/*|head -1|sed -e 's/.*_1_//g' |sed -e 's/.arc$//g'"`
echo "CHECK TIME:"${DATE} echo echo "remote file : "$remotefile cd $filepath varfile=`ls -t | head -1|sed -e 's/.*_1_//g' |sed -e 's/.arc$//g'`
echo "archive file : "$filepath$varfile cd $logpath varlog=`grep "Media Recovery Log" alert_${ORACLE_SID}.log | awk '{print $4}' | tail -1 |sed -e 's/.*_1_//g' | sed -e 's/.arc$//g'` echo "applice file : "$varlog echo echo >> $HOME/dblog/check_DG_log.log
echo "CHECK TIME:"${DATE} >> $HOME/dblog/check_DG_log.log
echo >> $HOME/dblog/check_DG_log.log echo "remote file : "$remotefile >> $HOME/dblog/check_DG_log.log
echo "archive file : "$filepath$varfile >> $HOME/dblog/check_DG_log.log
echo "applice file : "$varlog >> $HOME/dblog/check_DG_log.log echo >> $HOME/dblog/check_DG_log.log

六、主库与备库的正常切换注意：
Swithover时只能先从Primary切到Standby，再从Standby切到Primary. 以下顺序不能颠倒，如果采用standby redo log的需要注意在切换前在主数据库创建同样的standby redo log。

1．切换之前先要准备init参数文件最简单的办法就是把两个数据库的文件互换，在一个机器上同时保留主数据库的初始化文件与备用数据库的初始化文件。
2. 从Primary切换到standby的脚本： [
oracle@db worksh]$ more swithstandby.sh
#!/bin/bash cd $HOME
. .bash_profile
sqlplus /nolog ALTER DATABASE REGISTER LOGFILE '/u01/oracle/oradata/tbdb/archive/1_87.dbf';
SQL>recover standby database;

（2）如果有活动日志，必须用 alter database recover managed standby database finish;
否则用 alter database recover managed standby database finish skip standby logfile;
这样切换的备用服务器可以避免最小的数据丢失和不用resetlogs，特别是对于用多个备用服务器的时候，该服务器可以马上作为主服务器而不用重新创建备用服务器。

2. 强行切换（激活）这样的切换是以激和备用服务器来完成的，在重新启动数据库的时候，备用机会 resetlogs，
这样会影响到其它备用服务器而且必须重新在主服务器上重新构造备用服务器，一般不建议这样做。

$ more activeprimary.sh
#!/bin/bash
#swith to primary with cancel
cd $HOME
. .bash_profile
#cancel and startup database sqlplus /nolog

http://ningoo.itpub.net/post/2149/233041

从Oracle10g开始，physical standby也可以临时的置于read/write状态，以便用于开发，测试以及做报表等，然后再通过flashback到先前的时间点，继续应用主库的归档。

下面通过一个实验演示整个过程：

1.设置闪回恢复区
SQL> alter system set db_recovery_file_dest_size=2G;

系统已更改。

SQL> alter system set db_recovery_file_dest='e:/oracle/back';

系统已更改。

2.取消备库的自动恢复状态
SQL> alter database recover managed standby database cancel;

数据库已更改。

3.创建一个还原点
SQL> create restore point restore_point_test guarantee flashback database;

还原点已创建。

4.在主库归档当前日志，确保前一步创建还原点的scn的归档日志已经传到备库
SQL> alter system archive log current;

系统已更改。

5.将主库到备库的归档目的地的状态设置为defer
SQL> alter system set log_archive_dest_state_2=defer;

系统已更改。

6.激活备库到read/write状态
SQL> alter database activate standby database;

数据库已更改。

SQL> alter database open;

数据库已更改。

7.此时可以在备库执行需要的读写操作
SQL> create table t1 as select * from all_objects where rownum<101;

表已创建。

SQL> drop table t1;

表已删除。

8.将数据库flashback回原来保存的还原点
SQL> startup mount force;
ORACLE 例程已经启动。

Total System Global Area 142606336 bytes
Fixed Size 1247732 bytes
Variable Size 83887628 bytes
Database Buffers 50331648 bytes
Redo Buffers 7139328 bytes
数据库装载完毕。

SQL> flashback database to restore point restore_point_test;

闪回完成。

9.转换成备库
SQL> alter database convert to physical standby;

数据库已更改。

10.将备库至于自动恢复状态
SQL> startup mount force;
ORACLE 例程已经启动。

Total System Global Area 142606336 bytes
Fixed Size 1247732 bytes
Variable Size 83887628 bytes
Database Buffers 50331648 bytes
Redo Buffers 7139328 bytes
数据库装载完毕。

http://www.itpub.net/showthread.php?s=&postid=8170201#post8170201

Subject: WAITEVENT: "PX Deq Credit: send blkd"
Doc ID: Note:271767.1 Type: REFERENCE
Last Revision Date: 21-MAY-2005 Status: PUBLISHED

"PX Deq Credit: send blkd" Reference Note
This is a reference note for the wait event "PX Deq Credit: send blkd" which includes the following subsections:
Brief definition
Individual wait details (eg: For waits seen in ) Systemwide wait details (eg: For waits seen in ) Reducing waits / wait times See Note 61998.1 for an introduction to Wait Events. Definition: @Source Symbol:kxfpdrew Versions:8.1 - 9.2 Documentation:None Waiting Process: QC,Slave The process wishes to send a message and does not have the flow control credit. Process must first dequeue a message to obtain the credit. Indicates that the receiver has not dequeued and/or completely consumed the prior message yet. "PX Deq Credit: send blkd" and "PX Deq Credit: need buffer" are nearly the same. Due to internal reason you see "PX Deq Credit: send blkd" more on local systems and "PX Deq Credit: need buffer" more on RAC systems. This is the same as the "Wait for credit - send blocked"" wait event in Oracle 8.0. Individual Waits: Parameters: P1 = sleeptime/senderid P2 = passes P3 = qref sleeptime/senderid You can decode the senderid with following SQL block set SERVEROUTPUT on undef p1 declare inst varchar(20); sender varchar(20); begin select bitand(&&p1, 16711680) - 65535 as SNDRINST, decode(bitand(&&p1, 65535),65535, 'QC', 'P'||to_char(bitand(&&p1, 65535),'fm000') ) as SNDR into inst , sender from dual where bitand(&&p1, 268435456) = 268435456; dbms_output.put_line('Instance = '||inst); dbms_output.put_line('Sender = '||sender ); end; / If you get no rows back than p1 is a sleeptime. As example senderid/sleeptime = 268501004. Than the script will give Instance = 1 Sender = P012 This means we have to investigate why slave P012 is not fast enough to dequeue the messages and send the blocks. passes Number times we have looped through waiting for a message so far. qref The identifier of the table queue from which we need the buffer. Wait Time: This is considered as idle wait event. You should investigate the sender (decode the senderid). In same cases it is normal that we see high waits on "PX Deq Credit: send blkd". An example: We do a select from a large table in parallel. select /*+ parallel(sales, 10) +/ * from sales) Than you see a lot of waits for "PX Deq Credit: send blkd" in the slave traces. All slaves wait for the QC to get back the credit bit. The QC can not dequeue fast enough the rows from the slaves, beause there are to many slaves that send rows back to the QC. In this case it is normal. Systemwide Waits: There is no general advice to reduce the waitime for this event. Reducing Waits / Wait times: There is no general advice to reduce the waitime for this event. Related: Parallel Query Wait Events Note 191103.1

参考链接：

https://twiki.cern.ch/twiki/bin/view/PSSGroup/RemoveNode

1. 停止服务：
crs_stop -all
如果使用crs_stat -t 看target字段是online，要执行
crs_stop 置为offline 。

2.
srvctl remove instance -d orcl -i orcl2
Remove instance orcl2 from the database orcl? (y/[n]) y

srvctl remove asm -n rac2

3.
srvctl remove instance -d <name> -i <inst_name> [-f]
srvctl remove service -d <name> -s <service_name> [-i <inst_name>] [-f]
srvctl remove nodeapps -n "<node_name_list>" [-f]
srvctl remove asm -n <node_name> [-i <asm_inst_name>] [-f]

./olsnodes -n
rac1 1
rac2 2

http://zwfha.itpub.net/post/20117/205632

从Oracle 9iR2 开始,ORACLE提供了表/表空间压缩技术,以减少磁盘开销,节省空间,并在某些情况下提高查询性能。

表压缩是如何工作的

在Orcle9i第2版中，表压缩特性通过删除在数据库表中发现的重复数据值来节省空间。压缩是在数据库的数据块级别上进行的。当确定一个表要被压缩后，数据库便在每一个数据库数据块中保留空间，以便储存在该数据块中的多个位置上出现的数据的单一拷贝。这一被保留的空间被称作符号表（symbol table）。被标识为要进行压缩的数据只存储在该符号表中，而不是在数据