Search My Oracle Blog

Custom Search

30 January, 2017

12cR1 RAC Posts -- 5 : Relocating OCR and VoteDisk

Most default installation guides for Grid Infrastructure will have you creating a DATA Disk Group during the install.  This results in the OCR, the Vote Disk and MGMTDB being created in the same DATA Disk Group.

The proper installation is to have OCR and Vote Disk separated from DATA.  If you create a different Disk Group during the installation, you would have all three components (MGMTDB is new in 12c, did not exist in 11g) and you can have these components properly placed in a non-DATA Disk Group that you create as the default.

But what if they have already been located in DATA ?  Can you relocate them to another Disk Group ?



I start with creating a new Disk Group called OCRVOTE on an existing unused ASM Disk.

[root@collabn1 ~]# su - oracle
[oracle@collabn1 ~]$ . oraenv
ORACLE_SID = [oracle] ? +ASM1
The Oracle base has been set to /u01/app/oracle
[oracle@collabn1 ~]$ sqlplus '/ as sysasm'

SQL*Plus: Release 12.1.0.2.0 Production on Mon Jan 30 22:34:38 2017

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Real Application Clusters and Automatic Storage Management options

SQL>
SQL> set pages600
SQL> col name format a12
SQL> col path format a16
SQL> select name, path, state, group_number, disk_number
  2  from v$asm_disk
  3  order by 4,5,1
  4  /

NAME         PATH             STATE    GROUP_NUMBER DISK_NUMBER
------------ ---------------- -------- ------------ -----------
             /dev/asm-disk4   NORMAL              0           0
DATA_0000    /dev/asm-disk1   NORMAL              1           0
DATA_0001    /dev/asm-disk2   NORMAL              1           1
DATA_0002    /dev/asm-disk5   NORMAL              1           2
FRA_0000     /dev/asm-disk3   NORMAL              2           0

SQL>
SQL> create diskgroup OCRVOTE external redundancy disk '/dev/asm-disk4';

Diskgroup created.

SQL>
SQL> select name, path, state, group_number, disk_number
  2  from v$asm_disk
  3  order by 4,5,1
  4  /

NAME         PATH             STATE    GROUP_NUMBER DISK_NUMBER
------------ ---------------- -------- ------------ -----------
DATA_0000    /dev/asm-disk1   NORMAL              1           0
DATA_0001    /dev/asm-disk2   NORMAL              1           1
DATA_0002    /dev/asm-disk5   NORMAL              1           2
FRA_0000     /dev/asm-disk3   NORMAL              2           0
OCRVOTE_0000 /dev/asm-disk4   NORMAL              3           0

SQL>
SQL> alter diskgroup OCRVOTE set attribute 'COMPATIBLE.ASM' = '12.1';

Diskgroup altered.

SQL>


I have identified the ASM Disk on /dev/asm-disk4 as available and created a DiskGroup on it.  I have to mount the DiskGroup on the second node as well.  (If I don't mount the DiskGroup on the second node collabn2, I get errors
"PROT-30: The Oracle Cluster Registry location to be added is not usable
PROC-50: The Oracle Cluster Registry location to be added is inaccessible on nodes collabn2."
when attempting to add the OCR on the DiskGroup).

[oracle@collabn2 trace]$ sqlplus

SQL*Plus: Release 12.1.0.2.0 Production on Mon Jan 30 22:52:18 2017

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Enter user-name: / as sysasm

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Real Application Clusters and Automatic Storage Management options

SQL> alter diskgroup OCRVOTE mount;

Diskgroup altered.

SQL> set pages60
SQL> col name format a12
SQL> col path format a16
SQL> select name, path, state, group_number, disk_number
  2  from v$asm_disk
  3  order by 4,5,1
  4  /

NAME         PATH             STATE    GROUP_NUMBER DISK_NUMBER
------------ ---------------- -------- ------------ -----------
DATA_0000    /dev/asm-disk1   NORMAL              1           0
DATA_0001    /dev/asm-disk2   NORMAL              1           1
DATA_0002    /dev/asm-disk5   NORMAL              1           2
FRA_0000     /dev/asm-disk3   NORMAL              2           0
OCRVOTE_0000 /dev/asm-disk4   NORMAL              3           0

SQL>


Next, I plan to relocate the OCR which is currently in the DATA DiskGroup.

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Real Application Clusters and Automatic Storage Management options
[oracle@collabn1 ~]$ ocrcheck
Status of Oracle Cluster Registry is as follows :
         Version                  :          4
         Total space (kbytes)     :     409568
         Used space (kbytes)      :       1688
         Available space (kbytes) :     407880
         ID                       :  827167720
         Device/File Name         :      +DATA
                                    Device/File integrity check succeeded

                                    Device/File not configured

                                    Device/File not configured

                                    Device/File not configured

                                    Device/File not configured

         Cluster registry integrity check succeeded

         Logical corruption check bypassed due to non-privileged user

[oracle@collabn1 ~]$
[oracle@collabn1 ~]$ su
Password:
[root@collabn1 oracle]# ocrconfig -add +OCRVOTE
[root@collabn1 oracle]# ocrcheck
Status of Oracle Cluster Registry is as follows :
         Version                  :          4
         Total space (kbytes)     :     409568
         Used space (kbytes)      :       1684
         Available space (kbytes) :     407884
         ID                       :  827167720
         Device/File Name         :      +DATA
                                    Device/File integrity check succeeded
         Device/File Name         :   +OCRVOTE
                                    Device/File integrity check succeeded

                                    Device/File not configured

                                    Device/File not configured

                                    Device/File not configured

         Cluster registry integrity check succeeded

         Logical corruption check succeeded

[root@collabn1 oracle]# ocrconfig -delete +DATA
[root@collabn1 oracle]# ocrcheck
Status of Oracle Cluster Registry is as follows :
         Version                  :          4
         Total space (kbytes)     :     409568
         Used space (kbytes)      :       1684
         Available space (kbytes) :     407884
         ID                       :  827167720
         Device/File Name         :   +OCRVOTE
                                    Device/File integrity check succeeded

                                    Device/File not configured

                                    Device/File not configured

                                    Device/File not configured

                                    Device/File not configured

         Cluster registry integrity check succeeded

         Logical corruption check succeeded

[root@collabn1 oracle]#


I first used OCRCHECK ("ocrcheck" at the commandline) to list the existing OCR location.  I then used root and OCRCONFIG -ADD to add +OCRVOTE as a location and verified it with OCRCHECK.  I then used OCRCONFIG -DELETE ("ocrconfig -add" and "ocrconfig -delete") to delete the old location.

I could have used "ocrconfig -replace" to replace the OCR location but I prefer ADD and DELETE.



How do I relocate the VoteDisk ?  Since I am using a single DiskGroup and External Redundancy with only 1 Failure Group, I have only 1 VoteDisk.

[root@collabn1 oracle]# crsctl query css votedisk
##  STATE    File Universal Id                File Name Disk group
--  -----    -----------------                --------- ---------
 1. ONLINE   96fbcb40bfeb4ff7bf18881adcfef149 (/dev/asm-disk1) [DATA]
