Menu Bar

Thursday, September 21, 2023

1) 1 MB table got dropped from 40TB of database , how do you recover it ?

 https://docs.oracle.com/en/database/oracle/oracle-database/tutorial-rec-flashback/index.html?opt-release-19c?learningpath=true&appuser=nobody&appsession=9331452079823&contentid=26524&activityname=Recover%20a%20Dropped%20Table%20Using%20Oracle%20Flashback%20Drop&eventid=6362

Recover a Dropped Table Using Oracle Flashback Drop

Background

Oracle Flashback Drop enables you to reverse the effects of dropping (deleting) a table, returning the dropped table to the database along with dependent objects such as indexes and triggers. This feature stores dropped objects in a recycle bin, from which they can be retrieved until the recycle bin is purged, either explicitly or because space is needed.


Create a New Table

In this section you create a new table.

  1. Invoke SQL*Plus and connect as the SYSTEM user.
  2. Alter session to pluggable database container orclpdb.
    # SQL>  alter session set container=orclpdb;

Session altered.
  3. Create a new table named HR.REGIONS_HIST. Use the following statement to create a table that has the same structure and content as the HR.REGIONS table.
    SQL>create table hr.regions_hist as select * from hr.regions; 

Table created.
  4. Query the new HR.REGIONS_HIST table.
    SQL>select * from hr.regions_hist;

 REGION_ID REGION_NAME
---------- -------------------------
         1 Europe
         2 Americas
         3 Asia
4 Middle East and Africa

section 2Drop a Table

In this section you drop your new HR.REGIONS_HIST table.

  1. Execute the DROP TABLE command to drop the table.
    SQL>drop table hr.regions_hist;

Table dropped.
  2. Query the HR.REGIONS_HIST table.
    SQL>select * from hr.regions_hist;
select * from hr.regions_hist
                 *
ERROR at line 1:
ORA-00942: table or view does not exist

    Notice that you get an error because the table is dropped.

section 3Recover the Dropped Table

In this section you recover the HR.REGIONS_HIST table from the recycle bin.

  1. Execute the FLASHBACK TABLE command to recover the dropped table.
    SQL>flashback table hr.regions_hist to before drop;

Flashback complete.
  2. Once again query the HR.REGIONS_HIST table to verify the data has been restored.
    SQL>select * from hr.regions_hist;

 REGION_ID REGION_NAME
---------- -------------------------
         1 Europe
         2 Americas
         3 Asia
4 Middle East and Africa
  3. Exit from SQL*Plus.
on No comments:

Friday, September 15, 2023

Install OEL 6.5

 Install OEL 6.5

https://edelivery.oracle.com/osdc/faces/SoftwareDelivery




































In the Oracle Virtual Box --> New





Give the name of the Machine, Type - Linux --> Version Oracle 64 bit










Allocate 4 GB memory to machine 










Next select the default option









Select the default option









Select default option









Allocate 80 GB storage










Click on create . Once the machine is created-> Right click and select Settings






Click on System -> Uncheck floppy disk and move it down the order






Next Click on Storge -> select empty 























Next Choose Network 







In RAC , we need to have 2 Network Adapters , 1 for Public and 1 for Private Interconnect 


MAke sure Adapter 1 is set to Bridged Adapter, to allow to connect to Internet












Once you've done all the setting Clcik on Start 
















Click Inside Vid and Hit enter to [Install or Upgrade an existing system ]


Use Arrow Keys to Skip the Disk Media Test and  hit enter


















select default language English








Basic Storage
















Give the name to machine


















We have 2 Network Cards 










Choose eth0 -> Click on Edit 


Check Connect Automatically , Go to IPV4 settings










We have to manually allocate IP address

Check your host systems details with ipconfig





Keep the Public IP Address in the same subnet as youtr wi-fi Ip address 192.168.1 subnet










Provide IP address for Private Network, as we don't need to communiate to internet with Private IP, no need for Gatewat detail










Clikc on , Click next , Set root password









Now , we'll create partitions on the disk 









This is our 80GB hardisk that we allocated









select it and click on create 























Next swap of 15gb 










Allocate remaining space to root mount 











Click Next 









Click Format










Click write changes to disk 







Levae all defaults, click next










Click Customize Now











Base System: 

• Base • Client management tools • Compatibility libraries • Hardware monitoring utilities • Large Systems Performance • Network file system client • Performance Tools • Perl Support 

Servers: • Server Platform • System administration tools • Desktops • Desktop • Desktop Platform • Fonts • General Purpose Desktop • Graphical Administration Tools • Input Methods • X Window System Development: • Additional Development • Development Tools

 Applications: • Internet Browser















Click Reboot









Editing Network Settings after Installation 











================================
Install yum repos  

yum -y install oracle-rdbms* 






[root@11gRacNode1 ~]# yum -y install oracle-rdbms*

Loaded plugins: langpacks, ulninfo

Resolving Dependencies

--> Running transaction check

---> Package oracle-rdbms-server-11gR2-preinstall.x86_64 0:1.0-6.el7 will be installed

--> Processing Dependency: ksh for package: oracle-rdbms-server-11gR2-preinstall-1.0-6.el7.x86_64

--> Processing Dependency: libaio-devel for package: oracle-rdbms-server-11gR2-preinstall-1.0-6.el7.x86_64

---> Package oracle-rdbms-server-12cR1-preinstall.x86_64 0:1.0-7.el7 will be installed

