Thinking Out Loud

December 13, 2016

Linux locate/print block device attributes ASMLib

Filed under: ASM,linux — mdinh @ 2:38 pm

Just learned about this specifically to identify whether disk is being used by ASMLib

blkid – command-line utility to locate/print block device attributes

Oracle Linux Server release 6.4

[root@rac02:/root]
# ll /etc/*release*
-rw-r--r--. 1 root root 32 Feb 22  2013 /etc/oracle-release
-rw-r--r--. 1 root root 55 Feb 22  2013 /etc/redhat-release
lrwxrwxrwx. 1 root root 14 Nov 29  2014 /etc/system-release -> oracle-release
-rw-r--r--. 1 root root 45 Feb 22  2013 /etc/system-release-cpe

[root@rac02:/root]
# cat /etc/system-release
Oracle Linux Server release 6.4

[root@rac02:/root]
# cat /etc/redhat-release
Red Hat Enterprise Linux Server release 6.4 (Santiago)

[root@rac02:/root]
# /sbin/blkid |sort
/dev/mapper/vg01-lv_root: UUID="97968448-9997-42a0-a106-c438a47345b8" TYPE="ext4"
/dev/mapper/vg01-lv_swap: UUID="8fe8b719-a367-4448-9ae2-76b376ad91d5" TYPE="swap"
/dev/sda1: UUID="33162370-b7a1-45ae-9c8c-966b8bd720e3" TYPE="ext4"
/dev/sda2: UUID="qHuGcf-Ntnr-hoLR-qtEb-cdgz-2sph-jgaKDK" TYPE="LVM2_member"
/dev/sdb1: LABEL="DISK1" TYPE="oracleasm"
/dev/sdc1: LABEL="DISK2" TYPE="oracleasm"
/dev/sdd1: LABEL="DISK3" TYPE="oracleasm"
/dev/sde1: LABEL="DISK4" TYPE="oracleasm"
/dev/sdf1: LABEL="DISK5" TYPE="oracleasm"
/dev/sdg1: LABEL="DISK6" TYPE="oracleasm"
/dev/sdh1: LABEL="DISK7" TYPE="oracleasm"
/dev/sdi1: LABEL="DISK8" TYPE="oracleasm"

Oracle Linux Server release 6.6

[root@arrow1 ~]# ll /etc/*release*
-rw-r--r--. 1 root root 32 Oct 15  2014 /etc/oracle-release
-rw-r--r--. 1 root root 55 Oct 15  2014 /etc/redhat-release
lrwxrwxrwx. 1 root root 14 Jun 24  2015 /etc/system-release -> oracle-release
-rw-r--r--. 1 root root 45 Oct 15  2014 /etc/system-release-cpe

[root@arrow1 ~]# cat /etc/system-release
Oracle Linux Server release 6.6

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

[root@arrow1 ~]# /sbin/blkid
/dev/sda1: UUID="7c42cea0-f23d-4fec-ad0f-b1bf4b50b17e" TYPE="ext4"
/dev/sda2: UUID="ZVWdTj-8EQN-F3ac-3Tev-xTsb-ssL8-7euz5X" TYPE="LVM2_member"
/dev/sdb1: UUID="KUEy1I-X5i2-CuSm-krTA-R5K5-i4je-Ek56ZK" TYPE="LVM2_member"
/dev/mapper/vg01-LogVol01: UUID="8e2c236c-dd87-4be6-9eaf-f72f32f0dcc6" TYPE="ext4"
/dev/mapper/vg01-LogVol00: UUID="919bab13-82b6-425b-95c8-87975cb0bf20" TYPE="swap"

Oracle Linux Server release 7.3

[root@owl ~]# ll /etc/*release*
-rw-r--r--. 1 root root  32 Nov  7 22:07 /etc/oracle-release
-rw-r--r--. 1 root root 398 Nov  7 22:07 /etc/os-release --- (New as of OEL7?)
-rw-r--r--. 1 root root  52 Nov  7 22:07 /etc/redhat-release
lrwxrwxrwx. 1 root root  14 Dec 12 23:31 /etc/system-release -> oracle-release
-rw-r--r--. 1 root root  31 Nov  7 22:07 /etc/system-release-cpe

[root@owl ~]# cat /etc/system-release
Oracle Linux Server release 7.3

[root@owl ~]# cat /etc/redhat-release
Red Hat Enterprise Linux Server release 7.3 (Maipo)

[root@owl ~]# cat /etc/os-release
NAME="Oracle Linux Server"
VERSION="7.3"
ID="ol"
VERSION_ID="7.3"
PRETTY_NAME="Oracle Linux Server 7.3"
ANSI_COLOR="0;31"
CPE_NAME="cpe:/o:oracle:linux:7:3:server"
HOME_URL="https://linux.oracle.com/"
BUG_REPORT_URL="https://bugzilla.oracle.com/"
ORACLE_BUGZILLA_PRODUCT="Oracle Linux 7"
ORACLE_BUGZILLA_PRODUCT_VERSION=7.3
ORACLE_SUPPORT_PRODUCT="Oracle Linux"
ORACLE_SUPPORT_PRODUCT_VERSION=7.3

