Oracle 12c - RAC Clusterware Setup using VMWare

#################################
Oracle 12c RAC Clusterware Setup
#################################

======================
HOST SYSTEMS SETUP
=====================
127.0.0.1       localhost.localdomain localhost
#Public
192.168.168.80 RAC1.rac.lab RAC1
192.168.168.81 RAC2.rac.lab RAC2
#Private
10.10.10.80  RAC1.rac.lab RAC1-priv
10.10.10.81  RAC2.rac.lab RAC2-priv
#VIP
192.168.168.82  RAC1-vip.rac.lab RAC1-vip
192.168.168.83  RAC2-vip.rac.lab RAC2-vip
#SCAN NAME - SCAN-RAC
192.168.168.84  SCAN-RAC.rac.lab SCAN-RAC
192.168.168.85  SCAN-RAC.rac.lab SCAN-RAC
192.168.168.86  SCAN-RAC.rac.lab SCAN-RAC


======================
OS GROUP USER SETUP
=====================
[root@rac1 yum.repos.d]# groupadd oinstall -g 9000
[root@rac1 yum.repos.d]#  groupadd asmadmin -g 9001
[root@rac1 yum.repos.d]# groupadd asmdba -g 9002
[root@rac1 yum.repos.d]# groupadd asmoper -g 9003

[root@rac1 yum.repos.d]# useradd -m -u 9100 -g oinstall -G asmadmin,asmdba,asmoper grid

[root@rac1 yum.repos.d]# groupadd dba -g 9200
[root@rac1 yum.repos.d]# groupadd oper  -g 9201
[root@rac1 yum.repos.d]# useradd -m -u 9300 -g oinstall -G dba,oper,asmdba oracle
[root@rac1 yum.repos.d]# passwd oracle
[root@rac1 yum.repos.d]# passwd grid



=============================
GI & ORACLE ORACLE_HOME DIR
=============================
For Grid Infrastrucutre: owned by grid with oinstall group
ORACLE_BASE=/u01/app/grid
ORACLE_HOME=/u01/app/12cR1/grid

For Oracle RDBMS: owned by oracle with oinstall group
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/12cR1

[root@rac2 ~]# mkdir -p /u01/app/grid
[root@rac2 ~]# mkdir -p /u01/app/12cR1/grid
[root@rac2 ~]# chown -R grid:oinstall /u01
[root@rac2 ~]#  mkdir -p /u01/app/oracle
[root@rac2 ~]# chown oracle:oinstall /u01/app/oracle
[root@rac2 ~]# su - oracle
[oracle@rac2 ~]$ cd /u01/app/oracle/
[oracle@rac2 oracle]$  mkdir -p 12cR1


======================
ENV VARIABLES
=====================
[root@rac1 ~]# su - grid
[grid@rac1 ~]$ vi .bash_profile

ORACLE_SID=+ASM1; export ORACLE_SID
ORACLE_BASE=/u01/app/grid; export ORACLE_BASE
ORACLE_HOME=/u01/app/12cR1/grid; export ORACLE_HOME
PATH=$PATH:$HOME/bin:$ORACLE_HOME/bin:.
export PATH

[root@rac1 ~]# su - oracle

ORACLE_SID=RAC1; export ORACLE_SID
ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
ORACLE_HOME=/u01/app/oracle/12cR1; export ORACLE_HOME
PATH=$PATH:$HOME/bin:$ORACLE_HOME/bin:.
export PATH


=========================
SELINUX & FIREWALL
=========================
[root@rac1 ~]# vi /etc/selinux/config
SELINUX=disabled

[root@rac1 ~]# sestatus
SELinux status:                 enabled
SELinuxfs mount:                /selinux
Current mode:                   enforcing
Mode from config file:          disabled
Policy version:                 26
Policy from config file:        targeted

[root@rac1 ~]# /etc/rc.d/init.d/iptables stop
[root@rac1 ~]# /etc/rc.d/init.d/ip6tables stop

[root@rac1 ~]# chkconfig iptables off
[root@rac1 ~]# chkconfig ip6tables off

[root@rac1 ~]# /sbin/service ntpd stop
[root@rac1 ~]# chkconfig ntpd off

[root@rac1 ~]# mv /etc/ntp.conf /etc/ntp.conf.orig


=========================
ORACLE PUBLIC REPO
=========================
# cd /etc/yum.repos.d
# wget http://public-yum.oracle.com/public-yum-ol6.repo

###Modify .repo to look like this
[root@RAC1 yum.repos.d]# cat public-yum-ol6.repo
[ol6_latest]
name=Oracle Linux $releasever Latest ($basearch)
baseurl=http://public-yum.oracle.com/repo/OracleLinux/OL6/latest/$basearch/
gpgkey=http://public-yum.oracle.com/RPM-GPG-KEY-oracle-ol6
gpgcheck=1
enabled=1

[ol6_addons]
name=Oracle Linux $releasever Add Ons ($basearch)
baseurl=http://public-yum.oracle.com/repo/OracleLinux/OL6/addons/$basearch/
gpgkey=http://public-yum.oracle.com/RPM-GPG-KEY-oracle-ol6
gpgcheck=1
enabled=1

[ol6_UEK_latest]
name=Latest Unbreakable Enterprise Kernel for Oracle Linux $releasever ($basearch)
baseurl=http://public-yum.oracle.com/repo/OracleLinux/OL6/UEK/latest/$basearch/
gpgkey=http://public-yum.oracle.com/RPM-GPG-KEY-oracle-ol6
gpgcheck=1
enabled=1

[root@RAC2 ~]# yum clean all


=========================
PRE-INSTALL RPM
=========================
# yum install oracle-rdbms-server-12cR1-preinstall

##INSTALL cvuqdisk-1.0.9-1.rpm
Note: Copy the .rpm from /tmp/grid/rpm folder to /tmp
[root@RAC1 tmp]# rpm -iv cvuqdisk-1.0.9-1.rpm

[root@RAC1 tmp]# cat /etc/resolv.conf
# Generated by NetworkManager
search rac.lab

=========================
ASMLIB INSTALLATION
=========================
[root@rac1 ~]# yum -y install kmod-oracleasm
[root@rac1 ~]# yum -y install oracleasm-support
[root@rac1 ~]# cd /tmp
[root@rac1 tmp]# rpm -Uvh oracleasmlib-2.0.4-1.el6.x86_64.rpm

[root@RAC1 tmp]# rpm -qa | grep oracleasm


=========================
ORACLE ASM CONFIGURATION
=========================
[root@rac1 tmp]# /usr/sbin/oracleasm configure -i
Default user to own the driver interface []: grid
Default group to own the driver interface []: asmadmin

[root@rac1 tmp]# /usr/sbin/oracleasm init


=========================
GRID SOFTWARE DOWNLOAD
=========================
[root@RAC1 tmp]# chown -R grid:oinstall grid
[root@RAC1 tmp]# chmod -R u+x grid

#### SHUTDOWN BOTH NODES ####


=========================
ASM CLUSTERED SHARED DISK
=========================
Note: Add 40GB Hard Disk on RAC1
Note: Change Disk to go SCSI 1:0
Note: INDEPENDANT - PERSISTENT MODE

Add following lines in RAC1.vmx RAC2.vms

disk.locking = "FALSE"
diskLib.dataCacheMaxSize = "0"
diskLib.dataCacheMaxReadAheadSize = "0"
diskLib.dataCacheMinReadAheadSize = "0"
diskLib.dataCachePageSize = "4096"
diskLib.maxUnsyncedWrites = "0"
scsi1.sharedBus = "virtual"

#### REBOOT BOTH NODES ####

NODE 1:
[root@RAC1 ~]# fdisk /dev/sdb
Command (m for help): n
Command action
   e   extended
   p   primary partition (1-4)
p
Partition number (1-4): 1
First cylinder (1-5221, default 1):
Command (m for help): t
Hex code (type L to list codes): 8e
Command (m for help): w
The partition table has been altered!

Calling ioctl() to re-read partition table.
Syncing disks.

[root@RAC1 ~]# cat /proc/partitions

NOTE 2:
[root@RAC2 ~]# fdisk /dev/sdb
Command (m for help): p
Command (m for help): w

NODE 1:
[root@RAC1 ~]# /usr/sbin/oracleasm init
[root@RAC1 ~]# /usr/sbin/oracleasm scandisks
[root@RAC1 ~]# /usr/sbin/oracleasm listdisks
[root@RAC1 ~]# /usr/sbin/oracleasm createdisk SANDISK1 /dev/sdb1
[root@RAC1 ~]# /usr/sbin/oracleasm scandisks
[root@RAC1 ~]# /usr/sbin/oracleasm listdisks

NODE 2:
[root@RAC2 ~]# /usr/sbin/oracleasm init
[root@RAC2 ~]# /usr/sbin/oracleasm scandisks
[root@RAC2 ~]# /usr/sbin/oracleasm listdisks


#### RUN INSTALLER ON RAC1 - This should install GI with no issues

Enjoy....Abid Malik

Oracle 12c - Data Guard Setup - Create Physical Standby Database

=================================================
How to Create Dataguard physical standby database
=================================================

###################
On Production
###################

--------------------------------
Step 0. Primary DB Configuration
--------------------------------
[root@DBPROD ~]# uname --kernel-name --kernel-release --kernel-version
Linux 3.8.13-16.2.1.el6uek.x86_64 #1 SMP Thu Nov 7 17:01:44 PST 2013

[root@DBPROD ~]# cat /etc/redhat-release
Red Hat Enterprise Linux Server release 6.5 (Santiago)


-------------------------------------
Step 1. Primary DB should use SPFILE
-------------------------------------
SQL> show parameter spfile;

NAME         TYPE        VALUE
-------      ----------  --------------------------------------------
spfile       string      /u01/app/oracle/11gR2/dbs/spfileDBPROD.ora


-------------------------------------------------
Step 2. Primary DB ARCHIVELOG Mode
-------------------------------------------------
SQL>SELECT log_mode from v$database;