--> Running transaction check

---> Package ksh.x86_64 0:20120801-144.0.1.el7_9 will be installed

---> Package libaio-devel.x86_64 0:0.3.109-13.el7 will be installed

--> Finished Dependency Resolution


Dependencies Resolved


===============================================================================================================================================

 Package                                            Arch                 Version                                Repository                Size

===============================================================================================================================================

Installing:

 oracle-rdbms-server-11gR2-preinstall               x86_64               1.0-6.el7                              ol7_latest                22 k

 oracle-rdbms-server-12cR1-preinstall               x86_64               1.0-7.el7                              ol7_latest                21 k

Installing for dependencies:

 ksh                                                x86_64               20120801-144.0.1.el7_9                 ol7_latest               882 k

 libaio-devel                                       x86_64               0.3.109-13.el7                         ol7_latest                12 k


Transaction Summary

===============================================================================================================================================

Install  2 Packages (+2 Dependent packages)


Total download size: 937 k

Installed size: 3.2 M

Downloading packages:

warning: /var/cache/yum/x86_64/7Server/ol7_latest/packages/libaio-devel-0.3.109-13.el7.x86_64.rpm: Header V3 RSA/SHA256 Signature, key ID ec551f03: NOKEY

Public key for libaio-devel-0.3.109-13.el7.x86_64.rpm is not installed

(1/4): libaio-devel-0.3.109-13.el7.x86_64.rpm                                                                           |  12 kB  00:00:01

(2/4): oracle-rdbms-server-11gR2-preinstall-1.0-6.el7.x86_64.rpm                                                        |  22 kB  00:00:00

(3/4): oracle-rdbms-server-12cR1-preinstall-1.0-7.el7.x86_64.rpm                                                        |  21 kB  00:00:00

(4/4): ksh-20120801-144.0.1.el7_9.x86_64.rpm                                                                            | 882 kB  00:00:01

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

Total                                                                                                          613 kB/s | 937 kB  00:00:01

Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-oracle

Importing GPG key 0xEC551F03:

 Userid     : "Oracle OSS group (Open Source Software group) <build@oss.oracle.com>"

 Fingerprint: 4214 4123 fecf c55b 9086 313d 72f9 7b74 ec55 1f03

 Package    : 7:oraclelinux-release-7.9-1.0.9.el7.x86_64 (@anaconda/7.9)

 From       : /etc/pki/rpm-gpg/RPM-GPG-KEY-oracle

Running transaction check

Running transaction test

Transaction test succeeded

Running transaction

  Installing : ksh-20120801-144.0.1.el7_9.x86_64                                                                                           1/4

  Installing : libaio-devel-0.3.109-13.el7.x86_64                                                                                          2/4

  Installing : oracle-rdbms-server-11gR2-preinstall-1.0-6.el7.x86_64                                                                       3/4

  Installing : oracle-rdbms-server-12cR1-preinstall-1.0-7.el7.x86_64                                                                       4/4

  Verifying  : libaio-devel-0.3.109-13.el7.x86_64                                                                                          1/4

  Verifying  : ksh-20120801-144.0.1.el7_9.x86_64                                                                                           2/4

  Verifying  : oracle-rdbms-server-11gR2-preinstall-1.0-6.el7.x86_64                                                                       3/4

  Verifying  : oracle-rdbms-server-12cR1-preinstall-1.0-7.el7.x86_64                                                                       4/4


Installed:

  oracle-rdbms-server-11gR2-preinstall.x86_64 0:1.0-6.el7                oracle-rdbms-server-12cR1-preinstall.x86_64 0:1.0-7.el7


Dependency Installed:

  ksh.x86_64 0:20120801-144.0.1.el7_9                                   libaio-devel.x86_64 0:0.3.109-13.el7


Complete!

[root@11gRacNode1 ~]#

[root@11gRacNode1 ~]#



=====================









[root@11gRacNode1 ~]# yum -y install oracleasm*
Loaded plugins: langpacks, ulninfo
Resolving Dependencies
--> Running transaction check
---> Package oracleasm-support.x86_64 0:2.1.11-2.el7 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

===============================================================================================================================================
 Package                                Arch                        Version                              Repository                       Size
===============================================================================================================================================
Installing:
 oracleasm-support                      x86_64                      2.1.11-2.el7                         ol7_latest                       85 k

Transaction Summary
===============================================================================================================================================
Install  1 Package

Total download size: 85 k
Installed size: 266 k
Downloading packages:
oracleasm-support-2.1.11-2.el7.x86_64.rpm                                                                               |  85 kB  00:00:00
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : oracleasm-support-2.1.11-2.el7.x86_64                                                                                       1/1
Note: Forwarding request to 'systemctl enable oracleasm.service'.
Created symlink from /etc/systemd/system/multi-user.target.wants/oracleasm.service to /usr/lib/systemd/system/oracleasm.service.
  Verifying  : oracleasm-support-2.1.11-2.el7.x86_64                                                                                       1/1

Installed:
  oracleasm-support.x86_64 0:2.1.11-2.el7

Complete!
[root@11gRacNode1 ~]#


[root@11gRacNode1 ~]# yum -y install kmod-oracleasm
Loaded plugins: langpacks, ulninfo
ol7_UEKR6                                                                                                               | 3.0 kB  00:00:00
ol7_latest                                                                                                              | 3.6 kB  00:00:00
Resolving Dependencies
--> Running transaction check
---> Package kmod-oracleasm.x86_64 0:2.0.8-28.0.1.el7 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

