Monthly Archives: June 2018

DATAPUMP EXPDP/IMPDP – TABLES EXPORT AND IMPORT

Trying to export tables from one database to another database:

Source Database:

expdp system/manager directory=TEMP tables=SCHEMA1.TABLENAME1,SCHEMA2.TABLENAME2 dumpfile=SCHEMA_TABLES.dmp logfile=expdp_SCHEMATABLES.log

Dumpfile will be created under TEMP (To finout the Path/location: select DIRECTORY_PATH from dba directories where DIRECTORY_NAME=’TEMP’)

Target Database:

Copy the file from source database to target DATA_PUMP_DIR, use above query to findout location of directory DATA_PUMP_DIR.

Make sure tablespace names in source and target database should be same, other wise use remap_tablespace to avoid the issues.

impdp system/manager directory=DATA_PUMP_DIR dumpfile=SCHEMA_TABLES.dmp logfile=impdp_SCHEMATABLES.log

In impdp command, no need to expiciltly mention tables parameter as dumpfile contains two tables. If you wish to import only one table, use TABLES parameter set to required table so that other table will not import.

 

Thank You !!

Connect to Oracle Cloud Database Server using SSH

1. Login to http://cloud.oracle.com

2. Click on Dashboard -> Services -> Database

3. Click on Instance

4. Find out the PUBLIC IP ADDRESS from Instance overview.

5. Configure SSH PUTTY connectivity as below:

Provide Public IP Address:

Provide default username “opc” -> Under Connection -> data

Click on SSH -> AUH, Please provide saved private key location. which was asked you to save private key in previous post generate public and private keys.

6. Click on open, now you will connect to the Server:

As you connected to default user “opc”, please sudo to oracle user and access the database.

Thank you !!

Deploy Application using WEBLOGIC ADMIN Console

1. Login to Admin console http://hostname:7001/console

2. Go to Deployments under Domain structure. Click on deployments.
Click on “Lock&Edit” under change center.

Weblogic_deploy1

3. Click on install:

Weblogic_deploy2

4. Browse the location where the war/ear file placed on the server and check the radio button.

Weblogic_deploy3

5. Check the radio button, Install this deployment as an Application.

There are cases we may need to install libraries to support application, in such cases we will choose other library option.

Weblogic_deploy4

6. Select deployment target

Weblogic_deploy5

7. Check the radio button where our deployment file existed and click on Finish.

Weblogic_deploy6

8. Finish the process, save the changes and Activate it.

Now Start the Deployment which is in prepared state, Go to deployments, click on control, click ‘Start’ button. Select ‘Servicing all requests’. Click Yes to continue.

Weblogic_deploy7

Now the Deployment state comes to ACTIVE status.

Thank you !!

ORA-39142: incompatible version number 5.1 in dump file

PROBLEM:

Trying to export schema/tables from higher Database version 12.2.0.1.0 and import to version 12.1.0.1.0, causing following error message.

ERROR:

Import: Release 12.1.0.2.0 – Production on Wed Jun 20 05:22:35 2018

Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.

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
ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-39142: incompatible version number 5.1 in dump file “xxx.dmp”

SOLUTION:

Please set parameter Version=12.1 in export command and import the data without issue.

Source Database(12.2.0.1):

expdp scott/tiger@PRIM directory=DATAPUMP tables=scott.testtb Version=12.1 dumpfile=Exp_Scott_test.dmp logfile=Exp_Scott_test.log

Target Database(12.1.0.1):

impdp scott/tiger@TEST directory=DATAPUMP tables=scott.testtb dumpfile=Exp_Scott_test.dmp logfile=Imp_Scott.log

Thank you !!

Configure listener and tnsnames files (Oracle Network Configuration)

listener.ora:

The “listerner.ora” file contains server side network configuration parameters. It can be found in the “$ORACLE_HOME/network/admin” directory on the server.