LOG_MODE
------------
ARCHIVELOG


-----------------------------------------------
Step 3. Primary DB in force loggings
-----------------------------------------------
SQL>SELECT force_logging FROM v$database;

FORCE_LOGGING
---------------------------------------
NO

SQL> ALTER DATABASE FORCE LOGGING;
Database altered.


------------------------------------------------
Step 4. DB Unique Name
------------------------------------------------
Note: DB_NAME on both PRIMARY & STANDBY should be same
      DB_UNIQUE_NAME should be different on both.

SQL>show parameter db_name;
SQL>show parameter db_unique_name;


----------------------------------------
Step 5. Set Dataguard Parameters
----------------------------------------
Note:LOG_ARCHIVE_CONFIG enables or disables the sending of redo logs to remote destinatins.
Also receipt of remote logs. specifies unique database name for each db in Data Guard Configuration.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(DBPROD,DBSTAND)' SCOPE=SPFILE;

System altered.

Note: LOG_ARCHIVE_DEST_n must contain LOCATION OR SERVICE attribute.
SQL>ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=/u05/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=DBPROD' SCOPE=SPFILE;
System altered.

SQL>ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_1=ENABLE;

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=DBSTAND VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DBSTAND' SCOPE=SPFILE;

System altered.

SQL>ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;

System altered.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_FORMAT='%t_%s_%r.arc' SCOPE=SPFILE;

System altered.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=3;

System altered.

SQL> ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE SCOPE=SPFILE;

System altered.

Note: FAL_SERVER specifies the FAL(Fetch Archive Log) server for standby database.
SQL>ALTER SYSTEM SET FAL_SERVER=DBSTAND;
System altered.

SQL>ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO SCOPE=SPFILE;
System altered.

Note: This Parameter only requires if directory structure on PRIMARY is different in STANDBY.
SQL>ALTER SYSTEM SET DB_FILE_NAME_CONVERT='DBSTAND','DBPROD' SCOPE=SPFILE;
System altered.

Note: This Parameter only requires if directory structure on PRIMARY is different in STANDBY.
SQL>ALTER SYSTEM SET LOG_FILE_NAME_CONVERT='DBSTAND','DBPROD' SCOPE=SPFILE;
System altered.


-------------------------------------
Step 6. Modify TNSNAMES.ORA File
-------------------------------------
DBPROD =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = DBPROD.lab)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = DBPROD)
    )
  )

DBSTAND =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = DBSTAND.lab)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = DBSTAND)
    )
  )


-----------------------------------
Step 7. Backup Primary Database
-----------------------------------
[oracle@DBPROD ~]$ rman target /

connected to target database: DBPROD (DBID=1792084780)

RMAN> backup format '/u02/rman/DBPROD/DBPROD_bkup_%t_%s_%p.bak' database plus archivelog;


Starting backup at 14-JUN-14
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=1 RECID=1 STAMP=849547450
...

Finished backup at 14-JUN-14

Starting backup at 14-JUN-14
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=27 RECID=28 STAMP=850204583
channel ORA_DISK_1: starting piece 1 at 14-JUN-14
channel ORA_DISK_1: finished piece 1 at 14-JUN-14
piece handle=/u02/rman/DBPROD/DBPROD_bkup_850204583_32_1.bak tag=TAG20140614T075623 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 14-JUN-14

Starting Control File and SPFILE Autobackup at 14-JUN-14
piece handle=/u02/rman/DBPROD/cf_c-3503257845-20140614-00 comment=NONE
Finished Control File and SPFILE Autobackup at 14-JUN-14

-----------------------------------
Step 8. Create CNTRL FILE
-----------------------------------
SQL>ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/tmp/control01.ctl';
Database altered.


#######################
ON STANDBY DATABASE
#######################

--------------------------------
Step 0. Standby DB Configuration
--------------------------------
[root@DBSTAND ~]# uname --kernel-name --kernel-release --kernel-version
Linux 3.8.13-16.2.1.el6uek.x86_64 #1 SMP Thu Nov 7 17:01:44 PST 2013

[root@DBSTAND ~]# cat /etc/redhat-release
Red Hat Enterprise Linux Server release 6.5 (Santiago)

-------------------------------------
Step 1. Install Oracle Software-Only
-------------------------------------
Note: Software has been installed on /u01

ORACLE_SID=DBSTAND
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/12cR1


-------------------------------------
Step 2. Create PFILE & CONTROL FILE
-------------------------------------
--On Primary
[oracle@DBPROD dbs]$ scp /tmp/control01.ctl  oracle@DBSTAND.lab:/u03/oradata/datafiles/control01.ctl
Note: Create PFILE from SPFILE on PROD and Copy over to Standby and modify it
SQL> CREATE PFILE FROM SPFILE;
File created.
[oracle@DBPROD dbs]$ scp initDBPROD.ora oracle@DBSTAND.lab:/u01/app/oracle/12cR1/dbs/initDBSTAND.ora
oracle@DBSTAND.lab's password:
initDBPROD.ora                                                        100%  940     0.9KB/s   00:00
[oracle@DBPROD dbs]$ rm initDBPROD.ora

--On Standby
Modified initDBSTAND.ora
[oracle@DBSTAND dbs]$ cat initDBSTAND.ora
*.compatible='12.1.0.0.0'
*.control_files='/u03/oradata/datafiles/control01.ctl','/u03/oradata/datafiles/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='DBPROD'
*.db_unique_name='DBSTAND'
*.diagnostic_dest='/u01/app/oracle'
*.enable_pluggable_database=true
*.fal_server='DBPROD'
*.log_archive_config='DG_CONFIG=(DBPROD,DBSTAND)'
*.log_archive_dest_1='LOCATION=/u05/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=DBSTAND'
*.log_archive_dest_2='SERVICE=DBPROD VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DBPROD'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_format='%t_%s_%r.arc'
*.log_archive_max_processes=3
*.remote_login_passwordfile='EXCLUSIVE'
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'

-------------------------------------
Step 3. Startup Standby
-------------------------------------
[oracle@DBSTAND dbs]$ echo $ORACLE_SID
DBSTAND
[oracle@DBSTAND dbs]$ echo $ORACLE_HOME
/u01/app/oracle/12cR1
[oracle@DBSTAND dbs]$ . oraenv
ORACLE_SID = [DBSTAND] ? DBSTAND
ORACLE_HOME = [/home/oracle] ? /u01/app/oracle/12cR1
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@DBSTAND dbs]$ sqlplus / as sysdba;

Connected to an idle instance.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area  217157632 bytes
Fixed Size                  2286656 bytes
Variable Size             159386560 bytes
Database Buffers           50331648 bytes
Redo Buffers                5152768 bytes
SQL> CREATE SPFILE FROM PFILE;

File created.

SQL> !rm $ORACLE_HOME/dbs/initDBSTAND.ora

SQL> shutdown immediate;
ORA-01507: database not mounted

ORACLE instance shut down.
SQL> startup nomount;
ORACLE instance started.

Total System Global Area  217157632 bytes
Fixed Size                  2286656 bytes
Variable Size             159386560 bytes
Database Buffers           50331648 bytes
Redo Buffers                5152768 bytes
SQL> show parameter spfile;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /u01/app/oracle/12cR1/dbs/spfi
                                                 leDBSTAND.ora


--------------------------------------------
Step 4. Modify LISTENER & TNSNAMES.ORA Files
--------------------------------------------
Note: Make sure tnsping is working and listener is up
[oracle@DBSTAND ~]$ tnsping DBPROD
[oracle@DBPROD ~]$ tnsping DBSTAND

-------------------------------------
Step 5. Create directory structure
-------------------------------------
Note: Make sure the directory structure is same as PROD(if not, use LOG/DB_FILE_NAME_CONVERT)

--On Primary run this to get all the DATAFILES location
RMAN> report schema;