[root@owl ~]# /sbin/blkid
/dev/sr0: UUID="2016-11-21-16-51-51-00" LABEL="VBOXADDITIONS_5.1.10_112026" TYPE="iso9660"
/dev/sda1: UUID="65bf9c73-fb56-49c3-b55e-85de8f318892" TYPE="xfs"
/dev/sda2: UUID="p4rqW2-uzvr-6DpX-nof0-7tgf-Yfvn-n8ehaB" TYPE="LVM2_member"
/dev/mapper/vg01-root: UUID="de9c65d9-60cc-45b8-b9fa-7459ef3f3850" TYPE="xfs"
/dev/mapper/vg01-swap: UUID="1f85c253-261e-4021-9468-651de69b8e7a" TYPE="swap"
[root@owl ~]#

July 27, 2016

Losing /u01

Filed under: 11g,ASM,oracle — mdinh @ 3:54 pm

There was an incident where /u01 was lost.

This means having to reinstall and patch software again.

When installing grid and I encountered:

CRS is already configured on this node for crshome=/u01/app/oracle/product/11.2.0/grid

Ah Crap! This is me being melodramatic.

The system was using Oracle Restart, it needs to be removed.

# /u01/app/oracle/product/11.2.0/grid/crs/install/roothas.pl -deconfig -force -verbose  

In hindsight, it would have been much easier to delete from /etc/oracle.

Guess what? ASM will need to be recreated and there’s a post for that.

ReCreate ASM Disks

++++++++++

# /u01/app/oracle/product/11.2.0/grid/perl/bin/perl -I/u01/app/oracle/product/11.2.0/grid/perl/lib -I/u01/app/oracle/product/11.2.0/grid/crs/install /u01/app/oracle/product/11.2.0/grid/crs/install/roothas.pl
Using configuration parameter file: /u01/app/oracle/product/11.2.0/grid/crs/install/crsconfig_params
Creating trace directory
User ignored Prerequisites during installation
/u01/app/oracle/product/11.2.0/grid/bin/crsctl query crs activeversion ... failed rc=-1 with message:
 Failure in execution (rc=-1, 0, No such file or directory) for command /u01/app/oracle/product/11.2.0/grid/bin/crsctl query crs activeversion 

CRS is already configured on this node for crshome=/u01/app/oracle/product/11.2.0/grid
Cannot configure two CRS instances on the same cluster.
Please deconfigure before proceeding with the configuration of new home. 

# ps -ef|grep pmon
root     143547 143405  0 15:22 pts/2    00:00:00 grep pmon

++++++++++

# /u01/app/oracle/product/11.2.0/grid/crs/install/roothas.pl -deconfig -force -verbose  
Using configuration parameter file: /u01/app/oracle/product/11.2.0/grid/crs/install/crsconfig_params
Failure in execution (rc=-1, 256, No such file or directory) for command /u01/app/oracle/product/11.2.0/grid/bin/crsctl stop resource ora.cssd -f
Failure in execution (rc=-1, 256, No such file or directory) for command /u01/app/oracle/product/11.2.0/grid/bin/crsctl delete resource ora.cssd -f
Failure in execution (rc=-1, 256, No such file or directory) for command /u01/app/oracle/product/11.2.0/grid/bin/crsctl stop has -f
You must kill ohasd processes or reboot the system to properly 
cleanup the processes started by Oracle clusterware
Successfully deconfigured Oracle Restart stack
# 

++++++++++

# /u01/app/oracle/product/11.2.0/grid/perl/bin/perl -I/u01/app/oracle/product/11.2.0/grid/perl/lib -I/u01/app/oracle/product/11.2.0/grid/crs/install /u01/app/oracle/product/11.2.0/grid/crs/install/roothas.pl
Using configuration parameter file: /u01/app/oracle/product/11.2.0/grid/crs/install/crsconfig_params
User ignored Prerequisites during installation
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 arrow successfully pinned.
Adding Clusterware entries to inittab

arrow     2016/07/26 15:30:21     /u01/app/oracle/product/11.2.0/grid/cdata/arrow/backup_20160726_153021.olr
Successfully configured Oracle Grid Infrastructure for a Standalone Server
# 

++++++++++

$ ll /etc/oracle
total 2256
drwxrwx--- 2 oracle oinstall    4096 Jul 26 15:29 lastgasp
-rw-r----- 1 oracle oinstall      92 Jul 26 15:29 ocr.loc
-rw-r--r-- 1 root   root          16 Jul 26 15:29 ocr.loc.orig
-rw-r----- 1 root   oinstall     125 Jul 26 15:29 olr.loc
-rw-r--r-- 1 root   root           0 Jul 26 15:29 olr.loc.orig
drwxrwx--- 5 root   oinstall    4096 Jul 26 15:29 oprocd
drwxr-x--- 3 root   oinstall    4096 Jul 26 15:29 scls_scr
-rws--x--- 1 root   oinstall 2280039 Jul 26 15:59 setasmgid

+++++++++

ASM disk creation failed because it's already a MEMBER.

SQL> alter system set asm_diskstring='ORCL:*' scope=both;

System altered.

SQL> CREATE DISKGROUP REDO01 EXTERNAL REDUNDANCY DISK 'ORCL:REDO*';
CREATE DISKGROUP REDO01 EXTERNAL REDUNDANCY DISK 'ORCL:REDO*'
*
ERROR at line 1:
ORA-15018: diskgroup cannot be created
ORA-15033: disk 'ORCL:REDO01_002' belongs to diskgroup "REDO01"
ORA-15033: disk 'ORCL:REDO01_001' belongs to diskgroup "REDO01"


