In the present IT environment Testing on Demand (ToD) is becoming a huge necessity. The cloud infrastructure has allowed us to take advantage of this and it has proven to be very cost efficient. ToD in the cloud means that an infrastructure team can spawn a server for testing purposes within a few minutes and decommission it immediately after testing has completed. Doing this in the cloud will save corporations thousands in server procurement, installation and decommissioning, man power, time etc.
As a DBA I found the need for ToD using AWS when I had to test Oracle 12c patches and upgrades, along with scripted jobs and migrations to name a few. The environments I administered resided in AWS and had Oracles ASM as the storage infrastructure. The smallest environment was 2+TB and each server had 8 vCPUs and 64GB of RAM.
Please note that the data will only be up to the date that the AWS AMI was taken. If you want current data you will need to import (impdp), restores & recover (RMAN) or load (SQL Loader) all the data that has accumulated since the AMI was taken.
What I needed the database for did not require current data.
Purpose: Restore a database on a replicated AWS AMI. This document is for an Oracle single instance database that uses ASM as its storage infrastructure.
Author: Dennis Woodbine
Contact: denniswoodbine@yahoo.com
https://www.linkedin.com/in/dennis-woodbine-011101140/
ATTENTION: This document represents the steps I took to solve an issue in my environment. Please review content before implementing in your environment. This document does not guarantee the same results in any other environment. Any and all issues faced are your responsibility and cannot be attributed to the steps taken in this document.
SCENARIO: The database team received a new AWS server that was a replica of an existing AWS production server. The production server is still being used so therefore the sys admin had to assign the server created from the AWS AMI a new virtual NIC.
Please note that this was a complete replica of the production server, so all database files are still present on the new AWS server that was provided to use by the sys admin.
It will be our job to restore the ASM storage infrastructure and then restore the database to a functional state.
NOTE:
If restoring a Data Guard environment that uses ASM as the storage infrastructure then there are a few additional steps. In addition to restoring both the Physical and Standby, the DBA may have to coordinate with the sys admin to check with the security group/s. AWS AMI carry over security group settings when they are restored so communication between primary standby may be affected. You will also need to update the network files/names that use all production network names/aliases.
—-START
–AS ORACLE
–SET Environment
[oracle@ip-10-0-1-### ~]$ . oraenv
ORACLE_SID = [BOOGYMAN] ?
The Oracle base has been set to /opt/oracle/app/oracle
–CHECK LISTENER
[oracle@ip-10-0-1-### ~]$ lsnrctl status
LSNRCTL for Linux: Version 12.1.0.2.0 – Production on 28-JUL-2017 10:55:06
Copyright (c) 1991, 2014, Oracle. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
TNS-12541: TNS:no listener
TNS-12560: TNS:protocol adapter error
TNS-00511: No listener
Linux Error: 111: Connection refused
–CHECK STORAGE/MEMORY
[oracle@ip-10-0-1-### ~]$ df -h
Filesystem SizeUsed Avail Use% Mounted on
/dev/xvda1 9.8G7.1G2.2G77% /
tmpfs 50G 0 50G 0% /dev/shm
/dev/xvdf 99G 25G 69G27% /usr/home
/dev/xvdb 99G 41G 53G44% /opt/oracle/app
/dev/xvdg 493G104G364G23% /orabackup
/dev/xvdi 99G 33G 61G35% /opt/grid/app
/dev/xvdj 1008G423G535G45% /u01
/dev/xvdl 493G204G264G44% /orabackup2
[oracle@ip-10-0-1-### ~]$ cat /proc/meminfo | grep MemTotal
MemTotal: 62904000 kB —-> 62.9 GB
–TRY TO STARTUP DATABASE
[oracle@ip-10-0-1-### ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Fri Jul 28 10:58:36 2017
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to an idle instance.
SELECT SYS_CONTEXT (‘USERENV’, ‘CON_NAME’) FROM DUAL
*
ERROR at line 1:
ORA-01034: ORACLE not available
Process ID: 0
Session ID: 0 Serial number: 0
SQL> startup
ORA-01078: failure in processing system parameters
ORA-01565: error in identifying file ‘+DATA/BOOGYMAN/spfileBOOGYMAN.ora’
ORA-17503: ksfdopn:2 Failed to open file +DATA/BOOGYMAN/spfileBOOGYMAN.ora
ORA-29701: unable to connect to Cluster Synchronization Service
–AS GRID
–SET ENV to +ASM
[oracle@ip-10-0-1-### ~]$ su – grid
Password:
[grid@ip-10-0-1-### ~]$ . oraenv
ORACLE_SID = [grid] ? +ASM
The Oracle base has been set to /opt/grid/app/grid
–CHECK LISTENER
[grid@ip-10-0-1-### ~]$ lsnrctl status
LSNRCTL for Linux: Version 12.1.0.2.0 – Production on 28-JUL-2017 11:00:38
Copyright (c) 1991, 2014, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ip-10-0-1-###.ec2.internal)(PORT=1521)))
TNS-01189: The listener could not authenticate the user
–CHECK CLUSTERWARE RESOURCES
[grid@ip-10-0-1-### ~]$ crsctl status res -t -init
CRS-4639: Could not contact Oracle High Availability Services
CRS-4000: Command Status failed, or completed with errors.
–TRY TO STARTUP ASM SERVICE
[grid@ip-10-0-1-### ~]$ sqlplus / as sysasm
SQL*Plus: Release 12.1.0.2.0 Production on Fri Jul 28 11:01:28 2017
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORA-01078: failure in processing system parameters
ORA-29701: unable to connect to Cluster Synchronization Service
[grid@ip-10-0-1-### ~]$ asmcmd
Connected to an idle instance.
ASMCMD> startup
ORA-01078: failure in processing system parameters
ORA-29701: unable to connect to Cluster Synchronization Service
Connected to an idle instance.
–START THE FIX
–AS GRID
–Copy listener.ora
[grid@ip-10-0-1-### ~]$ cd /opt/grid/app/grid/product/12.1.0/grid/network/admin/
[grid@ip-10-0-1-### admin]$ ls -altrh
total 40K
-rw-r–r–. 1 grid oinstall373 Oct 312013 shrept.lst
drwxr-xr-x. 2 grid oinstall 4.0K Dec 112014 samples
drwxr-xr-x. 10 grid oinstall 4.0K Dec 112014 ..
-rw-r–r–. 1 grid oinstall194 Dec 112014 sqlnet.ora
-rw-r–r–. 1 grid oinstall352 Dec 112014 listener.ora.bak.ip-10-0-1-###
-rw-r–r–. 1 grid oinstall490 Dec 182014 listener.ora.bak.ip-10-0-1-###
-rw-r–r–. 1 grid oinstall194 Dec 182014 sqlnet1412182PM0826.bak
-rw-r–r–. 1 grid oinstall490 Dec 182014 listener1412182PM0826.bak
-rw-r–r–. 1 grid oinstall856 Jan 182015 listener.ora
drwxr-xr-x. 3 grid oinstall 4.0K Apr 262016 .
[grid@ip-10-0-1-### admin]$ cp listener.ora listener.ora.7_28_2017
[grid@ip-10-0-1-### admin]$ ls -altrh
total 44K
-rw-r–r–. 1 grid oinstall373 Oct 31 2013 shrept.lst
drwxr-xr-x. 2 grid oinstall 4.0K Dec 112014 samples
drwxr-xr-x. 10 grid oinstall 4.0K Dec 112014 ..
-rw-r–r–. 1 grid oinstall194 Dec 112014 sqlnet.ora
-rw-r–r–. 1 grid oinstall352 Dec 112014 listener.ora.bak.ip-10-0-1-###
-rw-r–r–. 1 grid oinstall490 Dec 182014 listener.ora.bak.ip-10-0-1-25
-rw-r–r–. 1 grid oinstall194 Dec 182014 sqlnet1412182PM0826.bak
-rw-r–r–. 1 grid oinstall490 Dec 182014 listener1412182PM0826.bak
-rw-r–r–. 1 grid oinstall856 Jan 182015 listener.ora
-rw-r–r– 1 grid oinstall856 Jul 28 11:12 listener.ora.7_28_2017
drwxr-xr-x. 3 grid oinstall 4.0K Jul 28 11:12 .
–AS ROOT
[grid@ip-10-0-1-### install]$ exit
logout
[oracle@ip-10-0-1-### ~]$ sudo -i
[sudo] password for oracle:
[root@ip-10-0-1-### ~]# cd /opt/grid/app/grid/product/12.1.0/grid/crs/install/
[root@ip-10-0-1-### install]# ls
appcluster.pl crsconfig_params.sbscrska.pm crstfa.pm dropdb.pl installRemove.excloracle-ohasd.servicerootcrs.sh s_crsconfig_ip-10-0-1-25_env.txt
cmdllroot.sh crsdeconfig.pm crsoc4j.pm crsupgrade.pminittab oraacfs.pm oracss.pm roothas.pl s_crsconfig_ip-10-0-1-37_env.txt
crsconfig_addparams.sbscrsgpnp.pm crsohasd.pmcrsutils.pm install.excloraafd.pm paramfile.crs roothas.sh s_crsutils.pm
crsconfig_params crsinstall.pm crspatch.pmdropdb install.incloracle-ohasd.conf rootcrs.pl s_crsconfig_defstfa_setup
[root@ip-10-0-1-### install]# ./roothas.pl -deconfig -force
Using configuration parameter file: ./crsconfig_params
CRS-4639: Could not contact Oracle High Availability Services
CRS-4000: Command Stop failed, or completed with errors.
CRS-4639: Could not contact Oracle High Availability Services
CRS-4000: Command Delete failed, or completed with errors.
CLSU-00100: operating system function: opendir failed with error data: 2
CLSU-00101: operating system error message: No such file or directory
CLSU-00103: error location: scrsearch1
CLSU-00104: additional error information: cant open scr home dir scls_scr_getval
CRS-4639: Could not contact Oracle High Availability Services
CRS-4000: Command Stop failed, or completed with errors.
2017/07/28 11:18:55 CLSRSC-337: Successfully deconfigured Oracle Restart stack
[root@ip-10-0-1-### install]# cd /opt/grid/app/grid/product/12.1.0/grid/
[root@ip-10-0-1-### grid]# ls
addnode cdata crs dbs diagnosticsgipchs javavmJRE md oc4j OPatch_origord p6880880_121010_Linux-x86-64.zipQOpatch rest slax suptoolswlm
assistantscfgtoollogscss dc_ocm dmu gnsdinstall jdbc ldapmdns ohasd opmn osysmondperl racg root.sh sqlpatchucp wwg
auth clone ctssdeinstalleons gpnpinstantclientjdk lib networkologgerdoracore oui plsql rdbms rootupgrade.shsqlplus usm xag
bin crf cv demo evm has inventory jlib log nls OPatch oraInst.locowm precomp relnotesscheduler srvm utl xdk
[root@ip-10-0-1-### grid]# ./root.sh
Performing root user operation.
The following environment variables are set as:
ORACLE_OWNER= grid
ORACLE_HOME= /opt/grid/app/grid/product/12.1.0/grid
Enter the full pathname of the local bin directory: [/usr/local/bin]:–press enter here. dont type anything.
The contents of “dbhome” have not changed. No need to overwrite.
The contents of “oraenv” have not changed. No need to overwrite.
The contents of “coraenv” have not changed. No need to overwrite.
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: /opt/grid/app/grid/product/12.1.0/grid/crs/install/crsconfig_params
LOCAL ADD MODE
Creating OCR keys for user ‘grid’, 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 ip-10-0-1-### successfully pinned.
2017/07/28 11:20:16 CLSRSC-330: Adding Clusterware entries to file ‘oracle-ohasd.conf’
ip-10-0-1-### 2017/07/28 11:20:34 /opt/grid/app/grid/product/12.1.0/grid/cdata/ip-10-0-1-###/backup_20170728_112034.olr 0
CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on ‘ip-10-0-1-###’
CRS-2673: Attempting to stop ‘ora.evmd’ on ‘ip-10-0-1-###’
CRS-2677: Stop of ‘ora.evmd’ on ‘ip-10-0-1-###’ succeeded
CRS-2793: Shutdown of Oracle High Availability Services-managed resources on ‘ip-10-0-1-###’ has completed
CRS-4133: Oracle High Availability Services has been stopped.
CRS-4123: Oracle High Availability Services has been started.
2017/07/28 11:22:45 CLSRSC-327: Successfully configured Oracle Restart for a standalone server
–AS GRID
–Lets test the listener on the new IP
–1st lets configure the listener.ora file
[root@ip-10-0-1-### admin]# exit
logout
[oracle@ip-10-0-1-### ~]$ su – grid
Password:
[grid@ip-10-0-1-### ~]$ cd /opt/grid/app/grid/product/12.1.0/grid/network/admin/
[grid@ip-10-0-1-### admin]$ ls
listener1412182PM0826.baklistener.oralistener.ora.7_28_2017listener.ora.bak.ip-10-0-1-### listener.ora.bak.ip-10-0-1-### samplesshrept.lst sqlnet1412182PM0826.baksqlnet.ora
[grid@ip-10-0-1-### admin]$ more listener.ora
# listener.ora Network Configuration File: /opt/grid/app/grid/product/12.1.0/grid/network/admin/listener.ora
# Generated by Oracle configuration tools.
VALID_NODE_CHECKING_REGISTRATION_LISTENER = SUBNET
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ip-10-0-1-###.ec2.internal)(PORT = 1521))–Note this is still the original servers IP Address
)
)
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(SDU=32767)
(SEND_BUF_SIZE=465000)
(RECV_BUF_SIZE=465000)
(GLOBAL_DBNAME=BOOGYMAN.mysite.org)
(SID_NAME=BOOGYMAN)
(ORACLE_HOME=/opt/oracle/app/oracle/product/12.1.0/dbhome_1)
)
(SID_DESC=
(GLOBAL_DBNAME=BOOGYMAN_DGMGRL.mysite.org)
(SID_NAME=BOOGYMAN)
(ORACLE_HOME=/opt/oracle/app/oracle/product/12.1.0/dbhome_1)
)
)
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER = ON
VALID_NODE_CHECKING_REGISTRATION_LISTENER=SUBNET
[grid@ip-10-0-1-### admin]$ vi listener.ora
–Update IP to 10-0-1-###
(ADDRESS = (PROTOCOL = TCP)(HOST = ip-10-0-1-25.ec2.internal)(PORT = 1521))–Note this is still the original servers IP Address
–START LISTENER
[grid@ip-10-0-1-### admin]$ lsnrctl start
LSNRCTL for Linux: Version 12.1.0.2.0 – Production on 28-JUL-2017 12:21:45
Copyright (c) 1991, 2014, Oracle. All rights reserved.
Starting /opt/grid/app/grid/product/12.1.0/grid/bin/tnslsnr: please wait…
TNSLSNR for Linux: Version 12.1.0.2.0 – Production
System parameter file is /opt/grid/app/grid/product/12.1.0/grid/network/admin/listener.ora
Log messages written to /opt/grid/app/grid/diag/tnslsnr/ip-10-0-1-###/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ip-10-0-1-###.ec2.internal)(PORT=1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ip-10-0-1-###.ec2.internal)(PORT=1521)))
STATUS of the LISTENER
————————
Alias LISTENER
Version TNSLSNR for Linux: Version 12.1.0.2.0 – Production
Start Date 28-JUL-2017 12:21:45
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /opt/grid/app/grid/product/12.1.0/grid/network/admin/listener.ora
Listener Log File /opt/grid/app/grid/diag/tnslsnr/ip-10-0-1-###/listener/alert/log.xml
Listening Endpoints Summary…
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ip-10-0-1-###.ec2.internal)(PORT=1521)))
Services Summary…
Service “BOOGYMAN.mysite.org” has 1 instance(s).
Instance “BOOGYMAN”, status UNKNOWN, has 1 handler(s) for this service…
Service “BOOGYMAN_DGMGRL.mysite.org” has 1 instance(s).
Instance “BOOGYMAN”, status UNKNOWN, has 1 handler(s) for this service…
The command completed successfully
–check if cluster resources are available
[grid@ip-10-0-1-### admin]$ crsctl status res -t -init
——————————————————————————–
Name TargetState Server State details
——————————————————————————–
Local Resources
——————————————————————————–
ora.ons
OFFLINE OFFLINE ip-10-0-1-### STABLE
——————————————————————————–
Cluster Resources
——————————————————————————–
ora.cssd
1 OFFLINE OFFLINE STABLE
ora.diskmon
1 OFFLINE OFFLINE STABLE
ora.evmd
1 ONLINEONLINE ip-10-0-1-### STABLE
——————————————————————————–
[grid@ip-10-0-1-### admin]$
–Notice ASM is missing. We need to add the ASM resources and start it.
[grid@ip-10-0-1-### admin]$ srvctl add asm
[grid@ip-10-0-1-### admin]$ crsctl status res -t -init
——————————————————————————–
Name TargetState Server State details
——————————————————————————–
Local Resources
——————————————————————————–
ora.asm
OFFLINE OFFLINE ip-10-0-1-### STABLE
ora.ons
OFFLINE OFFLINE ip-10-0-1-### STABLE
——————————————————————————–
Cluster Resources
——————————————————————————–
ora.cssd
1 OFFLINE OFFLINE STABLE
ora.diskmon
1 OFFLINE OFFLINE STABLE
ora.evmd
1 ONLINEONLINE ip-10-0-1-### STABLE
——————————————————————————–
[grid@ip-10-0-1-### admin]$ srvctl start asm -startoption OPEN
[grid@ip-10-0-1-### admin]$ crsctl status res -t -init
——————————————————————————–
Name TargetState Server State details
——————————————————————————–
Local Resources
——————————————————————————–
ora.asm
ONLINEONLINE ip-10-0-1-### Started,STABLE
ora.ons
OFFLINE OFFLINE ip-10-0-1-### STABLE
——————————————————————————–
Cluster Resources
——————————————————————————–
ora.cssd
1 ONLINEONLINE ip-10-0-1-### STABLE
ora.diskmon
1 OFFLINE OFFLINE STABLE
ora.evmd
1 ONLINEONLINE ip-10-0-1-### STABLE
——————————————————————————–
–Check is ASM instance is up and running
[grid@ip-10-0-1-### admin]$ . oraenv
ORACLE_SID = [grid] ? +ASM
The Oracle base has been set to /opt/grid/app/grid
[grid@ip-10-0-1-### admin]$ sqlplus / as sysasm
SQL*Plus: Release 12.1.0.2.0 Production on Fri Jul 28 12:24:44 2017
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Automatic Storage Management option
SQL> startup
ORA-01081: cannot start already-running ORACLE – shut it down first
–Do some ASM checks
–check to see if you can discover the disks
[grid@ip-10-0-1-### admin]$ asmcmd
ASMCMD> lsdsk –discovery
Path
ORCL:VOL1
ORCL:VOL2
ORCL:VOL3
ORCL:VOL4
ORCL:VOL5
ASMCMD> exit
[grid@ip-10-0-1-### admin]$ /etc/init.d/oracleasm listdisks
VOL1
VOL2
VOL3
VOL4
VOL5
–AS ROOT
–Scan the ASM disks and check status
[grid@ip-10-0-1-### admin]$ exit
logout
[oracle@ip-10-0-1-### ~]$ sudo -i
[sudo] password for oracle:
[root@ip-10-0-1-### ~]# /etc/init.d/oracleasm scandisks
Scanning the system for Oracle ASMLib disks: [ OK]
[root@ip-10-0-1-### ~]# /etc/init.d/oracleasm status
Checking if ASM is loaded: yes
Checking if /dev/oracleasm is mounted: yes
–AS GRID
–using ASM Configuration Assistant check if disks are mounted
–If using putty be sure your GUI software (xming) is running and you have xming enabled on your putty session.
[root@ip-10-0-1-### ~]# exit
logout
[oracle@ip-10-0-1-### ~]$ su – grid
Password:
[grid@ip-10-0-1-### ~]$ . oraenv
ORACLE_SID = [grid] ? +ASM
The Oracle base has been set to /opt/grid/app/grid
[grid@ip-10-0-1-### ~]$ asmca
B3T Consulting 
Leave a comment