Oracle 12c Logo

Create Pluggable Database Manually using SQL Prompt

In earlier article, we have seen the method of creating pluggable databases using configuration assistant.

In this article we will have a look & hands-on on creating a pluggable database manually with the help of SQL Prompt.

We can create PDB using PDB_FILE_NAME_CONVERT initialization parameter, In following example i am going to create new Pluggable Database, name: ‘newpdb’

[oracle@OL6 ~]$ sqlplus “/ as sysdba”
// Ensure database globale name by following SQL query:
SQL> select global_name from global_name;

GLOBAL_NAME Continue reading

Oracle 11g Logo

Enabling Archive log mode in RAC – Oracle 11g

In Database environment, set following database parameter as sysdba:

ALTER SYSTEM SET log_archive_format=’arch_%t_%s_%r.arc’ SCOPE=spfile;
ALTER SYSTEM SET log_archive_dest_1=’location=/u01/oradata/MYSID/archive/’ SCOPE=spfile;

In Grid environment, stop the entire clustered database by following command as oracle user.
$ srvctl stop database -d SID
Note:
By above command, database will be shutdown.
SID: Global database name.

In Database environment, issue the following command to enable archive log mode:
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
SHUTDOWN IMMEDIATE;

In Grid environment, start the entire clustered database by following command as oracle user:
$ srvctl start database -d MYSID
By above method you will alter your RAC database to archive log mode.
************************************************************************

Note: Please don’t hesitate to revert in case of any query OR feedback.

Thanking you.

Have a easy life ahead.

Enabling Archive log mode in RAC – Oracle 10g

In Database environment, set following database parameter as sysdba:

ALTER SYSTEM SET log_archive_format=’arch_%t_%s_%r.arc’ SCOPE=spfile;
ALTER SYSTEM SET log_archive_dest_1=’location=Path_of_archive_dir’ SCOPE=spfile;

Mount your database in exlusive mode by following ‘alter system’ command:
ALTER SYSTEM SET cluster_database=FALSE SCOPE=spfile;
Note:
Database is not accessible to any application or user, being in exclusive mode.

In Grid environment, stop the entire clustered database by following command as oracle user.
$ srvctl stop database -d SID
Note:
By above command, database will be shutdown.

SID: Global database name.

 

In Database environment, issue the following command to enable archive log mode:
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER SYSTEM SET cluster_database=TRUE SCOPE=spfile;
SHUTDOWN IMMEDIATE;

Note:
CLUSTER_DATABASE parameter has been changed to its orignal value.

In Grid environment, start the entire clustered database by following command as oracle user:
$ srvctl start database -d MYSID

In Database environment, Ensure changes by following SQL command:
SQL> archive log list;

 

By above method you will alter your RAC database to archive log mode.

************************************************************************

Note: Please don’t hesitate to revert in case of any query OR feedback.

Thanking you.

Have a easy life ahead.

SQL Server 2014 logo

How to uninstall SQL-Server 2014 Instance

In this article you will find a perfect way to uninstall a stand-alone instance of SQL Server 2014.

Pre-requisites to uninstall an SQL Server instance:

  • You must login system as local administrator with permission to log on as a service.
  • You must have minimum amount of physical memory with enough page size. ( page size = 2 times of physical memory )

Important points to be consider before uninstall SQL Server 2014:

  • Backup your database.
  • Drop/Delete local security groups.
  • All SQL server services must be stop. This would leads to successful removal of SQL Server.

Continue reading

SQL Server 2014 logo

SQL server 2014 stand alone installation

This article shows you step by step installation guide of SQL server 2012 ( stand alone ) on windows data center edition 64bit OS.

 

Download SQL Server 2012 from Microsoft website.

Following are the step by step guide with screenshot to install SQL server 2014:

Double click on Setup.exe this will pop up SQL server Installation Center prompt as below:

SQL server 2014 stand alone installation

SQL server 2014 stand alone installation

 

SQL server 2014 stand alone installation - SQL Server Installation Center

SQL server 2014 stand alone installation – SQL Server Installation Center

 

On SQL Server Installation Center wizard,

Click on Installation option available to left panel of SQL server Installation Center.

Then click on New SQL Server Stand-alone-installation or add features to an existing installation.

SQL server 2014 stand alone installation - SQL Server Installation Center

SQL server 2014 stand alone installation – SQL Server Installation Center

 

