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.

Oracle 12c Logo

Unplug Pluggable Database using Database Configuration Assistant

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

Here we covered Database Configuration Assistant to unplug 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.

Unplug_plugdb_1

Unplug_plugdb_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.

Unplug_plugdb_3

 

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

& click Next.

Unplug_plugdb_4

 

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

In my case, “orcl” is Container Database.

& click Next.

Unplug_plugdb_5

 

On “Unplug Pluggable Database” prompt, select pluggable database to be unplug from drop down list & select “Generate Pluggable Database File Set” radio button for future purpose.

& click Next.

You can also select “Generate Pluggable Database Archive” radio button, as per your convenience. In my case, I would like to go with “Generate Pluggable Database File Set”.

Unplug_plugdb_6

 

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

Unplug_plugdb_7

 

Progress page will show you the progress of the same.

Unplug_plugdb_8

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

Unplug_plugdb_9

 

PlugDB – Pluggable database has been unplugged 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

Create Pluggable Database using Database Configuration Assistant

We can create Pluggable database at the time of creation of Container database & we can also create Pluggable database with the help of Database Configuration Assistant.

Here we considered Container database already installed, if not then kindly follow my one of the post: Oracle 12c Installation

 

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

Continue reading

Oracle 11g Logo

ORA-39171: Job is experiencing a resumable wait

While exporting my one of the database with data pump backup utility, Backup has been stuck with ORA-01691: unable to extend lob segment & ORA-39171: Job is experiencing a resumable wait ora errors.

Oracle error pretty much self explanatory. After diagnosing ORA-01691 & ORA-39171, I came across the system tablespace having lack of space allocated, after adding new datafile ( alter tablespace… ) to system tablespace backup job has been resumed & successfully completed.

//Export Logs as below:

OracleDB:ora 19> expdp system/manager directory=dirdmp schemas=Prod dumpfile=Prod_260314%U.dmp logfile=Prod_260314.log compression=all parallel=4 Continue reading

Oracle 11g Logo

Export Import Performance Optimization Tips

We can improve Export-Import performance optimization by considering following points.

Export Optimization tips:

  • Set the BUFFER parameter to high value (eg 2M)
  • Set the RECORDLENGTH parameter to a high value (e.g. 64K)
  • Stop unnecessary applications to free-up resources for your job.
  • DO NOT export to an NFS mounted filesystem. Continue reading
Oracle 12c Logo

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

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

Pre-requisite installation check on CentOS:

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

// Ensure swap size by issuing following command, if swap size do not meet minimum requirement then increase it. Continue reading

sql server 2008 logo

SQL server 2012 stand alone installation

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

Pre-requisites for SQL server 2012:

  • .NET 3.5 sp1
  • .NET 4.0 (SQL server installs this during feature installation steps )
  • Windows Power shell 2.0
  • Minimum 6GB disk space
  • Minimum 1GB RAM, in case of Express edition it need 512MB

Continue reading

sql server 2008 logo

Daily Backup Configuration in SQL Server 2008

As we are aware of how backup is more important in day to day life, please consider following methodologies to backup SQL server 2008 databases through Microsoft SQL Server management studio.

We can backup database with 2 methods as below:

Method 1: Individual backup:
We can take individual database backup by following steps:

Step 1: connect to Microsoft SQL Server management studio. Continue reading

Oracle 11g Logo

How to extract current Oracle user name and schema name?

Issue following query to extract current user name ( Session name ):

Syntax:
SELECT
sys_context(‘USERENV’,’SESSION_USER’) as “USER NAME”, 
sys_context(‘USERENV’, ‘CURRENT_SCHEMA’) as “CURRENT SCHEMA” 
FROM dual;

sys_context() : This function returns the value of parameter associated with the context namespace. Continue reading

Oracle 11g Logo

How to extract the Oracle table definition (Data Defining Language)

How to extract the Oracle table creation definition (Data Defining Language) from an Oracle database without digging dictionary views?

With the help of GET_DDL() function of DBMS_METADATA metadata package.

Syntax:

select DBMS_METADATA.GET_DDL(‘TABLE’,’TABLE_NAME’) from DUAL;

SET LONG 1000

SET PAGESIZE 0 Continue reading

Oracle 11g Logo

How to fetch last inserted row from any oracle table

This is easily done with the date datatype or timestamp datatype.

Ex:

select empno,ename,to_char(hiredate,’dd mon yyyy’) as hiredate
from emp
where hiredate =( select max(hiredate) from emp );

EMPNO      ENAME      HIREDATE
———- ———- ———–
7876       ADAMS      23 may 1987 Continue reading

Oracle 11g Logo