Datapump en 12c, incorpora un nuevo parámetro "VIEWS_AS_TABLES". Con este parámetro, podemos exportar una vista desde una base de datos origen e importarla como tabla en una base destino.
Veamos como funciona esto:
Creamos primero una vista:
SQL> create view dba_view( emp_number) as select emp from dbatool;
View created.
Verificamos:
SQL> select owner,object_name,object_type from dba_objects where object_name='DBA_VIEW';
OWNER OBJECT_NAM OBJECT_TYPE
------------------ ---------- -----------------------
SYS DBA_VIEW VIEW
Realizamos un export de la vista, como tabla, utilizando el parámetro: views_as_tables
[oracle@localhost ~]$ expdp dumpfile=view.dmp logfile=view.log directory=DUMP views_as_tables=DBA_VIEW
Export: Release 12.1.0.2.0 - Production on Sat Aug 22 07:07:54 2015
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
Username: sys/oracle@ORCL as sysdba
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Starting "SYS"."SYS_EXPORT_TABLE_01": sys/********@ORCL AS SYSDBA dumpfile=view.dmp logfile=view.log directory=DUMP views_as_tables=DBA_VIEW
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE
. . exported "SYS"."DBA_VIEW" 5.117 KB 8 rows
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
/home/oracle/DUMP/view.dmp
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at Sat Aug 22 07:08:17 2015 elapsed 0 00:00:11
Mostrando entradas con la etiqueta Datapump. Mostrar todas las entradas
Mostrando entradas con la etiqueta Datapump. Mostrar todas las entradas
[ 2017-07-23 ]
[ 2016-08-30 ]
Paralelizando datapump en instancias específicas de un RAC
En un ambiente de RAC, datapump utiliza todas las instancias disponibles para paralelizar cuando el parametro cluster=y (default).
Para limitar la cantidad de instancias podemos hacer lo siguiente:
Usar el parametro de DP service_name.
Crear un servicio en la base RAC indicando las instancias que queremos utilizar.
$ srvctl add service -d <DatabaseName> -s nodes_2_3 -a RAC2,RAC3 -r RAC1,RAC4 -P BASIC
Y luego ejecutar datapump con los parametros:
cluster=y service_name=nodes_2_3
Ref: How To Limit The Data Pump Slaves To Only A Handful Of RAC Instances? (Doc ID 1590699.1)
Para limitar la cantidad de instancias podemos hacer lo siguiente:
Usar el parametro de DP service_name.
Crear un servicio en la base RAC indicando las instancias que queremos utilizar.
$ srvctl add service -d <DatabaseName> -s nodes_2_3 -a RAC2,RAC3 -r RAC1,RAC4 -P BASIC
Y luego ejecutar datapump con los parametros:
cluster=y service_name=nodes_2_3
Ref: How To Limit The Data Pump Slaves To Only A Handful Of RAC Instances? (Doc ID 1590699.1)
[ 2015-11-09 ]
Buscando SID y SERIAL de un datapump job
set lines 150 pages 100 numwidth 7
col program for a38
col username for a10
col spid for a7
select to_char (sysdate,'YYYY-MM-DD HH24:MI:SS') "DATE", s.program, s.sid,
s.status, s.username, d.job_name, p.spid, s.serial#, p.pid
from v$session s, v$process p, dba_datapump_sessions d
where p.addr=s.paddr and s.saddr=d.saddr;
col program for a38
col username for a10
col spid for a7
select to_char (sysdate,'YYYY-MM-DD HH24:MI:SS') "DATE", s.program, s.sid,
s.status, s.username, d.job_name, p.spid, s.serial#, p.pid
from v$session s, v$process p, dba_datapump_sessions d
where p.addr=s.paddr and s.saddr=d.saddr;
[ 2015-01-06 ]
Cross-platform Transportable Tablespaces made easy
Comparto un post muy interesante sobre Transportable Tablespaces publicado por Harald van Breederode en su blog: https://prutser.wordpress.com/
Back in Oracle8i the Transportable Tablespace feature was introduced to make it convenient to transport a large amount of data between databases. In Oracle10g this useful feature was enhanced with cross-platform support which allowed a tablespace, or set of tablespaces, to be transported between databases deployed on different hardware platforms (even between platforms with a different endian format).
So until now the procedure to transport a tablespace set across platforms was as follows:
Place the tablespace set in read-only mode on the source database.
Verify that the tablespace set is self-contained.
Export the tablespace set’s metadata from the source database using the EXPDP utility.
Convert the datafile(s) to the target platform endian format using RMAN on the source database server.
Copy both the metadata dumpset and the (converted) datafile(s) to the target database server.
Convert the datafile(s) to the target database endian format using RMAN on the target database server.
Import the metadata dumpset into the target database using the IMPDP utility.
Place the tablespace set in read-write mode on the target database.
Note: You either perform step 4 or step 6 but not both.
As the list shows, RMAN, EXPDP and IMPDP have to be used to transport tablespace(s) across platforms. Also the RMAN CONVERT statement used in either step 4 or 6 operates only on datafiles.
What if this procedure was simpler and faster?
A cool new Oracle Database 12c feature is the ability to transport tablespaces across platforms using just RMAN and RMAN (compressed) backupsets! Lets see how this works by transporting a tablespace from Oracle Linux to Oracle Solaris.
SQL> select banner from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
CORE 12.1.0.2.0 Production
TNS for Linux: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production
Back in Oracle8i the Transportable Tablespace feature was introduced to make it convenient to transport a large amount of data between databases. In Oracle10g this useful feature was enhanced with cross-platform support which allowed a tablespace, or set of tablespaces, to be transported between databases deployed on different hardware platforms (even between platforms with a different endian format).
So until now the procedure to transport a tablespace set across platforms was as follows:
Place the tablespace set in read-only mode on the source database.
Verify that the tablespace set is self-contained.
Export the tablespace set’s metadata from the source database using the EXPDP utility.
Convert the datafile(s) to the target platform endian format using RMAN on the source database server.
Copy both the metadata dumpset and the (converted) datafile(s) to the target database server.
Convert the datafile(s) to the target database endian format using RMAN on the target database server.
Import the metadata dumpset into the target database using the IMPDP utility.
Place the tablespace set in read-write mode on the target database.
Note: You either perform step 4 or step 6 but not both.
As the list shows, RMAN, EXPDP and IMPDP have to be used to transport tablespace(s) across platforms. Also the RMAN CONVERT statement used in either step 4 or 6 operates only on datafiles.
What if this procedure was simpler and faster?
A cool new Oracle Database 12c feature is the ability to transport tablespaces across platforms using just RMAN and RMAN (compressed) backupsets! Lets see how this works by transporting a tablespace from Oracle Linux to Oracle Solaris.
SQL> select banner from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
CORE 12.1.0.2.0 Production
TNS for Linux: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production
[ 2014-03-03 ]
Oracle Database 12c: Full Transportable Export/Import
Introduction
Oracle Database 12c includes significant new features such as the Oracle Multitenant Option.
This multitenant architecture includes the ability to create pluggable databases (PDBs), a new way to consolidate multiple databases into a single multitenant container database (CDB).
Many customers will look forward to saving on database management costs, achieving more efficient hardware utilization, and realizing economies of scale by migrating to a consolidated database environment using pluggable databases.
Traditional methods used for database migration work seamlessly with pluggable databases.
Original Export/Import, Data Pump export/import, and transportable tablespaces can all be
used to migrate from earlier versions of Oracle Database to a pluggable database running with Oracle Database 12c. In addition, an exciting new feature, full transportable export/import, is available to make migration to Oracle Database 12c faster, easier, and more efficient than ever before.
This white paper describes the full transportable export/import feature in Oracle Database 12c. After giving an overview of the benefits of using full transportable export/import, it explains how the feature works and provides a detailed example of full transportable export/import to show the syntax and process flow of using this feature. Note that, while this white paper focuses on the use of full transportable export/import in a pluggable database environment, this feature can be used generally for migrations to a Oracle Database 12c non-CDB database as well.
http://www.oracle.com/technetwork/database/enterprise-edition/full-transportable-wp-12c-1973971.pdf
Oracle Database 12c includes significant new features such as the Oracle Multitenant Option.
This multitenant architecture includes the ability to create pluggable databases (PDBs), a new way to consolidate multiple databases into a single multitenant container database (CDB).
Many customers will look forward to saving on database management costs, achieving more efficient hardware utilization, and realizing economies of scale by migrating to a consolidated database environment using pluggable databases.
Traditional methods used for database migration work seamlessly with pluggable databases.
Original Export/Import, Data Pump export/import, and transportable tablespaces can all be
used to migrate from earlier versions of Oracle Database to a pluggable database running with Oracle Database 12c. In addition, an exciting new feature, full transportable export/import, is available to make migration to Oracle Database 12c faster, easier, and more efficient than ever before.
This white paper describes the full transportable export/import feature in Oracle Database 12c. After giving an overview of the benefits of using full transportable export/import, it explains how the feature works and provides a detailed example of full transportable export/import to show the syntax and process flow of using this feature. Note that, while this white paper focuses on the use of full transportable export/import in a pluggable database environment, this feature can be used generally for migrations to a Oracle Database 12c non-CDB database as well.
http://www.oracle.com/technetwork/database/enterprise-edition/full-transportable-wp-12c-1973971.pdf
Suscribirse a:
Entradas (Atom)