SQL server 2014 stand alone installation

SQL server 2014 stand alone installation

 

On Product Key wizard,

Provide your product key & click next.

In case you are evaluating SQL Server 2012 then go with Evaluation option.

SQL server 2014 stand alone installation - Product Key

SQL server 2014 stand alone installation – Product Key

 

On License Terms wizard,

Accept license terms & condition, click next.

SQL server 2014 stand alone installation - License Term

SQL server 2014 stand alone installation – License Term

 

On Install Rules wizard,

System Configuration Checker verifies your system Rules…

Click next.

SQL server 2014 stand alone installation - Install Rules

SQL server 2014 stand alone installation – Install Rules

 

On Install Setup Files wizard,

Downloading updates…

SQL server 2014 stand alone installation - Install Setup Files

SQL server 2014 stand alone installation – Install Setup Files

 

On Setup Role Wizard,

Select SQL Server Feature Installation radio button.

Click next.

SQL server 2014 stand alone installation - Setup Roles

SQL server 2014 stand alone installation – Setup Roles

 

On Feature Selection wizard,

Select feature according to your convenience.

Click next.

SQL server 2014 stand alone installation - Feature Selection

SQL server 2014 stand alone installation – Feature Selection

 

On Instance Configuration wizard,

Select Named instance radio button & give name of the instance.

In case of you wish to continue with default instance.

Click next.

SQL server 2014 stand alone installation - Instance Configuration

SQL server 2014 stand alone installation – Instance Configuration

 

On Server Configuration wizard, select Account Name as  NT AUTHORITY/NETWORK.

Set Startup Type as Automatic.

Click next.

SQL server 2014 stand alone installation - Server Configuration

SQL server 2014 stand alone installation – Server Configuration

 

On Database Engine Configuration wizard,

Select Mixed Mode radio button.

Enter your password.

Add current user.

Click next.

SQL server 2014 stand alone installation - Database Engine Configuration

SQL server 2014 stand alone installation – Database Engine Configuration

 

On Ready to Install wizard, Verify summary.

Click next.

SQL server 2014 stand alone installation - Ready to Install

SQL server 2014 stand alone installation – Ready to Install

 

On Installation Progress wizard, this will show you progress of software  installation.

SQL server 2014 stand alone installation - Installation Progress

SQL server 2014 stand alone installation – Installation Progress

 

On Complete Wizard, you will find list of features have been installed with status of completion.

SQL server 2014 stand alone installation

SQL server 2014 stand alone installation

 

By above steps you will accomplish installation of SQL Server 2014.

************************************************************************

Note: Please don’t hesitate to revert in case of any query OR feedback.

Thanking you.

Have a easy life ahead.

Oracle 11g Logo

ORA-15260: permission denied on ASM disk group

If you are connected to your ASM instance with SYSDBA & try to add disk to your existing diskgroup then mentioned error will be appeared.

Workaround for the oracle error: ‘ORA-15260: permission denied on ASM disk group’ & ‘ORA-15032: not all alterations performed’ is to connect your ASM instance as SYSASM.

// Please consider following example, in which i was failed to alter existing ASM diskgroup with as SYSDBA: ( In my case DATA is my existing DG )
[root@database1 ~]# su – oracle
[oracle@database1 ~]$ grid_env
[oracle@database1 grid]$ echo $ORACLE_HOME
/u01/app/11.2.0/grid
[oracle@database1 ~]$ sqlplus “/ as sysdba”

SQL*Plus: Release 11.2.0.3.0 Production on Tue May 20 14:54:00 2014
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
With the Real Application Clusters and Automatic Storage Management options

SQL> ALTER DISKGROUP DATA ADD DISK ‘/dev/oracleasm/disks/RMAN’ NAME DATA_0003 REBALANCE POWER 11;
ALTER DISKGROUP DATA ADD DISK ‘/dev/oracleasm/disks/RMAN’ NAME DATA_0003 REBALANCE POWER 11
*
ERROR at line 1:
ORA-15032: not all alterations performed
ORA-15260: permission denied on ASM disk group

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
With the Real Application Clusters and Automatic Storage Management options
// Now connect ASM instance as SYSASM as follow:

[oracle@database1 ~]$ grid_env

[oracle@database1 ~]$ sqlplus “/ as sysasm”