Located 1 voting disk(s).
[root@collabn1 oracle]#
[root@collabn1 oracle]# crsctl replace votedisk +OCRVOTE
Successful addition of voting disk a58b8b9d58064fb8bf6df0b3ee701e32.
Successful deletion of voting disk 96fbcb40bfeb4ff7bf18881adcfef149.
Successfully replaced voting disk group with +OCRVOTE.
CRS-4266: Voting file(s) successfully replaced
[root@collabn1 oracle]#
[root@collabn1 oracle]# crsctl query css votedisk
##  STATE    File Universal Id                File Name Disk group
--  -----    -----------------                --------- ---------
 1. ONLINE   a58b8b9d58064fb8bf6df0b3ee701e32 (/dev/asm-disk4) [OCRVOTE]
Located 1 voting disk(s).
[root@collabn1 oracle]#


With ASM as  the location for VoteDisk, I cannot use CRSCTL ADD VOTEDISK  but have to use CRSCTL REPLACE VOTEDISK.

So, I have added a new DiskGroup called OCRVOTE and relocated both OCR and VoteDisk to this new DiskGroup (on disk /dev/asm-disk4).

Can I relocate +MGMTDB ?  I am open to suggestions.

.
.
.

22 January, 2017

12cR1 RAC Posts -- 4 : Adding a Disk of a different size

How does 12.1.0.2 ASM handle adding a disk of a different size to an existing DiskGroup ?

I currently have 4 disks of 5GB each in 2 DiskGroups

[oracle@collabn1 ~]$ sqlplus / as sysasm

SQL*Plus: Release 12.1.0.2.0 Production on Sat Jan 21 23:48:00 2017

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Real Application Clusters and Automatic Storage Management options

SQL> select group_number, disk_number, name, state, total_mb
  2  from v$asm_disk
  3  order by 1,2,3
  4  /

GROUP_NUMBER DISK_NUMBER NAME                           STATE      TOTAL_MB
------------ ----------- ------------------------------ -------- ----------
           0           0                                NORMAL            0
           1           0 DATA_0000                      NORMAL         5114
           1           1 DATA_0001                      NORMAL         5114
           2           0 FRA_0000                       NORMAL         5114

SQL>
SQL> select group_number, name
  2  from v$asm_diskgroup
  3  order by 1
  4  /

GROUP_NUMBER NAME
------------ ------------------------------
           1 DATA
           2 FRA

SQL>


The DATA DiskGroup has 2 disks of 5GB each and the FRA DiskGroup has 1 disk of 5GB.  One disk (identified as DiskNumber=0) is not yet assigned.

What happens if I try to expand the DATA DiskGroup with a Disk of 10GB ?

[root@collabn1 dev]# 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 0xff8b0ab7.
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): m
Command action
   a   toggle a bootable flag
   b   edit bsd disklabel
   c   toggle the dos compatibility flag
   d   delete a partition
   l   list known partition types
   m   print this menu
   n   add a new partition
   o   create a new empty DOS partition table
   p   print the partition table
   q   quit without saving changes
   s   create a new empty Sun disklabel
   t   change a partition's system id
   u   change display/entry units
   v   verify the partition table
   w   write table to disk and exit
   x   extra functionality (experts only)

Command (m for help): p

Disk /dev/sdf: 12.9 GB, 12884901888 bytes
255 heads, 63 sectors/track, 1566 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0xff8b0ab7

   Device Boot      Start         End      Blocks   Id  System

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

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

Calling ioctl() to re-read partition table.
Syncing disks.
[root@collabn1 dev]#
[root@collabn1 dev]# /sbin/scsi_id -g -u -d /dev/sdf
1ATA_VBOX_HARDDISK_VB535deca9-9a295efe
[root@collabn1 dev]#
[root@collabn1 dev]# cd /etc/udev/rules.d
[root@collabn1 rules.d]# vi 99-oracle-asmdevices.rules
[root@collabn1 rules.d]# tail -1 99-oracle-asmdevices.rules
KERNEL=="sd?1", BUS=="scsi", PROGRAM=="/sbin/scsi_id -g -u -d /dev/$parent",  RESULT=="1ATA_VBOX_HARDDISK_VB535deca9-9a295efe", NAME="asm-disk5", OWNER="oracle", GROUP="dba", MODE="0660"
[root@collabn1 rules.d]#
[root@collabn1 rules.d]# /sbin/partprobe /dev/sdf1
[root@collabn1 rules.d]# /sbin/udevadm test /block/sdb/sdf1
run_command: calling: test
udevadm_test: version 147
This program is for debugging only, it does not run any program,
specified by a RUN key. It may show incorrect results, because
some values may be different, or not available at a simulation run.

