不用安装oracle客户端也可以用plsql连接远程oracle

不用安装oracle客户端也可以用plsql连接远程oracle
关键字: plsql连接远程oracle
每次问人家，plsql 可不可以直接远程连接服务器，他们都说要安装客户端，记得以前用过个什么sql deveplore 什么的工具，不用安装客户端的，而且是绿色软件来的，我就想，一定就办法，不用这么麻烦安装客户端的。上网找了找，下面把我的实践详细过程记录下来，希望对大家有帮助，有什么不对的，请大家多多评论，指出来。
1、在安装ORACLE服务器的机器上搜索下列文件，
oci.dll
ocijdbc10.dll
ociw32.dll
orannzsbb10.dll
oraocci10.dll
oraociei10.dll
sqlnet.ora
tnsnames.ora
classes12.jar
ojdbc14.jar
放到一个文件夹,如 oraclient
2、配置tnsnames.ora，如果本机上没有安装oracle，
可以从安装了oracle的机上拷贝一个(tnsnames.ora文件在%ORACLE_HOME%/network/admin下)放在上面的目录D:/oraclient下。
oracledata =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.58)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = oracledata)
)
)

3、添加一个环境变量，名为TNS_ADMIN，值为tnsnames.ora文件所在路径（如：D:/oraclient，特别是重装后或其它操作，忘了TNS_ADMIN变量，plsql登陆就会报无法解析指定的连接标识符)，这是为了能够找到上面说的tnsnames.ora。如果本机上安装了ORACLE，并且设置了ORACLE_HOME环境变量，那么会自动在%ORACLE_HOME%/network/admin/位置查找tnsnames.ora文件。

4、设置ORACLE的语言，添加环境变量：
“NLS_LANG = SIMPLIFIEDCHINESE_CHINA.ZHS16GBK”，(AMERICAN_AMERICA.US7ASCII 是ASCII编码类型，其它类型可自己到服务器看一下或网上查找一下)

oracle 监听静态注册举例解析

1、在 $ORACLE_HOME/network/admin/listener.ora 文件中加入一个静态注册的节点

• [oracle@guohui6 oracle]$ cd $ORACLE_HOME/network/admin

• [oracle@guohui6 admin]$ vi listener.ora

# listener.ora Network Configuration File: /mydatafile2/app/oracle/oracle/product/10.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
(SID_LIST =
  (SID_DESC =
   (SID_NAME = PLSExtProc)
   (ORACLE_HOME = /mydatafile2/app/oracle/oracle/product/10.2.0/db_1)
   (PROGRAM = extproc)
)

(SID_DESC =
  (SID_NAME = ORCL)
  (ORACLE_HOME = /mydatafile2/app/oracle/oracle/product/10.2.0/db_1)
  (GLOBAL_DBNAME=HJD.COM.CN)
  )
)

LISTENER =
(DESCRIPTION_LIST =
  (DESCRIPTION =
   (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
   (ADDRESS = (PROTOCOL = TCP)(HOST = guohui6)(PORT = 1521))
  )
)

注意这里的global_dbname=HJD.COM.CN

SID_NAME=ORCL

这个SID_NAME 应与你对外提供服务的 $ORACLE_SID 一致

[oracle@guohui6 admin]$ echo $ORACLE_SID
ORCL


2. 



配置对应的tnsnames.ora 中的节点


[oracle@guohui6 admin]$ vi tnsnames.ora
# tnsnames.ora Network Configuration File: 

/mydatafile2/app/oracle/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
ORCL=
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = guohui6)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORCL)
    )
  )

GUOHUIORCL=
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = guohui6)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = HJD.COM.CN)
    )
  ) 



tnsname GUOHUIORCL 中的 SERVICE_NAME=HJD.COM.CN 




这里的服务名为 HJD.COM.CN 而不是通常的 ORCL，因为在 listener.ora 中已经注册了 HJD.COM.CN,lsnrctl 
启动时会监听 HJD.COM.CN ，并对应到 SID_NAME=ORCL 上。

3、启动监听和服务



[oracle@guohui6 oracle]$ cat dbstart
lsnrctl start
sqlplus /nolog <<EOF
connect /as sysdba
startup
EOF
[oracle@guohui6 oracle]$ ./dbstart
LSNRCTL for Linux: 

Version 10.2.0.1.0 - Production on 13-FEB-2011 20:11:15
Copyright (c) 1991, 2005, Oracle.  All rights reserved.
Starting /mydatafile2/app/oracle/oracle/product/10.2.0/db_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 10.2.0.1.0 - ProductionSystem parameter  