SQL*Plus: Release 11.2.0.3.0 Production on Tue May 20 14:54:38 2014
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
With the Real Application Clusters and Automatic Storage Management options

SQL> ALTER DISKGROUP DATA ADD DISK ‘/dev/oracleasm/disks/RMAN’ NAME DATA_0003 REBALANCE POWER 11;

Diskgroup altered.

SQL>

 

Enjoy… 🙂

***********************************************************************
Note: Please don’t hesitate to revert in case of any query OR feedback.

Thanking you.

Have a easy life ahead.

Oracle 11g Logo

How to add disk to ASM diskgroup in Oracle 11g

Following step by step guide will help you to add disk to oracle ASM diskgroup.

// Following query will identify the Diskgroup to add disk:

SQL> select group_number, name from v$asm_diskgroup;
GROUP_NUMBER NAME
------------ ------------------------------
1 DATA

// Following query will identify the candidate disk:

SQL> SELECT MOUNT_STATUS,HEADER_STATUS,MODE_STATUS,STATE,TOTAL_MB,FREE_MB,NAME,PATH,LABEL FROM V$ASM_DISK;

MOUNT_S HEADER_STATU MODE_ST STATE TOTAL_MB FREE_MB NAME PATH LABEL
------- ------------ ------- -------- ---------- ---------- ------------------------------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------
CACHED MEMBER ONLINE NORMAL 10240 6528 DATA_0002 /dev/oracleasm/disks/OCR
CACHED MEMBER ONLINE NORMAL 307200 196112 DATA_0000 /dev/oracleasm/disks/ARCHIVE
CACHED MEMBER ONLINE NORMAL 307200 196141 DATA_0001 /dev/oracleasm/disks/DATA

// With the help of createdisk oracle utility, we will create oracle ASM disk. ( as root user ) Following command will create ASM disk:

[root@database1 ~]# /usr/sbin/oracleasm createdisk RMAN /dev/mapper/mpathg
Writing disk header: done
Instantiating disk: done

Note:
RMAN is the name of newly created ASM disk with path ‘/dev/mapper/mpathg’.

// Scan all oracle ASM disk after addition:

[root@database1 ~]# /usr/sbin/oracleasm scandisks
Reloading disk partitions: done
Cleaning any stale ASM disks...
Scanning system for ASM disks...

// Ensure all oracle ASM disk after scanning as follow:

[root@database1 ~]# /usr/sbin/oracleasm listdisks
ARCHIVE
DATA
OCR
 RMAN

OR

[oracle@database1 ~]$ cd /dev/oracleasm/disks/
[oracle@database1 disks]$ ls
ARCHIVE DATA OCR RMAN

// Now Add the newly created oracle ASM disk to existing ASM diskgroup with the help of following query:

[oracle@database1 disks]$ grid_env
[oracle@database1 disks]$ sqlplus "/ as sysasm"

SQL> ALTER DISKGROUP DATA ADD DISK '/dev/oracleasm/disks/RMAN' NAME DATA_0003 REBALANCE POWER 11;

Diskgroup altered.

// Ensure changes by issue following query:

SQL> SELECT MOUNT_STATUS,HEADER_STATUS,MODE_STATUS,STATE,TOTAL_MB,FREE_MB,NAME,PATH,LABEL FROM V$ASM_DISK;
MOUNT_S HEADER_STATU MODE_ST STATE TOTAL_MB FREE_MB NAME PATH LABEL
------- ------------ ------- -------- ---------- ---------- ------------------------------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------
CACHED MEMBER ONLINE NORMAL 307200 202544 DATA_0000 /dev/oracleasm/disks/ARCHIVE
CACHED MEMBER ONLINE NORMAL 10240 6743 DATA_0002 /dev/oracleasm/disks/OCR
CACHED MEMBER ONLINE NORMAL 307200 202578 DATA_0001 /dev/oracleasm/disks/DATA
CACHED MEMBER ONLINE NORMAL 307200 292732 DATA_0003 /dev/oracleasm/disks/RMAN

By above method, we can add disk to oracle ASM diskgroup.

***********************************************************************
Note: Please don’t hesitate to revert in case of any query OR feedback.

Thanking you.

Have a nice time. 🙂

SQL Server 2014 logo

How to change SQL Server 2014 database compatibility level