parse_file: reading '/lib/udev/rules.d/10-console.rules' as rules file
parse_file: reading '/lib/udev/rules.d/10-dm.rules' as rules file
parse_file: reading '/lib/udev/rules.d/11-dm-lvm.rules' as rules file
parse_file: reading '/lib/udev/rules.d/13-dm-disk.rules' as rules file
parse_file: reading '/lib/udev/rules.d/40-isdn.rules' as rules file
parse_file: reading '/lib/udev/rules.d/40-redhat.rules' as rules file
parse_file: reading '/lib/udev/rules.d/42-qemu-usb.rules' as rules file
parse_file: reading '/lib/udev/rules.d/50-firmware.rules' as rules file
parse_file: reading '/lib/udev/rules.d/50-udev-default.rules' as rules file
parse_file: reading '/etc/udev/rules.d/55-usm.rules' as rules file
parse_file: reading '/lib/udev/rules.d/60-alias-kmsg.rules' as rules file
parse_file: reading '/lib/udev/rules.d/60-cdrom_id.rules' as rules file
parse_file: reading '/etc/udev/rules.d/60-fprint-autosuspend.rules' as rules file
parse_file: reading '/lib/udev/rules.d/60-net.rules' as rules file
parse_file: reading '/etc/udev/rules.d/60-pcmcia.rules' as rules file
parse_file: reading '/lib/udev/rules.d/60-persistent-alsa.rules' as rules file
parse_file: reading '/lib/udev/rules.d/60-persistent-input.rules' as rules file
parse_file: reading '/lib/udev/rules.d/60-persistent-serial.rules' as rules file
parse_file: reading '/lib/udev/rules.d/60-persistent-storage-tape.rules' as rules file
parse_file: reading '/lib/udev/rules.d/60-persistent-storage.rules' as rules file
parse_file: reading '/lib/udev/rules.d/60-persistent-v4l.rules' as rules file
parse_file: reading '/etc/udev/rules.d/60-raw.rules' as rules file
parse_file: reading '/etc/udev/rules.d/60-vboxadd.rules' as rules file
parse_file: reading '/lib/udev/rules.d/61-mobile-action.rules' as rules file
parse_file: reading '/lib/udev/rules.d/61-option-modem-modeswitch.rules' as rules file
parse_file: reading '/lib/udev/rules.d/61-persistent-storage-edd.rules' as rules file
parse_file: reading '/lib/udev/rules.d/64-device-mapper.rules' as rules file
parse_file: reading '/lib/udev/rules.d/64-md-raid.rules' as rules file
parse_file: reading '/lib/udev/rules.d/65-md-incremental.rules' as rules file
parse_file: reading '/lib/udev/rules.d/69-dm-lvm-metad.rules' as rules file
parse_file: reading '/lib/udev/rules.d/70-acl.rules' as rules file
parse_file: reading '/lib/udev/rules.d/70-cups-libusb.rules' as rules file
parse_file: reading '/lib/udev/rules.d/70-hid2hci.rules' as rules file
parse_file: reading '/etc/udev/rules.d/70-persistent-cd.rules' as rules file
parse_file: reading '/etc/udev/rules.d/70-persistent-net.rules' as rules file
parse_file: reading '/lib/udev/rules.d/71-biosdevname.rules' as rules file
parse_file: reading '/lib/udev/rules.d/75-cd-aliases-generator.rules' as rules file
parse_file: reading '/lib/udev/rules.d/75-net-description.rules' as rules file
parse_file: reading '/lib/udev/rules.d/75-persistent-net-generator.rules' as rules file
parse_file: reading '/lib/udev/rules.d/75-tty-description.rules' as rules file
parse_file: reading '/lib/udev/rules.d/78-sound-card.rules' as rules file
parse_file: reading '/lib/udev/rules.d/79-fstab_import.rules' as rules file
parse_file: reading '/lib/udev/rules.d/80-drivers.rules' as rules file
parse_file: reading '/lib/udev/rules.d/80-iosched.rules' as rules file
parse_file: reading '/lib/udev/rules.d/80-mpath-iosched.rules' as rules file
parse_file: reading '/lib/udev/rules.d/85-regulatory.rules' as rules file
parse_file: reading '/lib/udev/rules.d/88-clock.rules' as rules file
parse_file: reading '/lib/udev/rules.d/89-microcode.rules' as rules file
parse_file: reading '/etc/udev/rules.d/90-alsa.rules' as rules file
parse_file: reading '/lib/udev/rules.d/90-btrfs.rules' as rules file
parse_file: reading '/etc/udev/rules.d/90-hal.rules' as rules file
parse_file: reading '/lib/udev/rules.d/91-drm-modeset.rules' as rules file
parse_file: reading '/lib/udev/rules.d/95-dm-notify.rules' as rules file
parse_file: reading '/lib/udev/rules.d/95-keyboard-force-release.rules' as rules file
parse_file: reading '/lib/udev/rules.d/95-keymap.rules' as rules file
parse_file: reading '/lib/udev/rules.d/95-udev-late.rules' as rules file
parse_file: reading '/etc/udev/rules.d/98-kexec.rules' as rules file
parse_file: reading '/etc/udev/rules.d/99-oracle-asmdevices.rules' as rules file
parse_file: reading '/dev/.udev/rules.d/99-root.rules' as rules file
udev_rules_new: rules use 32448 bytes tokens (2704 * 12 bytes), 19085 bytes buffer
udev_rules_new: temporary index used 19500 bytes (975 * 20 bytes)
unable to open device '/sys/block/sdb/sdf1'
[root@collabn1 rules.d]#
[root@collabn1 rules.d]# /sbin/udevadm control --reload-rules
[root@collabn1 rules.d]# /sbin/start_udev
Starting udev:                                             [  OK  ]
[root@collabn1 rules.d]# 
[root@collabn1 rules.d]# ls -l /dev/asm*
brw-rw----. 1 oracle dba 8, 17 Jan 22 00:07 /dev/asm-disk1
brw-rw----. 1 oracle dba 8, 33 Jan 22 00:07 /dev/asm-disk2
brw-rw----. 1 oracle dba 8, 49 Jan 22 00:07 /dev/asm-disk3
brw-rw----. 1 oracle dba 8, 65 Jan 22 00:05 /dev/asm-disk4
brw-rw----. 1 oracle dba 8, 81 Jan 22 00:05 /dev/asm-disk5


So I now have asm-disk5 as the new ASM Disk.  Let my try to add this disk.

SQL> set pages600
SQL> select group_number, disk_number, name, path, total_mb
  2  from v$asm_disk
  3  order by 1,2
  4  /

GROUP_NUMBER DISK_NUMBER NAME
------------ ----------- ------------------------------
PATH
--------------------------------------------------------------------------------
  TOTAL_MB
----------
           0           0
/dev/asm-disk5
         0

           0           1
/dev/asm-disk4
         0

           1           0 DATA_0000
/dev/asm-disk1
      5114

           1           1 DATA_0001
/dev/asm-disk2
      5114

           2           0 FRA_0000
/dev/asm-disk3
      5114


SQL>
SQL> alter diskgroup data add disk '/dev/asm-disk5';

Diskgroup altered.

SQL>
SQL> select group_number, name, total_mb
  2  from v$asm_diskgroup
  3  order by 1,2
  4  /

GROUP_NUMBER NAME                             TOTAL_MB
------------ ------------------------------ ----------
           1 DATA                                22512
           2 FRA                                  5114

SQL>
SQL> select group_number, name, type
  2  from v$asm_diskgroup
  3  order by 1,2
  4  /

GROUP_NUMBER NAME                           TYPE
------------ ------------------------------ ------
           1 DATA                           EXTERN
           2 FRA                            EXTERN

SQL>
SQL> select group_number, name, compatibility, database_compatibility
  2  from v$asm_diskgroup
  3  order by 1
  4  /

GROUP_NUMBER NAME
------------ ------------------------------
COMPATIBILITY
------------------------------------------------------------
DATABASE_COMPATIBILITY
------------------------------------------------------------
           1 DATA
12.1.0.0.0
10.1.0.0.0

           2 FRA
12.1.0.0.0
10.1.0.0.0


SQL>
SQL> select group_number, disk_number, name, path, total_mb
  2  from v$asm_disk
  3  order by 1,2,3
  4  /

GROUP_NUMBER DISK_NUMBER NAME
------------ ----------- ------------------------------
PATH
--------------------------------------------------------------------------------
  TOTAL_MB
----------
           0           0
/dev/asm-disk4
         0

           1           0 DATA_0000
/dev/asm-disk1
      5114

           1           1 DATA_0001
/dev/asm-disk2
      5114

           1           2 DATA_0002
/dev/asm-disk5
     12284

           2           0 FRA_0000
/dev/asm-disk3
      5114


SQL>


According to Oracle Support Document 1938950.1, adding a disk of a different size to an existing DiskGroup fails with an error ORA-15410 in 12.1.0.2.  However, that seems to apply to NORMAL or HIGH Redundancy and COMPATIBLE.ASM 12.1.0.2.   Here, I have EXTERNAL Redundancy and COMPATIBLE.ASM 12.1.0.0.0

Do I recommend Disks of different sizes ?  Absolutely *not* in Production.  This is a "play" environment in Virtual Machines on my desktop that I can destroy and recreate anytime.  I can monitor disk usage as well.
.
.
.

16 January, 2017

12cR1 RAC Posts -- 3 : Convert PolicyManaged DB back to AdminManaged

In the previous post on 12cR1 RAC, I had converted my AdminManaged Database to PolicyManaged.

Here, I convert it back to AdminManaged.