===============================================================================================================================================
 Package                             Arch                        Version                                 Repository                       Size
===============================================================================================================================================
Installing:
 kmod-oracleasm                      x86_64                      2.0.8-28.0.1.el7                        ol7_latest                      298 k

Transaction Summary
===============================================================================================================================================
Install  1 Package

Total download size: 298 k
Installed size: 1.5 M
Downloading packages:
kmod-oracleasm-2.0.8-28.0.1.el7.x86_64.rpm                                                                              | 298 kB  00:00:00
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : kmod-oracleasm-2.0.8-28.0.1.el7.x86_64                                                                                      1/1
  Verifying  : kmod-oracleasm-2.0.8-28.0.1.el7.x86_64                                                                                      1/1

Installed:
  kmod-oracleasm.x86_64 0:2.0.8-28.0.1.el7

Complete!
[root@11gRacNode1 ~]#


Disable firewall
systemctl status firewalld
systemctl stop firewalld
systemctl disable firewalld



Add entry in /etc/hosts files

# Public Network	- (eth0)
192.168.1.201	11gRacNode1.abhijit.com	11gRacNode1
192.168.1.202	11gRacNode2.abhijit.com	11gRacNode2

# Private Interconnect - (eth1)
10.10.0.2	11gRacNode1-priv.abhijit.com	11gRacNode1-priv
10.10.0.4	11gRacNode2-priv.abhijit.com	11gRacNode2-priv

# Public Virtual	IP (VIP) addresses - (eth0:1)	
192.168.1.205	11gRacNode1-vip.abhijit.com	11gRacNode1-vip
192.168.1.206	11gRacNode2-vip.abhijit.com	11gRacNode2-vip

# Single Client Access Name (SCAN)	
192.168.1.110	11gRacNode-scan.abhijit.com	11gRacNode-scan
























































[root@11gRacNode1 ~]# fdisk /dev/sdb
Welcome to fdisk (util-linux 2.23.2).

Changes will remain in memory only, until you decide to write them.
Be careful before using the write command.

Device does not contain a recognized partition table
Building a new DOS disklabel with disk identifier 0xac9e1e3c.

Command (m for help): n
Partition type:
   p   primary (0 primary, 0 extended, 4 free)
   e   extended
Select (default p): p
Partition number (1-4, default 1):
First sector (2048-104857599, default 2048):
Using default value 2048
Last sector, +sectors or +size{K,M,G} (2048-104857599, default 104857599): +10G
Partition 1 of type Linux and of size 10 GiB is set

Command (m for help): n
Partition type:
   p   primary (1 primary, 0 extended, 3 free)
   e   extended
Select (default p): p
Partition number (2-4, default 2):
First sector (20973568-104857599, default 20973568):
Using default value 20973568
Last sector, +sectors or +size{K,M,G} (20973568-104857599, default 104857599): +20G
Partition 2 of type Linux and of size 20 GiB is set

Command (m for help): n
Partition type:
   p   primary (2 primary, 0 extended, 2 free)
   e   extended
Select (default p): p
Partition number (3,4, default 3):
First sector (62916608-104857599, default 62916608):
Using default value 62916608
Last sector, +sectors or +size{K,M,G} (62916608-104857599, default 104857599): +20G
Value out of range.
Last sector, +sectors or +size{K,M,G} (62916608-104857599, default 104857599):
Using default value 104857599
Partition 3 of type Linux and of size 20 GiB is set

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

Calling ioctl() to re-read partition table.
Syncing disks.
[root@11gRacNode1 ~]#


[root@11gRacNode1 ~]# fdisk -l

Disk /dev/sda: 42.9 GB, 42949672960 bytes, 83886080 sectors
Units = sectors of 1 * 512 = 512 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk label type: dos
Disk identifier: 0x00028928

   Device Boot      Start         End      Blocks   Id  System
/dev/sda1   *        2048     1050623      524288   83  Linux
/dev/sda2         1050624    67502079    33225728   83  Linux
/dev/sda3        67502080    83886079     8192000   82  Linux swap / Solaris

Disk /dev/sdb: 53.7 GB, 53687091200 bytes, 104857600 sectors
Units = sectors of 1 * 512 = 512 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk label type: dos
Disk identifier: 0xac9e1e3c

   Device Boot      Start         End      Blocks   Id  System
/dev/sdb1            2048    20973567    10485760   83  Linux
/dev/sdb2        20973568    62916607    20971520   83  Linux
/dev/sdb3        62916608   104857599    20970496   83  Linux
[root@11gRacNode1 ~]#


[root@11gRacNode1 ~]# lsblk
NAME   MAJ:MIN RM  SIZE RO TYPE MOUNTPOINT
sdb      8:16   0   50G  0 disk
├─sdb2   8:18   0   20G  0 part
├─sdb3   8:19   0   20G  0 part
└─sdb1   8:17   0   10G  0 part
sr0     11:0    1 1024M  0 rom
sda      8:0    0   40G  0 disk
├─sda2   8:2    0 31.7G  0 part /
├─sda3   8:3    0  7.8G  0 part [SWAP]
└─sda1   8:1    0  512M  0 part /boot
[root@11gRacNode1 ~]#