The day before yesterday i have published my thoughts about “Truncate log file up to 504 KB – SQL Server 2008, 2008-R2 & 2012”, today i am going to explain how to change compatibility of SQL Server 2014 databases.

Note:
We need to change compatibility of our earlier version of databases in order to use new features of SQL Server 2014. ( i.e. 120 )

This can be achieve in two ways, one by GUI prompt, means with the help of Microsoft SQL Server Management Studio & second with the help of T-SQL Script.

 

1st: Microsoft SQL Server Management Studio: ( GUI )
To change compatibility of SQL server databases, open Microsoft SQL Server Management Studio & Expand Databases.

Right click on TestDB >> select Properties, as show below:

How to change SQL Server 2014 database compatibility level

How to change SQL Server 2014 database compatibility level

 

On Database Properties wizard, Select Options tab & go for the Compatibility level as SQL Server 2014 (120) and click Ok.

How to change SQL Server 2014 database compatibility level - Database Property

How to change SQL Server 2014 database compatibility level – Database Property

 

2nd: T-SQL

Issue following T-SQL in order to change compatibility of databases:

USE [master]
GO
ALTER DATABASE [TestDB] SET COMPATIBILITY_LEVEL = 120
GO

How to change SQL Server 2014 database compatibility level - T-SQL

How to change SQL Server 2014 database compatibility level – T-SQL

 

By above two methods we can change compatibility of databases in SQL Server 2014 as well as SQL Server 2008, 2008-R2 & 2012.

 

Is your database log file ( .ldf ) size is greater then the data file ( .mdf ) size, then refer my one of the blog to truncate log file up to 504 KB.

***********************************************************************

Note: Please don’t hesitate to revert in case of any query OR feedback.

Thanking you.

Have a easy life ahead.

SQL Server 2012 logo

Truncate log file up to 504 KB – SQL Server 2008, 2008-R2 & 2012

Yes, It’s true that we can truncate our log file up to the 504 KB with discard old log file & create new log file. This log truncation activity will help DBA in Capacity Management.

Note: 

  1. This can be done with only Database which have been configured with Full Recovery Mode option.
  2. Please consider full database backup before doing this activity so that In worst situation we will have safe backup in our hand.
  3. Refer my one of the post on SQL Server backup configuration to backup your SQL Server database.
  4. TestDB database is used to simulate for this activity.

After successful backup, Consider following step by step guide to truncate your DB log file:

Continue reading

Oracle 12c Logo

Audit Commands IN ORACLE 12C DATA PUMP

In Oracle 12c, Now we can also audit oracle data pump backups job by creating an audit policy just like a user/schema auditing.
With the help of this feature keeping eye on your data pump backups is easy now.

Syntax:
CREATE AUDIT POLICY Data_Pump_Policy_name
ACTIONS COMPONENT=DATAPUMP [EXPORT | IMPORT | ALL];

When this policy is applied to a user, their data pump jobs will appear in the audit trail.
The following policy audits all data pump operations. The policy is applied to the SYSTEM user. Continue reading

Oracle 11g Logo

ORA-27154 post/wait create failed

Today, while oracle database startup we got “ORA-27154: post/wait create failed” error message. ( We have multiple databases running on same env. )

After diagnosis, it was the problem with kernel parameter: kernel.sem

SQL> startup

ORA-27154: post/wait create failed

ORA-27300: OS system dependent operation:semget failed with status: 28

ORA-27301: OS failure message: No space left on device

ORA-27302: failure occurred at: sskgpcreates

Continue reading

Oracle 12c Logo

Pluggable Database Automatic Startup

Unfortunately, There is no default mechanism to start all Pluggable Databases.
While startup of Container Databases, all the Pluggable Databases will be in mounted state.
But we can accomplished same with the system trigger on Container Database to start PDB[s].

Following trigger will automatically open all PDB’s while starting up CDB.

CREATE OR REPLACE TRIGGER opn_pdbs
AFTER STARTUP ON DATABASE
BEGIN
EXECUTE IMMEDIATE ‘ALTER PLUGGABLE DATABASE ALL OPEN’;
END opn_pdbs;
/

We can customise it if we don’t want to start all PDB’s

***********************************************************************

Note: Please don’t hesitate to revert in case of any query OR feedback.

Thanking you.

Have a easy life ahead.

How to Flush DNS

Most of the OS & DNS clients shall automatically cache IP Addresses & other DNS results, this can be done to speed up subsequent requests to the same hostname.