First, I verify that the database is shutdown (note that I have only 1 node of the cluster currently up and running, I don't need both nodes and instances up).

[oracle@collabn1 ~]$ srvctl status database -d RAC
Instance RAC_1 is running on node collabn1
[oracle@collabn1 ~]$ srvctl stop database -d RAC
[oracle@collabn1 ~]$ ps -fuoracle |grep smon
oracle    3422     1  0 09:49 ?        00:00:00 asm_smon_+ASM1
oracle    4882     1  0 09:50 ?        00:00:00 mdb_smon_-MGMTDB
oracle   16889  9821  0 10:08 pts/0    00:00:00 grep smon
[oracle@collabn1 ~]$


Next, I remove the database from the Cluster Registry.

[oracle@collabn1 ~]$ srvctl config database -d RAC
Database unique name: RAC
Database name:
Oracle home: /u01/app/oracle/product/12.1.0/dbhome_1
Oracle user: oracle
Spfile:
Password file:
Domain: racattack
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: MyPool
Disk Groups: DATA,FRA
Mount point paths:
Services:
Type: RAC
Start concurrency:
Stop concurrency:
OSDBA group: dba
OSOPER group:
Database instances:
Configured nodes:
Database is policy managed
[oracle@collabn1 ~]$ srvctl remove database -d RAC
Remove the database RAC? (y/[n]) y
[oracle@collabn1 ~]$ srvctl config database -d RAC
PRCD-1120 : The resource for database RAC could not be found.
PRCR-1001 : Resource ora.rac.db does not exist
[oracle@collabn1 ~]$


I then remove the defined Server Pool that I used for this database.

[oracle@collabn1 ~]$ srvctl config srvpool
Server pool name: Free
Importance: 0, Min: 0, Max: -1
Category:
Candidate server names:
Server pool name: Generic
Importance: 0, Min: 0, Max: -1
Category:
Candidate server names:
Server pool name: MyPool
Importance: 100, Min: 1, Max: 2
Category:
Candidate server names: collabn1,collabn2
[oracle@collabn1 ~]$ srvctl remove srvpool -serverpool MyPool
[oracle@collabn1 ~]$ srvctl config srvpool
Server pool name: Free
Importance: 0, Min: 0, Max: -1
Category:
Candidate server names:
Server pool name: Generic
Importance: 0, Min: 0, Max: -1
Category:
Candidate server names:
[oracle@collabn1 ~]$


I then add the database back into the Cluster Registry.

[oracle@collabn1 ~]$ srvctl add database -d RAC \
> -oraclehome /u01/app/oracle/product/12.1.0/dbhome_1 \
> -pwfile +DATA/RAC/PASSWORD/pwdrac.277.931824933
[oracle@collabn1 ~]$ srvctl config database -d RAC
Database unique name: RAC
Database name:
Oracle home: /u01/app/oracle/product/12.1.0/dbhome_1
Oracle user: oracle
Spfile:
Password file: +DATA/RAC/PASSWORD/pwdrac.277.931824933
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools:
Disk Groups:
Mount point paths:
Services:
Type: RAC
Start concurrency:
Stop concurrency:
OSDBA group: dba
OSOPER group:
Database instances:
Configured nodes:
Database is administrator managed
[oracle@collabn1 ~]$


I start the second node of the cluster before I configure the instances (Note : I have the $ORACLE_HOME/dbs pfiles created in advance).

[oracle@collabn1 ~]$ srvctl add instance -d RAC -i RAC1 -n collabn1
[oracle@collabn1 ~]$ srvctl add instance -d RAC -i RAC2 -n collabn2
[oracle@collabn1 ~]$ srvctl config database -d RAC
Database unique name: RAC
Database name:
Oracle home: /u01/app/oracle/product/12.1.0/dbhome_1
Oracle user: oracle
Spfile:
Password file: +DATA/RAC/PASSWORD/pwdrac.277.931824933
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools:
Disk Groups:
Mount point paths:
Services:
Type: RAC
Start concurrency:
Stop concurrency:
OSDBA group: dba
OSOPER group:
Database instances: RAC1,RAC2
Configured nodes: collabn1,collabn2
Database is administrator managed
[oracle@collabn1 ~]$


I am now ready to start the database (instances).

[oracle@collabn1 ~]$ srvctl start database -d RAC
[oracle@collabn1 ~]$ srvctl status database -d RAC
Instance RAC1 is running on node collabn1
Instance RAC2 is running on node collabn2
[oracle@collabn1 ~]$ ps -fuoracle |grep smon
oracle    3422     1  0 09:49 ?        00:00:00 asm_smon_+ASM1
oracle    4882     1  0 09:50 ?        00:00:00 mdb_smon_-MGMTDB
oracle   25431     1  0 10:30 ?        00:00:00 ora_smon_RAC1
oracle   27533  9821  0 10:33 pts/0    00:00:00 grep smon
[oracle@collabn1 ~]$
[root@collabn2 ~]# ps -fuoracle |grep smon
oracle    3460     1  0 10:19 ?        00:00:00 asm_smon_+ASM2
oracle    9561     1  0 10:30 ?        00:00:00 ora_smon_RAC2
[root@collabn2 ~]#
[oracle@collabn1 ~]$ env |grep SID
ORACLE_SID=RAC1
[oracle@collabn1 ~]$ sqlplus '/ as sysdba'

SQL*Plus: Release 12.1.0.2.0 Production on Mon Jan 16 10:34:15 2017

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

SQL> select * from v$containers;

    CON_ID       DBID    CON_UID GUID
---------- ---------- ---------- --------------------------------
NAME                           OPEN_MODE  RES
------------------------------ ---------- ---
OPEN_TIME
---------------------------------------------------------------------------
CREATE_SCN TOTAL_SIZE BLOCK_SIZE RECOVERY SNAPSHOT_PARENT_CON_ID
---------- ---------- ---------- -------- ----------------------
         1 2519807290          1 FD9AC20F64D344D7E043B6A9E80A2F2F
CDB$ROOT                       READ WRITE NO
16-JAN-17 10.31.34.014 AM +08:00
         0          0       8192 ENABLED                       0


    CON_ID       DBID    CON_UID GUID
---------- ---------- ---------- --------------------------------
NAME                           OPEN_MODE  RES
------------------------------ ---------- ---
OPEN_TIME
---------------------------------------------------------------------------
CREATE_SCN TOTAL_SIZE BLOCK_SIZE RECOVERY SNAPSHOT_PARENT_CON_ID
---------- ---------- ---------- -------- ----------------------
         2 2061548092 2061548092 44BB5E17F41A2618E053334EA8C006B9
PDB$SEED                       READ ONLY  NO
16-JAN-17 10.31.34.859 AM +08:00
   1594413  859832320       8192 ENABLED                       0


    CON_ID       DBID    CON_UID GUID
---------- ---------- ---------- --------------------------------
NAME                           OPEN_MODE  RES
------------------------------ ---------- ---
OPEN_TIME
---------------------------------------------------------------------------
CREATE_SCN TOTAL_SIZE BLOCK_SIZE RECOVERY SNAPSHOT_PARENT_CON_ID
---------- ---------- ---------- -------- ----------------------
         3 1857084550 1857084550 44BBC69CE8F552AEE053334EA8C07365
PDB                            MOUNTED

   1755977          0       8192 ENABLED                       0


SQL> alter pluggable database PDB open;

Pluggable database altered.

SQL>
SQL> select con_id, name, open_mode from v$pdbs;

    CON_ID NAME                           OPEN_MODE
---------- ------------------------------ ----------
         2 PDB$SEED                       READ ONLY
         3 PDB                            READ WRITE

SQL>


Thus, I converted my PolicyManaged database to AdministratorManaged.
.
.
.

15 January, 2017

Copying a Tablespace from NonCDB to a PDB (using TTS)

A Tablespace can be "transported"  from a NonCDB to a PDB as a way of copying the Tablespace.  Here I work with ASM as well.

First in the NonCDB :

[oracle@ora12102 Desktop]$ . oraenv
ORACLE_SID = [oracle] ? NONCDB
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@ora12102 Desktop]$ sqlplus '/ as sysdba'

SQL*Plus: Release 12.1.0.2.0 Production on Sun Jan 15 16:03:43 2017

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area  402653184 bytes
Fixed Size      2924928 bytes
Variable Size    260050560 bytes
Database Buffers   134217728 bytes
Redo Buffers      5459968 bytes
Database mounted.
Database opened.
SQL> select file_name, bytes/1048576                    
  2  from dba_data_files
  3  where tablespace_name = 'EXAMPLE'
  4  /

