（ASM起不来）ORA-29701: unable to connect to Cluster Synchronization Service

  ORA-29701:unable to connect to Cluster Synchronization Service

  由于CSS服务没起来，导致ASM实例起不来 。解决方法

 [grid@vm11gr2] /home/grid> sqlplus "/as sysasm"
    SQL*Plus: Release 11.2.0.1.0 Production on Sun Oct 25 10:16:21 2009

    Copyright (c) 1982, 2009, Oracle. All rights reserved.

    Connected to an idle instance.
    SQL> startup
    ORA-01078: failure in processing system parameters
    ORA-29701: unable to connect to Cluster Synchronization Service
    SQL>
    无法连接到CSS服务上.到操作系统上检查一下看看

    [grid@vm11gr2] /home/grid> crsctl check css
    CRS-4530: Communications failure contacting Cluster Synchronization Services daemon
    [grid@vm11gr2] /home/grid>
    [grid@vm11gr2] /home/grid> ps -ef|grep cssd
    果然没有CSS的服务daemon进程,再看一下HAS(High Availability Service)的状态

    [grid@vm11gr2] /home/grid> crsctl check has
    CRS-4638: Oracle High Availability Services is online

    [grid@vm11gr2] /home/grid> ps -ef|grep d.bin
    grid 5886 1 0 10:06 ? 00:00:01 /u01/app/grid/product/11.2/grid/bin/ohasd.bin reboot
    [grid@vm11gr2] /home/grid>
    发现HAS的服务确实启动了的,而ora.cssd和ora.diskmon这2个服务是依赖于HAS维护的.
  

    进一步查看各资源的状态
    [grid@vm11gr2] /home/grid> crs_stat -t
    Name                Type                 Target    State Host
    --------------------------------------------------------------
    ora.FLASH_DATA.dg   ora.diskgroup.type   OFFLINE OFFLINE vm11gr2
    ora.SYS_DATA.dg     ora.diskgroup.type   OFFLINE OFFLINE vm11gr2
    ora.asm             ora.asm.type         OFFLINE OFFLINE vm11gr2
    ora.cssd            ora.cssd.type        OFFLINE OFFLINE vm11gr2
    ora.diskmon         ora.diskmon.type     OFFLINE OFFLINE vm11gr2
    [grid@vm11gr2] /home/grid>
  
    [grid@vm11gr2] /home/grid> crsctl status resource -t
    --------------------------------------------------------------------------------
    NAME               TARGET          STATE        SERVER     STATE_      DETAILS
    --------------------------------------------------------------------------------
    Local Resources
    --------------------------------------------------------------------------------
    ora.FLASH_DATA.dg
                      OFFLINE          OFFLINE     vm11gr2
    ora.SYS_DATA.dg 

                      OFFLINE          OFFLINE     vm11gr2
    ora.asm         

                      OFFLINE          OFFLINE     vm11gr2
    --------------------------------------------------------------------------------
    Cluster Resources
    --------------------------------------------------------------------------------
    ora.cssd       

            1         OFFLINE          OFFLINE
    ora.diskmon    

            1         OFFLINE          OFFLINE

    再看一下ora.cssd和ora.diskmon的属性
    [grid@vm11gr2] /home/grid> crs_stat -p ora.cssd
    NAME=ora.cssd
    TYPE=ora.cssd.type
    ACTION_SCRIPT=
    ACTIVE_PLACEMENT=0
    AUTO_START=never
    CHECK_INTERVAL=30
    DESCRIPTION="Resource type for CSSD"
    FAILOVER_DELAY=0
    FAILURE_INTERVAL=3
    FAILURE_THRESHOLD=5
    HOSTING_MEMBERS=
    PLACEMENT=balanced
    RESTART_ATTEMPTS=5
    SCRIPT_TIMEOUT=600
    START_TIMEOUT=600
    STOP_TIMEOUT=900
    UPTIME_THRESHOLD=1m

    [grid@vm11gr2] /home/grid> crs_stat -p ora.diskmon
    NAME=ora.diskmon
    TYPE=ora.diskmon.type
    ACTION_SCRIPT=
    ACTIVE_PLACEMENT=0
    AUTO_START=never
    CHECK_INTERVAL=20
    DESCRIPTION="Resource type for Diskmon"
    FAILOVER_DELAY=0
    FAILURE_INTERVAL=3
    FAILURE_THRESHOLD=5
    HOSTING_MEMBERS=
    PLACEMENT=balanced
    RESTART_ATTEMPTS=10
    SCRIPT_TIMEOUT=60
    START_TIMEOUT=60
    STOP_TIMEOUT=60
    UPTIME_THRESHOLD=5s
    [grid@vm11gr2] /home/grid>
    到这里基本就找到了原因了,可以看到这两个资源的AUTO_START属性默认都设置为never,也就是说他们不会随着HAS服务的启动而自动启动的,