Configure ASM on both nodes

On Node 1 as root user:
=======================
/usr/sbin/oracleasm configure -i

[root@11gRacNode1 ~]# id oracle
uid=54321(oracle) gid=54321(oinstall) groups=54321(oinstall),54322(dba)
[root@11gRacNode1 ~]# /usr/sbin/oracleasm configure -i
Configuring the Oracle ASM library driver.

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

Default user to own the driver interface []: oracle
Default group to own the driver interface []: dba
Start Oracle ASM library driver on boot (y/n) [n]: y
Scan for Oracle ASM disks on boot (y/n) [y]: y
Writing Oracle ASM library driver configuration: done
[root@11gRacNode1 ~]#





On Node 2 [root@11gRacNode2 ~]#
[root@11gRacNode2 ~]# /usr/sbin/oracleasm configure -i
Configuring the Oracle ASM library driver.

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

Default user to own the driver interface []: oracle
Default group to own the driver interface []: dba
Start Oracle ASM library driver on boot (y/n) [n]: y
Scan for Oracle ASM disks on boot (y/n) [y]: y
Writing Oracle ASM library driver configuration: done
[root@11gRacNode2 ~]#

[root@11gRacNode1 ~]# /usr/sbin/oracleasm init
Creating /dev/oracleasm mount point: /dev/oracleasm
Loading module "oracleasm": oracleasm
Configuring "oracleasm" to use device physical block size
Mounting ASMlib driver filesystem: /dev/oracleasm
[root@11gRacNode1 ~]#

[root@11gRacNode2 ~]# /usr/sbin/oracleasm init
Creating /dev/oracleasm mount point: /dev/oracleasm
Loading module "oracleasm": oracleasm
Configuring "oracleasm" to use device physical block size
Mounting ASMlib driver filesystem: /dev/oracleasm
[root@11gRacNode2 ~]#

[root@11gRacNode1 ~]# oracleasm createdisk CRS1 /dev/sdb1
Writing disk header: done
Instantiating disk: done
[root@11gRacNode1 ~]# oracleasm createdisk DATA1 /dev/sdb2
Writing disk header: done
Instantiating disk: done
[root@11gRacNode1 ~]#
[root@11gRacNode1 ~]# oracleasm createdisk RECO1 /dev/sdb3
Writing disk header: done
Instantiating disk: done
[root@11gRacNode1 ~]#

[root@11gRacNode1 ~]# oracleasm scandisks
Reloading disk partitions: done
Cleaning any stale ASM disks...
Scanning system for ASM disks...
[root@11gRacNode1 ~]#
[root@11gRacNode1 ~]#
[root@11gRacNode1 ~]# oracleasm listdisks
CRS1
DATA1
RECO1
[root@11gRacNode1 ~]#
[root@11gRacNode2 ~]# oracleasm scandisks
Reloading disk partitions: done
Cleaning any stale ASM disks...
Scanning system for ASM disks...
Instantiating disk "CRS1"
Instantiating disk "DATA1"
Instantiating disk "RECO1"
[root@11gRacNode2 ~]# oracleasm listdisks
CRS1
DATA1
RECO1
[root@11gRacNode2 ~]#
On Node 1 and Node 2:

=====================
#	yum install ntp
#	service ntpd start
#	chkconfig ntpd on

# vi /etc/sysconfig/ntpd	-> Open file delete all and put below line

OPTIONS="-x -u ntp:ntp -p /var/run/ntpd.pid -g"

#	service ntpd stop
#	ntpdate 1.tw.pool.ntp.org
#	service ntpd start===================================================================
[root@11gRacNode1 u02]# vi  /etc/sysconfig/ntpd
[root@11gRacNode1 u02]#
[root@11gRacNode1 u02]#
[root@11gRacNode1 u02]# service ntpd stop
Redirecting to /bin/systemctl stop ntpd.service
[root@11gRacNode1 u02]# ntpdate 1.tw.pool.ntp.org
19 Sep 12:51:45 ntpdate[3959]: adjust time server 111.235.248.121 offset 0.096665 sec
[root@11gRacNode1 u02]# service ntpd start
Redirecting to /bin/systemctl start ntpd.service
[root@11gRacNode1 u02]#



Set Bash Profile

Configure SSH Between Nodes
On node 1 as oracle user:
=========================
cd /u02
unzip <grid_software_file_name>

cd grid/sshsetup

./sshUserSetup.sh -user oracle -hosts "oraracn1 oraracn2" -noPromptPassphrase -confir m -advanced

./runInstaller


































 



CAUSE
There is a known issue where OL7 expects to use systemd than initd for running processes and restarting them and root.sh does not handle this currently.

This was reported in the following Unpublished Bug

