Friday, October 31, 2008

demo rens

Script started on Fri 31 Oct 2008 08:21:36 PM CET
rac1:-) sqlplus scott/tiger


SQL*Plus: Release 10.2.0.1.0 - Production on Fri Oct 31 20:21:40 2008

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> create tablespace scottie datafile '/tmp/scottie.dbf' size 100M ;
create tablespace scottie datafile '/tmp/scottie.dbf' size 100M
*
ERROR at line 1:
ORA-01119: error in creating database file '/tmp/scottie.dbf'
ORA-27038: created file already exists
Additional information: 1


SQL> i
2 reuse
3 /
creaet      create user
Tablespace created.

SQL> scottie identifei  ied by scottie default table  space scottie quotun  a unlimite  d on scottie ;

User created.

SQL> grant create session  , s resource to scottie ;

Grant succeeded.

SQL> exit    connect scott/tiger
Connected.
SQL> rem DATAPUMP export en import over database link naar sc            lin k  kl v    van scottie naar          naar SCIT^H^H      OTtie
SQL> co  creea  et  ate databse   ase      ase link scottie us  connect to scottie iedn   dentified by scottie ;

Database link created.

SQL> select * from scottie       v$database@scottie ;
select * from v$database@scottie
*
ERROR at line 1:
ORA-02019: connection description for remote database not found


SQL> s drop database link scottie ;

Database link dropped.

SQL> create database link scottie connect to scottie identifed    ied by scottie using 'devdb1';

Database link created.

SQL> select * from v$database@scottie ;
select * from v$database@scottie
*
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-02063: preceding line from SCOTTIE


SQL> select * from user_users@scottie ;

USERNAME USER_ID ACCOUNT_STATUS
------------------------------ ---------- --------------------------------
LOCK_DATE EXPIRY_DA DEFAULT_TABLESPACE
--------- --------- ------------------------------
TEMPORARY_TABLESPACE CREATED INITIAL_RSRC_CONSUMER_GROUP
------------------------------ --------- ------------------------------
EXTERNAL_NAME
--------------------------------------------------------------------------------
SCOTTIE 61 OPEN
SCOTTIE
TEMP 31-OCT-08 DEFAULT_CONSUMER_GROUP



SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
rac1:-) expdp help=yes


Export: Release 10.2.0.1.0 - Production on Friday, 31 October, 2008 20:26:54

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


The Data Pump export utility provides a mechanism for transferring data objects
between Oracle databases. The utility is invoked with the following command:

Example: expdp scott/tiger DIRECTORY=dmpdir DUMPFILE=scott.dmp

You can control how Export runs by entering the 'expdp' command followed
by various parameters. To specify parameters, you use keywords:

Format: expdp KEYWORD=value or KEYWORD=(value1,value2,...,valueN)
Example: expdp scott/tiger DUMPFILE=scott.dmp DIRECTORY=dmpdir SCHEMAS=scott
or TABLES=(T1:P1,T1:P2), if T1 is partitioned table

USERID must be the first parameter on the command line.

Keyword Description (Default)
------------------------------------------------------------------------------
ATTACH Attach to existing job, e.g. ATTACH [=job name].
COMPRESSION Reduce size of dumpfile contents where valid
keyword values are: (METADATA_ONLY) and NONE.
CONTENT Specifies data to unload where the valid keywords are:
(ALL), DATA_ONLY, and METADATA_ONLY.
DIRECTORY Directory object to be used for dumpfiles and logfiles.
DUMPFILE List of destination dump files (expdat.dmp),
e.g. DUMPFILE=scott1.dmp, scott2.dmp, dmpdir:scott3.dmp.
ENCRYPTION_PASSWORD Password key for creating encrypted column data.
ESTIMATE Calculate job estimates where the valid keywords are:
(BLOCKS) and STATISTICS.
ESTIMATE_ONLY Calculate job estimates without performing the export.
EXCLUDE Exclude specific object types, e.g. EXCLUDE=TABLE:EMP.
FILESIZE Specify the size of each dumpfile in units of bytes.
FLASHBACK_SCN SCN used to set session snapshot back to.
FLASHBACK_TIME Time used to get the SCN closest to the specified time.
FULL Export entire database (N).
HELP Display Help messages (N).
INCLUDE Include specific object types, e.g. INCLUDE=TABLE_DATA.
JOB_NAME Name of export job to create.
LOGFILE Log file name (export.log).
NETWORK_LINK Name of remote database link to the source system.
NOLOGFILE Do not write logfile (N).
PARALLEL Change the number of active workers for current job.
PARFILE Specify parameter file.
QUERY Predicate clause used to export a subset of a table.
SAMPLE Percentage of data to be exported;
SCHEMAS List of schemas to export (login schema).
STATUS Frequency (secs) job status is to be monitored where
the default (0) will show new status when available.
TABLES Identifies a list of tables to export - one schema only.
TABLESPACES Identifies a list of tablespaces to export.
TRANSPORT_FULL_CHECK Verify storage segments of all tables (N).
TRANSPORT_TABLESPACES List of tablespaces from which metadata will be unloaded.
VERSION Version of objects to export where valid keywords are:
(COMPATIBLE), LATEST, or any valid database version.

The following commands are valid while in interactive mode.
Note: abbreviations are allowed

Command Description
------------------------------------------------------------------------------
ADD_FILE Add dumpfile to dumpfile set.
CONTINUE_CLIENT Return to logging mode. Job will be re-started if idle.
EXIT_CLIENT Quit client session and leave job running.
FILESIZE Default filesize (bytes) for subsequent ADD_FILE commands.
HELP Summarize interactive commands.
KILL_JOB Detach and delete job.
PARALLEL Change the number of active workers for current job.
PARALLEL=.
START_JOB Start/resume current job.
STATUS Frequency (secs) job status is to be monitored where
the default (0) will show new status when available.
STATUS[=interval]
STOP_JOB Orderly shutdown of job execution and exits the client.
STOP_JOB=IMMEDIATE performs an immediate shutdown of the
Data Pump job.

rac1:-) expdp scott/tiger tables=emp network_link=scottie remp ap                                                   expdp     imp   sqlplus


SQL*Plus: Release 10.2.0.1.0 - Production on Fri Oct 31 20:30:34 2008

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

Enter user-name: scottie  /tiger

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

SQL> select * from dba_directories
2 ;

OWNER DIRECTORY_NAME
------------------------------ ------------------------------
DIRECTORY_PATH
--------------------------------------------------------------------------------
SYS ADMIN_DIR
/ade/aime_10.2_lnx_push/oracle/md/admin

SYS SUBDIR
/u01/app/oracle/product/10.2.0/db_1/demo/schema/order_entry//2002/Sep

SYS DATA_FILE_DIR
/u01/app/oracle/product/10.2.0/db_1/demo/schema/sales_history/


OWNER DIRECTORY_NAME
------------------------------ ------------------------------
DIRECTORY_PATH
--------------------------------------------------------------------------------
SYS WORK_DIR
/ade/aime_10.2_lnx_push/oracle/work

SYS LOG_FILE_DIR
/u01/app/oracle/product/10.2.0/db_1/demo/schema/log/

SYS MEDIA_DIR
/u01/app/oracle/product/10.2.0/db_1/demo/schema/product_media/


OWNER DIRECTORY_NAME
------------------------------ ------------------------------
DIRECTORY_PATH
--------------------------------------------------------------------------------
SYS XMLDIR
/u01/app/oracle/product/10.2.0/db_1/demo/schema/order_entry/

SYS DATA_PUMP_DIR
/u01/app/oracle/product/10.2.0/db_1/rdbms/log/


8 rows selected.

SQL> creaet  te directory / ' tempdir as '/tmp';

Directory created.

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
rac1:-) impdp scott/tiger direcoty   tory=tempdir network_link=scottie ramp   emap_schem
rac1:-) network_link=scottie remap_schema <=scott:scottie tables=emp