尽管默认情况下HAS服务是开机自动启动的.好了，那我们就手动启动一下吧:
    [grid@vm11gr2] /home/grid> crsctl start resource ora.cssd
    CRS-2672: Attempting to start 'ora.cssd' on 'vm11gr2'
    CRS-2679: Attempting to clean 'ora.diskmon' on 'vm11gr2'
    CRS-2681: Clean of 'ora.diskmon' on 'vm11gr2' succeeded
    CRS-2672: Attempting to start 'ora.diskmon' on 'vm11gr2'
    CRS-2676: Start of 'ora.diskmon' on 'vm11gr2' succeeded
    CRS-2676: Start of 'ora.cssd' on 'vm11gr2' succeeded
    [grid@vm11gr2] /home/grid>

    注:ora.cssd和ora.diskmon这两个服务是有依赖关系的，启动哪个都会把两个都起来.

    [grid@vm11gr2] /home/grid> crs_stat -t
    Name                Type                 Target    State Host
    --------------------------------------------------------------
    ora.FLASH_DATA.dg   ora.diskgroup.type   OFFLINE OFFLINE vm11gr2
    ora.SYS_DATA.dg     ora.diskgroup.type   OFFLINE OFFLINE vm11gr2
    ora.asm             ora.asm.type         OFFLINE OFFLINE vm11gr2
    ora.cssd            ora.cssd.type        ONLINE  ONLINE  vm11gr2
    ora.diskmon         ora.diskmon.type     ONLINE  ONLINE  vm11gr2
    [grid@vm11gr2] /home/grid>
    CSS服务起来了，重启动asm instance 

    [grid@vm11gr2] /home/grid> sqlplus "/as sysasm"

    SQL*Plus: Release 11.2.0.1.0 Production on Sun Oct 25 10:30:03 2009

    Copyright (c) 1982, 2009, Oracle. All rights reserved.

    Connected to an idle instance.

    SQL> startup
    ASM instance started

    Total System Global Area 284565504 bytes
    Fixed Size 1336036 bytes
    Variable Size 258063644 bytes
    ASM Cache 25165824 bytes
    ASM diskgroups mounted
    SQL> exit
    Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
    With the Automatic Storage Management option
    [grid@vm11gr2] /home/grid> crs_stat -t
    Name                Type                 Target    State Host
    --------------------------------------------------------------
    ora.FLASH_DATA.dg   ora.diskgroup.type   ONLINE  ONLINE  vm11gr2
    ora.SYS_DATA.dg     ora.diskgroup.type   ONLINE  ONLINE  vm11gr2
    ora.asm             ora.asm.type         ONLINE  ONLINE  vm11gr2
    ora.cssd            ora.cssd.type        ONLINE  ONLINE  vm11gr2
    ora.diskmon         ora.diskmon.type     ONLINE  ONLINE  vm11gr2
    [grid@vm11gr2] /home/grid>

    tips

      1)默认情况下HAS(High Availability Service)是自动启动的.通过如下命令可以取消和启用自动启动
        crsctl disable has
        crsctl enable has
      2)HAS手动启动和停止
        crsctl start has
        crsctl stop has
      3)查看HAS的状态
        crsctl check has
      4)如果想让ora.css和ora.diskmon服务随着HAS的启动而自动启动，那么你可以这两个服务的AUTO_START属性
        crsctl modify resource "ora.cssd" -attr "AUTO_START=1"
        or
        crsctl modify resource "ora.diskmon" -attr "AUTO_START=1"
      5)如果想取消ora.css和ora.diskmon的Auto start
        crsctl modify resource "ora.cssd" -attr "AUTO_START=never"
        crsctl modify resource "ora.diskmon" -attr "AUTO_START=never"