Many times bad results will be cached & so that we need to cleared it from the cache, results we will communicate with host properly.

This facility is available in all OS, Please follow below steps to flush DNS.

 

Please consider following step-by-step guide to flush DNS:

// Click on Start button. Continue reading

How to open firewall ports on Windows Server 2008 R2

Step by step guide to open firewall ports on Windows Server 2008 R2 Enterprise edition, as below:

 

// To do so, Click on Start button, then click on Administrative Tools, then click on  Windows Firewall With Advanced Security as below:

How to open firewall ports on Windows Server 2008 R2 Enterprise - Windows Firewall With Advanced Security

How to open firewall ports on Windows Server 2008 R2 Enterprise – Windows Firewall With Advanced Security

 

// On Windows Firewall With Advanced Security wizard , click on Inbound Rules: ( on left panel )

How to open firewall ports on Windows Server 2008 R2 Enterprise - Windows Firewall With Advanced Security

How to open firewall ports on Windows Server 2008 R2 Enterprise – Windows Firewall With Advanced Security

 

// On Windows Firewall With Advanced Security wizard, Click on New Rule on your right panel as below:

How to open firewall ports on Windows Server 2008 R2 Enterprise - Inbound Rule

How to open firewall ports on Windows Server 2008 R2 Enterprise – Inbound Rule

 

// On New Inbound Rule Wizard, Select Port radio button and click Next:

How to open firewall ports on Windows Server 2008 R2 Enterprise - Rule Type

How to open firewall ports on Windows Server 2008 R2 Enterprise – Rule Type

 

// On New Inbound Rule Wizard, Select TCP/UDP & also mentioned your rule will apply to specific local ports OR All local ports & click Next:

How to open firewall ports on Windows Server 2008 R2 Enterprise - Protocol and Ports

How to open firewall ports on Windows Server 2008 R2 Enterprise – Protocol and Ports

 

// On New Inbound Rule Wizard, select your action & click Next:

How to open firewall ports on Windows Server 2008 R2 Enterprise - Action

How to open firewall ports on Windows Server 2008 R2 Enterprise – Action

 

// On New Inbound Rule Wizard, click Next:

How to open firewall ports on Windows Server 2008 R2 Enterprise - Profile

How to open firewall ports on Windows Server 2008 R2 Enterprise – Profile

 

// Provide Name & Description for the newly created rule & click Finish:

How to open firewall ports on Windows Server 2008 R2 Enterprise - Name

How to open firewall ports on Windows Server 2008 R2 Enterprise – Name

 

Rule added successfully and will be reflect in Windows Firewall With Advanced Security wizard.

 

Are you experiencing “time-out” error messages with certain websites? Find solution.
OR
Is your browser loaded with negative cache entries? Find solution.

***********************************************************************

abc

Note: Please don’t hesitate to revert in case of any query OR feedback.

Thanking you.

Have a easy life ahead.

SQL Server 2012 logo

How to apply SQL Server 2012 licences

Many times we successfully installed evaluation copy of SQL Server and not aware about the procedure to apply/upgrade licences.

This article, contains step by step instruction on how to upgrade Microsoft SQL Server 2012 Evaluation Edition to Microsoft SQL Server Enterprise Core-base licence.

Steps

// Run SQL Server 2012 installer by double-click on Setup file as below:

How to apply SQL Server 2012 licenses - Setup

How to apply SQL Server 2012 licenses – Setup

 

// Processing…

How to apply SQL Server 2012 licenses - Processing

How to apply SQL Server 2012 licenses – Processing

 

// Chose Maintenance tab.

How to apply SQL Server 2012 licenses

How to apply SQL Server 2012 licenses

 

// Chose Maintenance tab.

How to apply SQL Server 2012 licenses - Maintenance

How to apply SQL Server 2012 licenses – Maintenance

 

// Processing…

How to apply SQL Server 2012 licenses - Processing

How to apply SQL Server 2012 licenses – Processing

 

// Verifying Setup Support Rules, after verification click OK.

How to apply SQL Server 2012 licenses - Setup Support Rule

How to apply SQL Server 2012 licenses – Setup Support Rule

 

// Processing…

How to apply SQL Server 2012 licenses - Processing

How to apply SQL Server 2012 licenses – Processing

 