-----------------------------------------
Step 6. Copy Backup files
-----------------------------------------
--backup files
SQL>  !scp -r oracle@DBPROD:/u02/rman/DBPROD/* oracle@DBSTAND:/u02/rman/DBPROD/

Note: I have created same directory structure in standby for backup as it was in primary so RMAN know the path

-----------------------------------------
Step 7. Create Password File
-----------------------------------------
Copy pwd file from DBPROD to DBSTAND and rename it

-----------------------------------------
Step 8. Restore Standby DB from RMAN bkup
-----------------------------------------
SQL> startup nomount;
ORACLE instance started.

Total System Global Area  217157632 bytes
Fixed Size                  2286656 bytes
Variable Size             159386560 bytes
Database Buffers           50331648 bytes
Redo Buffers                5152768 bytes
SQL> alter database mount;

Database altered.

[oracle@DBSTAND dbs]$ rman target /

connected to target database: DBPROD (not mounted)

RMAN> crosscheck backup;

RMAN> restore database;

channel ORA_DISK_1: piece handle=/u02/rman/DBPROD/DBPROD_bkup_850204537_31_1.bak tag=TAG20140614T075412
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:45
Finished restore at 14-JUN-14

-----------------------------------------
Step 9. Add Standby logs
-----------------------------------------

--Standby
SQL> ALTER DATABASE ADD STANDBY LOGFILE ('/u03/oradata/redo/standdb_redo01.log') SIZE 50M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE ('/u03/oradata/redo/standdb_redo02.log') SIZE 50M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE ('/u03/oradata/redo/standdb_redo03.log') SIZE 50M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE ('/u03/oradata/redo/standdb_redo04.log') SIZE 50M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE ('/u03/oradata/redo/standdb_redo05.log') SIZE 50M;

Database altered.


-----------------------------------------
Step 9. Add standby logs Production
-----------------------------------------
--Production
SQL> ALTER DATABASe ADD STANDBY LOGFILE ('/u03/oradata/redo/standby_redo01.log') SIZE 50M;
SQL>  ALTER DATABASe ADD STANDBY LOGFILE '/u03/oradata/redo/standby_redo02.log')SIZE  50M;
SQL> ALTER DATABASe ADD STANDBY LOGFILE ('/u03/oradata/redo/standby_redo03.log') SIZE 50M;
SQL> ALTER DATABASe ADD STANDBY LOGFILE ('/u03/oradata/redo/standby_redo04.log') SIZE 50M;
SQL> ALTER DATABASe ADD STANDBY LOGFILE ('/u03/oradata/redo/standby_redo05.log') SIZE 50M;

Database altered.



---------------------------------------
Step 10. Start Apply Process - Standby
---------------------------------------
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Database altered.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Database altered.


SQL>ALTER DATABASE OPEN READ ONLY;

SQL>SELECT OPEN_MODE FROM V$DATABASE;
OPEN_MODE
--------
READ ONLY

------------------
VERIFICATION
------------------

--PRIMARY
SQL> select thread#, max(sequence#) "Last Primary Seq Generated"
 from v$archived_log val, v$database vdb
 where val.resetlogs_change# = vdb.resetlogs_change#
 group by thread# order by 1;

   THREAD# Last Primary Seq Generated
---------- --------------------------
         1                         36

--STANDBY
SQL> select thread#, max(sequence#) "Last Standby Seq Applied"
 from v$archived_log val, v$database vdb
 where val.resetlogs_change# = vdb.resetlogs_change#
 and val.applied in ('YES','IN-MEMORY')
 group by thread# order by 1;

   THREAD# Last Standby Seq Applied
---------- ------------------------
         1                       36

SQL> SELECT NAME,DATABASE_ROLE,OPEN_MODE FROM V$DATABASE;

NAME      DATABASE_ROLE    OPEN_MODE
--------- ---------------- --------------------
DBPROD      PHYSICAL STANDBY MOUNTED

SQL> SELECT NAME,DATABASE_ROLE,OPEN_MODE FROM V$DATABASE;

NAME      DATABASE_ROLE    OPEN_MODE

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

DBPROD      PRIMARY          READ WRITE

Enjoy.....Abid Malik

How to configure database with Oracle 12c Restart

##################################################
How to configure database with Oracle 12c Restart
#################################################

Note: Although the Oracle Grid Infrastructure automatically registers database if GI is installed prior to DB but
in my case, I already had Oracle database created and GI was added later on.
Now I need to register my Oracle database with Oracle Restart (Grid Infrastructure on Standalone)


============================
Current configuration
============================
SQL> !srvctl config database

Note: The srvctl command is running from Oracle database ORACLE_HOME/bin


============================
Add database 
============================
SQL> !/u03/app/oracle/12.1.0/12cR1/bin/srvctl add database -d cdbdemo -o /u03/app/oracle/12.1.0/12cR1


SQL> !/u03/app/oracle/12.1.0/12cR1/bin/srvctl config
cdbdemo


SQL> !/u03/app/oracle/12.1.0/12cR1/bin/srvctl config database -d cdbdemo
Database unique name: cdbdemo
Database name:
Oracle home: /u03/app/oracle/12.1.0/12cR1
Oracle user: oracle
Spfile:
Password file:
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Database instance: cdbdemo
Disk Groups:
Services:



============================
Testing
============================
[root@dbtrmrk init.d]# ps -ef | grep smon | grep -v grep
oracle   20922     1  0 03:48 ?        00:00:00 asm_smon_+ASM
oracle   21824     1  0 05:17 ?        00:00:00 ora_smon_cdbdemo


[root@dbtrmrk init.d]# kill -9 21824
[root@dbtrmrk init.d]# ps -ef | grep smon | grep -v grep
oracle   20922     1  0 03:48 ?        00:00:00 asm_smon_+ASM

NOte: Oracle Database was not started by Restart....

hmm...what is missing? spfile ???? let's try





SQL> !srvctl modify database -db cdbdemo -spfile /u03/app/oracle/12.1.0/12cR1/dbs/spfilecdbdemo.ora


SQL> !srvctl config database -db cdbdemo
Database unique name: cdbdemo
Database name:
Oracle home: /u03/app/oracle/12.1.0/12cR1
Oracle user: oracle
Spfile: /u03/app/oracle/12.1.0/12cR1/dbs/spfilecdbdemo.ora
Password file:
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Database instance: cdbdemo
Disk Groups: DATA
Services:




[root@dbtrmrk init.d]# ps -ef | grep pmon
oracle   23317     1  0 06:09 ?        00:00:00 asm_pmon_+ASM
oracle   24267     1  0 06:14 ?        00:00:00 ora_pmon_cdbdemo

[root@dbtrmrk init.d]# kill -9 24267

Fri May 09 06:29:34 2014
Instance started by oraagent
Completed: ALTER DATABASE MOUNT /* db agent *//* {0:9:7} */
Fri May 09 06:30:32 2014
CJQ0 started with pid=56, OS id=27907
Completed: ALTER DATABASE OPEN /* db agent *//* {0:9:7} */



[root@dbtrmrk init.d]# /u10/app/gridbase/product/grid/12gR1/bin/crsctl status resource -t
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.ASMDG.dg
               ONLINE  ONLINE       dbtrmrk                  STABLE
ora.DATA.dg
               ONLINE  ONLINE       dbtrmrk                  STABLE
ora.LISTENER.lsnr
               ONLINE  ONLINE       dbtrmrk                  STABLE
ora.asm
               ONLINE  ONLINE       dbtrmrk                  Started,STABLE
ora.ons
               OFFLINE OFFLINE      dbtrmrk                  STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.cdbdemo.db
      1        ONLINE  ONLINE       dbtrmrk                  Open,STABLE
ora.cssd
      1        ONLINE  ONLINE       dbtrmrk                  STABLE
ora.diskmon
      1        OFFLINE OFFLINE                               STABLE
ora.evmd
      1        ONLINE  ONLINE       dbtrmrk                  STABLE
--------------------------------------------------------------------------------

Enjoy....Abid Malik

How to Move SPFILE & Password file into different ASM DISKGROUP

###############################################################
How to Move SPFILE & Password file into different ASM DISKGROUP
###############################################################


===============
Password File
==============
ASMCMD> pwget --asm
+DATA/ASM/PASSWORD/pwdasm.270.847074049

ASMCMD> pwcopy +DATA/ASM/PASSWORD/pwdasm.270.847074049 +ASMDG/orapwasm
copying +DATA/ASM/PASSWORD/pwdasm.270.847074049 -> +ASMDG/orapwasm

ASMCMD> ls -ltr +ASMDG/orapwasm


Type      Redund  Striped  Time             Sys  Name
PASSWORD  UNPROT  COARSE   MAY 09 03:00:00  N    orapwasm => +ASMDG/ASM/PASSWORD/pwdasm.256.847078823

ASMCMD> pwset --asm +ASMDG/ASM/PASSWORD/pwdasm.256.847078823

ASMCMD> pwget --asm
+ASMDG/ASM/PASSWORD/pwdasm.256.847078823


===============
SPFILE File
==============
ASMCMD> spget
+DATA/ASM/ASMPARAMETERFILE/REGISTRY.253.847072573

Note: I did not create ASM/ASMPFILE folder
ASMCMD> spcopy +DATA/ASM/ASMPARAMETERFILE/REGISTRY.253.847072573 +ASMDG/ASM/ASMPFILE/

ORA-15056: additional error message
ORA-17502: ksfdcre:4 Failed to create file +ASMDG/REGISTRY.253.847072573
ORA-15221: ASM operation requires compatible.asm of 11.2.0.0.0 or higher
ORA-06512: at line 7 (DBD ERROR: OCIStmtExecute)
ASMCMD> exit


SQL>ALTER DISKGROUP ASMDG SET ATTRIBUTE 'compatible.asm'='12.1';

Diskgroup altered.


ASMCMD> ls -ltr +ASMDG/ASM/ASMPARAMETERFILE/

Type              Redund  Striped  Time             Sys  Name
ASMPARAMETERFILE  UNPROT  COARSE   MAY 09 03:00:00  Y    REGISTRY.253.847078307


ASMCMD> spset +ASMDG/ASM/ASMPARAMETERFILE/REGISTRY.253.847078307


ASMCMD> spget --asm
+DATA/ASM/ASMPARAMETERFILE/REGISTRY.253.847072573


============
Verification
============
ASMCMD> shutdown --immediate
ASM diskgroups dismounted
ASM instance shutdown
Connected to an idle instance.


SQL> !crsctl stop has
CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on 'dbtrmrk'
CRS-2673: Attempting to stop 'ora.evmd' on 'dbtrmrk'
CRS-2673: Attempting to stop 'ora.LISTENER.lsnr' on 'dbtrmrk'
CRS-2677: Stop of 'ora.LISTENER.lsnr' on 'dbtrmrk' succeeded
CRS-2677: Stop of 'ora.evmd' on 'dbtrmrk' succeeded
CRS-2673: Attempting to stop 'ora.cssd' on 'dbtrmrk'
CRS-2677: Stop of 'ora.cssd' on 'dbtrmrk' succeeded
CRS-2793: Shutdown of Oracle High Availability Services-managed resources on 'dbtrmrk' has completed
CRS-4133: Oracle High Availability Services has been stopped.

SQL> !crsctl start has
CRS-4123: Oracle High Availability Services has been started.



ASMCMD> startup
ASM instance started

Total System Global Area 1135747072 bytes
Fixed Size                  2297344 bytes
Variable Size            1108283904 bytes
ASM Cache                  25165824 bytes
ASM diskgroups mounted

NAME                           STATE
------------------------------ -----------
ASMDG                          MOUNTED
DATA                           MOUNTED


SQL> !asmcmd spget --asm
+ASMDG/ASM/ASMPARAMETERFILE/REGISTRY.253.847078307

SQL>  !asmcmd pwget --asm
+ASMDG/ASM/PASSWORD/pwdasm.256.847078823



============
Remove old
============
ASMCMD> rm -r DATA/orapwasm
You may delete multiple files and/or directories.
Are you sure? (y/n) y


ASMCMD> rm -r DATA/ASM/ASMPARAMETERFILE
You may delete multiple files and/or directories.
Are you sure? (y/n) y