FILE_NAME
--------------------------------------------------------------------------------
BYTES/1048576
-------------
+DATA/NONCDB/DATAFILE/example.266.896482777
      1243.75


SQL> 
[oracle@ora12102 Desktop]$ expdp hemant/hemant \
> directory=data_pump_dir dumpfile=EXAMPLE_TTS.dmp \
> transport_tablespaces=EXAMPLE transport_full_check=Y

Export: Release 12.1.0.2.0 - Production on Sun Jan 15 16:08:27 2017

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
Starting "HEMANT"."SYS_EXPORT_TRANSPORTABLE_01":  hemant/******** directory=data_pump_dir dumpfile=EXAMPLE_TTS.dmp transport_tablespaces=EXAMPLE transport_full_check=Y 
ORA-39123: Data Pump transportable tablespace job aborted
ORA-39185: The transportable tablespace failure list is

ORA-29335: tablespace 'EXAMPLE' is not read only
Job "HEMANT"."SYS_EXPORT_TRANSPORTABLE_01" stopped due to fatal error at Sun Jan 15 16:08:41 2017 elapsed 0 00:00:06

[oracle@ora12102 Desktop]$ 


The tablespace has to be set READ ONLY before we can use export to transport it (also, it should be READ ONLY while the data files are being copied.

[oracle@ora12102 Desktop]$ sqlplus '/ as sysdba'

SQL*Plus: Release 12.1.0.2.0 Production on Sun Jan 15 16:09:10 2017

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

SQL> alter tablespace example read only;

Tablespace altered.

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
[oracle@ora12102 Desktop]$ expdp hemant/hemant \
> directory=data_pump_dir dumpfile=EXAMPLE_TTS.dmp \
> transport_tablespaces=EXAMPLE transport_full_check=Y

Export: Release 12.1.0.2.0 - Production on Sun Jan 15 16:09:58 2017

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
Starting "HEMANT"."SYS_EXPORT_TRANSPORTABLE_01":  hemant/******** directory=data_pump_dir dumpfile=EXAMPLE_TTS.dmp transport_tablespaces=EXAMPLE transport_full_check=Y 
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TYPE/TYPE_SPEC
Processing object type TRANSPORTABLE_EXPORT/TYPE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TRANSPORTABLE_EXPORT/TYPE/TYPE_BODY
Processing object type TRANSPORTABLE_EXPORT/PROCACT_INSTANCE
Processing object type TRANSPORTABLE_EXPORT/XMLSCHEMA/XMLSCHEMA
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TRANSPORTABLE_EXPORT/INDEX/INDEX
Processing object type TRANSPORTABLE_EXPORT/INDEX/FUNCTIONAL_INDEX/INDEX
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/INDEX/STATISTICS/FUNCTIONAL_INDEX/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/COMMENT
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/REF_CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/INDEX/BITMAP_INDEX/INDEX
Processing object type TRANSPORTABLE_EXPORT/INDEX/STATISTICS/BITMAP_INDEX/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/TRIGGER
Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/STATISTICS/MARKER
Processing object type TRANSPORTABLE_EXPORT/DOMAIN_INDEX/TABLE
Processing object type TRANSPORTABLE_EXPORT/DOMAIN_INDEX/SECONDARY_TABLE/INDEX
Processing object type TRANSPORTABLE_EXPORT/DOMAIN_INDEX/SECONDARY_TABLE/CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/DOMAIN_INDEX/INDEX
Processing object type TRANSPORTABLE_EXPORT/MATERIALIZED_VIEW
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PROCACT_INSTANCE
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PROCDEPOBJ
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Master table "HEMANT"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for HEMANT.SYS_EXPORT_TRANSPORTABLE_01 is:
  /u01/app/oracle/admin/NONCDB/dpdump/EXAMPLE_TTS.dmp
******************************************************************************
Datafiles required for transportable tablespace EXAMPLE:
  +DATA/NONCDB/DATAFILE/example.266.896482777
Job "HEMANT"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at Sun Jan 15 16:12:48 2017 elapsed 0 00:02:46

[oracle@ora12102 Desktop]$ 


Now, I need to copy the datafile (while the tablespace is READ ONLY).

[oracle@ora12102 Desktop]$ su - grid
Password: 
[grid@ora12102 ~]$ asmcmd
ASMCMD> cp +DATA/NONCDB/DATAFILE/example.266.896482777 /tmp/example.dbf
copying +DATA/NONCDB/DATAFILE/example.266.896482777 -> /tmp/example.dbf
ASMCMD> 
ASMCMD> exit
[grid@ora12102 ~]$ exit
logout
[oracle@ora12102 Desktop]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Sun Jan 15 16:16:24 2017

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

SQL> alter tablespace example read write ;

Tablespace altered.

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
[oracle@ora12102 Desktop]$ 


I must now identify the target location for the datafile in the CDB database.

[oracle@ora12102 Desktop]$ . oraenv
ORACLE_SID = [NONCDB] ? CDB1
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@ora12102 Desktop]$ sqlplus '/ as sysdba'

SQL*Plus: Release 12.1.0.2.0 Production on Sun Jan 15 16:17:44 2017

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 1644167168 bytes
Fixed Size      2925024 bytes
Variable Size    973082144 bytes
Database Buffers   654311424 bytes
Redo Buffers     13848576 bytes
Database mounted.
Database opened.
SQL> alter pluggable database pdb1 open;

Pluggable database altered.

SQL>        
SQL> alter session set container=PDB1;

Session altered.

SQL> select file_name from dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------
+DATA/CDB1/35208E5B92306007E0530F02000A969A/DATAFILE/system.284.914408541
+DATA/CDB1/35208E5B92306007E0530F02000A969A/DATAFILE/sysaux.285.914408541
+DATA/CDB1/35208E5B92306007E0530F02000A969A/DATAFILE/users.287.914408663
+DATA/CDB1/35208E5B92306007E0530F02000A969A/DATAFILE/hemant.288.914713623

SQL> 


Now that I have identiied the default location for all PDB1 files, I need to use ASMCMD to copy the datafile.

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
[oracle@ora12102 Desktop]$ su - grid
Password: 
[grid@ora12102 ~]$ 
[grid@ora12102 ~]$ asmcmd
ASMCMD> cp /tmp/example.dbf +DATA/CDB1/35208E5B92306007E0530F02000A969A/DATAFILE/
copying /tmp/example.dbf -> +DATA/CDB1/35208E5B92306007E0530F02000A969A/DATAFILE/example.dbf
ASMCMD> cd +DATA/CDB1/35208E5B92306007E0530F02000A969A/DATAFILE
ASMCMD> ls
HEMANT.288.914713623
SYSAUX.285.914408541
SYSTEM.284.914408541
USERS.287.914408663
example.dbf
ASMCMD> exit
[grid@ora12102 ~]$ 


Now, I need to import the tablespace with the datafile. Before that, I need to setup the user that will do the import and all the users of the target tablespace.

[grid@ora12102 ~]$ exit
logout
[oracle@ora12102 Desktop]$ 
[oracle@ora12102 Desktop]$ sqlplus system/oracle@PDB1

SQL*Plus: Release 12.1.0.2.0 Production on Sun Jan 15 16:28:16 2017

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

ERROR:
ORA-28002: the password will expire within 7 days


Last Successful login time: Sun Jan 15 2017 16:27:18 +08:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

SQL> grant select_catalog_role, imp_full_database to hemant;

Grant succeeded.

SQL> select privilege from dba_sys_privs where grantee = 'HEMANT';

PRIVILEGE
----------------------------------------
CREATE TABLE
CREATE SESSION

SQL> select granted_role from dba_role_privs where grantee = 'HEMANT';

GRANTED_ROLE
--------------------------------------------------------------------------------
SELECT_CATALOG_ROLE
IMP_FULL_DATABASE

SQL> 


As with the Export, I am using a non-DBA user for the import.  I also have to setup the users and their grants.

[oracle@ora12102 Desktop]$ sqlplus sys/oracle@PDB1 as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Sun Jan 15 16:33:17 2017

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

SQL> create directory imp_from_noncdb as '/u01/app/oracle/admin/NONCDB/dpdump';

Directory created.

SQL> grant read, write on directory imp_from_noncdb to hemant;

Grant succeeded.

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
[oracle@ora12102 Desktop]$ 
[oracle@ora12102 Desktop]$ sqlplus system/oracle@PDB1

SQL*Plus: Release 12.1.0.2.0 Production on Sun Jan 15 16:41:36 2017

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

ERROR:
ORA-28002: the password will expire within 7 days


Last Successful login time: Sun Jan 15 2017 16:28:16 +08:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

SQL> create user HR identified by  HR ;
create user IX identified by  IX ;
create user OE identified by  OE ;
create user PM identified by  PM ;
create user SH identified by  SH ;

User created.

SQL> 
User created.

SQL> 
User created.

SQL> 
User created.

SQL> 
User created.

SQL> SQL> 
SQL> @grants_to_EXAMPLE
SQL> spool grants_to_EXAMPLE
SQL> 
SQL> grant  ALTER SESSION  to  HR ;

Grant succeeded.

SQL> grant  ALTER SESSION  to  IX ;

Grant succeeded.

SQL> grant  ALTER SESSION  to  SH ;

Grant succeeded.

SQL> grant  CREATE CLUSTER  to IX ;

Grant succeeded.

SQL> grant  CREATE CLUSTER  to SH ;

Grant succeeded.

SQL> grant  CREATE DATABASE LINK  to  HR ;

Grant succeeded.

SQL> grant  CREATE DATABASE LINK  to  IX ;

Grant succeeded.

SQL> grant  CREATE DATABASE LINK  to  OE ;

Grant succeeded.

SQL> grant  CREATE DATABASE LINK  to  SH ;

Grant succeeded.

SQL> grant  CREATE DIMENSION  to  SH ;

Grant succeeded.

SQL> grant  CREATE INDEXTYPE  to  IX ;

Grant succeeded.

SQL> grant  CREATE MATERIALIZED VIEW  to  OE ;

Grant succeeded.

SQL> grant  CREATE MATERIALIZED VIEW  to  SH ;

Grant succeeded.

SQL> grant  CREATE OPERATOR to  IX ;

Grant succeeded.

SQL> grant  CREATE PROCEDURE  to  HR ;

Grant succeeded.

SQL> grant  CREATE PROCEDURE  to  IX ;

Grant succeeded.

SQL> grant  CREATE RULE  to IX ;

Grant succeeded.

SQL> grant  CREATE RULE SET to  IX ;

Grant succeeded.

SQL> grant  CREATE SEQUENCE to  HR ;

Grant succeeded.

SQL> grant  CREATE SEQUENCE to  IX ;

Grant succeeded.

SQL> grant  CREATE SEQUENCE to  SH ;

Grant succeeded.

SQL> grant  CREATE SESSION  to HR ;

Grant succeeded.

SQL> grant  CREATE SESSION  to IX ;

Grant succeeded.

SQL> grant  CREATE SESSION  to OE ;

Grant succeeded.

SQL> grant  CREATE SESSION  to SH ;

Grant succeeded.

SQL> grant  CREATE SYNONYM  to HR ;

Grant succeeded.

SQL> grant  CREATE SYNONYM  to IX ;

Grant succeeded.

SQL> grant  CREATE SYNONYM  to OE ;

Grant succeeded.

SQL> grant  CREATE SYNONYM  to SH ;

Grant succeeded.

SQL> grant  CREATE TABLE  to  IX ;

Grant succeeded.

SQL> grant  CREATE TABLE  to  SH ;

Grant succeeded.

SQL> grant  CREATE TRIGGER  to IX ;

Grant succeeded.

SQL> grant  CREATE TYPE  to IX ;

Grant succeeded.

SQL> grant  CREATE VIEW  to HR ;

Grant succeeded.

SQL> grant  CREATE VIEW  to IX ;

Grant succeeded.

SQL> grant  CREATE VIEW  to OE ;

Grant succeeded.

SQL> grant  CREATE VIEW  to SH ;

Grant succeeded.

SQL> grant  QUERY REWRITE  to  OE ;

Grant succeeded.

SQL> grant  QUERY REWRITE  to  SH ;

Grant succeeded.

SQL> grant  SELECT ANY DICTIONARY  to  IX ;

Grant succeeded.

SQL> grant  UNLIMITED TABLESPACE  to  HR ;

Grant succeeded.

SQL> grant  UNLIMITED TABLESPACE  to  IX ;

Grant succeeded.

SQL> grant  UNLIMITED TABLESPACE  to  OE ;

Grant succeeded.

SQL> grant  UNLIMITED TABLESPACE  to  PM ;

Grant succeeded.

SQL> grant  UNLIMITED TABLESPACE  to  SH ;

Grant succeeded.

SQL> 
SQL> spool off
SQL> @roles_to_EXAMPLE
SQL> set echo on
SQL> spool roles_to_EXAMPLE
SQL> 
SQL> grant  AQ_ADMINISTRATOR_ROLE  to  IX ;

Grant succeeded.

SQL> grant  AQ_USER_ROLE  to  IX ;

Grant succeeded.

SQL> grant  CONNECT to  IX ;

Grant succeeded.

SQL> grant  CONNECT to  PM ;

Grant succeeded.

SQL> grant  RESOURCE  to  HR ;

Grant succeeded.

SQL> grant  RESOURCE  to  IX ;

Grant succeeded.

SQL> grant  RESOURCE  to  OE ;

Grant succeeded.

SQL> grant  RESOURCE  to  PM ;

Grant succeeded.

SQL> grant  RESOURCE  to  SH ;

Grant succeeded.

SQL> grant  SELECT_CATALOG_ROLE  to IX ;

Grant succeeded.

SQL> grant  SELECT_CATALOG_ROLE  to SH ;

Grant succeeded.

SQL> grant  XDBADMIN  to  OE ;

Grant succeeded.

SQL> 
SQL> spool off
SQL> 


I am now ready to import the tablespace and datafile.

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
[oracle@ora12102 Desktop]$ impdp hemant/hemant@PDB1 \
> dumpfile=EXAMPLE_TTS.dmp directory=imp_from_noncdb \
> transport_datafiles=+DATA/CDB1/35208E5B92306007E0530F02000A969A/DATAFILE/example.dbf

Import: Release 12.1.0.2.0 - Production on Sun Jan 15 16:50:16 2017

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

UDI-28002: operation generated ORACLE error 28002
ORA-28002: the password will expire within 7 days

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
Master table "HEMANT"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Source time zone is +00:00 and target time zone is -07:00.
Starting "HEMANT"."SYS_IMPORT_TRANSPORTABLE_01":  hemant/********@PDB1 dumpfile=EXAMPLE_TTS.dmp directory=imp_from_noncdb transport_datafiles=+DATA/CDB1/35208E5B92306007E0530F02000A969A/DATAFILE/example.dbf 
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TYPE/TYPE_SPEC
Processing object type TRANSPORTABLE_EXPORT/TYPE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TRANSPORTABLE_EXPORT/TYPE/TYPE_BODY
Processing object type TRANSPORTABLE_EXPORT/PROCACT_INSTANCE
Processing object type TRANSPORTABLE_EXPORT/XMLSCHEMA/XMLSCHEMA
Processing object type TRANSPORTABLE_EXPORT/TABLE
ORA-39360: Table "OE"."ORDERS" was skipped due to transportable import and TSLTZ issues resulting from time zone mismatch.
ORA-39151: Table "OE"."PURCHASEORDER" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
Processing object type TRANSPORTABLE_EXPORT/GRANT/OWNER_GRANT/OBJECT_GRANT
ORA-39083: Object type OBJECT_GRANT failed to create with error:
ORA-01917: user or role 'BI' does not exist
Failing sql is:
GRANT SELECT ON "OE"."PROMOTIONS" TO "BI"
ORA-39083: Object type OBJECT_GRANT failed to create with error:
ORA-01917: user or role 'BI' does not exist
Failing sql is:
GRANT SELECT ON "OE"."PRODUCT_DESCRIPTIONS" TO "BI"
ORA-39083: Object type OBJECT_GRANT failed to create with error:
ORA-01917: user or role 'BI' does not exist
Failing sql is:
GRANT SELECT ON "OE"."PRODUCT_INFORMATION" TO "BI"
ORA-39083: Object type OBJECT_GRANT failed to create with error:
ORA-01917: user or role 'BI' does not exist
Failing sql is:
GRANT SELECT ON "OE"."INVENTORIES" TO "BI"
ORA-39112: Dependent object type OBJECT_GRANT:"OE" skipped, base object type TABLE:"OE"."ORDERS" creation failed
ORA-39112: Dependent object type OBJECT_GRANT:"OE" skipped, base object type TABLE:"OE"."ORDERS" creation failed
ORA-39083: Object type OBJECT_GRANT failed to create with error:
ORA-01917: user or role 'BI' does not exist
Failing sql is:
GRANT SELECT ON "OE"."ORDER_ITEMS" TO "BI"
ORA-39083: Object type OBJECT_GRANT failed to create with error:
ORA-01917: user or role 'BI' does not exist
Failing sql is:
GRANT SELECT ON "OE"."WAREHOUSES" TO "BI"
ORA-39083: Object type OBJECT_GRANT failed to create with error:
ORA-01917: user or role 'BI' does not exist
Failing sql is:
GRANT SELECT ON "OE"."CUSTOMERS" TO "BI"
ORA-39083: Object type OBJECT_GRANT failed to create with error:
ORA-01917: user or role 'BI' does not exist
Failing sql is:
GRANT SELECT ON "SH"."COSTS" TO "BI"
ORA-39083: Object type OBJECT_GRANT failed to create with error:
ORA-01917: user or role 'BI' does not exist
Failing sql is:
GRANT SELECT ON "SH"."SALES" TO "BI"
ORA-39083: Object type OBJECT_GRANT failed to create with error:
ORA-01917: user or role 'BI' does not exist
Failing sql is:
GRANT SELECT ON "SH"."COUNTRIES" TO "BI"
ORA-39083: Object type OBJECT_GRANT failed to create with error:
ORA-01917: user or role 'BI' does not exist
Failing sql is:
GRANT SELECT ON "SH"."CUSTOMERS" TO "BI"
ORA-39083: Object type OBJECT_GRANT failed to create with error:
ORA-01917: user or role 'BI' does not exist
Failing sql is:
GRANT SELECT ON "SH"."PROMOTIONS" TO "BI"
ORA-39083: Object type OBJECT_GRANT failed to create with error:
ORA-01917: user or role 'BI' does not exist
Failing sql is:
GRANT SELECT ON "SH"."CHANNELS" TO "BI"
ORA-39083: Object type OBJECT_GRANT failed to create with error:
ORA-01917: user or role 'BI' does not exist
Failing sql is:
GRANT SELECT ON "SH"."PRODUCTS" TO "BI"
ORA-39083: Object type OBJECT_GRANT failed to create with error:
ORA-01917: user or role 'BI' does not exist
Failing sql is:
GRANT SELECT ON "SH"."TIMES" TO "BI"
ORA-39083: Object type OBJECT_GRANT failed to create with error:
ORA-01917: user or role 'BI' does not exist
Failing sql is:
GRANT SELECT ON "SH"."FWEEK_PSCAT_SALES_MV" TO "BI"
ORA-39083: Object type OBJECT_GRANT failed to create with error:
ORA-01917: user or role 'BI' does not exist
Failing sql is:
GRANT SELECT ON "SH"."CAL_MONTH_SALES_MV" TO "BI"
Processing object type TRANSPORTABLE_EXPORT/INDEX/INDEX
ORA-39112: Dependent object type INDEX:"OE"."ORD_SALES_REP_IX" skipped, base object type TABLE:"OE"."ORDERS" creation failed
ORA-39112: Dependent object type INDEX:"OE"."ORD_ORDER_DATE_IX" skipped, base object type TABLE:"OE"."ORDERS" creation failed
ORA-39112: Dependent object type INDEX:"OE"."ORD_CUSTOMER_IX" skipped, base object type TABLE:"OE"."ORDERS" creation failed
ORA-39112: Dependent object type INDEX:"OE"."ORDER_PK" skipped, base object type TABLE:"OE"."ORDERS" creation failed
Processing object type TRANSPORTABLE_EXPORT/INDEX/FUNCTIONAL_INDEX/INDEX
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
ORA-39112: Dependent object type CONSTRAINT:"OE"."ORDER_MODE_LOV" skipped, base object type TABLE:"OE"."ORDERS" creation failed
ORA-39112: Dependent object type CONSTRAINT:"OE"."ORDER_TOTAL_MIN" skipped, base object type TABLE:"OE"."ORDERS" creation failed
ORA-39112: Dependent object type CONSTRAINT:"OE"."ORDER_PK" skipped, base object type TABLE:"OE"."ORDERS" creation failed
Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/INDEX/STATISTICS/FUNCTIONAL_INDEX/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/COMMENT
ORA-39112: Dependent object type COMMENT skipped, base object type TABLE:"OE"."ORDERS" creation failed
ORA-39112: Dependent object type COMMENT skipped, base object type TABLE:"OE"."ORDERS" creation failed
ORA-39112: Dependent object type COMMENT skipped, base object type TABLE:"OE"."ORDERS" creation failed
ORA-39112: Dependent object type COMMENT skipped, base object type TABLE:"OE"."ORDERS" creation failed
ORA-39112: Dependent object type COMMENT skipped, base object type TABLE:"OE"."ORDERS" creation failed
ORA-39112: Dependent object type COMMENT skipped, base object type TABLE:"OE"."ORDERS" creation failed
ORA-39112: Dependent object type COMMENT skipped, base object type TABLE:"OE"."ORDERS" creation failed
ORA-39112: Dependent object type COMMENT skipped, base object type TABLE:"OE"."ORDERS" creation failed
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/REF_CONSTRAINT
ORA-39083: Object type REF_CONSTRAINT:"OE"."ORDER_ITEMS_ORDER_ID_FK" failed to create with error:
ORA-00942: table or view does not exist
Failing sql is:
ALTER TABLE "OE"."ORDER_ITEMS" ADD CONSTRAINT "ORDER_ITEMS_ORDER_ID_FK" FOREIGN KEY ("ORDER_ID") REFERENCES "OE"."ORDERS" ("ORDER_ID") ON DELETE CASCADE ENABLE NOVALIDATE
ORA-39112: Dependent object type REF_CONSTRAINT:"OE"."ORDERS_SALES_REP_FK" skipped, base object type TABLE:"OE"."ORDERS" creation failed
ORA-39112: Dependent object type REF_CONSTRAINT:"OE"."ORDERS_CUSTOMER_ID_FK" skipped, base object type TABLE:"OE"."ORDERS" creation failed
Processing object type TRANSPORTABLE_EXPORT/INDEX/BITMAP_INDEX/INDEX
Processing object type TRANSPORTABLE_EXPORT/INDEX/STATISTICS/BITMAP_INDEX/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/TRIGGER
Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/STATISTICS/MARKER
Processing object type TRANSPORTABLE_EXPORT/DOMAIN_INDEX/TABLE
Processing object type TRANSPORTABLE_EXPORT/DOMAIN_INDEX/SECONDARY_TABLE/INDEX
Processing object type TRANSPORTABLE_EXPORT/DOMAIN_INDEX/SECONDARY_TABLE/CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/DOMAIN_INDEX/INDEX
Processing object type TRANSPORTABLE_EXPORT/MATERIALIZED_VIEW
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PROCACT_INSTANCE
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PROCDEPOBJ
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
ORA-39082: Object type TRIGGER:"HR"."SECURE_EMPLOYEES" created with compilation warnings
ORA-39082: Object type TRIGGER:"HR"."UPDATE_JOB_HISTORY" created with compilation warnings
Job "HEMANT"."SYS_IMPORT_TRANSPORTABLE_01" completed with 41 error(s) at Sun Jan 15 16:51:40 2017 elapsed 0 00:01:23

[oracle@ora12102 Desktop]$ 


The key error is the failure on the ORDERS table creation because of a TimeZone mismatch !   So, there is a lesson to be learnt !

.
.
.

09 January, 2017

V$RMAN_BACKUP_JOB_DETAILS, a caveat

Building on a previous blog post (you could read it before or after this post), here's a quick demo of a caveat or quirk with V$RMAN_BACKUP_JOB_DETAILS.

This in 11.2.0.4

[oracle@ora11204 Desktop]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Mon Jan 9 13:39:44 2017

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

connected to target database: ORCL (DBID=1362461976)

RMAN> backup as  compressed backupset
2> incremental level 1 database
3> plus archivelog ;


Starting backup at 09-JAN-17
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=153 device type=DISK
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=108 RECID=1245 STAMP=928093294
input archived log thread=1 sequence=109 RECID=1246 STAMP=928093719
input archived log thread=1 sequence=110 RECID=1247 STAMP=928093722
input archived log thread=1 sequence=111 RECID=1248 STAMP=928093724
...
...
...
input archived log thread=1 sequence=163 RECID=1318 STAMP=929802055
input archived log thread=1 sequence=164 RECID=1319 STAMP=932823436
input archived log thread=1 sequence=165 RECID=1320 STAMP=932823439
input archived log thread=1 sequence=166 RECID=1321 STAMP=932823606
channel ORA_DISK_1: starting piece 1 at 09-JAN-17
channel ORA_DISK_1: finished piece 1 at 09-JAN-17
piece handle=/u02/FRA/ORCL/backupset/2017_01_09/o1_mf_annnn_TAG20170109T134007_d768kr8l_.bkp tag=TAG20170109T134007 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:15
Finished backup at 09-JAN-17

Starting backup at 09-JAN-17
using channel ORA_DISK_1
no parent backup or copy of datafile 2 found
no parent backup or copy of datafile 1 found
no parent backup or copy of datafile 3 found
no parent backup or copy of datafile 4 found
channel ORA_DISK_1: starting compressed incremental level 0 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00002 name=/u01/app/oracle/oradata/ORCL/sysaux.dbf
input datafile file number=00001 name=/u01/app/oracle/oradata/ORCL/system.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/ORCL/undotbs1.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/ORCL/users.dbf
channel ORA_DISK_1: starting piece 1 at 09-JAN-17
channel ORA_DISK_1: finished piece 1 at 09-JAN-17
piece handle=/u02/FRA/ORCL/backupset/2017_01_09/o1_mf_nnnd0_TAG20170109T134123_d768n3v5_.bkp tag=TAG20170109T134123 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:45
channel ORA_DISK_1: starting compressed incremental level 1 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00011 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_cdgb60g4_.dbf
input datafile file number=00012 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_users_c552qnsh_.dbf
input datafile file number=00013 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_intermed_c552qpc7_.dbf
channel ORA_DISK_1: starting piece 1 at 09-JAN-17
channel ORA_DISK_1: finished piece 1 at 09-JAN-17
piece handle=/u02/FRA/ORCL/backupset/2017_01_09/o1_mf_nnnd1_TAG20170109T134123_d768ojmm_.bkp tag=TAG20170109T134123 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
Finished backup at 09-JAN-17

Starting backup at 09-JAN-17
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=167 RECID=1322 STAMP=932823743
channel ORA_DISK_1: starting piece 1 at 09-JAN-17
channel ORA_DISK_1: finished piece 1 at 09-JAN-17
piece handle=/u02/FRA/ORCL/backupset/2017_01_09/o1_mf_annnn_TAG20170109T134223_d768ozv9_.bkp tag=TAG20170109T134223 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 09-JAN-17

Starting Control File and SPFILE Autobackup at 09-JAN-17
piece handle=/u02/FRA/ORCL/autobackup/2017_01_09/o1_mf_s_932823745_d768p1jo_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 09-JAN-17

RMAN> 


What does V$RMAN_BACKUP_JOB_DETAILS tell us ?

SQL> l
  1  select to_char(start_time,'DD-MON HH24:MI') Start_At,
  2  to_char(end_time,'DD-MON HH24:MI') End_At,
  3  input_bytes/1048576 Input_MB, output_bytes/1048576 Output_MB,
  4  input_type, status
  5  from v$rman_backup_job_details
  6  where start_time > trunc(sysdate)
  7* order by start_time
SQL> /

START_AT       END_AT        INPUT_MB OUTPUT_MB INPUT_TYPE
--------------------- --------------------- ---------- ---------- -------------
STATUS
-----------------------
09-JAN 13:40       09-JAN 13:42     2917.06055 491.563477 DB INCR
COMPLETED


SQL> 


The view does NOT show how much of the input/output was for ArchiveLogs.  It clubs ArchiveLogs and the controlfile autobackup under the   single entry for "DB INCR".   Anyone reading this row from V$RMAN_BACKUP_JOB_DETAILS would NOT know if ArchiveLogs had been backed-up, would NOT know if a controlfile/spfile autobackup was created.
Furtheremore, if there is a failure (e.g. only the last ArchiveLog backupset failed ?), would you be able to identify what has successfully been backed up.  Also see my previous blog post.
.
.
.


Aggregated by orafaq.com

Aggregated by orafaq.com
This blog is being aggregated by orafaq.com