// Verifying Setup Support Rules, after verification click OK.

How to apply SQL Server 2012 licenses - Setup Support Rule

How to apply SQL Server 2012 licenses – Setup Support Rule

 

// Enter product key and click on Next button.

How to apply SQL Server 2012 licenses - Product Key

How to apply SQL Server 2012 licenses – Product Key

 

// Accepts the license terms and click Next.

How to apply SQL Server 2012 licenses - License Term

How to apply SQL Server 2012 licenses – License Term

 

// Select Instance

How to apply SQL Server 2012 licences - Select Instance

How to apply SQL Server 2012 licences – Select Instance

 

//  Verifying Edition Upgrade Rules, after verification click Next button.

How to apply SQL Server 2012 licences - Edition Upgrade Rule

How to apply SQL Server 2012 licences – Edition Upgrade Rule

 

// Click on Upgrade button.

How to apply SQL Server 2012 licences - Ready To Upgrade Edition

How to apply SQL Server 2012 licences – Ready To Upgrade Edition

 

// Upgrade successful with Succeeded status as below.

How to apply SQL Server 2012 licences - Complete

How to apply SQL Server 2012 licences – Complete

 

// You can verify licenses by right-click on instance name @ SQL Server Management Studio.

How to apply SQL Server 2012 licences - Server Property

How to apply SQL Server 2012 licences – Server Property

By above method we can apply/upgrade license to SQL server 2012.

***********************************************************************

Note: Please don’t hesitate to revert in case of any query OR feedback.

Thanking you.

Have a easy life ahead.

Oracle 12c Logo

In Database Archiving – Oracle 12c New Feature

Lets consider following points to understand “In Database Archiving” in depth:

  1. What is “In Database Archiving”?
  2. Benefits of “In Database Archiving”.
  3. Implementation & Hands-on on In Database Archiving.

What is “In Database Archiving”?

Most of the time, oracle enterprise databases(tables) are very large in size which has years & years of historical data. Very difficult in identifying current & old data, if any how we can manage to identify it, we can’t move it to Tape or Storage because we don’t know when these historical data will be needed for any reasons. If we managed to separate these as current and old data on tape or storage, In case of need, getting that data back in database will be very time-consuming  Continue reading

Oracle 12c Logo

Oracle 12c installation on Oracle Linux release-5 (64-bit)

Here we are installing Oracle 12c Release-1 (12.1)(64-bit) on Oracle Linux release-5 (64-bit)

Minimum swap size 2GB.
IP Tables off.
SELinux set to be permissive.

// Ensure swap size by issuing following command, if swap size do not meet minimum requirement then increase it.
[root@oracle5 ~]# free -m
total used free shared buffers cached
Mem: 664 461 202 0 100 202 Continue reading

Oracle 11g Logo

How to kill oracle datapump export job

We can kill oracle datapump job by two methods, First method includes killing data pump job via data pump export prompt and another method includes running SQL package on SQL prompt as sysdba.

//To simulate both the scenario, i am going to start oracle datapump export as below:

