- On the source machine:
- Set the tablespace(s) into read only mode
- SQL> Alter tablespace <tablespace> read only
- Set the tablespace(s) into read only mode
- Export the tablespace meta data using export
- % expdp system/<password> DUMPFILE=expdat.dmp DIRECTORY = dpump_dir TRANSPORT_TABLESPACES = <list of tablespaces separated by commas> TRANSPORT_FULL_CHECK=Y
- If the dumpdir is not set up, you will get an ‘invalid’ directory error:
- SQL> CREATE DIRECTORY dmpdir as ‘/somedir’;
- SQL> GRANT read,write on DIRECTORY to system;
- Export the data converting on the fly:
- Determine the platform name for the destination machine:
- SQL> SELECT PLATFORM_ID, PLATFORM_NAME, ENDIAN_FORMAT FROM V$TRANSPORTABLE_PLATFORM
- % rman TARGET /
- RMAN> CONVERT TABLESPACE <list of tablespaces separated by commas> to PLATFORM ‘<platform name from previous step>’ FORMAT=’/somedir/%U’;
- Determine the platform name for the destination machine:
- Put the tablespaces into read/write mode
- SQL> alter tablespace <tablespace> READ WRITE;
- Transfer the files to the destination machine (Setup the dumpdir if you haven’t already)
- Create the oracle user(s) with the same names as on the source oracle (if you don’t you will need to remap the ownership using the REMAP_SCHEMA for the impdb )
- Import the tablespace schema and data (repeat for each tablespace)
- % impdp system/<password> DUMPFILE=expdat.dmp DIRECTORY = dpump_dir TRANSPORT_DATAFILES = <Full path to each tablespace datafile separated by commas>
- Put the tablespaces in to read/write mode
- SQL> alter tablespace <tablespace> READ WRITE;
Tag Archive: Database
I asked Jeff Tallman if I could redistribute his excellent MDA posters for Sybase’s ASE 15.5 database server. He said yes so … here they are in both Adobe PDF and Sybase PowerDesigner PDM formats!
Jeff Tallman also provided us with the MDA posters for Sybase 15.0.3 last year.
Back in December 
of 2007, Andrew E. Bruno wrote an excellent article on how to use the open source program Dia to design databases. He used the resultant design to create a MySQL database but the same principles could be used to create just about any relational database from the design. Dia is available for Linux, *nix, and Windows.
Database Design with Dia
In this post I’m going to give a quick how-to on creating database schemas with a wonderful tool called Dia. I’ve often found having a nice visual representation of a database to be quite helpful but can’t stand keeping it up to date. As soon as you add a new column or change the design around you end up having to sync your visual diagram with your SQL files. It’s tedious having to manage the various SQL for building the database and this can be a larger pain when trying to support different database platforms each with their own SQL syntax. So before you create your next database read on and see how Dia can make your life a bit easier.
Dia is a program for creating diagrams and for this exercise we’ll be creating UML diagrams from within Dia. We’re also going to use a perl script called tedia2sql which will transform our Dia files directly to SQL for our target database. What’s also nice about creating database schemas this way is that you can generate SQL for multiple target databases without the maintenance overhead.
Over at the Database Geek Blog, Lewis Cunningham has provided the first of three blog posts on Oracle Security. The first post is well done IMHO:
Oracle 10g+ Security and Audit – Part 1
This is a three-part definition of Oracle Security (specifically in 10g but applies to later versions also). Part 1 covers the various types of security Oracle provides. Part 2 deals with Users/Schemas, Roles, Permissions and Data Access. Part 2 will be a more technical discussion than parts 1 or 3. In part 3, I will discuss implementing an Oracle auditing scheme and how to ensure you comply with security and audit regulations.
Sybase graciously still allows us to download SQL Server (since renamed ASE) 11.0.3.3 for Linux… for FREE:
The current version (ASE 11.0.3.3 ESD#6) package is different from previous distribution of ASE 11.0.3.3. The
Sybase
new package separates ASE and OC/OS SDK. Also OC/OS is now available for Alpha platform. You can also find FreeBSD version of the package. Intel, Alpha and FreeBSD releases differ only in packaging and they are all built from the same source.
Software is FREE for development as well as Deployment.
Adaptive Server Enterprise 11.0.3.3 ESD#6 for Linux runs on Linux systems with Kernel 2.2.12 and glibc-2.1.2-11 installed. Verify that your system meets the hardware/software requirements before installing the software.
ASE 11.0.3.3 Linux download and install instructions from Europe site.
To install Linux ASE 11.0.3.3 software:
- Download the following packages. The downloadable image are in RPM (Linux) or GNU-zipped tar (FreeBSD) format for the available different platforms.
Linux Intelsybase-ase-11.0.3.3-6.i386.rpm – ASE (run-time) 20MB archive, 70MB installed
sybase-ocsd-10.0.4-6.i386.rpm – OCSD (software dev kit) 19MB archive, 60MB installed
sybase-doc-11.0.3.3-6.i386.rpm – DOC (PostScript and html) 9MB archive, 40MB installed
Linux Alpha
sybase-ocsd-10.0.4-5.alpha.rpm – OCSD (software dev kit) 18MB archive, 56MB installed
sybase-doc-11.0.3.3-5.alpha.rpm – DOC (PostScript and html) 9MB archive, 40MB installed
FreeBSD with Linux ABI installed (The SDK is native)
sybase-ase-11.0.3.3-FreeBSD-i386.tgz – ASE (run-time) 20MB archive, 70MB installed
sybase-ocsd-10.0.4-FreeBSD-i386.tgz – OCSD (FreeBSD native SDK) 9MB archive, 30MB installed
sybase-doc-11.0.3.3-FreeBSD-i386.tgz – DOC (PostScript and html) 9MB archive, 40MB installed
- Download Cover Letter and follow the instructions. After extracting the software README files in various directories, $SYBASE/CONFIGURE and $SYBASE/doc/howto/howto-ase-quickstart.html provide more information to install and configure the product