SQL> CREATE DISKGROUP DATA01 EXTERNAL REDUNDANCY DISK 'ORCL:DATA*';
CREATE DISKGROUP DATA01 EXTERNAL REDUNDANCY DISK 'ORCL:DATA*'
*
ERROR at line 1:
ORA-15018: diskgroup cannot be created
ORA-15033: disk 'ORCL:DATA01_002' belongs to diskgroup "DATA01"
ORA-15033: disk 'ORCL:DATA01_001' belongs to diskgroup "DATA01"

SQL> select HEADER_STATUS||','||path from v$asm_disk;

HEADER_STATUS||','||PATH
--------------------------------------------------------------------------------
MEMBER,ORCL:DATA01_001
MEMBER,ORCL:DATA01_002
MEMBER,ORCL:DATA01_003
MEMBER,ORCL:DATA01_004
MEMBER,ORCL:REDO01_005
MEMBER,ORCL:REDO01_001
MEMBER,ORCL:REDO01_002
MEMBER,ORCL:REDO01_003
MEMBER,ORCL:REDO01_004
MEMBER,ORCL:DATA01_005

10 rows selected.

SQL> 

ReCreate ASM Disks

Filed under: 11g,ASM,oracle — mdinh @ 1:33 pm

The following ASM disks were created which does not follow convention:
ASM_REDO01_006, ASM_REDO01_007, ASM_REDO01_008, ASM_REDO01_009, and ASM_REDO01_010

It should be:
ASM_REDO01_001, ASM_REDO01_002, ASM_REDO01_003, ASM_REDO01_004, and ASM_REDO01_005

Hence, we will need to recreate them. Here’s the process for me to remember.

conn / as sysasm
drop diskgroup DATA01;
drop diskgroup REDO01;
SQL> show parameter disk

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
asm_diskgroups			     string
asm_diskstring			     string	 ORCL:*

++++++++++

# /etc/init.d/oracleasm querydisk -d ASM_DATA01_001
Disk "ASM_DATA01_001" is a valid ASM disk on device [120,33]

# /etc/init.d/oracleasm querydisk -d ASM_DATA01_002
Disk "ASM_DATA01_002" is a valid ASM disk on device [120,49]

# /etc/init.d/oracleasm querydisk -d ASM_DATA01_003
Disk "ASM_DATA01_003" is a valid ASM disk on device [120,65]

# /etc/init.d/oracleasm querydisk -d ASM_DATA01_004
Disk "ASM_DATA01_004" is a valid ASM disk on device [120,81]

# /etc/init.d/oracleasm querydisk -d ASM_DATA01_005
Disk "ASM_DATA01_005" is a valid ASM disk on device [120,97]

++++++++++

$ /etc/init.d/oracleasm querydisk -d ASM_REDO01_006
Disk "ASM_REDO01_006" is a valid ASM disk on device [120,113]

$ /etc/init.d/oracleasm querydisk -d ASM_REDO01_007
Disk "ASM_REDO01_007" is a valid ASM disk on device [120,129]

$ /etc/init.d/oracleasm querydisk -d ASM_REDO01_008
Disk "ASM_REDO01_008" is a valid ASM disk on device [120,145]

$ /etc/init.d/oracleasm querydisk -d ASM_REDO01_009
Disk "ASM_REDO01_009" is a valid ASM disk on device [120,161]

$ /etc/init.d/oracleasm querydisk -d ASM_REDO01_010
Disk "ASM_REDO01_010" is a valid ASM disk on device [120,177]

++++++++++

$ ls -l /dev/|grep 120
drwxr-xr-x  6 root root       120 May  5 13:19 disk
brw-r-----  1 root disk  120,   0 May 18 08:35 emcpowera
brw-r-----  1 root disk  120,   1 May 18 08:42 emcpowera1
brw-r-----  1 root disk  120,  16 May 18 08:35 emcpowerb
brw-r-----  1 root disk  120,  17 May 18 08:43 emcpowerb1
brw-r-----  1 root disk  120,  32 May 18 00:19 emcpowerc
brw-r-----  1 root disk  120,  33 May 18 08:36 emcpowerc1
brw-r-----  1 root disk  120,  48 May 18 00:19 emcpowerd
brw-r-----  1 root disk  120,  49 May 18 08:36 emcpowerd1
brw-r-----  1 root disk  120,  64 May 18 00:19 emcpowere
brw-r-----  1 root disk  120,  65 May 18 08:36 emcpowere1
brw-r-----  1 root disk  120,  80 May 18 00:19 emcpowerf
brw-r-----  1 root disk  120,  81 May 18 08:36 emcpowerf1
brw-r-----  1 root disk  120,  96 May 18 00:29 emcpowerg
brw-r-----  1 root disk  120,  97 May 18 08:36 emcpowerg1
brw-r-----  1 root disk  120, 112 May 18 00:19 emcpowerh
brw-r-----  1 root disk  120, 113 May 18 08:36 emcpowerh1
brw-r-----  1 root disk  120, 128 May 18 00:19 emcpoweri
brw-r-----  1 root disk  120, 129 May 18 08:36 emcpoweri1
brw-r-----  1 root disk  120, 144 May 18 00:19 emcpowerj
brw-r-----  1 root disk  120, 145 May 18 08:36 emcpowerj1
brw-r-----  1 root disk  120, 160 May 18 00:19 emcpowerk
brw-r-----  1 root disk  120, 161 May 18 08:36 emcpowerk1
brw-r-----  1 root disk  120, 176 May 18 00:19 emcpowerl
brw-r-----  1 root disk  120, 177 May 18 08:36 emcpowerl1
brw-r-----  1 root disk  120, 192 May 18 08:35 emcpowerm
brw-r-----  1 root disk  120, 193 May 18 08:40 emcpowerm1