Enjoy....Abid Malik

How to Migrate Oracle 12c Database to ASM

###############################################
How to Migrate Oracle 12c Database to ASM
###############################################


======================================================
Step 1. Install Grid Infrastructure in new ORACLE_HOME
======================================================
Note: Already done (Read my post about it).



======================================================
Step 2. Creast ASM Instance
======================================================
Note: Already done (Read my post about it).



======================================================
Step 3. Create PFILE and modify control_file parameter
======================================================
SQL> create pfile from spfile;

File created.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.


SQL> !vi $ORACLE_HOME/dbs/initcdbdemo.ora

control_files='+DATA'

SQL> CREATE SPFILE FROM PFILE;

File created.



========================================================
Step 4. Note down the location of Current Control Files
========================================================
SQL> select name from v$controlfile;

NAME
------------------------------------------------------------
/u03/oradata/cdbdemo/control01.ctl
/u01/app/oracle/fast_recovery_area/cdbdemo/control02.ctl




========================================================
Step 5. Startup in NOMOUNT
========================================================
SQL> startup nomount;
ORACLE instance started.

Total System Global Area 1252663296 bytes
Fixed Size                  2287864 bytes
Variable Size             788530952 bytes
Database Buffers          452984832 bytes
Redo Buffers                8859648 bytes



========================================================
Step 6. Restore Controlfile and MOUNT
========================================================
SQL> !rman target /

Recovery Manager: Release 12.1.0.1.0 - Production on Thu May 8 03:25:17 2014

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

connected to target database: CDBDEMO (not mounted)


RMAN> restore controlfile from '/u03/oradata/cdbdemo/control01.ctl';

Starting restore at 08-MAY-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=126 device type=DISK

channel ORA_DISK_1: copied control file copy
output file name=+DATA/CDBDEMO/CONTROLFILE/current.256.846991571
Finished restore at 08-MAY-14


RMAN> alter database mount;

Statement processed
released channel: ORA_DISK_1


========================================================
Step 7. Backup database into ASM
========================================================
RMAN> backup as copy database format '+DATA';

Starting backup at 08-MAY-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=126 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/u03/oradata/cdbdemo/system01.dbf
output file name=+DATA/CDBDEMO/DATAFILE/system.257.846991607 tag=TAG20140508T032646 RECID=3 STAMP=846991644
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:45
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/u03/oradata/cdbdemo/undotbs01.dbf
output file name=+DATA/CDBDEMO/DATAFILE/undotbs1.258.846991651 tag=TAG20140508T032646 RECID=4 STAMP=846991697
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:55
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/u03/oradata/cdbdemo/sysaux01.dbf
output file name=+DATA/CDBDEMO/DATAFILE/sysaux.259.846991707 tag=TAG20140508T032646 RECID=5 STAMP=846991740
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting datafile copy
input datafile file number=00007 name=/u03/oradata/cdbdemo/pdbseed/sysaux01.dbf
output file name=+DATA/CDBDEMO/F84403ED7DE357E2E043C9A8A8C0FB1F/DATAFILE/sysaux.260.846991743 tag=TAG20140508T032646 RECID=6 STAMP=846991771
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=/u03/oradata/cdbdemo/pdbseed/system01.dbf
output file name=+DATA/CDBDEMO/F84403ED7DE357E2E043C9A8A8C0FB1F/DATAFILE/system.261.846991777 tag=TAG20140508T032646 RECID=7 STAMP=846991790
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00006 name=/u03/oradata/cdbdemo/users01.dbf
output file name=+DATA/CDBDEMO/DATAFILE/users.262.846991793 tag=TAG20140508T032646 RECID=8 STAMP=846991792
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 08-MAY-14

Starting Control File and SPFILE Autobackup at 08-MAY-14
piece handle=/u01/app/oracle/fast_recovery_area/CDBDEMO/autobackup/2014_05_08/o1_mf_s_846990695_9ppdmoxb_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 08-MAY-14



========================================================
Step 8. Switch to ASM
========================================================
RMAN> switch database to copy;

datafile 1 switched to datafile copy "+DATA/CDBDEMO/DATAFILE/system.257.846991607"
datafile 3 switched to datafile copy "+DATA/CDBDEMO/DATAFILE/sysaux.259.846991707"
datafile 4 switched to datafile copy "+DATA/CDBDEMO/DATAFILE/undotbs1.258.846991651"
datafile 5 switched to datafile copy "+DATA/CDBDEMO/F84403ED7DE357E2E043C9A8A8C0FB1F/DATAFILE/system.261.846991777"
datafile 6 switched to datafile copy "+DATA/CDBDEMO/DATAFILE/users.262.846991793"
datafile 7 switched to datafile copy "+DATA/CDBDEMO/F84403ED7DE357E2E043C9A8A8C0FB1F/DATAFILE/sysaux.260.846991743"



========================================================
Step 9. Open Database
========================================================
RMAN> alter database open;

Statement processed




========================================================
Step 10. Move Online Redo Log Files
========================================================
RMAN> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/u03/oradata/cdbdemo/redo03.log
/u03/oradata/cdbdemo/redo02.log
/u03/oradata/cdbdemo/redo01.log

RMAN> ALTER DATABASE DROP LOGFILE GROUP 2;

Statement processed


RMAN> ALTER DATABASE ADD LOGFILE GROUP 2 ('+DATA/CDBDEMO/redo02.log') SIZE 10M;

Statement processed


RMAN> SELECT group#,status from V$LOG;

    GROUP# STATUS
---------- ----------------
         1 ACTIVE
         2 CURRENT
         3 UNUSED
         4 UNUSED

RMAN> ALTER SYSTEM CHECKPOINT;

Statement processed



========================================================
Step 11. Move TEMP Files - CDB
========================================================
SQL> select name from v$tempfile;

NAME
------------------------------------------------------------
/u03/oradata/cdbdemo/temp01.dbf
/u03/oradata/cdbdemo/pdbseed/pdbseed_temp01.dbf

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '+DATA' SIZE 10M;

Tablespace altered.



========================================================
Step 12. Move TEMP Files - PDBS
========================================================
SQL> ALTER SESSION SET CONTAINER=PDB$SEED;

Session altered.

SQL> select name from v$tempfile;

NAME
------------------------------------------------------------
/u03/oradata/cdbdemo/pdbseed/pdbseed_temp01.dbf


SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '+DATA' SIZE 20M;

Tablespace altered.




============================================
MOVE DATAFILE ONLINE to ASM (Oracle 12c)
===========================================
SQL> SELECT FILE_NAME,Round( BYTES/1024/1024) "SizeMB",STATUS,ONLINE_STATUS FROM DBA_DATA_FILES
WHERE TABLESPACE_NAME = 'USERS';

FILE_NAME                                                   SizeMB STATUS          ONLINE_STATUS
------------------------------------------------------- ---------- --------------- ---------------
+DATA/CDBDEMO/DATAFILE/users.262.846991793                    5.00 AVAILABLE       ONLINE
/u03/oradata/cdbdemo/users02.dbf                             10.00 AVAILABLE       ONLINE


SQL> ALTER DATABASE MOVE DATAFILE '/u03/oradata/cdbdemo/users02.dbf' TO '+DATA';

Database altered.


Enjoy....Abid Malik

How to install Grid Infrastructure for Oracle 12c

##################################################
How to install Grid Infrastructure for Oracle 12c
##################################################


=================================================
Step 1. Download Grid Infrastructure software
=================================================
[oracle@dbtrmrk tmp]$ ll
total 1906440
drwxr-xr-x  3 oracle oinstall       4096 Apr 30 05:39 CVU_12.1.0.1.0_oracle
drwxr-xr-x  2 oracle oinstall       4096 May  4 03:54 grid_1201
-rw-r--r--  1 oracle oinstall 1750478910 May 10  2014 linuxamd64_12c_grid_1of2.zip
-rw-r--r--  1 oracle oinstall  201673595 May 10  2014 linuxamd64_12c_grid_2of2.zip




=================================================
Step 2. Unzip Grid Infrastructure software
=================================================
[oracle@dbtrmrk tmp]$ unzip linuxamd64_12c_grid_1of2.zip -d /tmp/grid_1201/
[oracle@dbtrmrk tmp]$ unzip linuxamd64_12c_grid_2of2.zip -d /tmp/grid_1201/



=================================================
Step 3. Create Dir for Grid Infrastructure Home
=================================================
[root@dbtrmrk tmp]# mkdir /u10
[root@dbtrmrk tmp]# chown -R oracle:oinstall /u10
[root@dbtrmrk tmp]# su - oracle
[oracle@dbtrmrk ~]$ mkdir -p /u10/app/gridbase/product/grid/12gR1




===============================================================
Step 4. Set ORACLE_HOME and ORACLE_BASE for Grid Infrastructure
===============================================================
/u10/app/gridbase/product/grid/12gR1
[oracle@dbtrmrk 12gR1]$ export ORACLE_BASE=/u10/app/gridbase
[oracle@dbtrmrk 12gR1]$ export ORACLE_HOME=/u10/app/gridbase/product/grid/12gR1
[oracle@dbtrmrk 12gR1]$ cd /tmp/grid_1201/grid/response/




=====================================================
Step 5. Prepare Response File for Grid Infrastructure
=====================================================
[oracle@dbtrmrk response]$ cat grid.rsp