exp/imp transport tablespace(传输表空间)

使用exp imp
transport tablespace
与使用expdp/impdp是步骤一样的 语法上有些不一样

限制条件与expdp/impdp一样 (查看expdp/impdp transport tablespace)

将db xh tablespace smallts transport 到db orcl
SQL> conntr/a123@xh
已连接。
SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
UNDOTBS1
SYSAUX
TEMP
USERS
EXAMPLE
RMAN_TS
XHTEST
XHTR
OUTLINE_TS
SMALLTS

已选择11行。

SQL> select count(*) from t1;

COUNT(*)
----------
0

SQL> insert into t1 values(1);

已创建 1 行。

SQL> select count(*) from t1;

COUNT(*)
----------
1

SQL> commit;

提交完成。

SQL> select tablespace_name from user_tables where table_name='T1';

TABLESPACE_NAME
------------------------------
USERS

SQL> alter table t1 move tablespace smallts;

表已更改。

SQL> select tablespace_name from user_tables where table_name='T1';

TABLESPACE_NAME
------------------------------
SMALLTS

SQL> execute sys.dbms_tts.transport_set_check('smallts',true,true);

PL/SQL 过程已成功完成。

SQL> select * from sys.transport_set_violations;

VIOLATIONS
--------------------------------------------------------------------------------
Index TR.T1_IND in tablespace USERS points to table TR.T1 in tablespace SMALLTS

发现t1_ind 在USER TABLESPACE 与表不在同一个表空间
(expdp的话用TRANSPORT_FULL_CHECK 设置为N 可以导出时候不报告错误 成功导出 不过只导出INDEX 所在表空间 不导出依赖的表所在表空间还是会报错,impdp时也一样)(exp/imp时用 TTS_FULL_CHECK 一样) 在这里 我们还是手动解决下这个问题

SQL> alter index t1_IND REBUilD TABLESPACE smallts
2 ;

索引已更改。

SQL> execute sys.dbms_tts.transport_set_check('smallts',true,true);

PL/SQL 过程已成功完成。

SQL> select * from sys.transport_set_violations;

未选定行~~~~~~~~~~~~~~~~~可以了

C:\>set oracle_sid=orcl
C:\>sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on 星期二 9月 29 11:43:05 2009

Copyright (c) 1982, 2005, Oracle. All rights reserved.

连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> select name from v$database;

NAME
---------
ORCL
SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
UNDOTBS1
SYSAUX
TEMP
USERS
EXAMPLE
TEST
RMAN_TS
TESTXH
XHTEST
XHTR

已选择11行。

SQL> host exp'sys/a831115@xhas sysdba' transport_tablespace=y file=d:\exp\tablesp
ace.dmp tablespaces=smallts

Export: Release 10.2.0.1.0 - Production on 星期二 9月 29 11:47:43 2009

Copyright (c) 1982, 2005, Oracle. All rights reserved.

连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
已导出 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集
注: 将不导出表数据 (行)
即将导出可传输的表空间元数据...
EXP-00008: 遇到 ORACLE 错误 29335
ORA-29335: 表空间 'SMALLTS' 不为只读~~~~~~~~必须要 read only
ORA-06512: 在 "SYS.DBMS_PLUGTS", line 507
ORA-06512: 在 line 1
EXP-00000: 导出终止失败

另外必须 sysdba connect(EXP-00044: 必须以 'SYSDBA' 身份连接, 才能进行时间点恢复或可传输的表空间导入)
SQL> alter tablespace smallts read only;

表空间已更改。

SQL> host exp'sys/a831115@xhas sysdba' transport_tablespace=y file=d:\exp\tablesp
ace.dmp tablespaces=smallts

Export: Release 10.2.0.1.0 - Production on 星期二 9月 29 11:48:10 2009

Copyright (c) 1982, 2005, Oracle. All rights reserved.