Make sure hostname, oracle_home location, port number and db names are pointed correct in below example:

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = <<SERVER NAME/IP>>)(PORT = 1521))
)
)

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(ORACLE_HOME= /u01/oracle/product/12c)
(SID_NAME = ORCL)
)
)

Listener Operations:

lsnrctl status –> To know the status
lsnrctl start –> To start the service
lsnrctl stop –> To stop the service
lsnrctl reload –> To restart the service

tnsnames.ora:

The “tnsnames.ora” file contains client side network configuration parameters. It can be found in the “$ORACLE_HOME/network/admin” directory on the client.

ORCL=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=<<SERVERNAME/IP>>)(PORT=1521))
(CONNECT_DATA=
(SERVICE_NAME=ORCL)
(INSTANCE_NAME=ORCL)
)
)

To avoid confusion on names, normally we keep listener name and tns name set to DBNAME for better understanding.

 

Thank you !!

Clone/Refresh Database using RMAN

For our understanding

Source Database: PRIM (It would be TARGET in case of RMAN Terminology)

Auxiliary/Clone Database: TEST

1) Take FULL backup on source database using RMAN:

Verify Source database is configured in archivelog mode.

SQL> archive log list;

Database log mode Archive Mode

Automatic archival Enabled

RMAN> backup database plus archivelog;

2) Perform following preparation tasks in Auxiliary Database:

Create a password file for the duplicate/Target instance, create password file in source and copy to auxiliary would also work here.

orapwd file=/u01/oracle/product/12c/dbs/orapwTEST password=password entries=10

Configure listener and tnsnames files for Auxiliary/TEST database server. Configure listener and tnsnames files

Create pfile for auxiliary environment and start the database to nomount status:

Add following entries to pfile, in case directory path for Auxiliary server is different for DB and log files when compare to source database.

Db_file_name_convert=’target db(PRIM) oradata path’,’clone db(TEST) oradata path’
Log_file_name_convert=’target db(PRIM) oradata path’,’clone db(TEST) oradata path

SQL> conn /as sysdba
SQL> startup nomount pfile=/u01/oracle/product/12c/dbs/initTEST.ora

 Add tnsentries of source database in Auxiliary, please do vice versa

3) duplicate command to create TEST database:

Veryfy the RMAN connection connecting to TARGET Database(PRIM):

rman TARGET sys/password@PRIM

Veryfy the RMAN connection connecting to TARGET Database(PRIM) and AUXILIARY(TEST):

rman TARGET sys/password@PRIM auxiliary /

Now We can use duplicate database command to create TEST database.

rman > duplicate target database to ‘TEST’;

This process takes a while depending on the size of database and server configuration.

Thank you !!

 

 

Generate Public and Private Keys using PuTTYgen

Start the PuTTYgen utility, by double-clicking on its .exe file

For Type of key to generate, select RSA. In the Number of bits in a generated key field, specify either 2048 or 4096 (increasing the bits makes it harder to crack the key by brute-force methods). Click the Generate button.

Putty Gen1

Move your mouse pointer around in the blank area of the Key section, below the progress bar (to generate some randomness) until the progress bar is full.

Putty Gen2

A private/ public key pair has now been generated, Click the Save public and private keys. If you lose your keys and have disabled username/password logins, you will no longer be able to log in server.

Putty Gen3

Thank you !!

 

 

Creating Single Instance Database in Oracle Cloud

 

Login to Oracle cloud (https://cloud.oracle.com/home) by providing Identity Domain, you will see following Dashboard console:

Cloud creat1

Click on create Instance and database:

Cloud create2

Click on Custom:

Cloud create3

Provide instance name, DB Software version and other details…

Cloud create4

Provide DB Name and other details as below, Most important upload ssh public key which can be generated using puttygen Generate Public and Private Keys using PuTTYgen, save respective private key which can be used to connect server once after the configuration.

Please provide username and password when you are planning to use cloud storage container for backups.

Cloud create5

Double check the provided configuration before click on create database

Cloud create6

Database creation and configuration takes a while to complete the process and there you go..

Cloud create7

Thank you !!