#------------------------------------------------------------------------------
# Do not change the following system generated value.
#------------------------------------------------------------------------------
oracle.install.responseFileVersion=/oracle/install/rspfmt_crsinstall_response_schema_v12.1.0
ORACLE_HOSTNAME=dbtrmrk.localdomain
INVENTORY_LOCATION=/u01/app/oraInventory
SELECTED_LANGUAGES=en
oracle.install.option=CRS_SWONLY
ORACLE_BASE=/u10/app/gridbase
ORACLE_HOME=/u10/app/gridbase/product/grid/12gR1
oracle.install.asm.OSDBA=dba
oracle.install.asm.OSOPER=oinstall
oracle.install.asm.OSASM=dba
oracle.install.crs.config.ClusterType=STANDARD
oracle.install.crs.config.gpnp.configureGNS=true
oracle.install.crs.config.autoConfigureClusterNodeVIP=true
oracle.install.crs.config.gpnp.gnsOption=CREATE_NEW_GNS
oracle.install.crs.managementdb.configure=true
oracle.install.crs.config.sharedFileSystemStorage.votingDiskLocations=
oracle.install.crs.config.sharedFileSystemStorage.votingDiskRedundancy=NORMAL
oracle.install.crs.config.sharedFileSystemStorage.ocrLocations=
oracle.install.crs.config.sharedFileSystemStorage.ocrRedundancy=NORMAL
oracle.install.crs.config.useIPMI=false
oracle.install.crs.config.ignoreDownNodes=false
oracle.installer.autoupdates.option=SKIP_UPDATES
PROXY_PORT=0




==============================================================
Step 7. Run Installer for Grid Infrastructure Software install
==============================================================
[oracle@dbtrmrk grid]$ ./runInstaller -silent -responseFile /tmp/grid_1201/grid/response/grid.rsp
Starting Oracle Universal Installer...

Checking Temp space: must be greater than 120 MB.   Actual 12558 MB    Passed
Checking swap space: must be greater than 150 MB.   Actual 3999 MB    Passed
You can find the log of this install session at:
 /u01/app/oraInventory/logs/installActions2014-05-04_10-30-56AM.log

The installation of Oracle Grid Infrastructure 12c was successful.

As a root user, execute the following script(s):
        1. /u10/app/gridbase/product/grid/12gR1/root.sh





=====================================================
Step 8. Run script as root for Grid Infrastructure
=====================================================
[root@dbtrmrk tmp]# /u10/app/gridbase/product/grid/12gR1/root.sh
Check /u10/app/gridbase/product/grid/12gR1/install/root_dbtrmrk.localdomain_2014-05-04_10-44-03.log for the output of root script

[root@dbtrmrk tmp]# cat /u10/app/gridbase/product/grid/12gR1/install/root_dbtrmrk.localdomain_2014-05-04_10-44-03.log
Performing root user operation for Oracle 12c

The following environment variables are set as:
    ORACLE_OWNER= oracle
    ORACLE_HOME=  /u10/app/gridbase/product/grid/12gR1
   Copying dbhome to /usr/local/bin ...
   Copying oraenv to /usr/local/bin ...
   Copying coraenv to /usr/local/bin ...

Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root script.
Now product-specific root actions will be performed.

To configure Grid Infrastructure for a Stand-Alone Server run the following command as the root user:
/u10/app/gridbase/product/grid/12gR1/perl/bin/perl -I/u10/app/gridbase/product/grid/12gR1/perl/lib -I/u10/app/gridbase/product/grid/12gR1/crs/install /u10/app/gridbase/product/grid/12gR1/crs/install/roothas.pl


To configure Grid Infrastructure for a Cluster execute the following command as oracle user:
/u10/app/gridbase/product/grid/12gR1/crs/config/config.sh
This command launches the Grid Infrastructure Configuration Wizard. The wizard also supports silent operation, and the parameters can be passed through the response file that is available in the installation media.



=============================================================
Step 9. Run script as Oracle to confiture Grid Infrastructure
=============================================================
[root@dbtrmrk tmp]# /u10/app/gridbase/product/grid/12gR1/perl/bin/perl -I/u10/app/gridbase/product/grid/12gR1/perl/lib -I/u10/app/gridbase/product/grid/12gR1/crs/install /u10/app/gridbase/product/grid/12gR1/crs/install/roothas.pl
Using configuration parameter file: /u10/app/gridbase/product/grid/12gR1/crs/install/crsconfig_params
LOCAL ADD MODE
Creating OCR keys for user 'oracle', privgrp 'oinstall'..
Operation successful.
LOCAL ONLY MODE
Successfully accumulated necessary OCR keys.
Creating OCR keys for user 'root', privgrp 'root'..
Operation successful.
CRS-4664: Node dbtrmrk successfully pinned.

2014/05/04 11:09:27 CLSRSC-327: Successfully configured Oracle Grid Infrastructure for a Standalone Server





===============================================================
Step 10. Verify Successfull installation of Grid Infrastructure
===============================================================
[root@dbtrmrk tmp]# //u10/app/gridbase/product/grid/12gR1/bin/crsctl check has
CRS-4638: Oracle High Availability Services is online


[oracle@dbtrmrk grid]$$ORACLE_HOME/bin/asmca

ASM Created and Started Successfully.
Disk Group DGDATA created successfully.



SQL> select instance_name,host_name,status from v$instance;

INSTANCE_NAME    HOST_NAME                                                        STATUS
---------------- ---------------------------------------------------------------- ------------
+ASM             dbtrmrk.localdomain                                              STARTED


ASMCMD> lsdg
State    Type    Rebal  Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
MOUNTED  NORMAL  N         512   4096  1048576      4076     3944             1019            1462              0             N  DGDATA/

Note: I have created DGDATA with NORMAL REDUNDANCY



Enjoy.....Abid Malik

How to stamp Disks for ASM using ASMLib Package

#########################################################
How to stamp Disks for ASM using ASMLib Package
#########################################################

Note: I have added 10 1GB SCSI disks in my database host, will stamp them as ASM DISKS:


===============================
Step 1. Add Hard Disk(s)
===============================
[root@dbtrmrk ~]# ls -ltr /dev/sd*
brw-rw---- 1 root disk 8, 128 May  4 01:09 /dev/sdi
brw-rw---- 1 root disk 8,  64 May  4 01:09 /dev/sde
brw-rw---- 1 root disk 8, 112 May  4 01:09 /dev/sdh
brw-rw---- 1 root disk 8,  96 May  4 01:09 /dev/sdg
brw-rw---- 1 root disk 8,  80 May  4 01:09 /dev/sdf
brw-rw---- 1 root disk 8,  32 May  4 01:09 /dev/sdc
brw-rw---- 1 root disk 8,  16 May  4 01:09 /dev/sdb
brw-rw---- 1 root disk 8, 144 May  4 01:09 /dev/sdj
brw-rw---- 1 root disk 8, 160 May  4 01:09 /dev/sdk
brw-rw---- 1 root disk 8,  48 May  4 01:09 /dev/sdd
brw-rw---- 1 root disk 8,   0 May  4 01:09 /dev/sda
brw-rw---- 1 root disk 8,   2 May  4 01:09 /dev/sda2
brw-rw---- 1 root disk 8,   1 May  4 01:09 /dev/sda1


Note: Hard Disks b-k has been added


===============================
Step 2. Format Hard Disk(s)
===============================
-----------------------------------------------------------------------------------
[root@dbtrmrk ~]# fdisk /dev/sdb
Device contains neither a valid DOS partition table, nor Sun, SGI or OSF disklabel
Building a new DOS disklabel with disk identifier 0x88c10643.
Changes will remain in memory only, until you decide to write them.
After that, of course, the previous content won't be recoverable.

Warning: invalid flag 0x0000 of partition table 4 will be corrected by w(rite)

WARNING: DOS-compatible mode is deprecated. It's strongly recommended to
         switch off the mode (command 'c') and change display units to
         sectors (command 'u').

Command (m for help): n
Command action
   e   extended
   p   primary partition (1-4)
p
Partition number (1-4): 1
First cylinder (1-130, default 1):
Using default value 1
Last cylinder, +cylinders or +size{K,M,G} (1-130, default 130):
Using default value 130

Command (m for help): w
The partition table has been altered!

Calling ioctl() to re-read partition table.
Syncing disks.

---------------------------------------------------------------------------------------
[root@dbtrmrk ~]# fdisk /dev/sdc
Device contains neither a valid DOS partition table, nor Sun, SGI or OSF disklabel
Building a new DOS disklabel with disk identifier 0xaa414d27.
Changes will remain in memory only, until you decide to write them.
After that, of course, the previous content won't be recoverable.

Warning: invalid flag 0x0000 of partition table 4 will be corrected by w(rite)

WARNING: DOS-compatible mode is deprecated. It's strongly recommended to
         switch off the mode (command 'c') and change display units to
         sectors (command 'u').

Command (m for help): n
Command action
   e   extended
   p   primary partition (1-4)
p
Partition number (1-4): 1
First cylinder (1-130, default 1):
Using default value 1
Last cylinder, +cylinders or +size{K,M,G} (1-130, default 130):
Using default value 130

Command (m for help): w
The partition table has been altered!

Calling ioctl() to re-read partition table.
Syncing disks.
----------------------------------------------------------------------------------------
[root@dbtrmrk ~]# fdisk /dev/sdd
Device contains neither a valid DOS partition table, nor Sun, SGI or OSF disklabel
Building a new DOS disklabel with disk identifier 0x3945f228.
Changes will remain in memory only, until you decide to write them.
After that, of course, the previous content won't be recoverable.

Warning: invalid flag 0x0000 of partition table 4 will be corrected by w(rite)

WARNING: DOS-compatible mode is deprecated. It's strongly recommended to
         switch off the mode (command 'c') and change display units to
         sectors (command 'u').

Command (m for help): n
Command action
   e   extended
   p   primary partition (1-4)
p
Partition number (1-4): 1
First cylinder (1-130, default 1): 1
Last cylinder, +cylinders or +size{K,M,G} (1-130, default 130):
Using default value 130

Command (m for help): w
The partition table has been altered!

Calling ioctl() to re-read partition table.
Syncing disks.
----------------------------------------------------------------------------------------
[root@dbtrmrk ~]# fdisk /dev/sde
Device contains neither a valid DOS partition table, nor Sun, SGI or OSF disklabel
Building a new DOS disklabel with disk identifier 0x7b431510.
Changes will remain in memory only, until you decide to write them.
After that, of course, the previous content won't be recoverable.

Warning: invalid flag 0x0000 of partition table 4 will be corrected by w(rite)