+++++++++

# /etc/init.d/oracleasm deletedisk ASM_REDO01_006
Removing ASM disk "ASM_REDO01_006":                        [  OK  ]

# /etc/init.d/oracleasm deletedisk ASM_REDO01_007
Removing ASM disk "ASM_REDO01_007":                        [  OK  ]

# /etc/init.d/oracleasm deletedisk ASM_REDO01_008
Removing ASM disk "ASM_REDO01_008":                        [  OK  ]

# /etc/init.d/oracleasm deletedisk ASM_REDO01_009
Removing ASM disk "ASM_REDO01_009":                        [  OK  ]

# /etc/init.d/oracleasm deletedisk ASM_REDO01_010
Removing ASM disk "ASM_REDO01_010": 

++++++++++

# /etc/init.d/oracleasm createdisk DATA01_001 /dev/emcpowerc1
Marking disk "DATA01_001" as an ASM disk:                  [  OK  ]

# /etc/init.d/oracleasm createdisk DATA01_002 /dev/emcpowerd1
Marking disk "DATA01_002" as an ASM disk:                  [  OK  ]

# /etc/init.d/oracleasm createdisk DATA01_003 /dev/emcpowere1
Marking disk "DATA01_003" as an ASM disk:                  [  OK  ]

# /etc/init.d/oracleasm createdisk DATA01_004 /dev/emcpowerf1
Marking disk "DATA01_004" as an ASM disk:                  [  OK  ]

# /etc/init.d/oracleasm createdisk DATA01_005 /dev/emcpowerg1
Marking disk "DATA01_005" as an ASM disk:                  [  OK  ]

+++++++++

# /etc/init.d/oracleasm createdisk REDO01_001 /dev/emcpowerh1
Marking disk "REDO01_001" as an ASM disk:                  [  OK  ]

# /etc/init.d/oracleasm createdisk REDO01_002 /dev/emcpoweri1
Marking disk "REDO01_002" as an ASM disk:                  [  OK  ]

# /etc/init.d/oracleasm createdisk REDO01_003 /dev/emcpowerj1
Marking disk "REDO01_003" as an ASM disk:                  [  OK  ]

# /etc/init.d/oracleasm createdisk REDO01_004 /dev/emcpowerk1
Marking disk "REDO01_004" as an ASM disk:                  [  OK  ]

# /etc/init.d/oracleasm createdisk REDO01_005 /dev/emcpowerl1
Marking disk "REDO01_005" as an ASM disk:                  [  OK  ]

+++++++++

# /etc/init.d/oracleasm listdisks
DATA01_001
DATA01_002
DATA01_003
DATA01_004
DATA01_005
REDO01_001
REDO01_002
REDO01_003
REDO01_004
REDO01_005

SQL> select HEADER_STATUS||','||path from v$asm_disk;

HEADER_STATUS||','||PATH
--------------------------------------------------------------------------------
PROVISIONED,ORCL:DATA01_001
PROVISIONED,ORCL:DATA01_002
PROVISIONED,ORCL:DATA01_003
PROVISIONED,ORCL:DATA01_004
PROVISIONED,ORCL:REDO01_005
PROVISIONED,ORCL:REDO01_001
PROVISIONED,ORCL:REDO01_002
PROVISIONED,ORCL:REDO01_003
PROVISIONED,ORCL:REDO01_004
PROVISIONED,ORCL:DATA01_005

10 rows selected.


SQL> CREATE DISKGROUP REDO01 EXTERNAL REDUNDANCY DISK 'ORCL:REDO*';

Diskgroup created.

SQL> CREATE DISKGROUP DATA01 EXTERNAL REDUNDANCY DISK 'ORCL:DATA*';

Diskgroup created.

SQL> show parameter disk

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
asm_diskgroups			     string	 REDO01, DATA01
asm_diskstring			     string	 ORCL:*

SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Automatic Storage Management option

+++++++++

$ asmcmd
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  EXTERN  N         512   4096  1048576    511994   511936                0          511936              0             N  DATA01/
MOUNTED  EXTERN  N         512   4096  1048576     51195    51137                0           51137              0             N  REDO01/
ASMCMD> 

++++++++++

$ crsctl stat res -t
--------------------------------------------------------------------------------
NAME           TARGET  STATE        SERVER                   STATE_DETAILS       
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATA01.dg
               ONLINE  ONLINE       arrow                                    
ora.LISTENER.lsnr
               ONLINE  ONLINE       arrow                                    
ora.REDO01.dg
               ONLINE  ONLINE       arrow                                    
ora.asm
               ONLINE  ONLINE       arrow                Started             
ora.ons
               OFFLINE OFFLINE      arrow                                    
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.cssd
      1        ONLINE  ONLINE       arrow                                    
ora.diskmon
      1        OFFLINE OFFLINE                                                   
ora.evmd
      1        ONLINE  ONLINE       arrow                                    
$

November 18, 2015

Extent Allocation

Filed under: 11g,ASM,oracle,performance — mdinh @ 11:43 pm

