Know your Hostname in MySQL

I was trying to execute script in mysql from server level, was stuck at displaying hostname in output file. Many of you must have faced similar situation, here are different ways to achieve the same.
I have listed number of methods for the displaying hostname/server name while logged in mysql database but please note that this is not the limit, you can discover number of ways to do the same

• select hostname as variable

mysql> show variables like 'hostname';

+---------------+----------------------------+

| Variable_name | Value                      |

+---------------+----------------------------+

| hostname      | testsrv5215                | 

+---------------+----------------------------+

1 row in set (0.00 sec)

• Pulling data from information_schema.global_variables into a user defined variable and displaying its value

mysql> SELECT variable_value INTO @local_hostname

    -> FROM information_schema.global_variables

    -> WHERE variable_name = 'hostname';

Query OK, 1 row affected, 1 warning (0.00 sec)

mysql>

mysql> select @local_hostname;

+----------------------------+

| @local_hostname            |

+----------------------------+

| testsrv5215                |

+----------------------------+

1 row in set (0.00 sec)

• Same method as above with small query

mysql> select @@hostname into @local_hostname;

Query OK, 1 row affected (0.00 sec)

mysql> select @local_hostname;

+----------------------------+

| @local_hostname            |

+----------------------------+

| testsrv5215                |

+----------------------------+

1 row in set (0.00 sec)

mysql>

• select , assigning and displaying hostname in single step

mysql> SELECT @@hostname hostname;

+----------------------------+

| hostname                   |

+----------------------------+

| testsrv5215                |

+----------------------------+

1 row in set (0.00 sec)

Feel free to comment or give feedback

Read More

Oracle 12c: Starting and Stopping PDB

In this article we will learn how to start and stop 12c database having multitenant databases.
For shutting down and starting container database, method is same as standalone database like 11g. Connect to container database(cdb$root) with sysdba and check the status of PDB's
SQL> show pdbs

    CON_ID CON_NAME                  OPEN MODE  RESTRICTED

---------- -------------------------- ---------- ----------

         2 PDB$SEED                       READ ONLY  NO

         3 PDB_1                          READ WRITE NO

         4 PDB_TECHON                     READ WRITE NO

Here we can see pdbs are in read only and read write mode. first check where we are, whether in PDB or CDB

SQL> show con_name

CON_NAME

------------------------------

CDB$ROOT

SQL>

We are in root container. Now will shutdown one of the pluggable database PDB_TECHON
Below is command we use to stop and start pluggable database

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------

         2 PDB$SEED                       READ ONLY  NO

         3 PDB_1                          READ WRITE NO

         4 PDB_TECHON                     READ WRITE NO

SQL> alter  pluggable database PDB_TECHON close;

Pluggable database altered.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------

         2 PDB$SEED                       READ ONLY  NO

         3 PDB_1                          READ WRITE NO

         4 PDB_TECHON                     MOUNTED

SQL> 

Whenever we start container database, container database(CDB$ROOT) will be opened in read write mode and all PDB's will be in MOUNTED state and default SEED database will always be in READ only mode.

Now will be start our PDB from container database

SQL> show con_name

CON_NAME

------------------------------

CDB$ROOT

SQL>

SQL> alter pluggable database PDB_TECHON open;

Pluggable database altered.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------

         2 PDB$SEED                       READ ONLY  NO

         3 PDB_1                          READ WRITE NO

         4 PDB_TECHON                     READ WRITE NO

SQL>

Now perform the same operations from particular PDB (PDB_TECHON). first we need switch to pluggable database using alter session command. We do not need to mentioned pluggable database name while start and stop

Now switch back to container database and check the status of all PDB's

SQL> alter session set container=CDB$ROOT;

Session altered.

SQL>

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------

         2 PDB$SEED                       READ ONLY  NO

         3 PDBGD                          READ WRITE NO

         4 PDB_TECHON                     READ WRITE NO