连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
已导出 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集
注: 将不导出表数据 (行)
即将导出可传输的表空间元数据...
对于表空间 SMALLTS...
. 正在导出簇定义
. 正在导出表定义
. . 正在导出表 T1
. 正在导出引用完整性约束条件
. 正在导出触发器
. 结束导出可传输的表空间元数据
成功终止导出, 没有出现警告。

SQL> host copy d:\xhdatafile\smallfile.dbf E:\orcldatafile\smallfile.dbf
已复制 1 个文件。

SQL> alter tablespace smallts read write
2 ;

表空间已更改。

orcl db
SQL> host imp '/ as sysdba' transport_tablespace=y file=d:\exp\tablespace.dmp data
files=e:\orcldatafile\smallfile.dbf fromuser=tr touser=system

Import: Release 10.2.0.1.0 - Production on 星期二 9月 29 11:54:31 2009

Copyright (c) 1982, 2005, Oracle. All rights reserved.

连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

经由常规路径由 EXPORT:V10.02.01 创建的导出文件
即将导入可传输的表空间元数据...
已经完成 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集中的导入
. 正在将 TR 的对象导入到 SYSTEM
. . 正在导入表 "T1"
成功终止导入, 没有出现警告。

SQL> conn system/a831115
已连接。
SQL> select count(*) from t1;

COUNT(*)
----------
1

SQL> select tablespace_name from user_tables where table_name='T1';

TABLESPACE_NAME
------------------------------
SMALLTS

SQL> select file_name from dba_data_files where tablespace_name='SMALLTS';

FILE_NAME
--------------------------------------------------------------------------------
E:\ORCLDATAFILE\SMALLFILE.DBF

Creating a Recovery Catalog

This is an excerpt from the bestselling book Oracle Grid & Real Application Clusters.  To get immediate access to the code depot of working RAC scripts, buy it directly from the publisher and save more than 30%.

---

RMAN can be used either with or without a recovery catalog. A recovery catalog is a schema stored in a database that tracks backups and stores scripts for use in RMAN backup and recovery situations. Generally, an experienced DBA would suggest that the Enterprise Manager instance schema and RMAN catalog schema be placed in the same utility database on a server separate from the main servers. The RMAN schema generally only requires 15 megabyte per year per database backed up.

The RMAN schema owner is created in the RMAN database using the following steps:

• 1. Start SQL*Plus and connect as a user with administrator privileges to the database containing the recovery catalog. For example, enter:

CONNECT SYS/oracle@catdb AS SYSDBA

• 2. Create a user and schema for the recovery catalog. For example, enter:

CREATE USER rman IDENTIFIED BY cat
  TEMPORARY TABLESPACE temp
  DEFAULT TABLESPACE tools
  QUOTA UNLIMITED ON tools;

• 3. Grant the recovery_catalog_owner role to the user. This role provides all of the privileges required to maintain and query the recovery catalog:

SQL> GRANT RECOVERY_CATALOG_OWNER TO rman;

Once the owner user is created, the RMAN recovery catalog schema can be added:

• 1. Connect to the database that contains the catalog owner. For example, using the RMAN user from the above example, enter the following from the operating system command line.  The use of the CATALOG keyword tells Oracle this database contains the repository:

% rman CATALOG rman/cat@catdb

• 2. It is also possible to connect from the RMAN utility prompt:

% rman

RMAN> CONNECT CATALOG rman/cat@catdb

• 3. Now, the CREATE CATALOG command can be run to create the catalog. The creation of the catalog may take several minutes. If the catalog tablespace is this user's default tablespace, the command would look like the following:

CREATE CATALOG;

While the RMAN catalog can be created and used from either a 9i or 10g database, the Enterprise Manager Grid Control database must be a 9i database.  This is true at least for release 1, although this may change with future releases.

Each database that the catalog will track must be registered.

• Registering a Database with RMAN

The following process can be used to register a database with RMAN:

• 1. Make sure the recovery catalog database is open.

• 2. Connect RMAN to both the target database and recovery catalog database. For example, with a catalog database of RMANDB and user RMAN, owner of the catalog schema, and the target database, AULT1, which is the database to be backed up, database user SYS would issue:

% rman TARGET sys/oracle@ault1 CATALOG rman/cat@rmandb