Bug 18370031 – RC SCRIPTS (/ETC/RC.D/RC.*, /ETC/INIT.D/*) ON OL7 FOR CLUSTERWARE

SOLUTION
Because Oracle Linux 7 (and Redhat 7) use systemd than initd for starting / restarting processes and runs them as a service the current software install of both 11.2.0.4 & 12.1.0.1 will not succeed because the ohasd process does not start properly.

In OL7 it needs to be set up as a service so the following step is required BEFORE you run root.sh when prompted

1. As ROOT user

cd /etc/systemd/system /
touch ohasd.service

Nb this should create a file called ohasd.service

2. Edit the file ohasd.service and add the following text

[Unit]
DescriptioN = Oracle High Availability Services
After = syslog.target

[Service]
ExecStart = /etc/init.d/init.ohasd run > /dev/null 2>&1 Type=simple
Restart = always

[Install]
WantedBy = multi-user.target

3. Run the following commands (as root):

systemctl daemon-reload
systemctl enable ohasd.service
systemctl start ohasd.service

4. Now run root.sh from the Grid $ OH and it should succeed

REFERENCES
The NOTE: 1,951,613.1 – the Installation Walk-through – the Oracle the Grid / ON the RAC 11.2.0.4 the Linux the Oracle. 7
The NOTE: 1092213.1 – the ASM 11.2 the Configuration KIT (the ASM & the Configuration 11gR2 the Installation, Deinstallation, Upgrade, the ASM the Job Role Separation (Standalone Only).

































































=====
[root@oraclelab1 ~]# lsblk
NAME        MAJ:MIN RM SIZE RO TYPE MOUNTPOINT
sdb           8:16   0  50G  0 disk
sda           8:0    0  50G  0 disk
├─sda2        8:2    0  49G  0 part
│ ├─ol-swap 252:1    0  15G  0 lvm  [SWAP]
│ └─ol-root 252:0    0  34G  0 lvm  /
└─sda1        8:1    0   1G  0 part /boot
[root@oraclelab1 ~]#
[root@oraclelab1 ~]#
[root@oraclelab1 ~]#


[root@oraclelab1 ~]# fdisk /dev/sdb
Welcome to fdisk (util-linux 2.23.2).

Changes will remain in memory only, until you decide to write them.
Be careful before using the write command.

Device does not contain a recognized partition table
Building a new DOS disklabel with disk identifier 0xe02368a0.

Command (m for help): n
Partition type:
   p   primary (0 primary, 0 extended, 4 free)
   e   extended
Select (default p): p
Partition number (1-4, default 1): 1
First sector (2048-104857599, default 2048):
Using default value 2048
Last sector, +sectors or +size{K,M,G} (2048-104857599, default 104857599): +4G
Partition 1 of type Linux and of size 4 GiB is set

Command (m for help): n
Partition type:
   p   primary (1 primary, 0 extended, 3 free)
   e   extended
Select (default p): p
Partition number (2-4, default 2):
First sector (8390656-104857599, default 8390656):
Using default value 8390656
Last sector, +sectors or +size{K,M,G} (8390656-104857599, default 104857599): +23G
Partition 2 of type Linux and of size 23 GiB is set

Command (m for help): n
Partition type:
   p   primary (2 primary, 0 extended, 2 free)
   e   extended
Select (default p): p
Partition number (3,4, default 3):
First sector (56625152-104857599, default 56625152):
Using default value 56625152
Last sector, +sectors or +size{K,M,G} (56625152-104857599, default 104857599):
Using default value 104857599
Partition 3 of type Linux and of size 23 GiB is set

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

Calling ioctl() to re-read partition table.
Syncing disks.
[root@oraclelab1 ~]#
[root@oraclelab1 ~]# lsblk
NAME        MAJ:MIN RM SIZE RO TYPE MOUNTPOINT
sdb           8:16   0  50G  0 disk
├─sdb2        8:18   0  23G  0 part
├─sdb3        8:19   0  23G  0 part
└─sdb1        8:17   0   4G  0 part
sda           8:0    0  50G  0 disk
├─sda2        8:2    0  49G  0 part
│ ├─ol-swap 252:1    0  15G  0 lvm  [SWAP]
│ └─ol-root 252:0    0  34G  0 lvm  /
└─sda1        8:1    0   1G  0 part /boot
[root@oraclelab1 ~]#


[root@oraclelab1 ~]# oracleasm configure -i
Configuring the Oracle ASM library driver.

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

Default user to own the driver interface []: oracle
Default group to own the driver interface []: dba
Start Oracle ASM library driver on boot (y/n) [n]: y
Scan for Oracle ASM disks on boot (y/n) [y]: y
Writing Oracle ASM library driver configuration: done
[root@oraclelab1 ~]#
[root@oraclelab1 ~]#
[root@oraclelab1 ~]#


[root@oraclelab2 ~]# oracleasm configure -i
Configuring the Oracle ASM library driver.

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

Default user to own the driver interface []: oracle
Default group to own the driver interface []: dba
Start Oracle ASM library driver on boot (y/n) [n]: y
Scan for Oracle ASM disks on boot (y/n) [y]: y
Writing Oracle ASM library driver configuration: done
[root@oraclelab2 ~]#
[root@oraclelab2 ~]#
[root@oraclelab2 ~]#


[root@oraclelab1 ~]# oracleasm init
Creating /dev/oracleasm mount point: /dev/oracleasm
Loading module "oracleasm": oracleasm
Configuring "oracleasm" to use device physical block size
Mounting ASMlib driver filesystem: /dev/oracleasm
[root@oraclelab1 ~]#

[root@oraclelab1 ~]# oracleasm createdisk CRS1 /dev/sdb1
Writing disk header: done
Instantiating disk: done
[root@oraclelab1 ~]# oracleasm createdisk DATA1 /dev/sdb2
Writing disk header: done
Instantiating disk: done
[root@oraclelab1 ~]# oracleasm createdisk FRA1 /dev/sdb3
Writing disk header: done
Instantiating disk: done
[root@oraclelab1 ~]#


[root@oraclelab1 ~]# oracleasm scandisks
Reloading disk partitions: done
Cleaning any stale ASM disks...
Scanning system for ASM disks...
[root@oraclelab1 ~]# oracleasm listdisks
CRS1
DATA1
FRA1






















































[root@oraclelab1 grid]# sh root.sh
Performing root user operation.

The following environment variables are set as:
    ORACLE_OWNER= oracle
    ORACLE_HOME=  /u01/app/12.1.0.2/grid

Enter the full pathname of the local bin directory: [/usr/local/bin]:
   Copying dbhome to /usr/local/bin ...
   Copying oraenv to /usr/local/bin ...
   Copying coraenv to /usr/local/bin ...


Creating /etc/oratab file...
Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root script.
Now product-specific root actions will be performed.
Using configuration parameter file: /u01/app/12.1.0.2/grid/crs/install/crsconfig_params
2023/09/21 14:24:01 CLSRSC-4001: Installing Oracle Trace File Analyzer (TFA) Collector.

2023/09/21 14:24:50 CLSRSC-4002: Successfully installed Oracle Trace File Analyzer (TFA) Collector.

2023/09/21 14:24:54 CLSRSC-363: User ignored prerequisites during installation

OLR initialization - successful
  root wallet
  root wallet cert
  root cert export
  peer wallet
  profile reader wallet
  pa wallet
  peer wallet keys
  pa wallet keys
  peer cert request
  pa cert request
  peer cert
  pa cert
  peer root cert TP
  profile reader root cert TP
  pa root cert TP
  peer pa cert TP
  pa peer cert TP
  profile reader pa cert TP
  profile reader peer cert TP

2023/09/21 14:26:59 CLSRSC-330: Adding Clusterware entries to file 'oracle-ohasd.service'
CRS-4133: Oracle High Availability Services has been stopped.
CRS-4123: Oracle High Availability Services has been started.
CRS-4133: Oracle High Availability Services has been stopped.
CRS-2672: Attempting to start 'ora.evmd' on 'oraclelab1'
CRS-2672: Attempting to start 'ora.mdnsd' on 'oraclelab1'
CRS-2676: Start of 'ora.mdnsd' on 'oraclelab1' succeeded
CRS-2676: Start of 'ora.evmd' on 'oraclelab1' succeeded
CRS-2672: Attempting to start 'ora.gpnpd' on 'oraclelab1'
CRS-2676: Start of 'ora.gpnpd' on 'oraclelab1' succeeded
CRS-2672: Attempting to start 'ora.cssdmonitor' on 'oraclelab1'
CRS-2672: Attempting to start 'ora.gipcd' on 'oraclelab1'
CRS-2676: Start of 'ora.cssdmonitor' on 'oraclelab1' succeeded
CRS-2676: Start of 'ora.gipcd' on 'oraclelab1' succeeded
CRS-2672: Attempting to start 'ora.cssd' on 'oraclelab1'
CRS-2672: Attempting to start 'ora.diskmon' on 'oraclelab1'
CRS-2676: Start of 'ora.diskmon' on 'oraclelab1' succeeded
CRS-2676: Start of 'ora.cssd' on 'oraclelab1' succeeded

CRS-2676: Start of 'ora.cssd' on 'oraclelab1' succeeded

ASM created and started successfully.

Disk Group CRS created successfully.

CRS-2672: Attempting to start 'ora.crf' on 'oraclelab1'
CRS-2672: Attempting to start 'ora.storage' on 'oraclelab1'
CRS-2676: Start of 'ora.storage' on 'oraclelab1' succeeded
CRS-2676: Start of 'ora.crf' on 'oraclelab1' succeeded
CRS-2672: Attempting to start 'ora.crsd' on 'oraclelab1'
CRS-2676: Start of 'ora.crsd' on 'oraclelab1' succeeded
CRS-4256: Updating the profile
Successful addition of voting disk 373e998d271b4f0ebfde00ade663d087.
Successfully replaced voting disk group with +CRS.
CRS-4256: Updating the profile
CRS-4266: Voting file(s) successfully replaced
##  STATE    File Universal Id                File Name Disk group
--  -----    -----------------                --------- ---------
 1. ONLINE   373e998d271b4f0ebfde00ade663d087 (/dev/oracleasm/disks/CRS1) [CRS]
Located 1 voting disk(s).
CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on 'oraclelab1'
CRS-2673: Attempting to stop 'ora.crsd' on 'oraclelab1'
CRS-2677: Stop of 'ora.crsd' on 'oraclelab1' succeeded
CRS-2673: Attempting to stop 'ora.mdnsd' on 'oraclelab1'
CRS-2673: Attempting to stop 'ora.gpnpd' on 'oraclelab1'
CRS-2673: Attempting to stop 'ora.ctssd' on 'oraclelab1'
CRS-2673: Attempting to stop 'ora.evmd' on 'oraclelab1'
CRS-2673: Attempting to stop 'ora.storage' on 'oraclelab1'
CRS-2677: Stop of 'ora.storage' on 'oraclelab1' succeeded
CRS-2673: Attempting to stop 'ora.asm' on 'oraclelab1'
CRS-2677: Stop of 'ora.mdnsd' on 'oraclelab1' succeeded
CRS-2677: Stop of 'ora.gpnpd' on 'oraclelab1' succeeded
CRS-2677: Stop of 'ora.ctssd' on 'oraclelab1' succeeded
CRS-2677: Stop of 'ora.evmd' on 'oraclelab1' succeeded
CRS-2677: Stop of 'ora.asm' on 'oraclelab1' succeeded
CRS-2673: Attempting to stop 'ora.cluster_interconnect.haip' on 'oraclelab1'
CRS-2677: Stop of 'ora.cluster_interconnect.haip' on 'oraclelab1' succeeded
CRS-2673: Attempting to stop 'ora.cssd' on 'oraclelab1'
CRS-2677: Stop of 'ora.cssd' on 'oraclelab1' succeeded
CRS-2673: Attempting to stop 'ora.crf' on 'oraclelab1'
CRS-2677: Stop of 'ora.crf' on 'oraclelab1' succeeded
CRS-2673: Attempting to stop 'ora.gipcd' on 'oraclelab1'
CRS-2677: Stop of 'ora.gipcd' on 'oraclelab1' succeeded
CRS-2793: Shutdown of Oracle High Availability Services-managed resources on 'oraclelab1' has completed
CRS-4133: Oracle High Availability Services has been stopped.
CRS-6023: Starting Oracle Cluster Ready Services-managed resources
CRS-6017: Processing resource auto-start for servers: oraclelab1
CRS-6016: Resource auto-start has completed for server oraclelab1
CRS-6024: Completed start of Oracle Cluster Ready Services-managed resources
CRS-4123: Oracle High Availability Services has been started.
2023/09/21 14:33:29 CLSRSC-343: Successfully started Oracle Clusterware stack

CRS-2672: Attempting to start 'ora.asm' on 'oraclelab1'
CRS-2676: Start of 'ora.asm' on 'oraclelab1' succeeded
CRS-2672: Attempting to start 'ora.CRS.dg' on 'oraclelab1'
CRS-2676: Start of 'ora.CRS.dg' on 'oraclelab1' succeeded
CRS-6024: Completed start of Oracle Cluster Ready Services-managed resources
CRS-4123: Oracle High Availability Services has been started.
2023/09/21 14:33:29 CLSRSC-343: Successfully started Oracle Clusterware stack

CRS-2672: Attempting to start 'ora.asm' on 'oraclelab1'
CRS-2676: Start of 'ora.asm' on 'oraclelab1' succeeded
CRS-2672: Attempting to start 'ora.CRS.dg' on 'oraclelab1'
CRS-2676: Start of 'ora.CRS.dg' on 'oraclelab1' succeeded
Preparing packages...
cvuqdisk-1.0.9-1.x86_64
2023/09/21 14:35:29 CLSRSC-325: Configure Oracle Grid Infrastructure for a Cluster ... succeeded

[ro



[root@oraclelab2 grid]# sh root.sh
Performing root user operation.

The following environment variables are set as:
    ORACLE_OWNER= oracle
    ORACLE_HOME=  /u01/app/12.1.0.2/grid

Enter the full pathname of the local bin directory: [/usr/local/bin]:
   Copying dbhome to /usr/local/bin ...
   Copying oraenv to /usr/local/bin ...
   Copying coraenv to /usr/local/bin ...


Creating /etc/oratab file...
Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root script.
Now product-specific root actions will be performed.
Using configuration parameter file: /u01/app/12.1.0.2/grid/crs/install/crsconfig_params
2023/09/21 14:36:19 CLSRSC-4001: Installing Oracle Trace File Analyzer (TFA) Collector.
Creating /etc/oratab file...
Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root script.
Now product-specific root actions will be performed.
Using configuration parameter file: /u01/app/12.1.0.2/grid/crs/install/crsconfig_params
2023/09/21 14:36:19 CLSRSC-4001: Installing Oracle Trace File Analyzer (TFA) Collector.

2023/09/21 14:37:25 CLSRSC-4002: Successfully installed Oracle Trace File Analyzer (TFA) Collector.

2023/09/21 14:37:29 CLSRSC-363: User ignored prerequisites during installation

OLR initialization - successful
2023/09/21 14:39:17 CLSRSC-330: Adding Clusterware entries to file 'oracle-ohasd.service'

CRS-4133: Oracle High Availability Services has been stopped.
CRS-4123: Oracle High Availability Services has been started.
CRS-4133: Oracle High Availability Services has been stopped.
CRS-4123: Oracle High Availability Services has been started.
CRS-4133: Oracle High Availability Services has been stopped.




CRS-2672: Attempting to start 'ora.storage' on 'oraclelab2'
CRS-2676: Start of 'ora.storage' on 'oraclelab2' succeeded
CRS-2672: Attempting to start 'ora.crf' on 'oraclelab2'
CRS-2676: Start of 'ora.crf' on 'oraclelab2' succeeded
CRS-2672: Attempting to start 'ora.crsd' on 'oraclelab2'
CRS-2676: Start of 'ora.crsd' on 'oraclelab2' succeeded
CRS-6017: Processing resource auto-start for servers: oraclelab2
CRS-2672: Attempting to start 'ora.net1.network' on 'oraclelab2'
CRS-2676: Start of 'ora.net1.network' on 'oraclelab2' succeeded
CRS-2672: Attempting to start 'ora.ons' on 'oraclelab2'
CRS-2676: Start of 'ora.ons' on 'oraclelab2' succeeded
CRS-6016: Resource auto-start has completed for server oraclelab2
CRS-6024: Completed start of Oracle Cluster Ready Services-managed resources
CRS-4123: Oracle High Availability Services has been started.
2023/09/21 14:43:24 CLSRSC-343: Successfully started Oracle Clusterware stack

Preparing packages...
cvuqdisk-1.0.9-1.x86_64
2023/09/21 14:44:02 CLSRSC-325: Configure Oracle Grid Infrastructure for a Cluster ... succeeded

[root@oraclelab2 grid]#


















































































Situation :

While installing GRID s/w I used up all my allocated ASM disk space to create the CRS mount/diskgroup

Now I need to create database and need DATA and RECO disk group , but i'm unable to create it as all the disks are already taken up by CRS mount.

SQL> column diskgroup format a10;
SQL> column disk_name format a10;
SQL> column failgroup format a10;
SQL> select dg.name diskgroup, dk.name disk_name, dk.failgroup, dk.mount_status, dk.mode_status from v$asm_disk dk, v$asm_diskgroup dg where dk.group_number = dg.group_number;

DISKGROUP  DISK_NAME  FAILGROUP  MOUNT_S MODE_ST
---------- ---------- ---------- ------- -------
CRS        CRS_0002   CRS_0002   CACHED  ONLINE
CRS        CRS_0001   CRS_0001   CACHED  ONLINE
CRS        CRS_0000   CRS_0000   CACHED  ONLINE

SQL>

ASMCMD> lsdsk -k
Total_MB  Free_MB  OS_MB  Name      Failgroup  Failgroup_Type  Library  Label  UDID  Product  Redund   Path
    4096     3706   4096  CRS_0000  CRS_0000   REGULAR         System                         UNKNOWN  /dev/oracleasm/disks/CRS1
   23552    21510  23552  CRS_0001  CRS_0001   REGULAR         System                         UNKNOWN  /dev/oracleasm/disks/DATA1
   23551    21510  23551  CRS_0002  CRS_0002   REGULAR         System                         UNKNOWN  /dev/oracleasm/disks/FRA1
ASMCMD>
ASMCMD>

SQL> SELECT SUBSTR(dg.name,1,16) AS diskgroup, SUBSTR(d.name,1,16) AS asmdisk,
d.mount_status, d.state, SUBSTR(d.failgroup,1,16) AS failgroup
FROM V$ASM_DISKGROUP dg, V$ASM_DISK d WHERE dg.group_number = d.group_number;  2    3

DISKGROUP        ASMDISK          MOUNT_S STATE    FAILGROUP
---------------- ---------------- ------- -------- ----------------
CRS              CRS_0002         CACHED  NORMAL   CRS_0002
CRS              CRS_0000         CACHED  NORMAL   CRS_0000
CRS              CRS_0001         CACHED  NORMAL   CRS_0001


[oracle@oraclelab1 ~]$ 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     51199    46726                0           46726              0             Y  CRS/
ASMCMD>
AS

SQL> SELECT name, type, total_mb, free_mb, required_mirror_free_mb, usable_file_mb FROM V$ASM_DISKGROUP;

NAME                           TYPE     TOTAL_MB    FREE_MB
------------------------------ ------ ---------- ----------
REQUIRED_MIRROR_FREE_MB USABLE_FILE_MB
----------------------- --------------
CRS                            EXTERN      51199      46726
                      0          46726



As you can see my all 50GB disk space is used up by CRS.
So lets free it up 

lets drop 

Name         Path
CRS_0000     /dev/oracleasm/disks/CRS1
CRS_0001     /dev/oracleasm/disks/DATA1
CRS_0002     /dev/oracleasm/disks/FRA1

SQL> 
alter diskgroup CRS drop disk CRS_0001 rebalance power 5 wait;

ASMCMD shows those disks are freed from ASM.
ASMCMD> lsdsk
Path
/dev/oracleasm/disks/CRS1
/dev/oracleasm/disks/FRA1
ASMCMD> lsdsk --candidate
Path
/dev/oracleasm/disks/DATA1
ASMCMD>





SQL> alter diskgroup CRS drop disk CRS_0002;
alter diskgroup CRS drop disk CRS_0002
*
ERROR at line 1:
ORA-15032: not all alterations performed
ORA-15250: insufficient diskgroup space for rebalance completion


SQL> set lines 999;
SQL> col diskgroup for a10
SQL> col diskname for a12
col path for a30
SQL> SQL> select a.name DiskGroup,b.name DiskName, b.total_mb,
(b.total_mb-b.free_mb) Used_MB, b.free_mb,b.path,b.header_status from v$asm_disk b, v$asm_diskgroup
a where a.group_number (+) =b.group_number order by b.group_number,b.name;
  2    3
DISKGROUP  DISKNAME       TOTAL_MB    USED_MB    FREE_MB PATH                           HEADER_STATU
---------- ------------ ---------- ---------- ---------- ------------------------------ ------------
CRS        CRS_0000           4096       1025       3071 /dev/oracleasm/disks/CRS1      MEMBER
CRS        CRS_0002          23551       3445      20106 /dev/oracleasm/disks/FRA1      MEMBER
DATA       DATA_0000         23552         96      23456 /dev/oracleasm/disks/DATA1     MEMBER

SQL>
SQL>
SQL>


on No comments:
Subscribe to: Posts (Atom)