Some references on extents dealing with extent trimming, initial extent for parallel load, tablespace fragmentation, etc…

ASM AU Size And LMT AUTOALLOCATE
http://oracle-randolf.blogspot.com/2013/04/asm-au-size-and-lmt-autoallocate.html

Parallel Load: Uniform or AutoAllocate extents?
https://blogs.oracle.com/datawarehousing/entry/parallel_load_uniform_or_autoallocate

Tom Kyte covers this problem in great details in his post Loading and Extents
http://www.oracle.com/technetwork/issue-archive/2007/07-may/o37asktom-101781.html

System Managed Extent Size – 11g Improvements
https://antognini.ch/2009/08/system-managed-extent-size-11g-improvements/

PX and system allocation
https://jonathanlewis.wordpress.com/2012/06/14/px-and-system-allocation/

Initial Extent Size of a Partition Changed to 8MB from 64KB After Upgrade to 11.2.0.2 or Later (Doc ID 1295484.1)

Bug 19912552 : PARTITIONED INDEXES CREATED WITH 8M INITIAL EXTENT WHEN _INDEX_PARTITION_LARGE

Segments of type INDEX PARTITION or INDEX SUBPARTITION will be created with 8M initial extent in version 12.1.0.2, instead of 64K in earlier versions.
Global indexes or indexes on non partitioned tables still have 64K.

December 13, 2013

Cloning Database without ASM

Filed under: 11g,ASM — mdinh @ 2:09 am

So there I was, working on a project to duplicate a database from a volume copy at the storage level with the database being shutdown.

Sounds pretty simple right? Wrong! Put the database on ASM and it becomes complicated and convoluted.

Volume1 has ASM disk group in the fashion +DG1/db01/datafile, +DG1/db01/onlinelog +DG1/db01/tempfile

Volume1 will be copied to Volume2 at the storage level. What happens to all the ASM disk group?

The ASM disk group will need to be renamed using renamdg. Great! (with a little sarcasm)

So now the ASM disk groups will be +DG2/db01/datafile, +DG2/db01/onlinelog +DG2/db01/tempfile

What’s wrong with that picture?

Given I am an ASM noob, I did reach out to other and still waiting for a respond I can be confident with.

Now, if the database was not on ASM, I would be done with the project already.

1 hour to create a test case without using ASM, 8 hours too create action plan using ASM and still not completed.

Create and Edit Control File SQL

SQL> alter database backup controlfile to trace as '/tmp/cf.sql';

Parameter Copied from Source (DB01)

[oracle@arrow:db01]/u01/app/oracle/product/11.2.0.3/dbhome_1/dbs
$ cat initdb02.ora
*.audit_file_dest='/u01/app/oracle/admin/adump'
*.audit_trail='none'
*.compatible='11.2.0.3.0'
*.db_block_size=8192
*.db_create_file_dest='/oradata'
*.control_files='/oradata/DB02/controlfile/o1_mf_9bb5brjc_.ctl','/oradata/fra/DB02/controlfile/o1_mf_9bb5brxx_.ctl'
*.db_domain=''
*.db_name='db02'
*.db_recovery_file_dest='/oradata/fra'
*.db_recovery_file_dest_size=4g
*.diagnostic_dest='/u01/app/oracle'
*.event='10795 trace name context forever, level 2'
*.fast_start_mttr_target=300
*.java_pool_size=0
*.local_listener='(ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1531))'
*.pga_aggregate_target=268435456
*.processes=100
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=805306368
*.undo_tablespace='UNDOTBS'
[oracle@arrow:db01]/u01/app/oracle/product/11.2.0.3/dbhome_1/dbs

Shutdown database from Source (DB01) and perform copy

$ cd /oradata/
[oracle@arrow:db01]/oradata
$ ls *
DB01:
controlfile  datafile  onlinelog

fra:
DB01

[oracle@arrow:db01]/oradata
$ cp -rp DB01/ DB02/

[oracle@arrow:db01]/oradata
$ cd fra/
[oracle@arrow:db01]/oradata/fra
$ cp -rp DB01/ DB02/

Clone database from Source (DB01)

[oracle@arrow:db01]/oradata/fra
$ db02
The Oracle base remains unchanged with value /u01/app/oracle

IPC Resources for ORACLE_SID "db02" :
Shared Memory
ID              KEY
No shared memory segments used
Semaphores:
ID              KEY
No semaphore resources used
Oracle Instance not alive for sid "db02"
[oracle@arrow:db02]/oradata/fra
$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Thu Dec 12 17:04:23 2013

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

Connected to an idle instance.

SQL> set echo on
SQL> @/tmp/cf.sql
SQL> STARTUP NOMOUNT
ORACLE instance started.