[oracle@dbserver ~]$ expdp system/manager full=y directory=bkupdir dumpfile=Full_export.dmp logfile=Export_log.LOG
Export: Release 11.2.0.3.0 – Production on Fri Apr 11 16:43:56 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Release 11.2.0.3.0 – 64bit Production
Starting “SYSTEM”.”SYS_EXPORT_FULL_01″: system/******** full=y directory=bkupdir dumpfile=Full_export.dmp logfile=Export_log.LOG
Estimate in progress using BLOCKS method…
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 3.431 GB
Processing object type DATABASE_EXPORT/TABLESPACE
Processing object type DATABASE_EXPORT/PROFILE
Processing object type DATABASE_EXPORT/SYS_USER/USER
. .. …

First Method: Kill Data pump job by datapump export prompt:

//After initiating export backup, Kindly make sure datapump job by issuing the following query as sysdba:

SQL> select * from dba_datapump_jobs;

OWNER_NAME JOB_NAME
—————————— ——————————
OPERATION JOB_MODE
—————————— ——————————
STATE DEGREE ATTACHED_SESSIONS DATAPUMP_SESSIONS
—————————— ———- —————– —————–
SYSTEM SYS_EXPORT_FULL_01
EXPORT FULL
EXECUTING 1 1 3

//Now connect to datapump export prompt with JOB_NAME(attach) as below & issue the datapump command: KILL_JOB.

[oracle@dbserver ~]$ expdp system/manager attach=SYS_EXPORT_FULL_01
Export: Release 11.2.0.3.0 – Production on Fri Apr 11 17:01:13 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Release 11.2.0.3.0 – 64bit Production

Job: SYS_EXPORT_FULL_01
Owner: SYSTEM
Operation: EXPORT
Creator Privs: TRUE
GUID: F6C3A9B1D87AC043E0430100007F07F7
Start Time: Friday, 11 April, 2014 17:00:38
Mode: FULL
Instance: orcl
Max Parallelism: 1
EXPORT Job Parameters:
Parameter Name Parameter Value:
CLIENT_COMMAND system/******** full=y directory=bkupdir dumpfile=Full_export.dmp logfile=Export_log.LOG
State: EXECUTING
Bytes Processed: 0
Current Parallelism: 1
Job Error Count: 0
Dump File: /backup/Export/Full_export.dmp
bytes written: 4,096

Worker 1 Status:
Process Name: DW00
State: EXECUTING
Object Schema: ELET
Object Type: DATABASE_EXPORT/SCHEMA/PROCACT_SCHEMA
Completed Objects: 80
Worker Parallelism: 1

Export> KILL_JOB
Are you sure you wish to stop this job ([yes]/no): yes
[oracle@dbserver ~]$

//Datapump export job has been killed successfully. Same message will be display in datapump logfile as below:

[oracle@dbserver ~]$ expdp system/manager full=y directory=bkupdir dumpfile=Full_export.dmp logfile=Export_log.LOG
Export: Release 11.2.0.3.0 – Production on Fri Apr 11 16:43:56 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Release 11.2.0.3.0 – 64bit Production
Starting “SYSTEM”.”SYS_EXPORT_FULL_01″: system/******** full=y directory=bkupdir dumpfile=Full_export.dmp logfile=Export_log.LOG
Estimate in progress using BLOCKS method…
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 3.431 GB
Processing object type DATABASE_EXPORT/TABLESPACE
Processing object type DATABASE_EXPORT/PROFILE
Processing object type DATABASE_EXPORT/SYS_USER/USER
Processing object type DATABASE_EXPORT/SCHEMA/USER
Processing object type DATABASE_EXPORT/ROLE
Processing object type DATABASE_EXPORT/GRANT/SYSTEM_GRANT/PROC_SYSTEM_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/ROLE_GRANT
. .. …
Job “SYSTEM”.”SYS_EXPORT_FULL_01″ stopped due to fatal error at 16:45:35

—x—

Second Method: Kill Datapump job by running SQL package:

//After inititating the oracle datapump export, ensure datapump job by issuing the following query as sysdba:

SQL> select * from dba_datapump_jobs;

OWNER_NAME JOB_NAME
—————————— ——————————
OPERATION JOB_MODE
—————————— ——————————
STATE DEGREE ATTACHED_SESSIONS DATAPUMP_SESSIONS
—————————— ———- —————– —————–
SYSTEM SYS_EXPORT_FULL_01
EXPORT FULL
EXECUTING 1 1 3

//To kill datapump job, We need two parameter as input to SQL package are: JOB_NAME of the datapump job & OWNER_NAME who initiated export.

SQL> DECLARE
h1 NUMBER;
BEGIN
h1:=DBMS_DATAPUMP.ATTACH(‘SYS_EXPORT_FULL_01‘,’SYSTEM‘);
DBMS_DATAPUMP.STOP_JOB (h1,1,0);
END;
/

PL/SQL procedure successfully completed.
SQL>

//Datapump export job has been killed successfully, same message will be display in datapump logfile as below:

[oracle@dbserver ~]$ expdp system/manager full=y directory=bkupdir dumpfile=Full_export.dmp logfile=Export_log.LOG
Export: Release 11.2.0.3.0 – Production on Fri Apr 11 17:00:37 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Release 11.2.0.3.0 – 64bit Production

Starting “SYSTEM”.”SYS_EXPORT_FULL_01″: system/******** full=y directory=bkupdir dumpfile=Full_export.dmp logfile=Export_log.LOG
Estimate in progress using BLOCKS method…
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 3.431 GB
Processing object type DATABASE_EXPORT/TABLESPACE
Processing object type DATABASE_EXPORT/PROFILE
Processing object type DATABASE_EXPORT/SYS_USER/USER
Processing object type DATABASE_EXPORT/SCHEMA/USER
Processing object type DATABASE_EXPORT/ROLE
Processing object type DATABASE_EXPORT/GRANT/SYSTEM_GRANT/PROC_SYSTEM_GRANT
. .. …
Job “SYSTEM”.”SYS_EXPORT_FULL_01″ stopped due to fatal error at 17:01:23

By above two methods, we can kill oracle datapump export job.

***********************************************************************

abc

Note: Please don’t hesitate to revert in case of any query OR feedback.

Thanking you.

Have a easy life ahead.

Oracle 12c Logo

Delete Pluggable Database with Database Configuration Assistant

We can delete Pluggable database using Database Configuration Assistant OR manually from SQL prompt as sysdba privilege.

Here we covered Database Configuration Assistant to delete Pluggable database.

In this post, we considered Pluggable database already created, if not then kindly have a look at: Create Pluggable Database using Database Configuration Assistant

 

// To initiate database installer, start “Database Configuration Assistant” by issuing “dbca” command from bin library as shown below.

Del_plugdb_1 Del_plugdb_2

 

On “Database Operation” prompt, select the “Manage Pluggable Databases”

In this we can create new pluggable database delete it as well as manage existing pluggable databases.

Click Next to continue.

Del_plugdb_3

 

On “Manage Pluggable Databases” prompt, select the “Delete a Pluggable Database” radio button.

& click Next.

Del_plugdb_4

 

On “Database List” prompt, select the Container Database.

In my case, “orcl” is Container Database.

& click Next.

Del_plugdb_5

 

On “Delete Pluggable Database” prompt, select pluggable database to be delete from drop down list & click Next.

& click Next.

Del_plugdb_6

 

Ensure the summary about your provided database, if u wish to continue click Finish button.

Mentioned system01.dbf, sysaux01.dbf & PlugDB_users01.dbf file will be deleted permanently from the container database.

Del_plugdb_7

PlugDB – Pluggable database has been deleted successfully.

***********************************************************************

Note: Please don’t hesitate to revert in case of any query OR feedback.

Thanking you.

Have a easy life ahead.

Oracle 12c Logo

Plugin a Pluggable database using Database Configuration Assistant

We can plugin back a Pluggable database using Database Configuration Assistant OR manually from SQL prompt as sysdba privilege.

Here we covered Database Configuration Assistant to plugin back a Pluggable database.

 

// To initiate database installer, start “Database Configuration Assistant” by issuing “dbca” command from bin library as shown below.

Plugin_pluggdb_1

Plugin_pluggdb_2

 

On “Database Operation” prompt, select the “Manage Pluggable Databases”

In this we can create new pluggable database as well as manage existing pluggable databases.

Click Next to continue.

Plugin_pluggdb_3

On “Manage Pluggable Databases” prompt, select the “Create a Pluggable Database” radio button.

& click Next.

Plugin_pluggdb_4

 

On “Database List” prompt, select the Container Database.

In my case, “orcl” is Container Database.

& click Next.

Plugin_pluggdb_5

 

On “Create Pluggable Database” prompt, select the “Create Pluggable Database using PDB file set” radio button.

In my case, I have created PDB file set at the time of unplug pluggable database, that’s why I would like to go with “Create Pluggable Database using PDB file set” option.

If you unplug your Pluggable database with the help of “Create Pluggable Database From PDB archive” then choose same for re-plug pluggable database.

& click Next.

Plugin_pluggdb_6

Choose .xml file of your Pluggable database that you want to plug in back.

& click Ok.

Plugin_pluggdb_7

 

On “Pluggable Database Option” prompt.

Enter the pluggable database name that you want to plugin back, also provide PDB user details as below.

& click Next.

Plugin_pluggdb_8

 

Ensure the summary about your provided database, if u wish to continue click Finish button.

Plugin_pluggdb_9

 

Progress page will show you the progress of same.

Plugin_pluggdb_10

 

On completion of installation, click the “OK” button on the message dialog and the “Close” button on the main screen.

Plugin_pluggdb_11

PlugDB – Pluggable database has been plugin successfully.

***********************************************************************

Note: Please don’t hesitate to revert in case of any query OR feedback.

Thanking you.

Have a easy life ahead.