grid@Linux211:[/u01/app/PATCHES/22191577] $ srvctl stop asm -n Linux211
PRCR-1014 : Failed to stop resource ora.asm
PRCR-1065 : Failed to stop resource ora.asm
CRS-2529: Unable to act on 'ora.asm' because that would require stopping or relocating 'ora.DATA.dg', but the force option was not specified
grid@Linux211:[/u01/app/PATCHES/22191577] $
This behaviour is very correct, you might have Oracle OCR and Votedisk on ASM since it is supported in Oracle 11.2.0.2. If OCR and vote disk are on ASM you could not stop ASM instance, it would not allow you because of resource dependencies in Oracle clusterware.
To stop Oracle RAC environment you need to execute only single command. It would stop database,nodeapps and cluterware.
#crsctl stop crs
Since database and all rest of resources like nodeapps, crsd, cssd ,evmd registered with Oracle clusterware (OCR) with proper dependecies.
crsctl stop crs command would take care of depencies and stop all reources sequentially.
Thursday, April 21, 2016
Tuesday, April 19, 2016
USING SELECT 'X' in query/subqueries.
USING SELECT 'X' in query/sub-queries.
--------------------------------------------------------
-- DDL for Table TAB1
--------------------------------------------------------
CREATE TABLE "ATEST"."TAB1"
( "ID" NUMBER,
"NAME" VARCHAR2(20 BYTE)
) ;
Insert into ATEST.TAB1 (ID,NAME) values (1,'AAA');
Insert into ATEST.TAB1 (ID,NAME) values (2,'BBB');
Insert into ATEST.TAB1 (ID,NAME) values (3,'EEE');
Insert into ATEST.TAB1 (ID,NAME) values (4,'FFF');
--------------------------------------------------------
-- DDL for Table TAB2
--------------------------------------------------------
CREATE TABLE "ATEST"."TAB2"
( "ID" NUMBER,
"NAME" VARCHAR2(20 BYTE)
) ;
Insert into ATEST.TAB2 (ID,NAME) values (1,'CCC');
Insert into ATEST.TAB2 (ID,NAME) values (2,'DDD');
Get records that exits in TAB1 and not in TAB2 using select 'X' :
select * from TAB1 f where not exists (select 'X' from TAB2 where id=f.id);
ID NAME
-- ----
4 FFF
3 EEE
IN the above query we get output of all the records from TAB1 that doesnt match with TAB2 ID's.
Hence we do not get the records with ID's 1 & 2 as they only exits in TAB1.
This is just like using "select * from TAB1 f where not exists (select ID from TAB2 where id=f.id);"
Get records that exits in TAB1 and in TAB2 using select 'X' :
select * from TAB1 f where exists (select 'X' from TAB2 where id=f.id);
ID NAME
-- ----
1 AAA
2 BBB
IN the above query we get output of all the records from TAB1 that exist with same ID in TAB2 .
Hence we get only records with ID 1 & 2 as they exists in both TABLES.
This is just like using "select * from TAB1 f where exists (select ID from TAB2 where id=f.id);"
Wednesday, April 6, 2016
RAC creating directory structure and owners
To create the directories on the local file system.
Assuming you have a "grid" user for Grid infrastructure maintenance.
-
# mkdir -p /u01/app/oracle # mkdir -p /u01/app/oracle/product/10.2.0/crshome # mkdir -p /u01/app/oracle/product/10.2.0/dbhome_1 # chown -R oracle:oinstall /u01/app # chmod -R 775 /u01/app
# mkdir -p /u01/app/gridbase # mkdir -p /u01/app/grid/product/11.2.0/gridhome # chown -R grid:oinstall /u01/app # chmod -R 775 /u01/app # mkdir -p /u01/app/oracle # mkdir -p /u01/app/oracle/product/11.2.0/dbhome_1 # chown -R oracle:oinstall /u01/app/oracle # chmod -R 775 /u01/app/oracle
Tuesday, April 5, 2016
Adding disks to ASM library
[root@rac1 Desktop]# cd /dev
[root@rac1 dev]# ls sd*
sda sda1 sda2 sdb sdb1 sdc sdc1 sdd sdd1 sde sde1 sdf sdg
[root@rac1 dev]# /usr/sbin/oracleasm listdisks
DISK1
DISK2
DISK3
DISK4
[root@rac1 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 0xd8725891.
Changes will remain in memory only, until you decide to write them.
After that, of course, the previous content won't be recoverable.
Warning: invalid flag 0x0000 of partition table 4 will be corrected by w(rite)
WARNING: DOS-compatible mode is deprecated. It's strongly recommended to
switch off the mode (command 'c') and change display units to
sectors (command 'u').
Command (m for help): n
Command action
e extended
p primary partition (1-4)
p
Partition number (1-4): 1
First cylinder (1-652, default 1):
Using default value 1
Last cylinder, +cylinders or +size{K,M,G} (1-652, default 652):
Using default value 652
Command (m for help): p
Disk /dev/sdf: 5368 MB, 5368709120 bytes
255 heads, 63 sectors/track, 652 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: 0xd8725891
Device Boot Start End Blocks Id System
/dev/sdf1 1 652 5237158+ 83 Linux
Command (m for help): w
The partition table has been altered!
Calling ioctl() to re-read partition table.
Syncing disks.
[root@rac1 dev]# ls sd*
sda sda1 sda2 sdb sdb1 sdc sdc1 sdd sdd1 sde sde1 sdf sdf1 sdg
[root@rac1 dev]# fdisk /dev/sdg
Device contains neither a valid DOS partition table, nor Sun, SGI or OSF disklabel
Building a new DOS disklabel with disk identifier 0x3ca5b760.
Changes will remain in memory only, until you decide to write them.
After that, of course, the previous content won't be recoverable.
Warning: invalid flag 0x0000 of partition table 4 will be corrected by w(rite)
WARNING: DOS-compatible mode is deprecated. It's strongly recommended to
switch off the mode (command 'c') and change display units to
sectors (command 'u').
Command (m for help): n
Command action
e extended
p primary partition (1-4)
p
Partition number (1-4): 1
First cylinder (1-652, default 1):
Using default value 1
Last cylinder, +cylinders or +size{K,M,G} (1-652, default 652):
Using default value 652
Command (m for help): p
Disk /dev/sdg: 5368 MB, 5368709120 bytes
255 heads, 63 sectors/track, 652 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: 0x3ca5b760
Device Boot Start End Blocks Id System
/dev/sdg1 1 652 5237158+ 83 Linux
Command (m for help): w
The partition table has been altered!
Calling ioctl() to re-read partition table.
Syncing disks.
[root@rac1 dev]# ls sd*
sda sda1 sda2 sdb sdb1 sdc sdc1 sdd sdd1 sde sde1 sdf sdf1 sdg sdg1
[root@rac1 dev]# /usr/sbin/oracleasm createdisk DISK5 /dev/sdf1
Writing disk header: done
Instantiating disk: done
[root@rac1 dev]# /usr/sbin/oracleasm createdisk DISK6 /dev/sdg1
Writing disk header: done
Instantiating disk: done
[root@rac1 dev]# /usr/sbin/oracleasm scandisks
Reloading disk partitions: done
Cleaning any stale ASM disks...
Scanning system for ASM disks...
[root@rac1 dev]# /usr/sbin/oracleasm listdisks
DISK1
DISK2
DISK3
DISK4
DISK5
DISK6
[root@rac1 dev]#
[root@rac1 dev]# ls sd*
sda sda1 sda2 sdb sdb1 sdc sdc1 sdd sdd1 sde sde1 sdf sdg
[root@rac1 dev]# /usr/sbin/oracleasm listdisks
DISK1
DISK2
DISK3
DISK4
[root@rac1 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 0xd8725891.
Changes will remain in memory only, until you decide to write them.
After that, of course, the previous content won't be recoverable.
Warning: invalid flag 0x0000 of partition table 4 will be corrected by w(rite)
WARNING: DOS-compatible mode is deprecated. It's strongly recommended to
switch off the mode (command 'c') and change display units to
sectors (command 'u').
Command (m for help): n
Command action
e extended
p primary partition (1-4)
p
Partition number (1-4): 1
First cylinder (1-652, default 1):
Using default value 1
Last cylinder, +cylinders or +size{K,M,G} (1-652, default 652):
Using default value 652
Command (m for help): p
Disk /dev/sdf: 5368 MB, 5368709120 bytes
255 heads, 63 sectors/track, 652 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: 0xd8725891
Device Boot Start End Blocks Id System
/dev/sdf1 1 652 5237158+ 83 Linux
Command (m for help): w
The partition table has been altered!
Calling ioctl() to re-read partition table.
Syncing disks.
[root@rac1 dev]# ls sd*
sda sda1 sda2 sdb sdb1 sdc sdc1 sdd sdd1 sde sde1 sdf sdf1 sdg
[root@rac1 dev]# fdisk /dev/sdg
Device contains neither a valid DOS partition table, nor Sun, SGI or OSF disklabel
Building a new DOS disklabel with disk identifier 0x3ca5b760.
Changes will remain in memory only, until you decide to write them.
After that, of course, the previous content won't be recoverable.
Warning: invalid flag 0x0000 of partition table 4 will be corrected by w(rite)
WARNING: DOS-compatible mode is deprecated. It's strongly recommended to
switch off the mode (command 'c') and change display units to
sectors (command 'u').
Command (m for help): n
Command action
e extended
p primary partition (1-4)
p
Partition number (1-4): 1
First cylinder (1-652, default 1):
Using default value 1
Last cylinder, +cylinders or +size{K,M,G} (1-652, default 652):
Using default value 652
Command (m for help): p
Disk /dev/sdg: 5368 MB, 5368709120 bytes
255 heads, 63 sectors/track, 652 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: 0x3ca5b760
Device Boot Start End Blocks Id System
/dev/sdg1 1 652 5237158+ 83 Linux
Command (m for help): w
The partition table has been altered!
Calling ioctl() to re-read partition table.
Syncing disks.
[root@rac1 dev]# ls sd*
sda sda1 sda2 sdb sdb1 sdc sdc1 sdd sdd1 sde sde1 sdf sdf1 sdg sdg1
[root@rac1 dev]# /usr/sbin/oracleasm createdisk DISK5 /dev/sdf1
Writing disk header: done
Instantiating disk: done
[root@rac1 dev]# /usr/sbin/oracleasm createdisk DISK6 /dev/sdg1
Writing disk header: done
Instantiating disk: done
[root@rac1 dev]# /usr/sbin/oracleasm scandisks
Reloading disk partitions: done
Cleaning any stale ASM disks...
Scanning system for ASM disks...
[root@rac1 dev]# /usr/sbin/oracleasm listdisks
DISK1
DISK2
DISK3
DISK4
DISK5
DISK6
[root@rac1 dev]#
Sunday, April 3, 2016
create shared disks (vdi) on Virtualbox for ASM - vboxmanage command line
vboxmanage command line
For our scenario :
Lets create shared disks (vdi) on Virtualbox for ASM setup.
If you are getting error like below make sure you have virtual-box set in your environmental path:
In my case I have to add "C:\Program Files\Oracle\VirtualBox\" to my path. To add it to your environmental path goto :
Startup >> computer (right click) >> properties >> advanced system settings >> environmental variables >>
Add your virtual box installed path, usually c\programe files \ oracle
Now test the vboxmanage cmd :
You will get something like this if you have any virtual machines created.
Microsoft Windows [Version 6.1.7601]
Copyright (c) 2009 Microsoft Corporation. All rights reserved.
C:\Users\arvind>vboxmanage list vms
"linux1" {69b544d2-bf64-4f83-a525-a7f2dd2007e7}
Now lets create a shared asm drive for ASM :
Note : Always pick a different location, but make sure they are outside the existing VM directory.
$ mkdir C:/VirtualBox/
$ cd C:/VirtualBox/
$
$ # Create the disks and associate them with VirtualBox as virtual media.
VBoxManage createhd --filename asm1.vdi --size 5120 --format VDI --variant Fixed
C:\Virtualdisks>VBoxManage createhd --filename asm1.vdi --size 5120 --format VDI
--variant Fixed
0%...10%...20%...30%...40%...50%...60%...70%...80%...90%...100%
Disk image created. UUID: c8a5ef46-7304-4ea0-b685-e1efbf6ab41e
$ # Connect them to the VM.
VBoxManage storageattach linux1 --storagectl "SATA" --port 1 --device 0 --type hdd --medium asm1.vdi --mtype shareable
$ # Make shareable.
$ VBoxManage modifyhd asm1.vdi --type shareable
We now have shared disks added to Virtual box.
We can also add these shared disks via Graphical interface :
See below for instructions:
Step 1 :
Select >> add hard disks >>
Step 2:
select vdi
Step 3
select fixed size
Step 4
it will be added now.
Step 5
select (virtual box top left) File >> virtual Media manager >> select disk you want to modify >> right click >> click modify
Step 6
Select modify and close.
Note :
If you are going to use this for RAC setup. make sure you attach this shared disks to both of your environments.
For our scenario :
Lets create shared disks (vdi) on Virtualbox for ASM setup.
If you are getting error like below make sure you have virtual-box set in your environmental path:
In my case I have to add "C:\Program Files\Oracle\VirtualBox\" to my path. To add it to your environmental path goto :
Startup >> computer (right click) >> properties >> advanced system settings >> environmental variables >>
- Click on start
- Right click on Computer
- Click Properties
- Click Advanced system settings
- Click Environment Variables
- Go to system variables
- find Path and edit it
- add your file location at the end of the box.
Add your virtual box installed path, usually c\programe files \ oracle
Now test the vboxmanage cmd :
You will get something like this if you have any virtual machines created.
Microsoft Windows [Version 6.1.7601]
Copyright (c) 2009 Microsoft Corporation. All rights reserved.
C:\Users\arvind>vboxmanage list vms
"linux1" {69b544d2-bf64-4f83-a525-a7f2dd2007e7}
Now lets create a shared asm drive for ASM :
Note : Always pick a different location, but make sure they are outside the existing VM directory.
$ mkdir C:/VirtualBox/
$ cd C:/VirtualBox/
$
$ # Create the disks and associate them with VirtualBox as virtual media.
VBoxManage createhd --filename asm1.vdi --size 5120 --format VDI --variant Fixed
C:\Virtualdisks>VBoxManage createhd --filename asm1.vdi --size 5120 --format VDI
--variant Fixed
0%...10%...20%...30%...40%...50%...60%...70%...80%...90%...100%
Disk image created. UUID: c8a5ef46-7304-4ea0-b685-e1efbf6ab41e
$ # Connect them to the VM.
VBoxManage storageattach linux1 --storagectl "SATA" --port 1 --device 0 --type hdd --medium asm1.vdi --mtype shareable
$ # Make shareable.
$ VBoxManage modifyhd asm1.vdi --type shareable
We now have shared disks added to Virtual box.
We can also add these shared disks via Graphical interface :
See below for instructions:
Step 1 :
Select >> add hard disks >>
Step 2:
select vdi
Step 3
select fixed size
Step 4
it will be added now.
Step 5
select (virtual box top left) File >> virtual Media manager >> select disk you want to modify >> right click >> click modify
Step 6
Select modify and close.
Note :
If you are going to use this for RAC setup. make sure you attach this shared disks to both of your environments.
Friday, April 1, 2016
sql for first day of month and last day of month
select SYSDATE ,
last_day(sysdate) as LAST_DATE_CURR_MNTH,
ADD_MONTHS(last_day(sysdate),-1) as PREVIOUS_MON_LAST_DATE,
last_day(sysdate)+1 as NEXT_MON_FIRST_DATE,
ADD_MONTHS(last_day(sysdate),+1) as NEXT_MON_LAST_DATE,
ADD_MONTHS(last_day(sysdate),+5) as LAST_DATE_OF_5TH_MON,
ADD_MONTHS(last_day(sysdate),+5) +1 as FIRST_DATE_IN_6TH_MON_AFTR_NOW
from dual;
"SYSDATE" "LAST_DATE_CURR_MNTH" "PREVIOUS_MON_LAST_DATE" "NEXT_MON_FIRST_DATE" "NEXT_MON_LAST_DATE" "LAST_DATE_OF_5TH_MON"
----------- --------------------- ------------------------ --------------------- -------------------- -----------------------
"FIRST_DATE_IN_6TH_MON_AFTR_NOW"
-----------------------
01-APR-16 30-APR-16 31-MAR-16 01-MAY-16 31-MAY-16 30-SEP-16
-----------------------
01-OCT-16
CHANGE STANDBY DATABASE PROTECTION MODE
SQL> select protection_mode from v$database;
PROTECTION_MODE
--------------------
MAXIMUM PERFORMANCE
SQL> show parameter log_archive_dest_2
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2 string SERVICE=ORCLSTB1 NOAFFIRM ASYN
C VALID_FOR=(ONLINE_LOGFILES,P
RIMARY_ROLE) DB_UNIQUE_NAME=OR
CLSTB1
log_archive_dest_20 string
log_archive_dest_21 string
log_archive_dest_22 string
log_archive_dest_23 string
log_archive_dest_24 string
log_archive_dest_25 string
log_archive_dest_26 string
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_27 string
log_archive_dest_28 string
log_archive_dest_29 string
SQL> show parameter db_unique_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_unique_name string ORCL
SQL> show parameter log_archive_config
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_config string dg_config=(ORCL,ORCLSTB1,ORCLS
TB2)
SQL> alter system set log_archive_dest_2='SERVICE=ORCLSTB1 NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORCLSTB1';
System altered.
SQL> show parameter log_archive_dest_2
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2 string SERVICE=ORCLSTB1 NOAFFIRM ASYN
C VALID_FOR=(ONLINE_LOGFILES,P
RIMARY_ROLE) DB_UNIQUE_NAME=OR
CLSTB1
log_archive_dest_20 string
log_archive_dest_21 string
log_archive_dest_22 string
log_archive_dest_23 string
log_archive_dest_24 string
log_archive_dest_25 string
log_archive_dest_26 string
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_27 string
log_archive_dest_28 string
log_archive_dest_29 string
SQL> alter system set log_archive_dest_2='SERVICE=ORCLSTB1 NOAFFIRM SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORCLSTB1';
System altered.
SQL> alter database set standby database to maximize availability;
Database altered.
NOTE: You don’t need to shutdown your instance, when you are changing protection mode from MAXIMUM PERFORMANCE TO MAXIMUM AVAILABILITY.But you need to if you are going to MAXIMUM PROTECTION.
SQL> alter system switch logfile;
System altered.
SQL> select protection_mode from v$database;
PROTECTION_MODE
--------------------
MAXIMUM AVAILABILITY
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/oraarch/
Oldest online log sequence 239
Next log sequence to archive 241
Current log sequence 241
SQL> select group#,bytes/1024/1024 from v$standby_log;
GROUP# BYTES/1024/1024
---------- ---------------
4 52
5 52
6 52
7 52
SQL> select thread#,max(sequence#) from v$archived_log group by thread#;
THREAD# MAX(SEQUENCE#)
---------- --------------
1 240
SQL> select protection_mode from v$database;
PROTECTION_MODE
--------------------
MAXIMUM AVAILABILITY
SQL> alter system switch logfile;
System altered.
PROTECTION_MODE
--------------------
MAXIMUM PERFORMANCE
SQL> show parameter log_archive_dest_2
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2 string SERVICE=ORCLSTB1 NOAFFIRM ASYN
C VALID_FOR=(ONLINE_LOGFILES,P
RIMARY_ROLE) DB_UNIQUE_NAME=OR
CLSTB1
log_archive_dest_20 string
log_archive_dest_21 string
log_archive_dest_22 string
log_archive_dest_23 string
log_archive_dest_24 string
log_archive_dest_25 string
log_archive_dest_26 string
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_27 string
log_archive_dest_28 string
log_archive_dest_29 string
SQL> show parameter db_unique_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_unique_name string ORCL
SQL> show parameter log_archive_config
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_config string dg_config=(ORCL,ORCLSTB1,ORCLS
TB2)
SQL> alter system set log_archive_dest_2='SERVICE=ORCLSTB1 NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORCLSTB1';
System altered.
SQL> show parameter log_archive_dest_2
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2 string SERVICE=ORCLSTB1 NOAFFIRM ASYN
C VALID_FOR=(ONLINE_LOGFILES,P
RIMARY_ROLE) DB_UNIQUE_NAME=OR
CLSTB1
log_archive_dest_20 string
log_archive_dest_21 string
log_archive_dest_22 string
log_archive_dest_23 string
log_archive_dest_24 string
log_archive_dest_25 string
log_archive_dest_26 string
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_27 string
log_archive_dest_28 string
log_archive_dest_29 string
SQL> alter system set log_archive_dest_2='SERVICE=ORCLSTB1 NOAFFIRM SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORCLSTB1';
System altered.
SQL> alter database set standby database to maximize availability;
Database altered.
NOTE: You don’t need to shutdown your instance, when you are changing protection mode from MAXIMUM PERFORMANCE TO MAXIMUM AVAILABILITY.But you need to if you are going to MAXIMUM PROTECTION.
SQL> alter system switch logfile;
System altered.
SQL> select protection_mode from v$database;
PROTECTION_MODE
--------------------
MAXIMUM AVAILABILITY
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/oraarch/
Oldest online log sequence 239
Next log sequence to archive 241
Current log sequence 241
SQL> select group#,bytes/1024/1024 from v$standby_log;
GROUP# BYTES/1024/1024
---------- ---------------
4 52
5 52
6 52
7 52
SQL> select thread#,max(sequence#) from v$archived_log group by thread#;
THREAD# MAX(SEQUENCE#)
---------- --------------
1 240
SQL> select protection_mode from v$database;
PROTECTION_MODE
--------------------
MAXIMUM AVAILABILITY
SQL> alter system switch logfile;
System altered.
Subscribe to:
Posts (Atom)