Total System Global Area  801701888 bytes
Fixed Size                  2232640 bytes
Variable Size             222301888 bytes
Database Buffers          570425344 bytes
Redo Buffers                6742016 bytes
SQL> CREATE CONTROLFILE REUSE SET DATABASE "DB02" RESETLOGS     ARCHIVELOG
  2      MAXLOGFILES 16
  3      MAXLOGMEMBERS 2
  4      MAXDATAFILES 30
  5      MAXINSTANCES 1
  6      MAXLOGHISTORY 292
  7  LOGFILE
  8    GROUP 1 (
  9      '/oradata/DB02/onlinelog/o1_mf_1_9bb5bt5g_.log',
 10      '/oradata/fra/DB02/onlinelog/o1_mf_1_9bb5btk6_.log'
 11    ) SIZE 100M BLOCKSIZE 512,
 12    GROUP 2 (
 13      '/oradata/DB02/onlinelog/o1_mf_2_9bb5btq3_.log',
 14      '/oradata/fra/DB02/onlinelog/o1_mf_2_9bb5bv4g_.log'
 15    ) SIZE 100M BLOCKSIZE 512,
 16    GROUP 3 (
 17      '/oradata/DB02/onlinelog/o1_mf_3_9bb5bvc1_.log',
 18      '/oradata/fra/DB02/onlinelog/o1_mf_3_9bb5cny5_.log'
 19    ) SIZE 100M BLOCKSIZE 512
 20  -- STANDBY LOGFILE
 21  DATAFILE
 22    '/oradata/DB02/datafile/o1_mf_system_9bb5cx2c_.dbf',
 23    '/oradata/DB02/datafile/o1_mf_sysaux_9bb5ff55_.dbf',
 24    '/oradata/DB02/datafile/o1_mf_undotbs_9bb5gsjs_.dbf',
 25    '/oradata/DB02/datafile/o1_mf_users_9bb5j5wx_.dbf'
 26  CHARACTER SET AL32UTF8
 27  ;

Control file created.

SQL> ALTER DATABASE OPEN RESETLOGS;

Database altered.

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/oradata/DB02/datafile/o1_mf_temp_9bb5j4rf_.tmp'
  2       SIZE 268435456  REUSE AUTOEXTEND ON NEXT 268435456  MAXSIZE 8193M;

Tablespace altered.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning option

Check DBID for Cloned DB  (Same as Source)

[oracle@arrow:db01]/oradata
$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Thu Dec 12 17:34:06 2013

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

connected to target database: DB01 (DBID=1464916248)

RMAN>

[oracle@arrow:db02]/oradata/fra
$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Thu Dec 12 17:05:44 2013

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

connected to target database: DB02 (DBID=1464916248)

RMAN>

Change DBID for cloned DB

[oracle@arrow:db02]/oradata/fra
$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Thu Dec 12 17:05:54 2013

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning option

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

Total System Global Area  801701888 bytes
Fixed Size                  2232640 bytes
Variable Size             222301888 bytes
Database Buffers          570425344 bytes
Redo Buffers                6742016 bytes
Database mounted.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning option

[oracle@arrow:db02]/oradata/fra
$ nid target=sys dbname=DB02 logfile=/tmp/nid.log
Password:

[oracle@arrow:db02]/oradata/fra
$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Thu Dec 12 17:08:14 2013

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

connected to target database: DB02 (DBID=1464916248, not open)

RMAN> exit

Recovery Manager complete.

[oracle@arrow:db02]/oradata/fra
$ cat /tmp/nid.log

DBNEWID: Release 11.2.0.3.0 - Production on Thu Dec 12 17:08:06 2013

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

Connected to database DB02 (DBID=1464916248)

Connected to server version 11.2.0

Control Files in database:
    /oradata/DB02/controlfile/o1_mf_9bb5brjc_.ctl
    /oradata/fra/DB02/controlfile/o1_mf_9bb5brxx_.ctl

NID-00144: New name for database DB02 is the same as current name DB02

Change of database name and ID failed during validation - database is intact.
DBNEWID - Completed with validation errors.

Change DBID omitting DBNAME (changed from CF create)

[oracle@arrow:db02]/oradata/fra
$ nid target=sys logfile=/tmp/nid.log
Password:

[oracle@arrow:db02]/oradata/fra
$ cat /tmp/nid.log

DBNEWID: Release 11.2.0.3.0 - Production on Thu Dec 12 17:10:58 2013

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

Connected to database DB02 (DBID=1464916248)

Connected to server version 11.2.0

Control Files in database:
    /oradata/DB02/controlfile/o1_mf_9bb5brjc_.ctl
    /oradata/fra/DB02/controlfile/o1_mf_9bb5brxx_.ctl

Changing database ID from 1464916248 to 1581437733
    Control File /oradata/DB02/controlfile/o1_mf_9bb5brjc_.ctl - modified
    Control File /oradata/fra/DB02/controlfile/o1_mf_9bb5brxx_.ctl - modified
    Datafile /oradata/DB02/datafile/o1_mf_system_9bb5cx2c_.db - dbid changed
    Datafile /oradata/DB02/datafile/o1_mf_sysaux_9bb5ff55_.db - dbid changed
    Datafile /oradata/DB02/datafile/o1_mf_undotbs_9bb5gsjs_.db - dbid changed
    Datafile /oradata/DB02/datafile/o1_mf_users_9bb5j5wx_.db - dbid changed
    Datafile /oradata/DB02/datafile/o1_mf_temp_9bb5j4rf_.tm - dbid changed
    Control File /oradata/DB02/controlfile/o1_mf_9bb5brjc_.ctl - dbid changed
    Control File /oradata/fra/DB02/controlfile/o1_mf_9bb5brxx_.ctl - dbid changed
    Instance shut down

Database ID for database DB02 changed to 1581437733.
All previous backups and archived redo logs for this database are unusable.
Database is not aware of previous backups and archived logs in Recovery Area.
Database has been shutdown, open database with RESETLOGS option.
Succesfully changed database ID.
DBNEWID - Completed succesfully.