SQL>

Read More

Warning: PDB altered with errors- opening PDB in Oracle 12.1

I was facing below error while opening pluggable database created on 12.1.0.2 using SEED PDB

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------

         2 PDB$SEED                       READ ONLY  NO

         3 PDB_1                          READ WRITE NO

         4 PDB_TECHON                     MOUNTED

SQL>

SQL> alter pluggable database PDB_TECHON open;

Warning: PDB altered with errors.

I have been cheeking alert log for the errors but could not find anything related to this particular warning. after searching on Oracle Metalink, I could figure out this bug in 12.1.0.2
We can check the message related to warning in pdb_plug_in_violations

SQL> select message,time from pdb_plug_in_violations;

MESSAGE                                                                                              TIME

---------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------

Sync PDB failed with ORA-959 during 'CREATE USER "C##dev_owner"  PROFILE "DEFAULT"                      14-DEC-18 02.42.18.368009 AM

    IDENTIFIED BY *DEFAULT TABLESPACE "USERS" CONTAINER=ALL

    TEMPORARY TABLESPACE "TEMP"

    ACCOUNT UNLOCK'

Basically it checks the default tablespace of comman users in every PDB created and throws warning if not able find the same, so the solution is to create USERS tablesapce in newly created PDB (PDB_TECHON).

Below we have switched to container PDB_TECHON and created USERS tablespace and restarted the pluggable database by connecting root container (CDB$ROOT), we can see PDB is opened with any warning this time.

SQL> alter session set container=PDB_TECHON;

Session altered.

SQL> show con_name

CON_NAME

------------------------------

PDB_TECHON

SQL>

SQL> create tablespace users datafile '+DATA01' size 100m;

Tablespace created.

SQL>  show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------

         4 PDB_TECHON                     READ WRITE YES

SQL>

SQL>  alter session set container=CDB$ROOT;

Session altered.

SQL>  show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------

         2 PDB$SEED                       READ ONLY  NO

         3 PDB_1                          READ WRITE NO

         4 PDB_TECHON                     READ WRITE YES

SQL>

SQL> alter pluggable database PDB_TECHON close;

Pluggable database altered.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------

         2 PDB$SEED                       READ ONLY  NO

         3 PDB_1                          READ WRITE NO

         4 PDB_TECHON                     MOUNTED

SQL>

SQL> alter pluggable database PDB_TECHON open;

Pluggable database altered.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------

         2 PDB$SEED                       READ ONLY  NO

         3 PDB_1                          READ WRITE NO

         4 PDB_TECHON                     READ WRITE NO

SQL> 

Feel free to comment with feedback, issue or  concerns

Read More

Create Pluggable Database (PDB) in Oracle 12c

We have gone through features of 12c Multitenant database. Now we will have discussion on create pluggable databases (PDB). There are different ways to create pluggable database

We will discuss on below methods

1. Using SEED pdb

2. Using XML file

3. By Cloning

     a. Local PDB

     b. Remote PDB

Using seed PDB PDB$SEED

Below are steps to create PDB using PDB$SEED, first Check the PDB's available in container database

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------

         2 PDB$SEED                       READ ONLY  NO

         3 PDB_1                          READ WRITE NO

Now create PDB using below command, note that we have provided options to create admin user, default tablespaces and roles. FILE_NAME_CONVERT is required in case mount point or diskgroups are different, for understanding purpose we used in below example though diskgroup is same.

SQL> create pluggable database pdb_techon admin user techon_admin identified by techon#123 roles = (DBA)

default tablespace techon_tbs datafile '+DATA01' size 100M

FILE_NAME_CONVERT=('+DATA01','+DATA01');  2    3

Pluggable database created.

We can see PDB is default created in MOUNTED state. we need to open the same with alter pluggable database command as shown below

SQL>  show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------

         2 PDB$SEED                       READ ONLY  NO

         3 PDB_1                          READ WRITE NO

         4 PDB_TECHON                     MOUNTED