file is /mydatafile2/app/oracle/oracle/product/10.2.0/db_1/network/admin/listener.oraLog messages written 

to /mydatafile2/app/oracle/oracle/product/10.2.0/db_1/network/log/listener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=guohui6)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
STATUS of the LISTENER------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 10.2.0.1.0 - Production
Start Date                13-FEB-2011 20:11:15
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /mydatafile2/app/oracle/oracle/product/10.2.0/db_1/network/admin/listener.ora
Listener Log File         /mydatafile2/app/oracle/oracle/product/10.2.0/db_1/network/log/listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=guohui6)(PORT=1521)))
Services Summary...
Service "HJD.COM.CN" has 1 instance(s).
  Instance "ORCL", status UNKNOWN, has 1 handler(s) for this service...
Service "ORCL" has 1 instance(s).
  Instance "ORCL", status UNKNOWN, has 1 handler(s) for this service...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

SQL*Plus: Release 10.2.0.1.0 - Production on Sun Feb 13 20:11:16 2011
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
SQL> Connected to an idle instance.
SQL> ORA-32004: obsolete and/or deprecated parameter(s) specified
ORACLE instance started.
Total System Global Area  461373440 bytes
Fixed Size                  1220000 bytes
Variable Size              75498080 bytes
Database Buffers          381681664 bytes
Redo Buffers                2973696 bytes
Database mounted.
Database opened.
SQL> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options





可以看到  


Service "HJD.COM.CN" has 1 instance(s).
  Instance "ORCL", status UNKNOWN, 
has 1 handler(s) for this service...


正在被监听。

4、验证该服务可以到达

[oracle@guohui6 oracle]$ tnsping GUOHUIORCL
TNS Ping Utility for Linux: 

Version 10.2.0.1.0 - Production on 13-FEB-2011 20:14:59
Copyright (c) 1997, 2005, Oracle.  All rights reserved.
Used parameter files:
/mydatafile2/app/oracle/oracle/product/10.2.0/db_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = guohui6)(PORT = 1521)) 

(CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = HJD.COM.CN)))
OK (10 msec)


5.利用静态注册的服务登入oracle 



[oracle@guohui6 oracle]$ sqlplus tina/panda@guohuiorcl
SQL*Plus: Release 10.2.0.1.0 - Production on Sun Feb 13 20:17:27 2011
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> select count(*) from date_log;

  COUNT(*)----------
      3998

SQL> 

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

http://www.cnblogs.com/kelantas/archive/2011/02/14/1953992.html 

表空占用情况

SELECT a.tablespace_name, round(a.bytes / 1024 / 1024) "Sum MB",
       round((a.bytes - b.bytes) / 1024 / 1024) "used MB",
       round(b.bytes / 1024 / 1024) "free MB",
       round(((a.bytes - b.bytes) / a.bytes) * 100, 2) "percent_used"
  FROM (SELECT tablespace_name, SUM(bytes) bytes
           FROM dba_data_files
          GROUP BY tablespace_name) a,
       (SELECT tablespace_name, SUM(bytes) bytes, MAX(bytes) largest
           FROM dba_free_space
          GROUP BY tablespace_name) b
 WHERE a.tablespace_name = b.tablespace_name(+)
 ORDER BY ((a.bytes - b.bytes) / a.bytes) DESC;



select t.owner, t.segment_name, sum(t.BYTES) / 1024 / 1024 as size_M
  from dba_segments t
 where t.tablespace_name = ''
 group by t.owner, t.segment_name
 order by 3 desc;

内在地址占用

SELECT DECODE(T.GRANTYPE,
              1,
              'shared pool',
              2,
              'large pool',
              3,
              'java pool',
              4,
              'streams pool',
              5,
              'free',
              6,
              'DEFAULT buffer cache',
              7,
              'KEEP buffer cache',
              8,
              'RECYCLE buffer cache',
              9,
              'DEFAULT 2K buffer cache',
              1,
              'DEFAULT 4K buffer cache',
              1,
              'DEFAULT 8K buffer cache',
              T.GRANTYPE),
       T.granstate,
       t.baseaddr addr,
       t.gransize
  FROM X$KSMGE T
  order by 3;
------------------------------------------------------------
SQL> set pages 1000
SQL> /