• 3. Once connected, if the target database is not mounted, it should be opened or mounted:

RMAN> STARTUP;

--or--

RMAN> STARTUP MOUNT;

• 4. If this target database has not been registered, it should be registered it in the connected recovery catalog:

RMAN> REGISTER DATABASE;

The database can now be operated on using the RMAN utility.

• Example RMAN Operations

The following is an example of the command line connection to a RAC environment, assuming the RAC instances are AULT1 and AULT2:

$ rman TARGET SYS/kr87m@ault2 CATALOG rman/cat@rmandb

The connection string, in this case AULT2, can only apply to a single instance, so the entry in the tnsnames.ora for the AULT2 connection would be:

ault2 =
  (DESCRIPTION =
    (ADDRESS_LIST =
    (LOAD_BALANCE = OFF)
    (FAILOVER = ON)
      (ADDRESS = (PROTOCOL = TCP)(HOST = aultlinux2)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = ault)
      (INSTANCE_NAME = ault2)
    )

• If the instances use archive logs, RAC requires that a channel connection be specified for each instance that will resolve to only one instance. For example, using the AULT1 and AULT2 instances from the previous example:

CONFIGURE DEFAULT DEVICE TYPE TO sbt;
CONFIGURE DEVICE TYPE TO sbt PARALLELISM 2;
CONFIGURE CHANNEL 1 DEVICE TYPE sbt CONNECT = 'SYS/kr87m@ault1';
CONFIGURE CHANNEL 2 DEVICE TYPE sbt CONNECT = 'SYS/kr87m@ault2';

• This configuration only has to be specified once for a RAC environment. It should be changed only if nodes are added or removed from the RAC configuration. For this reason, it is known as a persistent configuration, and it need never be changed for the life of the RAC system. This configuration requires that each of the specified nodes be open, the database is operational, or closed, the database shutdown. If one specified instance is not in the same state as the others, the backup will fail.

RMAN is also aware of the node affinity of the various database files. The node with the greatest access will be used to backup those datafiles that the instance has greatest affinity for. Node affinity can, however, be overridden with manual commands, as follows:

BACKUP
            #Channel 1 gets datafiles 1,2,3
            (DATAFILE 1,2,3 CHANNEL ORA_SBT_TAPE_1)
            #Channel 2 gets datafiles 4,5,6,7
            (DATAFILE 4,5,6,7 CHANNEL ORA_SBT_TAPE2)

The nodes chosen to backup an Oracle RAC cluster must have the ability to see all of the files that require backup. For example:

BACKUP DATABASE PLUS ARCHIVELOG;

The specified nodes must have access to all archive logs generated by all instances. This could entail some special considerations when configuring the Oracle RAC environment.

The essential steps for using RMAN in Oracle RAC are:

* Configure the snapshot control file location.

* Configure the control file autobackup feature.

* Configure the archiving scheme.

* Change the archivemode of the database, although this is optional.

* Monitor the archiver process.

The following section will show how the snapshot control file location is configured.

定制log组的数量和大小

根据 统计事务量 来确定 日志组数
下列语句可以 统计事务量
select to_char(first_time,'YYYY-MM-DD HH24') v_time, count(*）from v$log_history
group by to_char(first_time, 'YYYY-MM-DD HH24') order by v_time desc

看一小时 切换 几个log 一小时 切换 不能多余5次，否则影响性能

加大日志文件 也可以 ， 但是当库坏，会丢的数据多些

redo 的 组多， 是为 避免 redo 一圈 使用完，要覆盖时， 却发现不能覆盖，要覆盖的还没归档
redo 的 大小：为了解决 checkpoint 数量过多 checkpoint 数量 每小时 5次， 越多越影响性能

++++++++++++++++++++++
关于影响性能说明：
1、
日志切换太频繁，那么就会引起数据写很频繁，那么就会影响性能
（日志切换就会产生checkpoint,产生了checkpoint那么就会写数据）

2、
checkpoint是把内存中的脏数据写到磁盘
checkpoint是一个数据库事件，它将已修改的数据从高速缓存刷新到磁盘，并更新控制文件和数据文件
如果checkpoint越大(即log_checkpoint_interval值)，一次写磁盘的数据

也会越多。另外，内存中的脏数据也会越多，而且这些脏数据部分占用了内存空间，其他可用的空间就会更少。
还有，checkpoint越大，数据库意外停止后的启动时，需要恢复的过程也会越长。
++++++++++++++++++++++

checkpoint ： date block 从 memeber --> disk 同步

redo 的 越大， checkpoint 数量越少（当 redo切换时，会做一次 checkpoint）

select a.member,a.group#,b.thread#,b.bytes,b.members,b.status from v$logfile a,v$log b where a.group#=b.group# order by a.group#;

6 rows selected.

如果看到将要当前使用日志组的下一组状态为active,那说明可能发生要切

换日志了但是日志还没归档完毕（active说明正在归档），所以有必要增

加一组日志来避免这种情况
也可以增大日志文件，但是增大日志文件会增大数据库的风险

获取更新的行数

一:在pl/sql下面,你可以用sql%rowcount;

举例：

1。 SQL> set serveroutput on
SQL> begin
2 update testa set salary=2600 where first_name='ccc' and rownum<2;
3 dbms_output.put_line(to_char(SQL%ROWCOUNT));
4 END;
5 /
1
PL/SQL 过程已成功完成。

举例
SQL> ed
Wrote file afiedt.buf

  1  begin
  2  update t5 set deptno=90 where deptno=30;
  3  dbms_output.put_line(to_char(sql%rowcount));
  4* end;
SQL> /
8

PL/SQL procedure successfully completed.

SQL> rollback
  2  ;

Rollback complete.

oracle 的 copy 命令

在数据表间复制数据是Oracle DBA经常面对的任务之一，Oracle为这一任务提供了多种解决方案，SQL*Plus Copy 命令便是其中之一。SQL*Plus Copy 命令通过SQL*Net在不同的表(同一服务器或是不同服务器)之间复制数据或移动数据。
在实际运行环境中若能恰当地选择使用SQL*Plus Copy 命令可以有效地提高数据复制的性能。
下面将简要介绍SQL*Plus Copy 命令使用，并在性能方面与其他两种方案进行对比，力求能提供一个使用Copy 命令的方案参考。
1.语法及使用说明
1.1 语法
下面我们来看一下SQL*Copy 命令的语法及使用说明。
在解释SQL*Plus Copy 命令的语法之前，我们必须要明确SQL*Plus Copy 命令不是一个方法或是函数，也不是一个SQL语句，它是一个命令(command)，当然这个命令必须在SQL*Plus里运行。

中国网管联盟bitsCN.com

SQL*Plus Copy 命令的语法：
网管联盟bitsCN_com

COPY {FROM database | TO database | FROM database TO database} {APPEND|CREATE|INSERT|REPLACE} destination_table [(column, column, column, ...)]
USING query

我们分部分来解释一下：

• COPY – 这个不太需要解释，主命令，声明要执行COPY操作

From Database – 源数据库
To Database – 目标数据库
此处注意花括号中有三种可选的写法(以”|”隔开)，如果源数据表和目标数据表在同一个Schema中，则可以只写From Database，也可以只写To Database，当然还可以是第三种写法，把From Database和To Database写全。但如果源数据表和目标数据表不在同一个Schema中，则必须用第三种写法，即把From Database和To Database都写全
From Database和To Database的格式是一样的：USERID/PASSWORD@SID，这个大家都应该很熟悉了。 网管网www_bitscn_com

{APPEND|CREATE|INSERT|REPLACE} – 声明操作数据的方式，下面分别解释一下：

• Append – 向已有的目标表中追加记录，如果目标表不存在，自动创建，这种情况下和Create等效。

• Create – 创建目标表并且向其中追加记录，如果目标表已经存在，则会返回错误。

• Insert – 向已有的目标表中插入记录，与Append不同的是，如果目标表不存在，不自动创建而是返回错误。

• Replace – 用查询出来的数据覆盖已有的目标表中的数据，如果目标表不存在，自动创建。

• destination_table – 目标表的名字

• [(column, column, column, ...)] – 可以指定目标表中列的名字，如果不指定，则自动使用Query中的列名。

• USING query – 查询语句，交流的数据来自这儿。

1.2 使用范例
下面我们通过几个例子来看一下SQL*Plus Copy 命令的使用：

1.2.1 在同一个服务器的同一个Schema中复制数据：
同时指定From database和To database
SQL> copy from scott/tiger@orcl to scott/tiger@orcl create dept1 using select * from dept;
只指定From Database
SQL> copy from scott/tiger@orcl create dept2 using select * from dept; 中国网管联盟bitsCN.com
只指定To Database
SQL> copy to scott/tiger@orcl create dept3 using select * from dept;

1.2.2 在同一个服务器的不同Schema中复制数据：
这种情况下必须同时指定From Database和To Database
SQL> copy from scott/tiger@orcl to u1/u1@orcl create dept using select * from dept;
注意这种情况下，using select * from dept 中并不需要使用scott.demp的形式。

1.2.3 在不同的服务器间复制数据：
SQL> conn u1/u1@sunserve
已连接。
SQL> copy from scott/tiger@orcl to u1/u1@sunserve create dept using select * from dept;
2.4 性能实验结果
实验数据：
记录数：5,082,500
数据量：504M
实验结果

方案---------------------执行时间(秒) ---------Undo(M) ------Redo(M)
Copy command ------------- 520.51--------------0 ----------- 592
Insert into…select …----   631.64 ------------345 ---------- 1720

网管论坛bbs_bitsCN_com

Create Table…------------ 244.79 -------------0 ------------ 515

2.总结

Create Table…as select…是最快的，而且生成的Undo和Redo信息最少，所以只要可能，请尽量使用这种方案。但这种方案有一定的限制，即目标表必须是不存在的，不能用它向已有的目标表中追加记录。
Insert into … select … 是最慢的，而且生成最多的Undo和Redo信息，对I/O的压力最大，优势在于大家对它比较熟悉，使用起来比较简单，适合于处理少量的数据，若要处理大量的数据，不推荐使用这种方案。
Copy Command可以处理Create Table不能处理的情况，即向已有的数据表中追加记录，相对于insert来说，效率更高一些，生成更少的Redo信息，不生成Undo信息，所以在执行大量的数据追加时，推荐使用Copy Command命令。

监听由动态改成静态

2.若不是unknow 状态则用以下方法修改

a)    sys连接到库 SQL> show parameter names 得到global_names和instrance_name(sid_name)的值。
b)    打开listener.ora 加入 如：