Check DBID for Cloned DB

[oracle@arrow:db02]/oradata/fra
$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Thu Dec 12 17:11:16 2013

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

connected to target database (not started)

RMAN> startup mount;

Oracle instance started
database mounted

Total System Global Area     801701888 bytes

Fixed Size                     2232640 bytes
Variable Size                222301888 bytes
Database Buffers             570425344 bytes
Redo Buffers                   6742016 bytes

RMAN> list incarnation;

using target database control file instead of recovery catalog

List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       DB02     1581437733       CURRENT 360639     12-DEC-2013 17:04:50

RMAN> alter database open;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 12/12/2013 17:12:14
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

Open database resetlogs

RMAN> alter database open resetlogs;

database opened

RMAN> exit

Recovery Manager complete.
[oracle@arrow:db02]/oradata/fra
$

November 24, 2013

Playing with ASM Online Migration

Filed under: 11g,ASM — mdinh @ 3:14 pm

“Everything should be made as simple as possible, but not simpler.” – Albert Einstein

I am curious as to why anyone would use ASM for a standalone database as it introduce more complexity of having to install, maintain, and upgrade Grid Infrastructure.

I asked the DBAs and here are the some responses:

ASM always bypass the OS caching layer (direct IO) which can be difficult and inconsistent using traditional FS.
ASM allows move to another type of storage in the future.
ASM provides transparent balance and migration between any type of block storage.
ASM provides online operations like adding/removing/resizing disks/LUNs from one disk array to another.

I asked a former manager who knows EMC storage and Solaris and here are his responses:

1. OS Caching: this was solved a long time ago. As you know, even plain old UFS on Solaris has been able to do this for years with Oracle.
2. Moving to another type of storage. That’s a function of either the volume manager on the OS or the array itself (both are possible and depend on your setup).
Oracle isn’t bringing anything new to the table with ASM.
3. Balance and migration. Again, a function of the volume manager and/or the array. Nothing new being done by ASM.
4. Online operations and migrations. Again, a function of the volume manager and/or the array. ASM brings nothing new.

It seems to me that ASM does, really, one thing: it eliminates the OS volume manager but at the same time expands the scope of the DBA to now include storage problems. That sounds like a distraction to me.

I would love to see a live debate on this.

Still not 100% satisfied, I started to research and explore ASM online operations.

ASM disks VENDOR101,VENDOR102,VENDOR103 are mapped to /dev/sdb1,/dev/sdc1,/dev/sdd1

[root@looney ~]# ls -l /dev/sd*
brw-rw---- 1 root disk 8,  0 Nov 23 09:23 /dev/sda
brw-rw---- 1 root disk 8,  1 Nov 23 09:23 /dev/sda1
brw-rw---- 1 root disk 8,  2 Nov 23 09:23 /dev/sda2
brw-rw---- 1 root disk 8, 16 Nov 23 09:23 /dev/sdb
brw-rw---- 1 root disk 8, 17 Nov 23 09:25 /dev/sdb1
brw-rw---- 1 root disk 8, 32 Nov 23 09:23 /dev/sdc
brw-rw---- 1 root disk 8, 33 Nov 23 09:27 /dev/sdc1
brw-rw---- 1 root disk 8, 48 Nov 23 09:23 /dev/sdd
brw-rw---- 1 root disk 8, 49 Nov 23 09:27 /dev/sdd1
brw-rw---- 1 root disk 8, 64 Nov 23 09:23 /dev/sde
brw-rw---- 1 root disk 8, 65 Nov 23 09:23 /dev/sde1
brw-rw---- 1 root disk 8, 80 Nov 23 09:23 /dev/sdf
brw-rw---- 1 root disk 8, 81 Nov 23 09:23 /dev/sdf1
brw-rw---- 1 root disk 8, 96 Nov 23 09:23 /dev/sdg
brw-rw---- 1 root disk 8, 97 Nov 23 09:23 /dev/sdg1

[root@looney ~]# ls -l /dev/oracleasm/disks/
total 0
brw-rw---- 1 oracle dba 8, 17 Nov 23 09:25 VENDOR101
brw-rw---- 1 oracle dba 8, 33 Nov 23 09:27 VENDOR102
brw-rw---- 1 oracle dba 8, 49 Nov 23 09:27 VENDOR103

[root@looney ~]# /etc/init.d/oracleasm listdisks
VENDOR101
VENDOR102
VENDOR103

[root@looney ~]# /etc/init.d/oracleasm querydisk -p VENDOR101
Disk "VENDOR101" is a valid ASM disk
/dev/sdb1: LABEL="VENDOR101" TYPE="oracleasm"

[root@looney ~]# /etc/init.d/oracleasm querydisk -p VENDOR102
Disk "VENDOR102" is a valid ASM disk
/dev/sdc1: LABEL="VENDOR102" TYPE="oracleasm"

[root@looney ~]# /etc/init.d/oracleasm querydisk -p VENDOR103
Disk "VENDOR103" is a valid ASM disk
/dev/sdd1: LABEL="VENDOR103" TYPE="oracleasm"

Create ASM disks for VENDOR201,VENDOR202,VENDOR203 using /dev/sde1,/dev/sdf1,/dev/sdg1

[root@looney ~]# /etc/init.d/oracleasm createdisk 'vendor201' /dev/sde1
Marking disk "vendor201" as an ASM disk:                   [  OK  ]