WARNING: DOS-compatible mode is deprecated. It's strongly recommended to
         switch off the mode (command 'c') and change display units to
         sectors (command 'u').

Command (m for help): n
Command action
   e   extended
   p   primary partition (1-4)
p
Partition number (1-4): 1
First cylinder (1-130, default 1):
Using default value 1
Last cylinder, +cylinders or +size{K,M,G} (1-130, default 130):
Using default value 130

Command (m for help): w
The partition table has been altered!

Calling ioctl() to re-read partition table.
Syncing disks.
----------------------------------------------------------------------------------------
[root@dbtrmrk ~]# fdisk /dev/sdf
Device contains neither a valid DOS partition table, nor Sun, SGI or OSF disklabel
Building a new DOS disklabel with disk identifier 0x6d543a55.
Changes will remain in memory only, until you decide to write them.
After that, of course, the previous content won't be recoverable.

Warning: invalid flag 0x0000 of partition table 4 will be corrected by w(rite)

WARNING: DOS-compatible mode is deprecated. It's strongly recommended to
         switch off the mode (command 'c') and change display units to
         sectors (command 'u').

Command (m for help): n
Command action
   e   extended
   p   primary partition (1-4)
p
Partition number (1-4): 1
First cylinder (1-130, default 1):
Using default value 1
Last cylinder, +cylinders or +size{K,M,G} (1-130, default 130):
Using default value 130

Command (m for help): w
The partition table has been altered!

Calling ioctl() to re-read partition table.
Syncing disks.
----------------------------------------------------------------------------------------
[root@dbtrmrk ~]# fdisk /dev/sdg
Device contains neither a valid DOS partition table, nor Sun, SGI or OSF disklabel
Building a new DOS disklabel with disk identifier 0x10adb6d6.
Changes will remain in memory only, until you decide to write them.
After that, of course, the previous content won't be recoverable.

Warning: invalid flag 0x0000 of partition table 4 will be corrected by w(rite)

WARNING: DOS-compatible mode is deprecated. It's strongly recommended to
         switch off the mode (command 'c') and change display units to
         sectors (command 'u').

Command (m for help): n
Command action
   e   extended
   p   primary partition (1-4)
p
Partition number (1-4): 1
First cylinder (1-130, default 1):
Using default value 1
Last cylinder, +cylinders or +size{K,M,G} (1-130, default 130):
Using default value 130

Command (m for help): w
The partition table has been altered!

Calling ioctl() to re-read partition table.
Syncing disks.
----------------------------------------------------------------------------------------
[root@dbtrmrk ~]# fdisk /dev/sdh
Device contains neither a valid DOS partition table, nor Sun, SGI or OSF disklabel
Building a new DOS disklabel with disk identifier 0xafdbdc34.
Changes will remain in memory only, until you decide to write them.
After that, of course, the previous content won't be recoverable.

Warning: invalid flag 0x0000 of partition table 4 will be corrected by w(rite)

WARNING: DOS-compatible mode is deprecated. It's strongly recommended to
         switch off the mode (command 'c') and change display units to
         sectors (command 'u').

Command (m for help): n
Command action
   e   extended
   p   primary partition (1-4)
p
Partition number (1-4): 1
First cylinder (1-130, default 1):
Using default value 1
Last cylinder, +cylinders or +size{K,M,G} (1-130, default 130):
Using default value 130

Command (m for help): w
The partition table has been altered!

Calling ioctl() to re-read partition table.
Syncing disks.
----------------------------------------------------------------------------------------
[root@dbtrmrk ~]# fdisk /dev/sdi
Device contains neither a valid DOS partition table, nor Sun, SGI or OSF disklabel
Building a new DOS disklabel with disk identifier 0xe4a79ffc.
Changes will remain in memory only, until you decide to write them.
After that, of course, the previous content won't be recoverable.

Warning: invalid flag 0x0000 of partition table 4 will be corrected by w(rite)

WARNING: DOS-compatible mode is deprecated. It's strongly recommended to
         switch off the mode (command 'c') and change display units to
         sectors (command 'u').

Command (m for help): n
Command action
   e   extended
   p   primary partition (1-4)
p
Partition number (1-4): 1
First cylinder (1-130, default 1): 1
Last cylinder, +cylinders or +size{K,M,G} (1-130, default 130):
Using default value 130

Command (m for help): w
The partition table has been altered!

Calling ioctl() to re-read partition table.
Syncing disks.
----------------------------------------------------------------------------------------
[root@dbtrmrk ~]# fdisk /dev/sdj
Device contains neither a valid DOS partition table, nor Sun, SGI or OSF disklabel
Building a new DOS disklabel with disk identifier 0x261ec39d.
Changes will remain in memory only, until you decide to write them.
After that, of course, the previous content won't be recoverable.

Warning: invalid flag 0x0000 of partition table 4 will be corrected by w(rite)

WARNING: DOS-compatible mode is deprecated. It's strongly recommended to
         switch off the mode (command 'c') and change display units to
         sectors (command 'u').

Command (m for help): n
Command action
   e   extended
   p   primary partition (1-4)
p
Partition number (1-4): 1
First cylinder (1-130, default 1): 1
Last cylinder, +cylinders or +size{K,M,G} (1-130, default 130):
Using default value 130

Command (m for help): w
The partition table has been altered!

Calling ioctl() to re-read partition table.
Syncing disks.
----------------------------------------------------------------------------------------
[root@dbtrmrk ~]# fdisk /dev/sdk
Device contains neither a valid DOS partition table, nor Sun, SGI or OSF disklabel
Building a new DOS disklabel with disk identifier 0xa5620de4.
Changes will remain in memory only, until you decide to write them.
After that, of course, the previous content won't be recoverable.

Warning: invalid flag 0x0000 of partition table 4 will be corrected by w(rite)

WARNING: DOS-compatible mode is deprecated. It's strongly recommended to
         switch off the mode (command 'c') and change display units to
         sectors (command 'u').

Command (m for help): n
Command action
   e   extended
   p   primary partition (1-4)
p
Partition number (1-4): 1
First cylinder (1-130, default 1):
Using default value 1
Last cylinder, +cylinders or +size{K,M,G} (1-130, default 130):
Using default value 130

Command (m for help): w
The partition table has been altered!

Calling ioctl() to re-read partition table.
Syncing disks.
----------------------------------------------------------------------------------------
[root@dbtrmrk ~]# ls -ltr /dev/sd*
brw-rw---- 1 root disk 8,   0 May  4 01:09 /dev/sda
brw-rw---- 1 root disk 8,   2 May  4 01:09 /dev/sda2
brw-rw---- 1 root disk 8,   1 May  4 01:09 /dev/sda1
brw-rw---- 1 root disk 8,  16 May  4 01:14 /dev/sdb
brw-rw---- 1 root disk 8,  17 May  4 01:14 /dev/sdb1
brw-rw---- 1 root disk 8,  32 May  4 01:15 /dev/sdc
brw-rw---- 1 root disk 8,  33 May  4 01:15 /dev/sdc1
brw-rw---- 1 root disk 8,  49 May  4 01:15 /dev/sdd1
brw-rw---- 1 root disk 8,  48 May  4 01:15 /dev/sdd
brw-rw---- 1 root disk 8,  65 May  4 01:16 /dev/sde1
brw-rw---- 1 root disk 8,  64 May  4 01:16 /dev/sde
brw-rw---- 1 root disk 8,  81 May  4 01:16 /dev/sdf1
brw-rw---- 1 root disk 8,  80 May  4 01:16 /dev/sdf
brw-rw---- 1 root disk 8,  97 May  4 01:16 /dev/sdg1
brw-rw---- 1 root disk 8,  96 May  4 01:16 /dev/sdg
brw-rw---- 1 root disk 8, 113 May  4 01:17 /dev/sdh1
brw-rw---- 1 root disk 8, 112 May  4 01:17 /dev/sdh
brw-rw---- 1 root disk 8, 129 May  4 01:17 /dev/sdi1
brw-rw---- 1 root disk 8, 128 May  4 01:17 /dev/sdi
brw-rw---- 1 root disk 8, 145 May  4 01:17 /dev/sdj1
brw-rw---- 1 root disk 8, 144 May  4 01:17 /dev/sdj
brw-rw---- 1 root disk 8, 161 May  4 01:17 /dev/sdk1
brw-rw---- 1 root disk 8, 160 May  4 01:17 /dev/sdk


=======================================================
Step 3. Verify ASM Module has been loaded into Kernerl
=======================================================
[root@dbtrmrk ~]# df -ha | grep oracleasm
oracleasmfs              0     0     0   -  /dev/oracleasm

[root@dbtrmrk ~]# /sbin/lsmod | grep oracleasm
oracleasm              53865  1




======================================
Step 4. Create ASM Disks
======================================
[root@dbtrmrk ~]# oracleasm listdisks

[root@dbtrmrk ~]# oracleasm createdisk ASM1 /dev/sdb1
Writing disk header: done
Instantiating disk: done

[root@dbtrmrk ~]# oracleasm createdisk ASM2 /dev/sdc1
Writing disk header: done
Instantiating disk: done

[root@dbtrmrk ~]# oracleasm createdisk ASM3 /dev/sdd1
Writing disk header: done
Instantiating disk: done

[root@dbtrmrk ~]# oracleasm createdisk ASM4 /dev/sde1
Writing disk header: done
Instantiating disk: done

[root@dbtrmrk ~]# oracleasm createdisk ASM5 /dev/sdf1
Writing disk header: done
Instantiating disk: done

[root@dbtrmrk ~]# oracleasm createdisk ASM6 /dev/sdg1
Writing disk header: done
Instantiating disk: done

[root@dbtrmrk ~]# oracleasm createdisk ASM7 /dev/sdh1
Writing disk header: done
Instantiating disk: done

[root@dbtrmrk ~]# oracleasm createdisk ASM8 /dev/sdi1
Writing disk header: done
Instantiating disk: done