SQL>

SQL> alter pluggable database PDB_TECHON open;

Pluggable database altered.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------

         2 PDB$SEED                       READ ONLY  NO

         3 PDB_1                          READ WRITE NO

         4 PDB_TECHON                     READ WRITE NO

We can check datafile created for tablespace techon_tbs

SQL> select name from v$datafile where name like '%tech%';

NAME

---------------------------------------------------------------

+DATA01/DEV12C1/TECHON_PDB/DATAFILE/techon_tbs.363.994818875

Using XML file

For carrying out this activity, I have created .xml file of our previous PDB, techno_pdb and dropped it using keep datafile option and generated xml file for the same

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------

         2 PDB$SEED                       READ ONLY  NO

         3 PDB_1                          READ WRITE NO

         4 PDB_TECHON                     READ WRITE NO

SQL> alter pluggable database PDB_TECHON close;

Pluggable database altered.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------

         2 PDB$SEED                       READ ONLY  NO

         3 PDB_1                          READ WRITE NO

         4 PDB_TECHON                     MOUNTED

SQL> alter pluggable database PDB_TECHON unplug into '/u01/pdb_backup/pdb_techon.xml';

Pluggable database altered.

SQL> drop pluggable database PDB_TECHON keep datafiles;

Pluggable database dropped.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------

         2 PDB$SEED                       READ ONLY  NO

         3 PDB_1                          READ WRITE NO

SQL>

Now lets see command to create PDB from xml file, as we have kept datafiles as it is during drop no file_name_convert or transfer of any datafile is required

SQL> Create pluggable database PDB_TECHON using '/u01/pdb_backup/pdb_techon.xml’;

Pluggable database created.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------

         2 PDB$SEED                       READ ONLY  NO

         3 PDB_1                          READ WRITE NO

         4 PDB_TECHON                     MOUNTED

SQL>

SQL> alter pluggable database PDB_TECHON open;

Pluggable database altered.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------

         2 PDB$SEED                       READ ONLY  NO

         3 PDB_1                          READ WRITE NO

         4 PDB_TECHON                     READ WRITE NO

Using clone from local or remote PDB

Using Local PDB

Now we will discuss to clone PDB from local PDB, Below are the steps discussed,

Open source PDB in read only mode using below method

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------

         2 PDB$SEED                       READ ONLY  NO

         3 PDB_1                          READ WRITE NO

         4 PDB_TECHON                     READ WRITE NO

SQL> alter pluggable database PDB_TECHON close;

Pluggable database altered.

SQL> alter pluggable database PDB_TECHON open read only;

Pluggable database altered.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------

         2 PDB$SEED                       READ ONLY  NO

         3 PDB_1                          READ WRITE NO

         4 PDB_TECHON                     READ ONLY  NO

Below are commands to create PDB from local PDB, we have created PDB techon_new and open both PDB's

SQL> create pluggable database techon_new from PDB_TECHON FILE_NAME_CONVERT=( '+DATA01', '+DATA01');

Pluggable database created.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------

         2 PDB$SEED                       READ ONLY  NO

         3 PDB_1                          READ WRITE NO

         4 PDB_TECHON                     READ ONLY  NO

         5 TECHON_NEW                     MOUNTED

SQL> alter pluggable database PDB_TECHON close;

Pluggable database altered.

SQL> alter pluggable database PDB_TECHON open;

Pluggable database altered.

SQL> alter pluggable database TECHON_NEW open;

Pluggable database altered.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------

         2 PDB$SEED                       READ ONLY  NO

         3 PDB_1                          READ WRITE NO

         4 PDB_TECHON                     READ WRITE NO

         5 TECHON_NEW                     READ WRITE NO

Using Remote PDB

For cloning remote follow the steps mentioned here Clone Remote PDB using DB link

You can also read (12c Features )

Read More