[root@looney ~]# /etc/init.d/oracleasm createdisk 'vendor202' /dev/sdf1
Marking disk "vendor202" as an ASM disk:                   [  OK  ]

[root@looney ~]# /etc/init.d/oracleasm createdisk 'vendor203' /dev/sdg1
Marking disk "vendor203" as an ASM disk:                   [  OK  ]
[root@looney ~]#

Perform ASM ONLINE Migration

Query ASM disks:

SYS@+ASM> @asmdisk

   INST_ID PATH                 DISK_NAME       GROUP_NUMBER GROUP_NAME      STATE      TYPE         TOTAL_GB    FREE_GB
---------- -------------------- --------------- ------------ --------------- ---------- ---------- ---------- ----------
         1 ORCL:VENDOR101       VENDOR101                  1 DATA1           MOUNTED    EXTERN          14.98      13.27
         1 ORCL:VENDOR102       VENDOR102                  1 DATA1           MOUNTED    EXTERN          14.98      13.27
         1 ORCL:VENDOR103       VENDOR103                  1 DATA1           MOUNTED    EXTERN          14.98      13.27
         1 ORCL:VENDOR201
         1 ORCL:VENDOR202
         1 ORCL:VENDOR203

6 rows selected.

Add new disks / Drop old disks from ASM diskgroup:

ASM power limit ranges from 1-11.
In the demonstration, power limit 1 was used to be able to query progress from v$asm_operation

SYS@+ASM> alter diskgroup DATA1
add disk  'ORCL:VENDOR201','ORCL:VENDOR202','ORCL:VENDOR203'
drop disk 'VENDOR101','VENDOR102','VENDOR103'
rebalance power 1;
  2    3    4

Diskgroup altered.

SYS@+ASM> SYS@+ASM> @asmop

GROUP_NUMBER OPERA STATE           POWER     ACTUAL      SOFAR   EST_WORK   EST_RATE EST_MINUTES ERROR_CODE
------------ ----- ---------- ---------- ---------- ---------- ---------- ---------- ----------- --------------------------------------------
           1 REBAL RUN                 1          1         58       1756         60          28

SYS@+ASM> r
  1* select * from v$asm_operation

GROUP_NUMBER OPERA STATE           POWER     ACTUAL      SOFAR   EST_WORK   EST_RATE EST_MINUTES ERROR_CODE
------------ ----- ---------- ---------- ---------- ---------- ---------- ---------- ----------- --------------------------------------------
           1 REBAL RUN                 1          1        178       1758        677           2

SYS@+ASM> r
  1* select * from v$asm_operation

GROUP_NUMBER OPERA STATE           POWER     ACTUAL      SOFAR   EST_WORK   EST_RATE EST_MINUTES ERROR_CODE
------------ ----- ---------- ---------- ---------- ---------- ---------- ---------- ----------- --------------------------------------------
           1 REBAL RUN                 1          1        298       1759        697           2

SYS@+ASM> r
  1* select * from v$asm_operation

no rows selected
SYS@+ASM> @asmdisk

   INST_ID PATH                 DISK_NAME       GROUP_NUMBER GROUP_NAME      STATE      TYPE         TOTAL_GB    FREE_GB
---------- -------------------- --------------- ------------ --------------- ---------- ---------- ---------- ----------
         1 ORCL:VENDOR101
         1 ORCL:VENDOR102
         1 ORCL:VENDOR103
         1 ORCL:VENDOR201       VENDOR201                  1 DATA1           MOUNTED    EXTERN          14.98      13.27
         1 ORCL:VENDOR202       VENDOR202                  1 DATA1           MOUNTED    EXTERN          14.98      13.27
         1 ORCL:VENDOR203       VENDOR203                  1 DATA1           MOUNTED    EXTERN          14.98      13.27

6 rows selected.

SYS@+ASM>

Delete ASM disks for /dev/sdb1, dev/sdd1, /dev/sdd1

[root@looney ~]# /etc/init.d/oracleasm deletedisk /dev/sdb1
Removing ASM disk "/dev/sdb1":                             [  OK  ]

[root@looney ~]# /etc/init.d/oracleasm deletedisk /dev/sdc1
Removing ASM disk "/dev/sdc1":                             [  OK  ]

[root@looney ~]# /etc/init.d/oracleasm deletedisk /dev/sdd1
Removing ASM disk "/dev/sdd1":                             [  OK  ]

[root@looney ~]# /etc/init.d/oracleasm scandisks
Scanning the system for Oracle ASMLib disks:               [  OK  ]

[root@looney ~]# /etc/init.d/oracleasm listdisks
VENDOR201
VENDOR202
VENDOR203

SYS@+ASM> @asmdisk

   INST_ID PATH                 DISK_NAME       GROUP_NUMBER GROUP_NAME      STATE      TYPE         TOTAL_GB    FREE_GB
---------- -------------------- --------------- ------------ --------------- ---------- ---------- ---------- ----------
         1 ORCL:VENDOR201       VENDOR201                  1 DATA1           MOUNTED    EXTERN          14.98      13.27
         1 ORCL:VENDOR202       VENDOR202                  1 DATA1           MOUNTED    EXTERN          14.98      13.27
         1 ORCL:VENDOR203       VENDOR203                  1 DATA1           MOUNTED    EXTERN          14.98      13.27

SYS@+ASM>

Would you used ASM for single instance database? Why or Why not?

Blog at WordPress.com.