Showing posts with label expdp. Show all posts
Showing posts with label expdp. Show all posts

Monday, November 30, 2009

DATAPUMP with SYS$SYS.* service_names

While we using DATAPUMP, we will find ALTER SYSTEM SET service_names='SYS$SYS.*...' in alert log file(RAC).
$ expdp directory=TMP logfile=full.log dumpfile=full.dmp full=y
In alert log file:
Mon Nov 30 01:14:22 2009
ALTER SYSTEM SET service_names='service2','service1','SYS$SYS.KUPC$C_1_20091130011411.ORCL' SCOPE=MEMORY SID='orcl1';
ALTER SYSTEM SET service_names='SYS$SYS.KUPC$C_1_20091130011411.ORCL','service2','service1','SYS$SYS.KUPC$S_1_20091130011411.ORCL' SCOPE=MEMORY SID='orcl1';
Mon Nov 30 01:14:29 2009
DM00 started with pid=56, OS id=26976, job SYS.SYS_EXPORT_SCHEMA_01

At "SERVICE_NAMES" parameter:
SQL> show parameter service_names

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
service_names string SYS$SYS.KUPC$C_1_20091130011411.ORCL, service2, service1, SYS$SYS.KUPC$S_1_20091130011411.ORCL
After DATAPUMP finished. In alert log file:

ALTER SYSTEM SET service_names='SYS$SYS.KUPC$S_1_20091130011602.ORCL','service2','service1' SCOPE=MEMORY SID='orcl1';

ALTER SYSTEM SET service_names='service2','service1' SCOPE=MEMORY SID='orcl1';

At SERVICE_NAMES parameter:
SQL> show parameter service_names

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
service_names string service2, service1
DATAPUMP doesn't change existing service name and no impact to existing services.
DATAPUMP just adds new services for its own queue operation.

read more metalink 363396.1

Wednesday, January 14, 2009

ORA-39165: Schema SYS was not found ORA-39166: Object AUD$ was not found.

I would like to use "expdp"... export sys.aud$ table. Ans I found some error...

$   expdp   directory=BACKUP  TABLES=sys.AUD$ DUMPFILE=dumpfile.dmp

Export: Release 10.2 - 64bit Production 
Username: / as sysdba

Connected to: Oracle Database 10g Enterprise Edition Release 10.2 
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
Starting "SYS"."SYS_EXPORT_TABLE_01":  /******** AS SYSDBA directory=BACKUP TABLES=sys.AUD$ DUMPFILE=dumpfile.dmp 
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 0 KB
ORA-39165: Schema SYS was not found.
ORA-39166: Object AUD$ was not found.
ORA-31655: no data or metadata objects selected for job
Job "SYS"."SYS_EXPORT_TABLE_01" completed with 3 error(s) at 16:58:49

Expdp command can not export system schemas  like SYS, ORDSYS, and MDSYS in any mode.
So, I need to use "exp" ...

exp system/ file=dumpfile.dmp log=logfile.log tables=sys.aud$

$ exp file=dumpfile.dmp log=logfile.log tables=sys.aud$

Export: Release 10.2

Username: / as sysdba

Connected to: Oracle Database 10g Enterprise Edition Release 10.2
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

About to export specified tables via Conventional Path ...
. . exporting table                           AUD$      10000 rows exported
Export terminated successfully without warnings.