SID_LIST_LISTENER =
(SID_LIST =
  (SID_DESC =
   (SID_NAME = PLSExtProc)
   (ORACLE_HOME = /export/home/11.2.0/grid/11.2.0)
   (PROGRAM = extproc)

--(ENVS = EXTPROC_DLLS = /xxx/ArcSDE/bst_shapelib.so")

    )
(SID_DESC =
  (SID_NAME = ORCL)
  (ORACLE_HOME = /export/home/11.2.0/grid/11.2.0)
  (GLOBAL_DBNAME=ORCL.EXAMPLE.COM)
  )
)

注意:

1.其中xxx代表oracle安装目录 。
2.这里的global_dbname=ORCL.EXAMPLE.COM ，SID_NAME=ORCL
这个SID_NAME 应与你对外提供服务的 $ORACLE_SID 一致

[oracle@luck~]$ echo $ORACLE_SID  得到ORCL

c) 打开 tnsnames.ora 加入

jingtai=
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = LUCKY)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORCL.EXAMPLE.COM)
    )
  )

EXTPROC_CONNECTION_DATA = 

  (DESCRIPTION = 

    (ADDRESS_LIST = 

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

     )

    (CONNECT_DATA =

       (SID = PLSExtProc)

       (PRESENTATION = RO)

    )

)
注意：

1.  这里的服务名为 ORCL.EXAMPLE.COM 而不是通常的 SID的ORCL，因为在 listener.ora 中已经注册了 ORCL.EXAMPLE.COM ,lsnrctl 启动时会监听 ORCL.EXAMPLE.COM ，并对

应到 SID_NAME=ORCL 上。
2.  HOST为对应的目标主机的主机名。
d) 停止、启动监听和服务
全部配置完成最后，重启数据库实例和监听