Import: Release 10.2.0.1.0 - Production on Friday, 31 October, 2008 20:32:48

Copyright (c) 2003, 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
ORA-31631: privileges are required
ORA-39149: cannot link privileged user to non-privileged user

rac1:-) sqlplus


SQL*Plus: Release 10.2.0.1.0 - Production on Fri Oct 31 20:33:06 2008

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

Enter user-name: / as sysdba

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

SQL> grant sysdba
2 to scottie ;

Grant succeeded.

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
rac1:-) sqlplusimpdp scott/tiger directory=tempdir network_link=scottie remap_schema >
rac1:-)


Import: Release 10.2.0.1.0 - Production on Friday, 31 October, 2008 20:33:15

Copyright (c) 2003, 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
ORA-31631: privileges are required
ORA-39149: cannot link privileged user to non-privileged user

rac1:-) sqlplus


SQL*Plus: Release 10.2.0.1.0 - Production on Fri Oct 31 20:35:34 2008

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

Enter user-name: scottie/scottie

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

SQL> create database link scottie using scott               connect to scott identified by tiger using 'devdb1';
create database link scottie connect to scott identified by tiger using 'devdb1'
*
ERROR at line 1:
ORA-01031: insufficient privileges


SQL> conn / as sysdba
Connected.
SQL> grant create databsae l     ase link to scottie ;

Grant succeeded.

SQL> connect scoi ttie/scottie
Connected.
SQL> create database link scottie connect to scott identified by tiger using 'devdb1';

Database link created.

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
rac1:-) sqlplus


SQL*Plus: Release 10.2.0.1.0 - Production on Fri Oct 31 20:36:25 2008

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

Enter user-name: / as sysdba

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

SQL> grant read, write on tempdir to scottie ;
grant read, write on tempdir to scottie
*
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> e ed
Wrote file afiedt.buf
7[?47h[?1h="afiedt.buf" 2L, 43Cgrant read, write on tempdir to scottie
/
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~grant read, write on -- INSERT --dtempdir to scottieitempdir to scottiertempdir to scottieetempdir to scottiectempdir to scottiettempdir to scottieotempdir to scottiertempdir to scottieytempdir to scottie tempdir to scottie:wq
"afiedt.buf" 2L, 53C written


[?1l>[?47l8
1* grant read, write on directory tempdir to scottie
SQL> /

Grant succeeded.

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
rac1:-) /impdimpdp scott/tiger directory=tempdir network_link=scottie remap_schema >
rac1:-) impdp scotttiger directory=tempdir network_link=scottie remap_schema=
rac1:-) impdp scottiger directory=tempdir network_link=scottie remap_schema=s
rac1:-) impdp scottger directory=tempdir network_link=scottie remap_schema=sc
rac1:-) impdp scotter directory=tempdir network_link=scottie remap_schema=sco
rac1:-) impdp scottr directory=tempdir network_link=scottie remap_schema=scot
rac1:-) impdp scott directory=tempdir network_link=scottie remap_schema=scot
rac1:-) impdp scotti directory=tempdir network_link=scottie remap_schema=sco
rac1:-) impdp scottie directory=tempdir network_link=scottie remap_schema=sco
rac1:-) impdp scottie/ directory=tempdir network_link=scottie remap_schema=sc
rac1:-) impdp scottie/s directory=tempdir network_link=scottie remap_schema=s
rac1:-) impdp scottie/sc directory=tempdir network_link=scottie remap_schema=
rac1:-) impdp scottie/sco directory=tempdir network_link=scottie remap_schema
rac1:-) impdp scottie/scot directory=tempdir network_link=scottie remap_schem
rac1:-) impdp scottie/scott directory=tempdir network_link=scottie remap_sche
rac1:-) impdp scottie/scotti directory=tempdir network_link=scottie remap_sch
rac1:-) impdp scottie/scottie directory=tempdir network_link=scottie remap_sc
rac1:-) impdp scottie/scottiee directory=tempdir network_link=scottie remap_s
rac1:-) pdir network_link=scottie remap_schema=scott:scottie tables=emp <hema=scott:scottie tables=em


Import: Release 10.2.0.1.0 - Production on Friday, 31 October, 2008 20:37:00