[root@dbtrmrk ~]# oracleasm createdisk ASM9 /dev/sdj1
Writing disk header: done
Instantiating disk: done

[root@dbtrmrk ~]# oracleasm createdisk ASM10 /dev/sdk1
Writing disk header: done
Instantiating disk: done

[root@dbtrmrk ~]# oracleasm listdisks
ASM1
ASM2
ASM3
ASM4
ASM5
ASM6
ASM7
ASM8
ASM9
ASM10



=========================================
Step 5. Verify ASM Disks
=========================================
[root@dbtrmrk ~]# oracleasm querydisk ASM1
Disk "ASM1" is a valid ASM disk

[root@dbtrmrk ~]#  oracleasm querydisk ASM2
Disk "ASM2" is a valid ASM disk

[root@dbtrmrk ~]#  oracleasm querydisk ASM3
Disk "ASM3" is a valid ASM disk

[root@dbtrmrk ~]#  oracleasm querydisk ASM4
Disk "ASM4" is a valid ASM disk

[root@dbtrmrk ~]#  oracleasm querydisk ASM5
Disk "ASM5" is a valid ASM disk

[root@dbtrmrk ~]#  oracleasm querydisk ASM6
Disk "ASM6" is a valid ASM disk

[root@dbtrmrk ~]#  oracleasm querydisk ASM7
Disk "ASM7" is a valid ASM disk

[root@dbtrmrk ~]#  oracleasm querydisk ASM8
Disk "ASM8" is a valid ASM disk

[root@dbtrmrk ~]#  oracleasm querydisk ASM9
Disk "ASM9" is a valid ASM disk

[root@dbtrmrk ~]#  oracleasm querydisk ASM10
Disk "ASM10" is a valid ASM disk


[root@dbtrmrk ~]# ls -ltr /dev/oracleasm/disks
total 0
brw-rw---- 1 oracle oinstall 8,  17 May  4 01:20 ASM1
brw-rw---- 1 oracle oinstall 8,  33 May  4 01:20 ASM2
brw-rw---- 1 oracle oinstall 8,  49 May  4 01:21 ASM3
brw-rw---- 1 oracle oinstall 8,  65 May  4 01:21 ASM4
brw-rw---- 1 oracle oinstall 8,  81 May  4 01:21 ASM5
brw-rw---- 1 oracle oinstall 8,  97 May  4 01:21 ASM6
brw-rw---- 1 oracle oinstall 8, 113 May  4 01:21 ASM7
brw-rw---- 1 oracle oinstall 8, 129 May  4 01:21 ASM8
brw-rw---- 1 oracle oinstall 8, 145 May  4 01:21 ASM9
brw-rw---- 1 oracle oinstall 8, 161 May  4 01:22 ASM10

Enjoy......Abid Malik

How to install ASMLib Package for Oracle 12c

##################################################################
How to install oracleasm (ASMLib) Package for Oracle Database 12c
##################################################################

======================================
Step 1 - Check Kernerl Driver Version
======================================
[root@dbtrmrk tmp]# uname -rm
2.6.39-400.209.1.el6uek.x86_64 x86_64

Note: It is Oracle Unbreakable Enterprise Kernal 2.6 (Linux 6) 64-bit OS



======================================
Step 2 - Download Oralce Public Yum
======================================
[root@dbtrmrk tmp]# cd /etc/yum.repos.d/
[root@dbtrmrk tmp]# wget https://public-yum.oracle.com/public-yum-ol6.repo

[root@dbtrmrk yum.repos.d]# ll
-rw-r--r-- 1 root root 1953 May  3 03:54 public-yum-ol6.repo


======================================
Step 3 - Enable repo in Public Yum ol6
======================================
[ol6_latest]
name=Oracle Linux $releasever Latest ($basearch)
baseurl=http://public-yum.oracle.com/repo/OracleLinux/OL6/latest/$basearch/
gpgkey=http://public-yum.oracle.com/RPM-GPG-KEY-oracle-ol6
gpgcheck=1
enabled=1


[ol6_UEK_latest]
name=Latest Unbreakable Enterprise Kernel for Oracle Linux $releasever ($basearch)
baseurl=http://public-yum.oracle.com/repo/OracleLinux/OL6/UEK/latest/$basearch/
gpgkey=http://public-yum.oracle.com/RPM-GPG-KEY-oracle-ol6
gpgcheck=1
enabled=1

[ol6_UEK_base]
name=Unbreakable Enterprise Kernel for Oracle Linux $releasever ($basearch)
baseurl=http://public-yum.oracle.com/repo/OracleLinux/OL6/UEK/base/$basearch/
gpgkey=http://public-yum.oracle.com/RPM-GPG-KEY-oracle-ol6
gpgcheck=1
enabled=1



======================================
Step 4 - Install kmod-oracleasm
======================================
[root@dbtrmrk yum.repos.d]# yum install kmod-oracleasm
Loaded plugins: refresh-packagekit, security
Setting up Install Process
Resolving Dependencies
--> Running transaction check
---> Package kmod-oracleasm.x86_64 0:2.0.6.rh1-2.el6 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

================================================================================================================================
 Package                          Arch                     Version                           Repository                    Size
================================================================================================================================
Installing:
 kmod-oracleasm                   x86_64                   2.0.6.rh1-2.el6                   ol6_latest                    34 k

Transaction Summary
================================================================================================================================
Install       1 Package(s)

Total download size: 34 k
Installed size: 115 k
Is this ok [y/N]: y
Downloading Packages:
kmod-oracleasm-2.0.6.rh1-2.el6.x86_64.rpm                                                                |  34 kB     00:00
Running rpm_check_debug
Running Transaction Test
Transaction Test Succeeded
Running Transaction
  Installing : kmod-oracleasm-2.0.6.rh1-2.el6.x86_64                                                                        1/1
  Verifying  : kmod-oracleasm-2.0.6.rh1-2.el6.x86_64                                                                        1/1

Installed:
  kmod-oracleasm.x86_64 0:2.0.6.rh1-2.el6

Complete!




======================================
Step 5 - Install oracleasm-support
======================================
[root@dbtrmrk yum.repos.d]# yum install oracleasm-support
Loaded plugins: refresh-packagekit, security
Setting up Install Process
Package oracleasm-support-2.1.8-1.el6.x86_64 already installed and latest version
Nothing to do





======================================
Step 6 - Install oracleasmlib
======================================
[root@dbtrmrk yum.repos.d]# yum install oracleasmlib
Loaded plugins: refresh-packagekit, security
Setting up Install Process
No package oracleasmlib available.
Error: Nothing to do


Note: Somehow the public yum repo could not find this package, so I downloaded it manually.
Note: I put the .rpm in /tmp and installed manually:

[root@dbtrmrk yum.repos.d]# cd /tmp
[root@dbtrmrk tmp]# ll
-rw-r--r--  1 oracle oinstall 13300 May  9  2014 oracleasmlib-2.0.4-1.el6.x86_64.rpm

[root@dbtrmrk tmp]# rpm -Uvh oracleasmlib-2.0.4-1.el6.x86_64.rpm
Preparing...                ########################################### [100%]
   1:oracleasmlib           ########################################### [100%]


[root@dbtrmrk yum.repos.d]# rpm -q -i oracleasmlib
Name        : oracleasmlib                 Relocations: (not relocatable)
Version     : 2.0.4                             Vendor: Oracle Corporation
Release     : 1.el6                         Build Date: Mon 26 Mar 2012 04:22:44 PM EDT
Install Date: Sat 03 May 2014 04:26:27 AM EDT      Build Host: ca-build44.us.oracle.com
Group       : System Environment/Kernel     Source RPM: oracleasmlib-2.0.4-1.el6.src.rpm
Size        : 27192                            License: Oracle Corporation
Signature   : RSA/8, Mon 26 Mar 2012 04:22:51 PM EDT, Key ID 72f97b74ec551f03
Packager    : Joel Becker <joel.becker@oracle.com>
URL         : http://oss.oracle.com/
Summary     : The Oracle Automatic Storage Management library userspace code.
Description :
The Oracle userspace library for Oracle Automatic Storage Management



======================================
Step 7 - Verify oracleasmlib
======================================
[root@dbtrmrk yum.repos.d]# rpm -qa | grep -i oracleasm
kmod-oracleasm-2.0.6.rh1-2.el6.x86_64
oracleasm-support-2.1.8-1.el6.x86_64
oracleasmlib-2.0.4-1.el6.x86_64




======================================
Step 8 - Configure ASMLib
======================================
[root@dbtrmrk yum.repos.d]# /usr/sbin/oracleasm configure -i
Configuring the Oracle ASM library driver.

This will configure the on-boot properties of the Oracle ASM library
driver.  The following questions will determine whether the driver is
loaded on boot and what permissions it will have.  The current values
will be shown in brackets ('[]').  Hitting <ENTER> without typing an
answer will keep that current value.  Ctrl-C will abort.

Default user to own the driver interface [oracle]: oracle
Default group to own the driver interface [install]: oinstall
Start Oracle ASM library driver on boot (y/n) [y]: y
Scan for Oracle ASM disks on boot (y/n) [y]: y
Writing Oracle ASM library driver configuration: done



======================================
Step 9 - Initialize oracleasm
======================================
[root@dbtrmrk yum.repos.d]# /usr/sbin/oracleasm init




======================================
Step 10 - Verify oracleasm mount point
======================================
[root@dbtrmrk yum.repos.d]# df -h /dev/oracleasm/
Filesystem            Size  Used Avail Use% Mounted on
oracleasmfs              0     0     0   -  /dev/oracleasm



Enjoy.....Abid Malik

How to Migrate a NON-CONTAINER-CDB to PDB using DBMS_PDB

########################################################
How to migrate a NON-CONTAINER-CDB to PDB using DBMS_PDB
########################################################


====================================
Before Migration to Pluggable DB
====================================
SQL> select name,cdb from v$database;

NAME      CDB
--------- ---
DBTRMRK   NO


SQL> select instance_name,host_name,version,status from gv$instance;

