How to Migrate SQL Profiles from One database to Another database

===============================How to Migrate SQL Profiles from One database to Another database=========================================


You can migrate SQL profile using export and import from one database to another database just like stored outline. Prior to oracle 10g you can migrate SQL profiles with the dbms_sqltune.import_sql_profile procedure where as in oracle 10g release 2 and beyond using dbms_sqltune package. In both case you have to create a staging table on the source database and populate that staging table with the relevant data. Below is the step to migrate SQL profile in 10g release 2.

Step1. Create the staging table to store SQL Profiles in source database
SQL> sys/oracle@orcl as sysdba
SQL> BEGIN
DBMS_SQLTUNE.CREATE_STGTAB_SQLPROF
(table_name => ‘SQL_PROFILES’,schema_name=>TEST');
     END;
/
PL/SQL procedure successfully completed.


Step2. Now Copy SQL profiles from SYS to the Staging table
SQL> BEGIN
DBMS_SQLTUNE.PACK_STGTAB_SQLPROF
(profile_name => 'PROF_4m0c9guyqybbd_2738228381',
staging_table_name => 'SQL_PROFILES',
staging_schema_owner=>'TEST');
END;
/
PL/SQL procedure successfully completed.
Note: As you need to copy all SQL profiles on my database ‘%’ value for profile_category was the best option.


Step3. Export the staging table at source
SQL> select count(*) from TEST.sql_profiles;
COUNT(*)
-------------
3
expdp system/***** dumpfile=expdp_sql_profiles.dmp TABLES=TEST.SQL_PROFILES DIRECTORY=DPUMP



Step4. Restore the database with the backup taken before all SQL profiles were generated and import the staging table at target database.
impdp system/***** dumpfile=expdp_sql_profiles.dmp TABLES=TEST.SQL_PROFILES DIRECTORY=DPUMP TABLE_EXISTS_ACTION=REPLACE
Note: Do not forget to create staging table on destination database. Use replace = TRUE if you need to have same SQL_Profiles on both the database.


Step5. Finally Unpack the SQL profiles from the staging table on destination database.
SQL> BEGIN
DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF
(staging_table_name => ‘SQL_PROFILES’,
staging_schema_owner=>’TEST2’, replace=>FALSE);
END;
/
PL/SQL procedure successfully completed.

Add partition in LVM linux

root@localhost dev]#
[root@localhost dev]# 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 0x50f6f010.
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): p

Disk /dev/sdc: 4294 MB, 4294967296 bytes
255 heads, 63 sectors/track, 522 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: 0x50f6f010

   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-522, default 1):
Using default value 1
Last cylinder, +cylinders or +size{K,M,G} (1-522, default 522):
Using default value 522

Command (m for help): p

Disk /dev/sdc: 4294 MB, 4294967296 bytes
255 heads, 63 sectors/track, 522 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: 0x50f6f010

   Device Boot      Start         End      Blocks   Id  System
/dev/sdc1               1         522     4192933+  83  Linux

Command (m for help): t
Selected partition 1
Hex code (type L to list codes): 8e
Changed system type of partition 1 to 8e (Linux LVM)

Command (m for help): p

Disk /dev/sdc: 4294 MB, 4294967296 bytes
255 heads, 63 sectors/track, 522 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: 0x50f6f010

   Device Boot      Start         End      Blocks   Id  System
/dev/sdc1               1         522     4192933+  8e  Linux LVM

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

Calling ioctl() to re-read partition table.
Syncing disks.
[root@localhost dev]#
[root@localhost dev]#
[root@localhost dev]#
[root@localhost dev]# pvscan
  PV /dev/sdb1   VG oracle_lab   lvm2 [2.00 GiB / 1.61 GiB free]
  PV /dev/sda2   VG VolGroup     lvm2 [19.51 GiB / 0    free]
  Total: 2 [21.50 GiB] / in use: 2 [21.50 GiB] / in no VG: 0 [0   ]
[root@localhost dev]#
[root@localhost dev]#
[root@localhost dev]# pvcreate /dev/sdc1
  Physical volume "/dev/sdc1" successfully created
[root@localhost dev]#
[root@localhost dev]# pvscan
  PV /dev/sdb1   VG oracle_lab      lvm2 [2.00 GiB / 1.61 GiB free]
  PV /dev/sda2   VG VolGroup        lvm2 [19.51 GiB / 0    free]
  PV /dev/sdc1                      lvm2 [4.00 GiB]
  Total: 3 [25.50 GiB] / in use: 2 [21.50 GiB] / in no VG: 1 [4.00 GiB]
[root@localhost dev]#
[root@localhost dev]# vgextend oracle_lab /dev/sdc1
  Volume group "oracle_lab" successfully extended
[root@localhost dev]#
[root@localhost dev]# pvs
  PV         VG         Fmt  Attr PSize  PFree
  /dev/sda2  VolGroup   lvm2 a--  19.51g    0
  /dev/sdb1  oracle_lab lvm2 a--   2.00g 1.61g
  /dev/sdc1  oracle_lab lvm2 a--   4.00g 4.00g
[root@localhost dev]#

[root@localhost oracle_lab]#
[root@localhost oracle_lab]# lvextend /dev/oracle_lab/u01 /dev/sdc1
  Extending logical volume u01 to 4.39 GiB
  Logical volume u01 successfully resized
[root@localhost oracle_lab]#

[root@localhost oracle_lab]# resize2fs /dev/oracle_lab/u01
resize2fs 1.43-WIP (20-Jun-2013)
Filesystem at /dev/oracle_lab/u01 is mounted on /u01; on-line resizing required
old_desc_blocks = 2, new_desc_blocks = 18
The filesystem on /dev/oracle_lab/u01 is now 4599808 blocks long.

[root@localhost oracle_lab]#
[root@localhost oracle_lab]# df -h
Filesystem                    Size  Used Avail Use% Mounted on
/dev/mapper/VolGroup-lv_root   18G  3.9G   13G  24% /
tmpfs                         2.0G  112K  2.0G   1% /dev/shm
/dev/sda1                     477M   55M  397M  13% /boot
/dev/mapper/oracle_lab-u01    4.3G  3.1M  4.1G   1% /u01
[root@localhost oracle_lab]#

How To Setup Logical Volume Manager (LVM) Step By Step in Linux

LVM allows users to dynamically extend or shrink Linux “partition” or file system in online mode.

We create lvm using /dev/sdb disk

[root@localhost ~]#
[root@localhost ~]# df -h
Filesystem                    Size  Used Avail Use% Mounted on
/dev/mapper/VolGroup-lv_root   18G  3.9G   13G  24% /
tmpfs                         2.0G  4.0K  2.0G   1% /dev/shm
/dev/sda1                     477M   55M  397M  13% /boot
[root@localhost ~]#
[root@localhost ~]#
[root@localhost ~]# fdisk -l

Disk /dev/sda: 21.5 GB, 21474836480 bytes
255 heads, 63 sectors/track, 2610 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: 0x000b52b7

   Device Boot      Start         End      Blocks   Id  System
/dev/sda1   *           1          64      512000   83  Linux
Partition 1 does not end on cylinder boundary.
/dev/sda2              64        2611    20458496   8e  Linux LVM

Disk /dev/sdb: 2147 MB, 2147483648 bytes
255 heads, 63 sectors/track, 261 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: 0x00000000


Disk /dev/mapper/VolGroup-lv_root: 18.8 GB, 18798870528 bytes
255 heads, 63 sectors/track, 2285 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: 0x00000000


Disk /dev/mapper/VolGroup-lv_swap: 2147 MB, 2147483648 bytes
255 heads, 63 sectors/track, 261 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: 0x00000000


root@localhost ~]# 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 0x96749b02.
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): p

Disk /dev/sdb: 2147 MB, 2147483648 bytes
255 heads, 63 sectors/track, 261 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: 0x96749b02

   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-261, default 1):
Using default value 1
Last cylinder, +cylinders or +size{K,M,G} (1-261, default 261):enter
Using default value 261

Command (m for help): p

Disk /dev/sdb: 2147 MB, 2147483648 bytes
255 heads, 63 sectors/track, 261 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: 0x96749b02

   Device Boot      Start         End      Blocks   Id  System
/dev/sdb1               1         261     2096451   83  Linux

Command (m for help): t
Selected partition 1
Hex code (type L to list codes): l

 0  Empty           24  NEC DOS         81  Minix / old Lin bf  Solaris
 1  FAT12           39  Plan 9          82  Linux swap / So c1  DRDOS/sec (FAT-
 2  XENIX root      3c  PartitionMagic  83  Linux           c4  DRDOS/sec (FAT-
 3  XENIX usr       40  Venix 80286     84  OS/2 hidden C:  c6  DRDOS/sec (FAT-
 4  FAT16 <32M      41  PPC PReP Boot   85  Linux extended  c7  Syrinx
 5  Extended        42  SFS             86  NTFS volume set da  Non-FS data
 6  FAT16           4d  QNX4.x          87  NTFS volume set db  CP/M / CTOS / .
 7  HPFS/NTFS       4e  QNX4.x 2nd part 88  Linux plaintext de  Dell Utility
 8  AIX             4f  QNX4.x 3rd part 8e  Linux LVM       df  BootIt
 9  AIX bootable    50  OnTrack DM      93  Amoeba          e1  DOS access
 a  OS/2 Boot Manag 51  OnTrack DM6 Aux 94  Amoeba BBT      e3  DOS R/O
 b  W95 FAT32       52  CP/M            9f  BSD/OS          e4  SpeedStor
 c  W95 FAT32 (LBA) 53  OnTrack DM6 Aux a0  IBM Thinkpad hi eb  BeOS fs
 e  W95 FAT16 (LBA) 54  OnTrackDM6      a5  FreeBSD         ee  GPT
 f  W95 Ext'd (LBA) 55  EZ-Drive        a6  OpenBSD         ef  EFI (FAT-12/16/
10  OPUS            56  Golden Bow      a7  NeXTSTEP        f0  Linux/PA-RISC b
11  Hidden FAT12    5c  Priam Edisk     a8  Darwin UFS      f1  SpeedStor
12  Compaq diagnost 61  SpeedStor       a9  NetBSD          f4  SpeedStor
14  Hidden FAT16 <3 63  GNU HURD or Sys ab  Darwin boot     f2  DOS secondary
16  Hidden FAT16    64  Novell Netware  af  HFS / HFS+      fb  VMware VMFS
17  Hidden HPFS/NTF 65  Novell Netware  b7  BSDI fs         fc  VMware VMKCORE
18  AST SmartSleep  70  DiskSecure Mult b8  BSDI swap       fd  Linux raid auto
1b  Hidden W95 FAT3 75  PC/IX           bb  Boot Wizard hid fe  LANstep
1c  Hidden W95 FAT3 80  Old Minix       be  Solaris boot    ff  BBT
1e  Hidden W95 FAT1
Hex code (type L to list codes): 8e
Changed system type of partition 1 to 8e (Linux LVM)

Command (m for help): p

Disk /dev/sdb: 2147 MB, 2147483648 bytes
255 heads, 63 sectors/track, 261 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: 0x96749b02

   Device Boot      Start         End      Blocks   Id  System
/dev/sdb1               1         261     2096451   8e  Linux LVM

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

Calling ioctl() to re-read partition table.
Syncing disks.
[root@localhost ~]#
[root@localhost ~]# partprobe /dev/sdb
[root@localhost ~]#
[root@localhost ~]# parted /dev/sdb print free
Model: ATA VBOX HARDDISK (scsi)
Disk /dev/sdb: 2147MB
Sector size (logical/physical): 512B/512B
Partition Table: msdos

Number  Start   End     Size    Type     File system  Flags
 1      32.3kB  2147MB  2147MB  primary               lvm
        2147MB  2147MB  686kB            Free Space

[root@localhost ~]#

[root@localhost ~]# pvcreate /dev/sdb1

  Physical volume "/dev/sdb1" successfully created

[root@localhost ~]#

[root@localhost ~]# vgcreate oracle_lab /dev/sdb1

  Volume group "oracle_lab" successfully created

[root@localhost ~]#

[root@localhost ~]# lvcreate oracle_lab -L +1G -n u01

  Logical volume "u01" created

-L -> To specify the size of the partition in our case 1GB
-n -> To specify the name of the Logical volume in our case /u01

[root@localhost ~]#

[root@localhost ~]# lvdisplay

  --- Logical volume ---

  LV Path                /dev/oracle_lab/u01

  LV Name                u01

  VG Name                oracle_lab

  LV UUID                ht0xTA-fhfn-FeNb-ymwB-Mylx-WoQR-53wiky

  LV Write Access        read/write

  LV Creation host, time localhost.localdomain, 2016-07-02 15:26:08 +0530

  LV Status              available

  # open                 0

  LV Size                1.00 GiB

  Current LE             256

  Segments               1

  Allocation             inherit

  Read ahead sectors     auto

  - currently set to     256

  Block device           252:2

  --- Logical volume ---

  LV Path                /dev/VolGroup/lv_root

  LV Name                lv_root

  VG Name                VolGroup

  LV UUID                sej0ko-wSqc-jpOP-9CL3-5v72-McmM-hU5pvY

  LV Write Access        read/write

  LV Creation host, time localhost.localdomain, 2016-07-02 12:02:04 +0530

  LV Status              available

  # open                 1

  LV Size                17.51 GiB

  Current LE             4482

  Segments               1

  Allocation             inherit

  Read ahead sectors     auto

  - currently set to     256

  Block device           252:0

  --- Logical volume ---

  LV Path                /dev/VolGroup/lv_swap

  LV Name                lv_swap

  VG Name                VolGroup

  LV UUID                VYtpkL-CmWl-SL4a-lZ2v-pEzr-9cyj-j5Mrci

  LV Write Access        read/write

  LV Creation host, time localhost.localdomain, 2016-07-02 12:02:09 +0530

  LV Status              available

  # open                 2

  LV Size                2.00 GiB

  Current LE             512

  Segments               1

  Allocation             inherit

  Read ahead sectors     auto

  - currently set to     256

  Block device           252:1

[root@localhost ~]#

[root@localhost ~]#

root@localhost ~]#

[root@localhost ~]# mkdir /u01

[root@localhost ~]#

[root@localhost ~]#

[root@localhost oracle_lab]#

[root@localhost oracle_lab]# mkfs.ext4 /dev/oracle_lab/u01

mke2fs 1.43-WIP (20-Jun-2013)

Filesystem label=

OS type: Linux

Block size=4096 (log=2)

Fragment size=4096 (log=2)

Stride=0 blocks, Stripe width=0 blocks

65536 inodes, 262144 blocks

13107 blocks (5.00%) reserved for the super user

First data block=0

Maximum filesystem blocks=268435456

8 block groups

32768 blocks per group, 32768 fragments per group

8192 inodes per group

Superblock backups stored on blocks:

        32768, 98304, 163840, 229376

Allocating group tables: done

Writing inode tables: done

Creating journal (8192 blocks): done

Writing superblocks and filesystem accounting information: done

[root@localhost oracle_lab]#

[root@localhost oracle_lab]#

[root@localhost oracle_lab]# mount /dev/oracle_lab/u01 /u01

[root@localhost oracle_lab]#

[root@localhost oracle_lab]# df -h

Filesystem                    Size  Used Avail Use% Mounted on

/dev/mapper/VolGroup-lv_root   18G  3.9G   13G  24% /

tmpfs                         2.0G  4.0K  2.0G   1% /dev/shm

/dev/sda1                     477M   55M  397M  13% /boot

/dev/mapper/oracle_lab-u01    976M  1.3M  924M   1% /u01

[root@localhost oracle_lab]#

[root@localhost oracle_lab]#

The added candidate disk device is not visible from all the nodes:ORA-15014: path '/dev/rASM' is not in the discovery set

when you add disk in asm diskgroup got error

Check in ASM alert log

SQL> Alter diskgroup DATA_HR add disk '/dev/rASM_PTT_001' ;
NOTE: GroupBlock outside rolling migration privileged region
ORA-15032: not all alterations performed
ORA-15031: disk specification '/dev/rASM_HR_001' matches no disks
ORA-15014: path '/dev/rASM_HR_001' is not in the discovery set
ERROR: Alter diskgroup DATA_HR add disk '/dev/rASM_HR_001'


If get error
Login as grid user

$sqlplus sys as sysasm

show parameter asm_diskstring

Alter system set asm_diskstring='/dev/rhdisk*', '/dev/rASM*'  scope=Both;



Here we have two type asm disk you can add more:-
/dev/rhdisk*', '/dev/rASM*'

Alter diskgroup DATA_HR add disk '/dev/rASM_PTT_001' ;

ref:
OERR: ORA-15014 "path '%s' is not in the discovery set" (varies by version) Reference Note ( Doc ID 194391.1 )
1389618.1 - Default Disk Discovery Path by Platform

----------------
The ASM_DISKSTRING initialization parameter specifies a comma-delimited list of strings that limits the set of disks that an Oracle ASM instance discovers.
The discovery strings can include wildcard characters. Only disks that match one of the strings are    discovered.
The same disk cannot be discovered twice.