Copyright (c) 2003, 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
Starting "SCOTTIE"."SYS_IMPORT_TABLE_01": scottie/******** directory=tempdir network_link=scottie remap_schema=scott:scottie tables=emp
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . imported "SCOTTIE"."EMP" 14 rows
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
ORA-39083: Object type REF_CONSTRAINT failed to create with error:
ORA-00942: table or view does not exist
Failing sql is:
ALTER TABLE "SCOTTIE"."EMP" ADD CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO") REFERENCES "SCOTTIE"."DEPT" ("DEPTNO") ENABLE

Job "SCOTTIE"."SYS_IMPORT_TABLE_01" completed with 1 error(s) at 20:37:55

rac1:-) sqlplus scottie/scottie


SQL*Plus: Release 10.2.0.1.0 - Production on Fri Oct 31 20:39:56 2008

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 * from cat ;

TABLE_NAME TABLE_TYPE
------------------------------ -----------
EMP TABLE

SQL> select tablespace_name from user_tables;

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

SQL> rem ok        
SQL> de  rem deze tabel moet st natuurlijk in een ander ta   e tablespace komen
SQL> drop table emp ;

Table dropped.

SQL> ex  revoke dba from scottie;
revoke dba from scottie
*
ERROR at line 1:
ORA-01031: insufficient privileges


SQL> select * from user_users;

USERNAME USER_ID ACCOUNT_STATUS
------------------------------ ---------- --------------------------------
LOCK_DATE EXPIRY_DA DEFAULT_TABLESPACE
--------- --------- ------------------------------
TEMPORARY_TABLESPACE CREATED INITIAL_RSRC_CONSUMER_GROUP
------------------------------ --------- ------------------------------
EXTERNAL_NAME
--------------------------------------------------------------------------------
SCOTTIE 61 OPEN
SCOTTIE
TEMP 31-OCT-08 DEFAULT_CONSUMER_GROUP



SQL> sho user
USER is "SCOTTIE"
SQL> create table bla tablespace users as select * from dual ;

Table created.

SQL> drop table bla;

Table dropped.

SQL> select * from user_roles            m session_roles;

ROLE
------------------------------
RESOURCE

SQL> re  select * from user_sys_privs;

USERNAME PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
SCOTTIE CREATE SESSION NO
SCOTTIE UNLIMITED TABLESPACE NO
SCOTTIE CREATE DATABASE LINK NO

SQL> conn / as sysdba
Connected.
SQL> revoke unlimited tablespace t from scottie ;

Revoke succeeded.

SQL> conn scottie/scottie
Connected.
SQL> create table bla tablespace users as select * from dual ;
create table bla tablespace users as select * from dual
*
ERROR at line 1:
ORA-01536: space quota exceeded for tablespace 'USERS'


SQL> c/users/scottie
1* create table bla tablespace scottie as select * from dual
SQL> /

Table created.

SQL> drop table bla;
exit

Table dropped.

SQL> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
rac1:-) sqlplus scottie/scottie
rac1:-) impdp scottie/scottie directory=tempdir network_link=scottie remap_sc >
rac1:-)


Import: Release 10.2.0.1.0 - Production on Friday, 31 October, 2008 20:41:54

Copyright (c) 2003, 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
Starting "SCOTTIE"."SYS_IMPORT_TABLE_01": scottie/******** directory=tempdir network_link=scottie remap_schema=scott:scottie tables=emp
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39083: Object type TABLE failed to create with error:
ORA-01536: space quota exceeded for tablespace 'USERS'
Failing sql is:
CREATE TABLE "SCOTTIE"."EMP" ("EMPNO" NUMBER(4,0), "ENAME" VARCHAR2(10), "JOB" VARCHAR2(9), "MGR" NUMBER(4,0), "HIREDATE" DATE, "SAL" NUMBER(7,2), "COMM" NUMBER(7,2), "DEPTNO" NUMBER(2,0)) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL D
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
ORA-39112: Dependent object type INDEX:"SCOTTIE"."PK_EMP" skipped, base object type TABLE:"SCOTTIE"."EMP" creation failed
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
ORA-39112: Dependent object type CONSTRAINT:"SCOTTIE"."PK_EMP" skipped, base object type TABLE:"SCOTTIE"."EMP" creation failed
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
ORA-39112: Dependent object type REF_CONSTRAINT:"SCOTTIE"."FK_DEPTNO" skipped, base object type TABLE:"SCOTTIE"."EMP" creation failed
Job "SCOTTIE"."SYS_IMPORT_TABLE_01" completed with 4 error(s) at 20:42:15

rac1:-) impdp help=yes


Import: Release 10.2.0.1.0 - Production on Friday, 31 October, 2008 20:42:30

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


The Data Pump Import utility provides a mechanism for transferring data objects
between Oracle databases. The utility is invoked with the following command:

Example: impdp scott/tiger DIRECTORY=dmpdir DUMPFILE=scott.dmp

You can control how Import runs by entering the 'impdp' command followed
by various parameters. To specify parameters, you use keywords:

Format: impdp KEYWORD=value or KEYWORD=(value1,value2,...,valueN)
Example: impdp scott/tiger DIRECTORY=dmpdir DUMPFILE=scott.dmp

USERID must be the first parameter on the command line.

Keyword Description (Default)
------------------------------------------------------------------------------
ATTACH Attach to existing job, e.g. ATTACH [=job name].
CONTENT Specifies data to load where the valid keywords are:
(ALL), DATA_ONLY, and METADATA_ONLY.
DIRECTORY Directory object to be used for dump, log, and sql files.
DUMPFILE List of dumpfiles to import from (expdat.dmp),
e.g. DUMPFILE=scott1.dmp, scott2.dmp, dmpdir:scott3.dmp.
ENCRYPTION_PASSWORD Password key for accessing encrypted column data.
This parameter is not valid for network import jobs.
ESTIMATE Calculate job estimates where the valid keywords are:
(BLOCKS) and STATISTICS.
EXCLUDE Exclude specific object types, e.g. EXCLUDE=TABLE:EMP.
FLASHBACK_SCN SCN used to set session snapshot back to.
FLASHBACK_TIME Time used to get the SCN closest to the specified time.
FULL Import everything from source (Y).
HELP Display help messages (N).
INCLUDE Include specific object types, e.g. INCLUDE=TABLE_DATA.
JOB_NAME Name of import job to create.
LOGFILE Log file name (import.log).
NETWORK_LINK Name of remote database link to the source system.
NOLOGFILE Do not write logfile.
PARALLEL Change the number of active workers for current job.
PARFILE Specify parameter file.
QUERY Predicate clause used to import a subset of a table.
REMAP_DATAFILE Redefine datafile references in all DDL statements.
REMAP_SCHEMA Objects from one schema are loaded into another schema.
REMAP_TABLESPACE Tablespace object are remapped to another tablespace.
REUSE_DATAFILES Tablespace will be initialized if it already exists (N).
SCHEMAS List of schemas to import.
SKIP_UNUSABLE_INDEXES Skip indexes that were set to the Index Unusable state.
SQLFILE Write all the SQL DDL to a specified file.
STATUS Frequency (secs) job status is to be monitored where
the default (0) will show new status when available.
STREAMS_CONFIGURATION Enable the loading of Streams metadata
TABLE_EXISTS_ACTION Action to take if imported object already exists.
Valid keywords: (SKIP), APPEND, REPLACE and TRUNCATE.
TABLES Identifies a list of tables to import.
TABLESPACES Identifies a list of tablespaces to import.
TRANSFORM Metadata transform to apply to applicable objects.
Valid transform keywords: SEGMENT_ATTRIBUTES, STORAGE
OID, and PCTSPACE.
TRANSPORT_DATAFILES List of datafiles to be imported by transportable mode.
TRANSPORT_FULL_CHECK Verify storage segments of all tables (N).
TRANSPORT_TABLESPACES List of tablespaces from which metadata will be loaded.
Only valid in NETWORK_LINK mode import operations.
VERSION Version of objects to export where valid keywords are:
(COMPATIBLE), LATEST, or any valid database version.
Only valid for NETWORK_LINK and SQLFILE.

The following commands are valid while in interactive mode.
Note: abbreviations are allowed

Command Description (Default)
------------------------------------------------------------------------------
CONTINUE_CLIENT Return to logging mode. Job will be re-started if idle.
EXIT_CLIENT Quit client session and leave job running.
HELP Summarize interactive commands.
KILL_JOB Detach and delete job.
PARALLEL Change the number of active workers for current job.
PARALLEL=.
START_JOB Start/resume current job.
START_JOB=SKIP_CURRENT will start the job after skipping
any action which was in progress when job was stopped.
STATUS Frequency (secs) job status is to be monitored where
the default (0) will show new status when available.
STATUS[=interval]
STOP_JOB Orderly shutdown of job execution and exits the client.
STOP_JOB=IMMEDIATE performs an immediate shutdown of the
Data Pump job.

rac1:-) impdp help=yes
rac1:-) impdp scottie/scottie directory=tempdir network_link=scottie remap_sc >
rac1:-) ap_schema=scott:scottie tables=emp < remap_tablespace=users:scottie


Import: Release 10.2.0.1.0 - Production on Friday, 31 October, 2008 20:44:02

Copyright (c) 2003, 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
Starting "SCOTTIE"."SYS_IMPORT_TABLE_01": scottie/******** directory=tempdir network_link=scottie remap_schema=scott:scottie tables=emp remap_tablespace=users:scottie
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . imported "SCOTTIE"."EMP" 14 rows
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
ORA-39083: Object type REF_CONSTRAINT failed to create with error:
ORA-00942: table or view does not exist
Failing sql is:
ALTER TABLE "SCOTTIE"."EMP" ADD CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO") REFERENCES "SCOTTIE"."DEPT" ("DEPTNO") ENABLE






Job "SCOTTIE"."SYS_IMPORT_TABLE_01" completed with 1 error(s) at 20:44:24

rac1:-)

rac1:-)

rac1:-)

rac1:-)

rac1:-)

rac1:-) rman     echo BACKUP maken !

BACKUP maken !
rac1:-) rman


Recovery Manager: Release 10.2.0.1.0 - Production on Fri Oct 31 20:45:39 2008

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

RMAN> connect target /

connected to target database: DEVDB1 (DBID=4274132913)

RMAN> backup database and     plus archivelog ;


Starting backup at 31-OCT-08
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=146 devtype=DISK
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=20 recid=95 stamp=669588348
channel ORA_DISK_1: starting piece 1 at 31-OCT-08
channel ORA_DISK_1: finished piece 1 at 31-OCT-08
piece handle=/u03/flash_recovery_area/DEVDB1/backupset/2008_10_31/o1_mf_annnn_TAG20081031T204549_4jpr3h3p_.bkp tag=TAG20081031T204549 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 31-OCT-08

Starting backup at 31-OCT-08
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/u01/app/oracle/oradata/devdb1/system01.dbf
input datafile fno=00006 name=/tmp/scottie.dbf
input datafile fno=00003 name=/u01/app/oracle/oradata/devdb1/sysaux01.dbf
input datafile fno=00002 name=/u01/app/oracle/oradata/devdb1/undotbs01.dbf
input datafile fno=00004 name=/u01/app/oracle/oradata/devdb1/users01.dbf
input datafile fno=00005 name=/u01/app/oracle/oradata/devdb1/example01.dbf
channel ORA_DISK_1: starting piece 1 at 31-OCT-08
channel ORA_DISK_1: finished piece 1 at 31-OCT-08
piece handle=/u03/flash_recovery_area/DEVDB1/backupset/2008_10_31/o1_mf_nnndf_TAG20081031T204552_4jpr3kch_.bkp tag=TAG20081031T204552 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:02:40
Finished backup at 31-OCT-08

Starting backup at 31-OCT-08
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=21 recid=96 stamp=669588512
channel ORA_DISK_1: starting piece 1 at 31-OCT-08
channel ORA_DISK_1: finished piece 1 at 31-OCT-08
piece handle=/u03/flash_recovery_area/DEVDB1/backupset/2008_10_31/o1_mf_annnn_TAG20081031T204832_4jpr8ks8_.bkp tag=TAG20081031T204832 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 31-OCT-08

Starting Control File and SPFILE Autobackup at 31-OCT-08
piece handle=/u03/flash_recovery_area/DEVDB1/autobackup/2008_10_31/o1_mf_s_669588515_4jpr8mgf_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 31-OCT-08

RMAN> exit


Recovery Manager complete.
rac1:-) sqlplus scottie/scottie


SQL*Plus: Release 10.2.0.1.0 - Production on Fri Oct 31 20:58:14 2008

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 ab  tab   table_name, tablespace_name from user_tables;

TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
EMP SCOTTIE

SQL> rem inderdaad in tablespace SCOTTIE
SQL> exit    conn / as sysdba
Connected.
SQL> alter tabla espace scottie add datafile '/tmp/scottie1.bdf     .bdf';  size 100M ;

Tablespace altered.

SQL>

SQL> SQL> rem dit is fout dus deze maar offline zetten
SQL> alter datafile '/tmp/scot ttie.bdf' offline ;
alter datafile '/tmp/scottie.bdf' offline
*
ERROR at line 1:
ORA-00940: invalid ALTER command


SQL> c/data/database data
1* alter database datafile '/tmp/scottie.bdf' offline
SQL> .
SQL> /

Database altered.

SQL> select * from v$recover_file;

FILE# ONLINE ONLINE_
---------- ------- -------
ERROR CHANGE#
----------------------------------------------------------------- ----------
TIME
---------
7 OFFLINE OFFLINE
647744
31-OCT-08


SQL> exit    host nu fouie  tief moeve   ven                        rem NU FOUTIEF MOVEN
SQL> host mt v /tmp/scottie.bdf /tmp/scottie.dbf

SQL> host ls -ltr /tmp/sco*
-rw-r----- 1 oracle oinstall 104865792 Oct 31 20:59 /tmp/scottie.dbf

SQL> select * from v$recover_file;

FILE# ONLINE ONLINE_
---------- ------- -------
ERROR CHANGE#
----------------------------------------------------------------- ----------
TIME
---------
7 OFFLINE OFFLINE
FILE NOT FOUND 0



SQL> alter system checkpoint ;

System altered.

SQL> host cp /tmp/scottie.dbf /tmp/scottie.bdf

SQL> select * from v$recover_file;

FILE# ONLINE ONLINE_
---------- ------- -------
ERROR CHANGE#
----------------------------------------------------------------- ----------
TIME
---------
7 OFFLINE OFFLINE
647744
31-OCT-08


SQL> alter system checkpoint ;

System altered.

SQL> select * from v$recover_file;

FILE# ONLINE ONLINE_
---------- ------- -------
ERROR CHANGE#
----------------------------------------------------------------- ----------
TIME
---------
7 OFFLINE OFFLINE
647744
31-OCT-08


SQL> alter datafile 7 online ;l 
alter datafile 7 online
*
ERROR at line 1:
ORA-00940: invalid ALTER command


SQL> alter database datafile 7 online ;
alter database datafile 7 online
*
ERROR at line 1:
ORA-01113: file 7 needs media recovery
ORA-01110: data file 7: '/tmp/scottie.bdf'


SQL> select * from v$recover_file;

FILE# ONLINE ONLINE_
---------- ------- -------
ERROR CHANGE#
----------------------------------------------------------------- ----------
TIME
---------
7 OFFLINE OFFLINE
647744
31-OCT-08


SQL> selct    ect status from dba_data_fl iles
2 where file_no=7 ;
where file_no=7
*
ERROR at line 2:
ORA-00904: "FILE_NO": invalid identifier


SQL> c/_
2* where fileno=7
SQL> /
where fileno=7
*
ERROR at line 2:
ORA-00904: "FILENO": invalid identifier


SQL> c/no/_id
2* where file_id=7
SQL> /

STATUS
---------
AVAILABLE

SQL> set pages 0
SQL> select * from dba_data_files;
/u01/app/oracle/oradata/devdb1/users01.dbf
4 USERS 128450560 15680 AVAILABLE
4 YES 3.4360E+10 4194302 160 128385024 15672
ONLINE

/u01/app/oracle/oradata/devdb1/sysaux01.dbf
3 SYSAUX 251658240 30720 AVAILABLE
3 YES 3.4360E+10 4194302 1280 251592704 30712
ONLINE

/u01/app/oracle/oradata/devdb1/undotbs01.dbf
2 UNDOTBS1 230686720 28160 AVAILABLE
2 YES 3.4360E+10 4194302 640 230621184 28152
ONLINE

/u01/app/oracle/oradata/devdb1/system01.dbf
1 SYSTEM 503316480 61440 AVAILABLE
1 YES 3.4360E+10 4194302 1280 503250944 61432
SYSTEM

/u01/app/oracle/oradata/devdb1/example01.dbf
5 EXAMPLE 104857600 12800 AVAILABLE
5 YES 3.4360E+10 4194302 80 104792064 12792
ONLINE

/tmp/scottie.dbf
6 SCOTTIE 104857600 12800 AVAILABLE
6 NO 0 0 0 104792064 12792
ONLINE

/tmp/scottie.bdf
7 SCOTTIE AVAILABLE
7
RECOVER


7 rows selected.

SQL> select * from v$datafole   ile;
1 9 30-JUN-05 0 1 SYSTEM READ WRITE
650612 31-OCT-08 0
561172 561173 28-OCT-08 503316480 61440
0 8192
/u01/app/oracle/oradata/devdb1/system01.dbf
0 8192
NONE
0

2 444079 30-JUN-05 1 2 ONLINE READ WRITE
650612 31-OCT-08 0
561172 561173 28-OCT-08 230686720 28160
0 8192
/u01/app/oracle/oradata/devdb1/undotbs01.dbf
0 8192
NONE
0

3 6609 30-JUN-05 2 3 ONLINE READ WRITE
650612 31-OCT-08 0
561172 561173 28-OCT-08 251658240 30720
0 8192
/u01/app/oracle/oradata/devdb1/sysaux01.dbf
0 8192
NONE
0

4 10566 30-JUN-05 4 4 ONLINE READ WRITE
650612 31-OCT-08 0
561172 561173 28-OCT-08 128450560 15680
0 8192
/u01/app/oracle/oradata/devdb1/users01.dbf
0 8192
NONE
0

5 467466 27-AUG-08 6 5 ONLINE READ WRITE
650612 31-OCT-08 0
561172 561173 28-OCT-08 104857600 12800
104857600 8192
/u01/app/oracle/oradata/devdb1/example01.dbf
0 8192
NONE
0

6 637748 31-OCT-08 7 6 ONLINE READ WRITE
650612 31-OCT-08 0
0 0 104857600 12800
104857600 8192
/tmp/scottie.dbf
0 8192
NONE
0

7 647743 31-OCT-08 7 7 RECOVER READ WRITE
647744 31-OCT-08 0 648264
31-OCT-08 0 0 104857600 12800
104857600 8192
/tmp/scottie.bdf
0 8192
NONE
0


7 rows selected.

SQL> desc v$datafile;
Name Null? Type
----------------------------------------- -------- ----------------------------
FILE# NUMBER
CREATION_CHANGE# NUMBER
CREATION_TIME DATE
TS# NUMBER
RFILE# NUMBER
STATUS VARCHAR2(7)
ENABLED VARCHAR2(10)
CHECKPOINT_CHANGE# NUMBER
CHECKPOINT_TIME DATE
UNRECOVERABLE_CHANGE# NUMBER
UNRECOVERABLE_TIME DATE
LAST_CHANGE# NUMBER
LAST_TIME DATE
OFFLINE_CHANGE# NUMBER
ONLINE_CHANGE# NUMBER
ONLINE_TIME DATE
BYTES NUMBER
BLOCKS NUMBER
CREATE_BYTES NUMBER
BLOCK_SIZE NUMBER
NAME VARCHAR2(513)
PLUGGED_IN NUMBER
BLOCK1_OFFSET NUMBER
AUX_NAME VARCHAR2(513)
FIRST_NONLOGGED_SCN NUMBER
FIRST_NONLOGGED_TIME DATE

SQL> recover datafile 7 ;
Media recovery complete.
SQL> select file_id, file_name from dba_data_files
2 where tablespace_name = 'SCOTTIE';
6
/tmp/scottie.dbf

7
/tmp/scottie.bdf


SQL> ed
Wrote file afiedt.buf
7[?47h[?1h="afiedt.buf" 3L, 82Cselect file_id, file_name from dba_data_files
where tablespace_name = 'SCOTTIE'
/
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~select file_id, file_nam-- INSERT --, from dba_data_filess from dba_data_filest from dba_data_filesa from dba_data_filest from dba_data_filess from dba_data_files from dba_data_filesu from dba_data_filess from dba_data_files:wq
"afiedt.buf" 3L, 89C written


[?1l>[?47l8
1 select file_id, file_name,status from dba_data_files
2* where tablespace_name = 'SCOTTIE'
SQL> /
6
/tmp/scottie.dbf
AVAILABLE

7
/tmp/scottie.bdf
AVAILABLE


SQL> desc v$datafile;
Name Null? Type
----------------------------------------- -------- ----------------------------
FILE# NUMBER
CREATION_CHANGE# NUMBER
CREATION_TIME DATE
TS# NUMBER
RFILE# NUMBER
STATUS VARCHAR2(7)
ENABLED VARCHAR2(10)
CHECKPOINT_CHANGE# NUMBER
CHECKPOINT_TIME DATE
UNRECOVERABLE_CHANGE# NUMBER
UNRECOVERABLE_TIME DATE
LAST_CHANGE# NUMBER
LAST_TIME DATE
OFFLINE_CHANGE# NUMBER
ONLINE_CHANGE# NUMBER
ONLINE_TIME DATE
BYTES NUMBER
BLOCKS NUMBER
CREATE_BYTES NUMBER
BLOCK_SIZE NUMBER
NAME VARCHAR2(513)
PLUGGED_IN NUMBER
BLOCK1_OFFSET NUMBER
AUX_NAME VARCHAR2(513)
FIRST_NONLOGGED_SCN NUMBER
FIRST_NONLOGGED_TIME DATE

SQL> select * from emp   scottie.emp ;
7369 SMITH CLERK 7902 17-DEC-80 800
20

7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300
30

7521 WARD SALESMAN 7698 22-FEB-81 1250 500
30

7566 JONES MANAGER 7839 02-APR-81 2975
20

7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400
30

7698 BLAKE MANAGER 7839 01-MAY-81 2850
30

7782 CLARK MANAGER 7839 09-JUN-81 2450
10

7788 SCOTT ANALYST 7566 19-APR-87 3000
20

7839 KING PRESIDENT 17-NOV-81 5000
10

7844 TURNER SALESMAN 7698 08-SEP-81 1500 0
30

7876 ADAMS CLERK 7788 23-MAY-87 1100
20

7900 JAMES CLERK 7698 03-DEC-81 950
30

7902 FORD ANALYST 7566 03-DEC-81 3000
20

7934 MILLER CLERK 7782 23-JAN-82 1300
10


14 rows selected.

SQL> analyze table scottie.emp compute statisticsc  ;

Table analyzed.

SQL> exit    desc v$datafile
Name Null? Type
----------------------------------------- -------- ----------------------------
FILE# NUMBER
CREATION_CHANGE# NUMBER
CREATION_TIME DATE
TS# NUMBER
RFILE# NUMBER
STATUS VARCHAR2(7)
ENABLED VARCHAR2(10)
CHECKPOINT_CHANGE# NUMBER
CHECKPOINT_TIME DATE
UNRECOVERABLE_CHANGE# NUMBER
UNRECOVERABLE_TIME DATE
LAST_CHANGE# NUMBER
LAST_TIME DATE
OFFLINE_CHANGE# NUMBER
ONLINE_CHANGE# NUMBER
ONLINE_TIME DATE
BYTES NUMBER
BLOCKS NUMBER
CREATE_BYTES NUMBER
BLOCK_SIZE NUMBER
NAME VARCHAR2(513)
PLUGGED_IN NUMBER
BLOCK1_OFFSET NUMBER
AUX_NAME VARCHAR2(513)
FIRST_NONLOGGED_SCN NUMBER
FIRST_NONLOGGED_TIME DATE

SQL> select file#,name from           status,name from v$datafile;
1 SYSTEM
/u01/app/oracle/oradata/devdb1/system01.dbf

2 ONLINE
/u01/app/oracle/oradata/devdb1/undotbs01.dbf

3 ONLINE
/u01/app/oracle/oradata/devdb1/sysaux01.dbf

4 ONLINE
/u01/app/oracle/oradata/devdb1/users01.dbf

5 ONLINE
/u01/app/oracle/oradata/devdb1/example01.dbf

6 ONLINE
/tmp/scottie.dbf

7 OFFLINE
/tmp/scottie.bdf


7 rows selected.

SQL> select enabled from v$datafile;
READ WRITE
READ WRITE
READ WRITE
READ WRITE
READ WRITE
READ WRITE
READ WRITE

7 rows selected.

SQL> select * from v$datafile;
1 9 30-JUN-05 0 1 SYSTEM READ WRITE
650612 31-OCT-08 0
561172 561173 28-OCT-08 503316480 61440
0 8192
/u01/app/oracle/oradata/devdb1/system01.dbf
0 8192
NONE
0

2 444079 30-JUN-05 1 2 ONLINE READ WRITE
650612 31-OCT-08 0
561172 561173 28-OCT-08 230686720 28160
0 8192
/u01/app/oracle/oradata/devdb1/undotbs01.dbf
0 8192
NONE
0

3 6609 30-JUN-05 2 3 ONLINE READ WRITE
650612 31-OCT-08 0
561172 561173 28-OCT-08 251658240 30720
0 8192
/u01/app/oracle/oradata/devdb1/sysaux01.dbf
0 8192
NONE
0

4 10566 30-JUN-05 4 4 ONLINE READ WRITE
650612 31-OCT-08 0
561172 561173 28-OCT-08 128450560 15680
0 8192
/u01/app/oracle/oradata/devdb1/users01.dbf
0 8192
NONE
0

5 467466 27-AUG-08 6 5 ONLINE READ WRITE
650612 31-OCT-08 0
561172 561173 28-OCT-08 104857600 12800
104857600 8192
/u01/app/oracle/oradata/devdb1/example01.dbf
0 8192
NONE
0

6 637748 31-OCT-08 7 6 ONLINE READ WRITE
650612 31-OCT-08 0
0 0 104857600 12800
104857600 8192
/tmp/scottie.dbf
0 8192
NONE
0

7 647743 31-OCT-08 7 7 OFFLINE READ WRITE
648264 31-OCT-08 0 648264
31-OCT-08 0 0 104857600 12800
104857600 8192
/tmp/scottie.bdf
0 8192
NONE
0


7 rows selected.

SQL> alter datafile '                
SQL>
SQL>
SQL> alter database    file      base datafile ' 7 online ;

Database altered.

SQL> host ls -ltr /tmp
total 206168
-r--r--r-- 1 root root 18650 May 13 2007 libaio-0.3.105-2.i386.rpm
-r--r--r-- 1 root root 1014759 May 13 2007 openmotif21-2.1.30-11.RHEL4.6.i386.rpm
drwx------ 2 root root 4096 Jul 24 2007 keyring-R9vpQI
drwx------ 2 root root 4096 Sep 5 2007 keyring-rXZAfi
drwx------ 2 oracle oinstall 4096 Mar 25 2008 keyring-7Av7GZ
srwxr-xr-x 1 oracle oinstall 0 Mar 25 2008 OSL_PIPE_500_SingleOfficeIPC_127f6b5c53f9ed4f4aadba64fadaf2ed
drwx------ 2 oracle oinstall 4096 Aug 26 16:48 keyring-UQkKFr
srwxr-xr-x 1 root root 0 Aug 27 15:18 mapping-root
drwx------ 2 oracle oinstall 4096 Oct 28 20:17 ssh-GZVyhq4093
drwx------ 3 oracle oinstall 4096 Oct 28 20:17 gconfd-oracle
drwx------ 2 oracle oinstall 4096 Oct 28 20:17 keyring-qm5QpG
srwxr-xr-x 1 oracle oinstall 0 Oct 28 20:17 mapping-oracle
-rw------- 1 oracle oinstall 0 Oct 28 20:59 cE9LpBbDvM
-rw------- 1 oracle oinstall 1125 Oct 28 21:08 xses-oracle.sraeNt
drwx------ 2 oracle oinstall 4096 Oct 28 21:08 orbit-oracle
-rwx------ 1 oracle oinstall 163 Oct 28 21:44 actie.sh
-rw-r--r-- 1 oracle oinstall 1215 Oct 31 20:44 import.log
-rw-r----- 1 oracle oinstall 104865792 Oct 31 20:59 scottie.dbf
drwxr-x--- 2 oracle oinstall 4096 Oct 31 21:01 hsperfdata_oracle
-rw-r----- 1 oracle oinstall 104865792 Oct 31 21:16 scottie.bdf

SQL> host rm scottie.dbf
rm: cannot remove `scottie.dbf': No such file or directory

SQL> host / r, m /tmp/scottie.dbf

SQL> rem m scottie.dbf is toch niet goed.
SQL> select * from v$daa tafile;
1 9 30-JUN-05 0 1 SYSTEM READ WRITE
650612 31-OCT-08 0
561172 561173 28-OCT-08 503316480 61440
0 8192
/u01/app/oracle/oradata/devdb1/system01.dbf
0 8192
NONE
0

2 444079 30-JUN-05 1 2 ONLINE READ WRITE
650612 31-OCT-08 0
561172 561173 28-OCT-08 230686720 28160
0 8192
/u01/app/oracle/oradata/devdb1/undotbs01.dbf
0 8192
NONE
0

3 6609 30-JUN-05 2 3 ONLINE READ WRITE
650612 31-OCT-08 0
561172 561173 28-OCT-08 251658240 30720
0 8192
/u01/app/oracle/oradata/devdb1/sysaux01.dbf
0 8192
NONE
0

4 10566 30-JUN-05 4 4 ONLINE READ WRITE
650612 31-OCT-08 0
561172 561173 28-OCT-08 128450560 15680
0 8192
/u01/app/oracle/oradata/devdb1/users01.dbf
0 8192
NONE
0

5 467466 27-AUG-08 6 5 ONLINE READ WRITE
650612 31-OCT-08 0
561172 561173 28-OCT-08 104857600 12800
104857600 8192
/u01/app/oracle/oradata/devdb1/example01.dbf
0 8192
NONE
0

6 637748 31-OCT-08 7 6 ONLINE READ WRITE
650612 31-OCT-08 0
0 0 104857600 12800
104857600 8192
/tmp/scottie.dbf
0 4294967295
NONE
0

7 647743 31-OCT-08 7 7 ONLINE READ WRITE
652121 31-OCT-08 0
0 0 104857600 12800
104857600 8192
/tmp/scottie.bdf
0 8192
NONE
0


7 rows selected.

SQL> alter tablespace scottie offline ;
alter tablespace scottie offline
*
ERROR at line 1:
ORA-01122: database file 6 failed verification check
ORA-01110: data file 6: '/tmp/scottie.dbf'
ORA-01251: Unknown File Header Version read for file number 6


SQL> sut  hutdown immedi      abort;
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
rac1:-) rman     startup mount;

ksh: startup: not found
rac1:-) sqlplus


SQL*Plus: Release 10.2.0.1.0 - Production on Fri Oct 31 21:18:32 2008

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

Enter user-name: start     / as sysdba
Connected to an idle instance.

SQL> startup mount ;
ORACLE instance started.

Total System Global Area 184549376 bytes
Fixed Size 1218412 bytes
Variable Size 117442708 bytes
Database Buffers 62914560 bytes
Redo Buffers 2973696 bytes
Database mounted.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
rac1:-) rman


Recovery Manager: Release 10.2.0.1.0 - Production on Fri Oct 31 21:18:47 2008

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

RMAN> connect target /

connected to target database: DEVDB1 (DBID=4274132913, not open)

RMAN> restore datafile 6;

Starting restore at 31-OCT-08
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=155 devtype=DISK

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00006 to /tmp/scottie.dbf
channel ORA_DISK_1: reading from backup piece /u03/flash_recovery_area/DEVDB1/backupset/2008_10_31/o1_mf_nnndf_TAG20081031T204552_4jpr3kch_.bkp
channel ORA_DISK_1: restored backup piece 1
piece handle=/u03/flash_recovery_area/DEVDB1/backupset/2008_10_31/o1_mf_nnndf_TAG20081031T204552_4jpr3kch_.bkp tag=TAG20081031T204552
channel ORA_DISK_1: restore complete, elapsed time: 00:00:16
Finished restore at 31-OCT-08

RMAN> recover database ;

Starting recover at 31-OCT-08
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:02

Finished recover at 31-OCT-08

RMAN> sql   alter database open ;

database opened

RMAN> exit


Recovery Manager complete.
rac1:-) cd /tmp

rac1:-) ls -trl

total 206168
-r--r--r-- 1 root root 18650 May 13 2007 libaio-0.3.105-2.i386.rpm
-r--r--r-- 1 root root 1014759 May 13 2007 openmotif21-2.1.30-11.RHEL4.6.i386.rpm
drwx------ 2 root root 4096 Jul 24 2007 keyring-R9vpQI
drwx------ 2 root root 4096 Sep 5 2007 keyring-rXZAfi
drwx------ 2 oracle oinstall 4096 Mar 25 2008 keyring-7Av7GZ
srwxr-xr-x 1 oracle oinstall 0 Mar 25 2008 OSL_PIPE_500_SingleOfficeIPC_127f6b5c53f9ed4f4aadba64fadaf2ed
drwx------ 2 oracle oinstall 4096 Aug 26 16:48 keyring-UQkKFr
srwxr-xr-x 1 root root 0 Aug 27 15:18 mapping-root
drwx------ 2 oracle oinstall 4096 Oct 28 20:17 ssh-GZVyhq4093
drwx------ 3 oracle oinstall 4096 Oct 28 20:17 gconfd-oracle
drwx------ 2 oracle oinstall 4096 Oct 28 20:17 keyring-qm5QpG
srwxr-xr-x 1 oracle oinstall 0 Oct 28 20:17 mapping-oracle
-rw------- 1 oracle oinstall 0 Oct 28 20:59 cE9LpBbDvM
-rw------- 1 oracle oinstall 1125 Oct 28 21:08 xses-oracle.sraeNt
drwx------ 2 oracle oinstall 4096 Oct 28 21:08 orbit-oracle
-rwx------ 1 oracle oinstall 163 Oct 28 21:44 actie.sh
-rw-r--r-- 1 oracle oinstall 1215 Oct 31 20:44 import.log
drwxr-x--- 2 oracle oinstall 4096 Oct 31 21:18 hsperfdata_oracle
-rw-r----- 1 oracle oinstall 104865792 Oct 31 21:24 scottie.dbf
-rw-r----- 1 oracle oinstall 104865792 Oct 31 21:24 scottie.bdf
rac1:-) hae    strings -a scottie.dbf | head -1 | od -c

0000000 } | { z \n
0000005
rac1:-) strings -a scottie.dbf | head -1 | od -c
rac1:-) strings -a scottie.dbf | head -12

0000000 } | { z \n D E V D B 1 \n
0000014
rac1:-) strings -a scottie.dbf | head -2 | od -c
rac1:-) strings -a scottie.dbf | head - | od -c 1 | od -c0 | od -c

0000000 } | { z \n D E V D B 1 \n S C O T
0000020 T I E \n , T A B L E _ E X P O R
0000040 T / T A B L E / C O N S T R A I
0000060 N T / R E F _ C O N S T R A I N
0000100 T \n R E F _ C O N S T R A I N T
0000120 \n , T A B L E _ E X P O R T / T
0000140 A B L E / C O N S T R A I N T /
0000160 R E F _ C O N S T R A I N T \n R
0000200 E F _ C O N S T R A I N T \n \t F
0000220 K _ D E P T N O \t F K _ D E P T
0000240 N O \n S C O T T I E \n S C O T T
0000260 \n
0000261
rac1:-) od -c scottie.dbf | pg

ksh: pg: not found
rac1:-) od -c scottie.dbf | pg
rac1:-) od -c scottie.dbf | p  more

0000000 \0 242 \0 \0 \0 \0 300 377 \0 \0 \0 \0 \0 \0 \0 \0
0000020 f 310 \0 \0 \0 \0 \0 \0 2 \0 \0 } | { z
0000040 240 201 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0
0000060 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0
*
0020000 \v 242 \0 \0 001 \0 200 001 \0 \0 \0 \0 \0 \0 001 004
0020020 253 303 \0 \0 \0 \0 \0 \0 \0 001 \n 261 027 302 376
0020040 D E V D B 1 \0 \0 h \t \0 \0 \0 2 \0 \0
0020060 \0 \0 \0 006 \0 003 \0 \0 \0 \0 \0 \0 \0 \0 \0
0020100 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0
*
0020140 \0 \0 \0 \0 4 273 \t \0 \0 \0 \0 \0 ' 026 351 '
0020160 221 5 345 ' 025 220 \b \0 \0 \0 \0 \0 \0 \0 \0 \0
0020200 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 004 \0 \f \0 \0 \0
0020220 q $ 351 ' \v \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0
0020240 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0
*
0020500 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0
0020520 \a \0 S C O T T I E \0 \0 \0 \0 \0 \0 \0
0020540 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0
0020560 006 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 ' 026 351 '
0020600 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0
*
0020640 365 333 221 ' { 316 006 \0 \0 \0 \0 \0 \0 \0 \0 \0
0020660 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0
*
0020740 \0 \0 \0 \0 317 A \n \0 \0 \0 377 277 r $ 351 '
0020760 001 \0 274 \f 027 \0 \0 \0 002 \0 \0 \0 020 \0 \0 \0
0021000 002 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0
0021020 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0
*
0021200 \0 \0 \0 \0 \n \0 \n \0 \n \0 001 \0 \0 \0 \0 \0
0021220 \0 \0 \0 \0 \0 \0 \0 \0 002 \0 200 001 \0 \0 \0 \0
0021240 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0
*
0037760 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 001 \v \0 \0
0040000 035 242 \0 \0 002 \0 200 001 302 340 \t \0 \0 \0 001 004
0040020 { \ \0 \0 006 \0 \0 \0 \b \0 \0 \0 \0 2 \0 \0
0040040 001 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \a \0 \0 \0
0040060 \0 2 \0 \0 001 \0 \0 \0 < 006 \0 \0 300 340 \t \0
0040100 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0
0040120 031 \0 \0 \0 \b \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0
0040140 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0
--More--
*
--More--
0057760 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 001 035 302 340
--More--
0060000 036 242 \0 \0 003 \0 200 001 302 340 \t \0 \0 \0 001 004
--More--
0060020 346 O \0 \0 006 \0 \0 \0 \t \0 \0 \0 \0 \0 \0 \0
--More--
0060040 001 \0 \0 \0 375 367 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0
--More--
0060060 \0 \0 \0 \0 \0 \0 \0 \0 201 001 \0 \0 \0 \0 \0 \0
--More--
0060100 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0
--More--
*
--More--
0077760 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 001 036 302 340
--More--
0100000 036 242 \0 \0 004 \0 200 001 9 273 \t \0 \0 \0 001 004
--More--
0100020 233 201 \0 \0 006 \0 \0 \0 \t 300 \a \0 \0 \0 \0 \0
--More--
0100040 \0 \0 \0 \0 \0 370 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0
--More--
0100060 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0
--More--
*
--More--
0117760 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 001 036 9 273
--More--
0120000 036 242 \0 \0 005 \0 200 001 ; 273 \t \0 \0 \0 001 004
--More--
rac1:-) od -c scottie.dbf | more
rac1:-) od c scottie.dbf | more
rac1:-) od scottie.dbf | more
rac1:-) od

0000000 121000 000000 000000 177700 000000 000000 000000 000000
0000020 144146 000000 020000 000000 031000 000000 076175 075173
0000040 100640 000000 000000 000000 000000 000000 000000 000000
0000060 000000 000000 000000 000000 000000 000000 000000 000000
*
0020000 121013 000000 000001 000600 000000 000000 000000 002001
0020020 141653 000000 000000 000000 000400 005040 013661 177302
0020040 042504 042126 030502 000000 004550 000000 031000 000000
0020060 020000 000000 000006 000003 000000 000000 000000 000000
0020100 000000 000000 000000 000000 000000 000000 000000 000000
*
0020140 000000 000000 135464 000011 000000 000000 013047 023751
0020160 032621 023745 110025 000010 000000 000000 000000 000000
0020200 000000 000000 000000 000000 000000 000004 000014 000000
0020220 022161 023751 000013 000000 000000 000000 000000 000000
0020240 000000 000000 000000 000000 000000 000000 000000 000000
*
0020500 000000 000000 000000 000000 000000 000000 000007 000000
0020520 000007 041523 052117 044524 000105 000000 000000 000000
0020540 000000 000000 000000 000000 000000 000000 000000 000000
0020560 000006 000000 000000 000000 000000 000000 013047 023751
0020600 000000 000000 000000 000000 000000 000000 000000 000000
*
0020640 155765 023621 147173 000006 000000 000000 000000 000000
0020660 000000 000000 000000 000000 000000 000000 000000 000000
*
0020740 000000 000000 040717 000012 000000 137777 022162 023751
0020760 000001 006274 000027 000000 000002 000000 000020 000000
0021000 000002 000000 000000 000000 000000 000000 000000 000000
0021020 000000 000000 000000 000000 000000 000000 000000 000000
*
0021200 000000 000000 000012 000012 000012 000001 000000 000000
0021220 000000 000000 000000 000000 000002 000600 000000 000000
0021240 000000 000000 000000 000000 000000 000000 000000 000000
*
0037760 000000 000000 000000 000000 000000 000000 005401 000000
0040000 121035 000000 000002 000600 160302 000011 000000 002001
0040020 056173 000000 000006 000000 000010 000000 031000 000000
0040040 000001 000000 000000 000000 000000 000000 000007 000000
0040060 031000 000000 000001 000000 003074 000000 160300 000011
0040100 000000 000000 000000 000000 000000 000000 000000 000000
0040120 000031 000000 000010 000000 000000 000000 000000 000000
0040140 000000 000000 000000 000000 000000 000000 000000 000000
--More--
rac1:-) pwd

/tmp
rac1:-) sqlplus


SQL*Plus: Release 10.2.0.1.0 - Production on Fri Oct 31 21:49:23 2008

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

Enter user-name: / as sysdba

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

SQL> select * from dual ;

D
-
X

SQL> drop tablespace scottie inclduing       uding contern  nts and datafiles;

Tablespace dropped.

SQL> create tablespc ace scottie datafile '/tmp/scottie.dbf';  size 10M ;

Tablespace created.

SQL> create table scott.in_user_tablespace tablespace users as select * from dba_objects ;
create index scott.
Table created.

SQL> index_)in   in_scoot  ttie_tablespace on scott.in_user_tablespace ( object_id ) ;

Index created.

SQL> host rm /tmp/scottie.dbf

SQL> alter index scott.index_in_c scottie_tablespce    ase    ce rebuil  d onlin  e tablespace users ;

Index altered.

SQL> select segment_name from dba_segments where tablespace_name = 'SCOTTIE';

no rows selected

SQL> alter index scott.index_in_scottie_tablespace rebuild online tablespace scottie ;

Index altered.

SQL> alter tal blespace              database datafile '/tmp/scottie.dbf' offline ;

Database altered.

SQL> alter index scott.index_in_scottie_tablespace rebuild online tablespace users ;

Index altered.

SQL> select segment_name from dba_segments where tablespace_name = 'SCOTTIE';

SEGMENT_NAME
--------------------------------------------------------------------------------
6.19

SQL> rem tempors ary segment blijft bestaan tot  dat de datafile weer online is.
SQL> alter database daf tafile '/tmp/scottie.dbf  ' online ;
alter database datafile '/tmp/scottie.dbf' online
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01110: data file 6: '/tmp/scottie.dbf'


SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
rac1:-) rman


Recovery Manager: Release 10.2.0.1.0 - Production on Fri Oct 31 21:55:59 2008

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

RMAN> connect target /

connected to target database: DEVDB1 (DBID=4274132913)

RMAN> resotre      tore datafile '/tmp/scottie.dbf';

Starting restore at 31-OCT-08
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=159 devtype=DISK

creating datafile fno=6 name=/tmp/scottie.dbf
restore not done; all files readonly, offline, or already restored
Finished restore at 31-OCT-08

RMAN> recover datafile '/tmp/scottie.dbf';

Starting recover at 31-OCT-08
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:04

Finished recover at 31-OCT-08

RMAN> alter datafile                exit


Recovery Manager complete.
rac1:-) sqlpl     ls -tlr /tmp/scoottie.dbf

-rw-r----- 1 oracle oinstall 10493952 Oct 31 21:56 /tmp/scottie.dbf
rac1:-) sqlplus


SQL*Plus: Release 10.2.0.1.0 - Production on Fri Oct 31 21:57:15 2008

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

Enter user-name: / as sysdba

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

SQL> select file           alter database datafile '/tmp/scottie.dbf' online ;

Database altered.

SQL> rem test van startup
SQL> startup force ;
ORACLE instance started.

Total System Global Area 184549376 bytes
Fixed Size 1218412 bytes
Variable Size 109054100 bytes
Database Buffers 71303168 bytes
Redo Buffers 2973696 bytes
Database mounted.
Database opened.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
rac1:-) /impdpimpdp scottie/scottie directory=tempdir network_link=scottie remap_sc >
rac1:-)

7[?47h[?1h="/tmp/sh25872.000" 1L, 130Cimpdp scottie/scottie directory=tempdir network_link=scottie remap_schema=scott:scottie tables=emp remap_tablespace=users:scottie
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~Type :quit to exit Vim:q
[?1l>[?47l8impdp scottie/scottie directory=tempdir network_link=scottie remap_schema=scott:scottie tables=emp remap_tablespace=users:scottie

Import: Release 10.2.0.1.0 - Production on Friday, 31 October, 2008 22:02:59

Copyright (c) 2003, 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
UDI-00001: user requested cancel of current operation


rac1:-) ^C
rac1:-) ^C
rac1:-) ^D


Script done on Fri 31 Oct 2008 10:03:01 PM CET
Geplaatst door op No comments:

netwerk import met impdp

impdp scottie/scottie directory=tempdir network_link=scottie remap_schema=scott:scottie tables=emp remap_tablespace=users:scottie
Geplaatst door op No comments:

Tuesday, October 21, 2008

rman plugged tablespaces not backupped

Dame, heren
Met onderstaand statement krijg je de tablespaces die niet door Rman worden meegenomen.
select distinct t.name from v$datafile vd
, sys.ts$ t
where vd.file# in (
select file# from v$datafile
minus
select distinct file# from v$backup_datafile )
and vd.plugged_in = 1
and t.ts#=vd.ts#
NB historie is wel afhankelijk van de database parameter control_file_record_keep_time.
Indien je een tablespace inplugged dan krijgt die in dba_tablespaces de status PLUGGED_IN=YES
Tevens krijg je de status PLUGGED_IN in v$datafile.
Zolang de tablespace niet een keer READ/WRITE zijn gezet worden ze niet gebackupped. Zet de tablespace read/write indien je geen andere backup van de betrokken datafiles hebt.
Skip_read_only vlag in rman heeft geen invloed.
Indien je met transportable tablespaces aan de slag moet moet je hier wel om denken!!!
Je kunt dit verifieren door met rman als volgt de betrokken backup van de tablespace op te vragen:
rman> connect target /
rman> list backup of tablespace ts1 ;


Het is allemaal gedocumenteerd:


Onderstaande extractie is uit de documentatie van 9,10 en 11.:

Oracle9i Recovery Manager ReferenceRelease 2 (9.2)Part Number A96565-0
When using the RMAN BACKUP command, you cannot perform any of the following actions:
Make a backup (either normal or incremental) in NOARCHIVELOG mode when the database is open or is closed after an instance failure or SHUTDOWN ABORT. You can only make a NOARCHIVELOG backup when the database after a consistent shutdown.
Stripe a single backup set across multiple channels.
Stripe a single input file across multiple backup sets.
Combine archived redo log files and datafiles into a single backup.
Back up files with different block sizes into the same backup set. RMAN can back up tablespaces with different block sizes, but puts each differently sized datafile into its own backup set.
Back up locally-managed temporary tablespaces (although you can back up dictionary-managed tablespaces)
Back up transportable tablespaces that were not made read/write after being transported.

Dit is in 10G ook het geval :
Oracle® Database Backup and Recovery Reference10g Release 2 (10.2)Part Number B14194-03
You cannot back up transportable tablespaces that were not made read-write after being transported.

Oracle® Database Backup and Recovery Reference11g Release 1 (11.1)Part Number B28273-03
f the following conditions are met, then RMAN can back up transportable tablespaces that have not been made read/write after being transported:
The COMPATIBLE initialization parameter is set to 11.0.0 or higher.
You are using an Oracle Database 11g RMAN client.
If any of the preceding conditions is not met, then RMAN automatically skips transportable tablespaces that have not yet been made read/write. Note that if you specify a transportable tablespace explicitly when any of the conditions is not met, then RMAN issues an error saying that the tablespace does not exist.
Geplaatst door op No comments:

Out of memory at line 56 Apex applications Oracle

In Oracle Application Express kan het voorkomen dat de melding Out of memory at line 56 optreedt.
De oorzaak is dat er een oude versie (9.0.16.0) van Adobe Flash Player wordt gebruikt.
Na de upgrade zijn treden deze fouten niet meer op.
Download ; http://www.adobe.com/

Dank aan Alje Dijksterhuis om dit voor me uit te zoeken.

In English :

In Oracle Application Expres sometimes 'Out of memory at line 56' errors occurs.
When using old version ( 9.0.16.0 ) of Adobe Flash Player the error raises.
After upgrading the errors disappeared.
Download ; http://www.adobe.com/


Thanks to Alje Dijksterhuis for sorting this out.
Geplaatst door op No comments:
Labels:
Subscribe to: Posts (Atom)