DECODE(T.GRANTYPE,1,'SHAREDPOOL',2,'LARG GRANSTATE        ADDR               GRANSIZE
---------------------------------------- ---------------- ---------------- ----------
0                                        INVALID          000007FF00000000   16777216
0                                        INVALID          000007FF01000000   16777216
0                                        INVALID          000007FF02000000   16777216
0                                        INVALID          000007FF03000000   16777216
0                                        INVALID          000007FF04000000   16777216
0                                        INVALID          000007FF05000000   16777216
0                                        INVALID          000007FF06000000   16777216
0                                        INVALID          000007FF07000000   16777216
0                                        INVALID          000007FF08000000   16777216
0                                        INVALID          000007FF09000000   16777216
0                                        INVALID          000007FF0A000000   16777216
0                                        INVALID          000007FF0B000000   16777216
0                                        INVALID          000007FF0C000000   16777216
0                                        INVALID          000007FF0D000000   16777216
0                                        INVALID          000007FF0E000000   16777216
0                                        INVALID          000007FF0F000000   16777216
0                                        INVALID          000007FF10000000   16777216
0                                        INVALID          000007FF11000000   16777216
0                                        INVALID          000007FF12000000   16777216
0                                        INVALID          000007FF13000000   16777216
0                                        INVALID          000007FF14000000   16777216
0                                        INVALID          000007FF15000000   16777216
0                                        INVALID          000007FF16000000   16777216
0                                        INVALID          000007FF17000000   16777216
0                                        INVALID          000007FF18000000   16777216
0                                        INVALID          000007FF19000000   16777216
0                                        INVALID          000007FF1A000000   16777216
0                                        INVALID          000007FF1B000000   16777216
0                                        INVALID          000007FF1C000000   16777216
0                                        INVALID          000007FF1D000000   16777216
0                                        INVALID          000007FF1E000000   16777216
0                                        INVALID          000007FF1F000000   16777216
0                                        INVALID          000007FF20000000   16777216
0                                        INVALID          000007FF21000000   16777216
0                                        INVALID          000007FF22000000   16777216
0                                        INVALID          000007FF23000000   16777216
KEEP buffer cache                        ALLOC            000007FF24000000   16777216
KEEP buffer cache                        ALLOC            000007FF25000000   16777216
KEEP buffer cache                        ALLOC            000007FF26000000   16777216
KEEP buffer cache                        ALLOC            000007FF27000000   16777216
KEEP buffer cache                        ALLOC            000007FF28000000   16777216
KEEP buffer cache                        ALLOC            000007FF29000000   16777216
KEEP buffer cache                        ALLOC            000007FF2A000000   16777216
KEEP buffer cache                        ALLOC            000007FF2B000000   16777216
KEEP buffer cache                        ALLOC            000007FF2C000000   16777216
KEEP buffer cache                        ALLOC            000007FF2D000000   16777216
KEEP buffer cache                        ALLOC            000007FF2E000000   16777216
KEEP buffer cache                        ALLOC            000007FF2F000000   16777216
KEEP buffer cache                        ALLOC            000007FF30000000   16777216
KEEP buffer cache                        ALLOC            000007FF31000000   16777216
KEEP buffer cache                        ALLOC            000007FF32000000   16777216
KEEP buffer cache                        ALLOC            000007FF33000000   16777216
KEEP buffer cache                        ALLOC            000007FF34000000   16777216
streams pool                             ALLOC            000007FF35000000   16777216
streams pool                             ALLOC            000007FF36000000   16777216
java pool                                ALLOC            000007FF37000000   16777216
large pool                               ALLOC            000007FF38000000   16777216
shared pool                              ALLOC            000007FF39000000   16777216
shared pool                              ALLOC            000007FF3A000000   16777216
shared pool                              ALLOC            000007FF3B000000   16777216
shared pool                              ALLOC            000007FF3C000000   16777216
shared pool                              ALLOC            000007FF3D000000   16777216
shared pool                              ALLOC            000007FF3E000000   16777216
shared pool                              ALLOC            000007FF3F000000   16777216
shared pool                              ALLOC            000007FF40000000   16777216
shared pool                              ALLOC            000007FF41000000   16777216
shared pool                              ALLOC            000007FF42000000   16777216
shared pool                              ALLOC            000007FF43000000   16777216
shared pool                              ALLOC            000007FF44000000   16777216
shared pool                              ALLOC            000007FF45000000   16777216
shared pool                              ALLOC            000007FF46000000   16777216
shared pool                              ALLOC            000007FF47000000   16777216
shared pool                              ALLOC            000007FF48000000   16777216
shared pool                              ALLOC            000007FF49000000   16777216
shared pool                              ALLOC            000007FF4A000000   16777216
shared pool                              ALLOC            000007FF4B000000   16777216
shared pool                              ALLOC            000007FF4C000000   16777216
shared pool                              ALLOC            000007FF4D000000   16777216
shared pool                              ALLOC            000007FF4E000000   16777216
shared pool                              ALLOC            000007FF4F000000   16777216
shared pool                              ALLOC            000007FF50000000   16777216
shared pool                              ALLOC            000007FF51000000   16777216
shared pool                              ALLOC            000007FF52000000   16777216
shared pool                              ALLOC            000007FF53000000   16777216
shared pool                              ALLOC            000007FF54000000   16777216
shared pool                              ALLOC            000007FF55000000   16777216
shared pool                              ALLOC            000007FF56000000   16777216

87 rows selected.

SQL>

Expdp的include导出procedure

SQL> SELECT * FROM dba_directories ;

OWN DIRECTORY_NAME DIRECTORY_PATH
------- ---------------------- ---------------
SYS DPUMPDIR /oracle/bakup

SQL>grant read ,write on directory DPUMPDIR to &user;

C:/Users/Liangwei>expdp help=y
...

INCLUDE
包括特定对象类型。
例如, INCLUDE=TABLE_DATA。

expdp user/password directory=DPUMPDIR dumpfile=prodedure.dmp include=PROCEDURE

当然PROCEDURE 也可以换成其它对象如：

INDEX，TABLE，FUNCTION，VIEW，DATABASE LINK，SYNONYM，PACKAGE BODY，SEQUENCE，LOB，PACKAGE

C:/Users/Liangwei>impdp help=y

...

SQLFILE
将所有的 SQL DDL 写入指定的文件。

impdp user/password directory=DPUMPDIR dumpfile=prodedure.dmp SQLFILE=procedure.sql

将prodedure.dmp 中的信息导出为SQL语句，prodedure.dmp 中的存储过程并不会导入到数据库中。

redo log corruption and recovery

You will see any of these errors, in case of redo log corruption

ORA-16038 log %s sequence# %s cannot be archived
ORA-367 checksum error in log file header
ORA-368 checksum error in redo log block
ORA-354 corrupt redo log block header
ORA-353 log corruption near block change time

Solution:- Try to clear the log file without shutdown the database.

You have to be careful when using 'alter database clear logfile', because the command erases all data in the logfile.

eg: alter database clear logfile group 1;
alter database clear unarchived logfile group 1;

Dropping/clearing the redo logs is not possible, if there are only two log groups and the corrupt logfile belongs to CURRENT/ACTIVE, it may be needed for instance recovery. You may receive ORA-1624 error.

If you receive ORA-1624 then you have to perform incomplete recovery stopping just before the redo log file which was corrupted.

Regards,
Satishbabu Gunukula
http://www.oracleracexpert.com/

Oracle data pump expdp/impdp

Oracle Data Pump utility is used for exporting data and metadata into set of operating system files and it is newer, faster and flexible alternative to “export/import” utilities.

Oracle Datapump utility introduced in Oracle 10g Release1 and this utility can be invoked using expdp(export) and impdb(import) commands. User need to specify the export/import parameters to determine the operation and you can specify the parameters on the command line or in a parameter file.

The expdp and impdp uses the procedures provided in the DBMS_DATAPUMP package to execute the commands and DBMS_METADATA package is used to move the data.

Please note that it is not possible to start or restart data pump jobs on one instance in Oracle RAC if jobs currently running on other instances.

Oracle Data Pump Export :-

1. Create directory object as SYS user.
SQL> create or replace directory export_dir as '/oradata/export’;

2. Grant Read/Write privilege on the directory to the user, who invokes the Data pump export.
SQL> grant read,write on directory export_dir to test_user;

3. Take Data Pump Export

Click here to see Roles/privileges required for Export modes.

Oracle data pump export examples for all 5 modes.

(i) Full Database Export
$ expdp test_user/test123 full=y directory=export_dir dumpfile=expdp_fulldb.dmp logfile=expdp_fulldb.log

(ii) Schema Export
$expdp test_user/test123 schemas=test_user directory= export _dir dumpfile=expdp_test_user.dmp logfile=expdp_test_user.log

If you want to export more than one schema then specify the schema names separated by comma.

(iii)Table Export
$ expdp test_user/test123 tables=emp,dept directory= export _dir dumpfile=expdp_tables.dmp logfile=expdp_tables.log

You can specify more than one table.

(iv) Tablespace Export
$ expdp test_user/test123 tablespaces=test_user_tbs directory= export _dir dumpfile=expdp_tbs.dmp logfile=expdp_tbs.log

You can specify more than one tablespace.

(v) Transportable tablespace
$ expdp test_user/test123 transport_tablespaces=test_user_tbs transport_full_check=y directory= export _dir dumpfile=expdp_trans_tbs.dmp logfile=expdp_trans_tbs.log

Click here to learn more on Transportable Tablespace with examples.

Oracle Data Pump Import :-
Data Pump Import utility is used for loading an export dump files into a target system and we can load one or more files.

Copy the dump file to the target system where you to import.

1. Create directory object as SYS user.
SQL> create directory import_dir as '/oradata/import';

2. Grant Read/Write privilege on the Directory to the user, who invokes the Data Pump import.
SQL> grant read,write on directory import_dir to test_user;

3. Import the data using Data Pump Import.

Oracle data pump import examples for all 5 modes.

(i) Full Database Import
$ impdp test_user/test123 full=Y directory=imp_dir dumpfile=expdp_fulldb.dmp logfile=imp_fulldb.log

(ii) Schema Import
$impdp test_user/test123 schemas=test_user directory=imp_dir dumpfile=expdp_test_user.dmp Logfile=impdp_test_user.log

(iii) Table Import
$ impdp test_user/test123 tables=emp,dept directory=imp_dir dumpfile=expdp_tables.dmp logfile=impdp_tables.log

From 11g, you can reaname a table during the import
REMAP_TABLE=[schema.]old_tablename[.partition]:new_tablename
$ impdp test_user/test123 remap_table=test_user.emp:emp1 directory=imp_dir dumpfile=expdp_tables.dmp logfile=impdp_tables.log

Tables will not be remapped if they already exist even if the TABLE_EXISTS_ACTION is set to TRUNCATE or APPEND

(iv) Tablespace Import
$ impdp test_user/test123 tablespaces=test_user_tbs directory=imp_dir dumpfile=expdp_tbs.dmp logfile=impdp_tbs.log

Above example imports all tables that have data in tablespaces test_user_tbs and it assumes that the tablespaces already exist.

(v) Transportable Tablespace
Click here to to import data using Transportable Tablespace method.

Common Errors with Data pump import (impdp) utility:-

1. ORA-31631: privileges are required
   ORA-39122: Unprivileged users may not perform REMAP_SCHEMA remapping
Cause: A user attempted to remap objects during an import but lacked the IMPORT_FULL_DATABASE privilege.
Action: Retry the job from a schema that owns the IMPORT_FULL_DATABASE privilege.

2. ORA-31631: privileges are required
    ORA-39161: Full database jobs require privileges
Cause: Either an attempt to perform a full database export without the EXP_FULL_DATABASE role or an attempt to perform a full database import over a network link without the IMP_FULL_DATABASE role.
Action: Retry the operation in a schema that has the required roles.

3. ORA-01950: no privileges on tablespace "string"
    Cause: User does not have privileges to allocate an extent in the specified tablespace.
   Action: Grant the user the appropriate system privileges or grant the user space resource on the tablespace.

Click here to learn Roles/ privileges required for Data pump Export and Import.

4. import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
    IMP-00017: following statement failed with ORACLE error 3113:
    "BEGIN "
    "SYS.DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE   SYS.DBMS_RULE_ADM.CREATE_EVALUATIO" "N_CONTEXT_OBJ, 'SYS',TRUE);"
Cause: Import fails while executing the following command.
Action: Login as sys and run the following scripts
$ORACLE_HOME/rdbms/admin/dbmsread.sql
$ORACLE_HOME/rdbms/admin/prvtread.plb

5. Import failed with below errors
IMP-00003: ORACLE error 6550 encountered
ORA-06550: line 2, column 1:
PLS-00201: identifier 'CTXSYS.DRIIMP' must be declared
Cause: The user that you are importing does not have privileages on CTXSYS.DRIIMP package or CTXSYS user does not exists
Action: Create CTXSYS user or grant required permissions

Please see the Data pump Export and Import related documents:
Click here for Data Pump Export modes and Interfaces.

Regards,
Satishbabu Gunukula
http://www.oracleracexpert.com/