INSTANCE_NAME HOST_NAME                           VERSION    STATUS
------------- ----------------------------------- ---------- --------
dbtrmrk       dbtrmrk.localdomain                 12.1.0.1.0 OPEN



====================================
Migration Tasks
====================================

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.


SQL> STARTUP OPEN READ ONLY;
ORACLE instance started.

Total System Global Area  801701888 bytes
Fixed Size                  2293496 bytes
Variable Size             327155976 bytes
Database Buffers          465567744 bytes
Redo Buffers                6684672 bytes
Database mounted.
Database opened.



====================================
DESCRIBE the NON-CONTAINER-CDB
====================================
SQL> BEGIN
  2      DBMS_PDB.DESCRIBE(
  3        pdb_descr_file => '/tmp/dbtrmrk.xml');
  4  END;
  5  /

PL/SQL procedure successfully completed.



====================================
SHUTDOWN NON-CONTAINER-CDB
====================================
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.



====================================
Analyze NON-CONTAINER-CDB XML File
====================================

[oracle@dbtrmrk ~]$ cat /tmp/dbtrmrk.xml
<?xml version="1.0" encoding="UTF-8"?>
<PDB>
  <pdbname>dbtrmrk</pdbname>
  <cid>0</cid>
  <byteorder>1</byteorder>
  <vsn>186646528</vsn>
  <dbid>863520319</dbid>
  <cdbid>863520319</cdbid>
  <guid>F7F3F5DD6A1E30D5E043C9A8A8C0C264</guid>
  <uscnbas>3396841</uscnbas>
  <uscnwrp>0</uscnwrp>
  <rdba>4303816</rdba>
  <tablespace>
    <name>SYSTEM</name>
    <type>0</type>
    <tsn>0</tsn>
    <status>1</status>
    <issft>0</issft>
    <file>
      <path>/u01/app/oracle/dbdata/dbtrmrk/system01.dbf</path>
      <afn>1</afn>
      <rfn>1</rfn>
      <createscnbas>7</createscnbas>
      <createscnwrp>0</createscnwrp>
      <status>1</status>
      <fileblocks>117760</fileblocks>
      <blocksize>8192</blocksize>
      <vsn>186646528</vsn>
      <fdbid>863520319</fdbid>
      <fcpsw>0</fcpsw>
      <fcpsb>3396840</fcpsb>
      <frlsw>0</frlsw>
      <frlsb>995548</frlsb>
      <frlt>826799617</frlt>
    </file>
  </tablespace>
  <tablespace>
    <name>SYSAUX</name>
    <type>0</type>
    <tsn>1</tsn>
    <status>1</status>
    <issft>0</issft>
    <file>
      <path>/u01/app/oracle/dbdata/dbtrmrk/sysaux01.dbf</path>
      <afn>2</afn>
      <rfn>2</rfn>
      <createscnbas>2188</createscnbas>
      <createscnwrp>0</createscnwrp>
      <status>1</status>
      <fileblocks>119040</fileblocks>
      <blocksize>8192</blocksize>
      <vsn>186646528</vsn>
      <fdbid>863520319</fdbid>
      <fcpsw>0</fcpsw>
      <fcpsb>3396840</fcpsb>
      <frlsw>0</frlsw>
      <frlsb>995548</frlsb>
      <frlt>826799617</frlt>
    </file>
  </tablespace>
  <tablespace>
    <name>TEMP</name>
    <type>1</type>
    <tsn>3</tsn>
    <status>1</status>
    <issft>0</issft>
    <bmunitsize>128</bmunitsize>
    <file>
      <path>/u01/app/oracle/dbdata/dbtrmrk/dbtrmrk/temp01.dbf</path>
      <afn>1</afn>
      <rfn>1</rfn>
      <createscnbas>995619</createscnbas>
      <createscnwrp>0</createscnwrp>
      <status>1</status>
      <fileblocks>2560</fileblocks>
      <blocksize>8192</blocksize>
      <vsn>186646528</vsn>
      <autoext>1</autoext>
      <maxsize>4194302</maxsize>
      <incsize>80</incsize>
    </file>
  </tablespace>
  <tablespace>
    <name>USERS</name>
    <type>0</type>
    <tsn>4</tsn>
    <status>1</status>
    <issft>0</issft>
    <file>
      <path>/u01/app/oracle/dbdata/dbtrmrk/users01.dbf</path>
      <afn>4</afn>
      <rfn>4</rfn>
      <createscnbas>18919</createscnbas>
      <createscnwrp>0</createscnwrp>
      <status>1</status>
      <fileblocks>640</fileblocks>
      <blocksize>8192</blocksize>
      <vsn>186646528</vsn>
      <fdbid>863520319</fdbid>
      <fcpsw>0</fcpsw>
      <fcpsb>3396840</fcpsb>
      <frlsw>0</frlsw>
      <frlsb>995548</frlsb>
      <frlt>826799617</frlt>
    </file>
  </tablespace>
  <optional>
    <csid>178</csid>
    <ncsid>2000</ncsid>
    <options>
      <option>APS=12.1.0.1.0</option>
      <option>CATALOG=12.1.0.1.0</option>
      <option>CATJAVA=12.1.0.1.0</option>
      <option>CATPROC=12.1.0.1.0</option>
      <option>CONTEXT=12.1.0.1.0</option>
      <option>JAVAVM=12.1.0.1.0</option>
      <option>ORDIM=12.1.0.1.0</option>
      <option>OWM=12.1.0.1.0</option>
      <option>SDO=12.1.0.1.0</option>
      <option>XDB=12.1.0.1.0</option>
      <option>XML=12.1.0.1.0</option>
      <option>XOQ=12.1.0.1.0</option>
    </options>
    <dv>0</dv>
    <ncdb2pdb>1</ncdb2pdb>
    <APEX>4.2.0.00.27:0</APEX>
    <parameters>
      <parameter>processes=300</parameter>
      <parameter>sessions=472</parameter>
      <parameter>sga_target=805306368</parameter>
      <parameter>db_block_size=8192</parameter>
      <parameter>compatible=11.2.0.0.0</parameter>
      <parameter>open_cursors=300</parameter>
      <parameter>pga_aggregate_target=268435456</parameter>
    </parameters>
    <tzvers>
      <tzver>primary version:18</tzver>
      <tzver>secondary version:0</tzver>
    </tzvers>
    <walletkey>0</walletkey>
  </optional>
</PDB>



====================================
LOGIN TO CONTAINER DATABASE
====================================
SQL> select name,cdb from v$database;

NAME      CDB
--------- ---
CDBDEMO   YES


SQL> select con_id,name from v$pdbs;

    CON_ID NAME
---------- ------------------------------
         2 PDB$SEED
         3 PDB1



====================================
Choose Destination PDB Location
====================================
SQL> select name from v$datafile;

NAME
----------------------------------------
/u03/oradata/cdbdemo/system01.dbf
/u03/oradata/cdbdemo/sysaux01.dbf
/u03/oradata/cdbdemo/undotbs01.dbf
/u03/oradata/cdbdemo/pdbseed/system01.dbf
/u03/oradata/cdbdemo/users01.dbf
/u03/oradata/cdbdemo/pdbseed/sysaux01.dbf
/u03/oradata/cdbdemo/pdb1/system01.dbf
/u03/oradata/cdbdemo/pdb1/sysaux01.dbf
/u03/oradata/cdbdemo/pdb1/pdb1_users01.dbf



====================================
Create PDB  
====================================
Note: In order to save time, I have already copied over the files so using NOCOPY option.

SQL>CREATE PLUGGABLE DATABASE PDBTRMRK USING '/tmp/dbtrmrk.xml'
SOURCE_FILE_NAME_CONVERT = ('/u01/app/oracle/dbdata/dbtrmrk/','/u03/oradata/cdbdemo/pdbtrmrk/')
NOCOPY TEMPFILE REUSE;

Pluggable database created.



SQL>  select name,open_mode from v$pdbs;

NAME                           OPEN_MODE
------------------------------ ----------
PDB$SEED                       READ ONLY
PDB1                           MOUNTED
PDBTRMRK                       MOUNTED




====================================
Clean-Up New PDB 
====================================
SQL> ALTER SESSION SET CONTAINER=PDBTRMRK;

Session altered.

SQL> @$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql
SQL> SET SERVEROUTPUT ON
SQL> SET FEEDBACK 1
SQL> SET NUMWIDTH 10
SQL> SET LINESIZE 80
SQL> SET TRIMSPOOL ON
SQL> SET TAB OFF
SQL> SET PAGESIZE 100
SQL>
SQL> WHENEVER SQLERROR EXIT;
SQL>
SQL> DOC
DOC>#######################################################################
DOC>#######################################################################
DOC>   The following statement will cause an "ORA-01403: no data found"
DOC>   error if we're not in a PDB.
DOC>   This script is intended to be run right after plugin of a PDB,
DOC>   while inside the PDB.
DOC>#######################################################################
DOC>#######################################################################
DOC>#
SQL>
.
..
...

SQL> alter system flush shared_pool;

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> alter session set container = "&pdbname";

Session altered.

SQL>
SQL> -- leave the PDB in the same state it was when we started
SQL> BEGIN
  2    execute immediate '&open_sql &restricted_state';
  3  EXCEPTION
  4    WHEN OTHERS THEN
  5    BEGIN
  6      IF (sqlcode <> -900) THEN
  7        RAISE;
  8      END IF;
  9    END;
 10  END;
 11  /

PL/SQL procedure successfully completed.

SQL>
SQL> WHENEVER SQLERROR CONTINUE;




====================================
OPEN PDB 
====================================
SQL> ALTER PLUGGABLE DATABASE "PDBTRMRK" OPEN;

Pluggable database altered.


SQL> select name,open_mode from v$pdbs;

NAME                           OPEN_MODE
------------------------------ ----------
PDB$SEED                       READ ONLY
PDB1                           MOUNTED
PDBTRMRK                       READ WRITE


Enjoy.....Abid Malik