I noticed my version of APEX was a bit out of date, so decided to patch it to the latest version.
It took me a bit of time to do it, because I was looking in the wrong place. If you go to the APEX download page -
http://www.oracle.com/technetwork/developer-tools/apex/downloads/index.html
You can download the latest version - but don't, because if you already have V4 installed, it won't work.
If you download it and try to run @apexins sysaux sysaux temp /i/ , you will get this:
Error: This version of Application Express is already installed (APEX_040200).
declare
*
ERROR at line 1:
ORA-00900: invalid SQL statement
ORA-06512: at line 9
You actually need to download a patch and apply that. But don't go to the Oracle site and try to search for the latest APEX patch there, you'll just end up going round in circles.
Instead, open the Release Notes from the download page, and in there you will find a reference to the patch number:
2.2 Downloading and Extracting the Installation Software
Download the patch set to a computer with access to the Oracle Database. You will also need access to the Oracle home.
To download and extract the patch set installation software:
Download the p19595896_426_Generic.zip patch set installation archive to a directory that is not the Oracle home directory or under the Oracle home directory.
So go to the Oracle Support site and search for this number in the patches tab.
Once downloaded, just read the instructions in the notes.
Short version - cd to the directory the patch was extracted.
cd /tmp/apex/patch
You might want to take a database backup here, just in case.
Run the script to apply the patch (go and get a coffee, this takes a while):
sqlplus> @apxpatch.sql
Then
sqlplus> @apxldimg.sql /tmp/apex/patch
Log into APEX and the new version number should be shown in the bottom right of the page.
Monday, December 15, 2014
Thursday, November 13, 2014
Resolving a CDC process due to a missing log file - WAITING FOR DICTIONARY REDO
CDC on a database was stopped and restarted twice a day by an
automated process. Apparently this was because it would send false-positive SMS alerts otherwise.
There must be a long-running transaction, because every time
it restarted, it started to mine the same logfile (seq# 252207). This went on
for a couple of days, until eventually this log was deleted by a clean-up job.
Then the CDC failed with this:
State
: WAITING FOR DICTIONARY REDO: FILE
/apps/oracle/admin/PROD/PROD_arch/PROD_1_708192618_0000252207.arc
Luckily this file was still on ASM disk, so I copied it back
down to the file system, and it started to mine it.
However, for some reason it wouldn't move on, it just sat
there, even though the other files (252208, 252209) were also there on disk.
In the meantime, the older logs were getting deleted by the clean up job. I identified
the logs that were missing from disk (252210 – 252243) copied what I could from
ASM, and also had to restore some from tape.
The destination file system didn't have enough space, so I
had to borrow some and moved them to a staging file
system (I'll call it /cdc_arch_staging)
I then registered them with the database and voila – the
mining kicked in.
I still don’t know why it just stopped after I’d copied
252207 – 252210.
Here is a summary of the commands I used:
To copy files from ASM:
for i in $(asmcmd ls
+ARCH01/PROD/ARCHIVELOG/2014_11_04); do
asmcmd cp
+ARCH01/PROD/ARCHIVELOG/2014_11_04/$i /cdc_arch_staging/PROD/arch
done
Note that this copied them to the file system with names
like “thread_1_seq_252369.6617.862722301”, and I had to rename them to
“PROD_1_708192618_0000252369.arc” with a combination of awk and manually
editing.
I had to restore archived logs that had been deleted from ASM by the clean up job:
connect target /
connect catalog
rman/xxx@RMAN;
run {
set archivelog
destination to '/cdc_arch_staging/PROD/arch';
allocate
channel T1 type SBT_TAPE;
allocate
channel T2 type SBT_TAPE;
allocate
channel T3 type SBT_TAPE;
allocate
channel T4 type SBT_TAPE;
restore (archivelog
from logseq=252208 until logseq=252423);
}
Registering files with CDC:
alter
database register or replace logical logfile '/cdc_arch_staging/PROD/arch/PROD_1_708192618_0000252491.arc'
for 'CDC$C_CS_IEE';
alter
database register or replace logical logfile
'/cdc_arch_staging/PROD/arch/PROD_1_708192618_0000252492.arc' for
'CDC$C_CS_IEE';
alter
database register or replace logical logfile '/cdc_arch_staging/PROD/arch/PROD_1_708192618_0000252493.arc'
for 'CDC$C_CS_IEE';
I created a script with awk to do that, using an 'ls' to list the logs to a file and then wrapping the command around it.
Once I’d run that, I checked the alert log and it was mining
away.
Monday, September 29, 2014
Script to list sessions on a RAC instance by node
As part of the Exadata Patching, I needed to provide the application owners with a list of sessions running on the node.
This script does that - it uses the oratab to grab the database names then loops through them and runs a couple of SQL scripts.
I thought I'd put it up here because it can be adapted to run a SQL command against every database on a host, something I often have cause to do and end up having to write the script again. You may need to change the 'dbhome_1' to 'db_1' depending on how you installed.
#!/bin/sh
#
# This script lists all sessions connected to the specified hostname in the query.
# It ignores the SYS, DBSNMP and PUBLIC users
#
# Hard code the ORACLE_HOME and a valid SID, and the host_name
#
export ORACLE_HOME=/u01/app/oracle/product/11.2.0.4/dbhome_1
export ORACLE_SID=PROD1
export PATH=$ORACLE_HOME/bin:$PATH
rm sessions.lst
db_name=`grep dbhome_1 /etc/oratab | cut -f 1 -d ':' | grep "[1-9]\b" | grep -v "#"`
for db in $db_name
do
export ORACLE_SID=$db
sqlplus -s "/ as sysdba" <
col inst_id form 99
col sid form 99999
col serial# form 9999999
col program form a40
col username form a20
col host_name form a10
set lines 160
set feedback off
spool ${db}_sessions.txt
select instance_name from v\$instance;
set feedback on
select a.inst_id,b.host_name,a.sid,a.serial#,a.program,a.username
from gv\$session a, gv\$instance b where username is not null and a.inst_id=b.inst_id and b.host_name='xu1db06' and username
not in ('SYS','DBSNMP','PUBLIC') order by 1;
spool off
exit
EOF
done
for f in `ls *.txt`
do
cat $f >> sessions.lst
done
rm *sessions.txt
#
# End of Script
#
While I'm at it, here's a script that sets up the patching process - it lists the services running, and generates the scripts to stop the services, the instances, and then restart them at the end.
#!/bin/sh
#
# This script lists all services running on the node
# and generates scripts to stop and start them
#
# Hard code the node name in the line below where it says "NODENAME"
#
db_name=`grep dbhome_1 /etc/oratab | cut -f 1 -d ':' | grep -v "[1-9]\b" | grep -v "#"`
echo "Make sure you've changed the node name in the script - ctrl-c to exit this if you didn't, otherwise hit Enter"
read input
rm service_status.lst
rm stop_services.sh
rm services_before_patch.lst
echo Scanning for Services...will take a minute
for db in $db_name
do
srvctl status service -d $db >> service_status.tmp
tail -1 service_status.tmp
done
cp service_status.tmp services_before_patch.lst
grep -v not service_status.tmp > service_status.lst
rm service_status.tmp
echo
echo
echo "This has been saved in services_before_patch.lst"
#
# Hard code the node name here (replace NODENAME)
awk '{print $2" "$7}' service_status.lst | awk -F, '{print $1}' | sed 's/[0-9]$//' | awk '{print "srvctl stop service -d "$2" -s "$1" -n NODENAME "}' > stop_services.sh
sed 's/stop/start/g' stop_services.sh > start_services.sh
inst_name=`ps -ef | grep pmon | grep -v grep | grep -v perl |awk '{print $8}' | grep -v ASM | cut -c 10-`
for i_name in $inst_name
do
echo "srvctl stop instance -d "$i_name" -i "$i_name >> temp_si
done
cat temp_si | sed 's/[0-9]//' > temp_si_1
grep -v XUFSDB temp_si_1 > stop_instances.sh
rm temp_si temp_si_1
sed 's/stop/start/g' stop_instances.sh > start_instances.sh
echo
echo "You need to do the following:"
echo
echo "chmod +x stop_services.sh"
echo
echo "chmod +x start_services.sh"
echo
echo "chmod +x stop_instances.sh"
echo
echo "chmod +x start_instances.sh"
echo
echo "Then run them in this order once the application owners have been advised:"
echo
echo "./stop_services.sh"
echo
echo "./stop_instances.sh"
echo
echo "Stop the DBFS mount service and the XU1FSDB instance"
echo
echo "APPLY THE PATCH"
echo
echo "./start_services.sh"
echo
echo "Restart the DBFS mount service"
echo
echo "./start_instances.sh"
echo
echo "Compare the running services and instances before and after the stop/start"
#
# End of Script
#
This script does that - it uses the oratab to grab the database names then loops through them and runs a couple of SQL scripts.
I thought I'd put it up here because it can be adapted to run a SQL command against every database on a host, something I often have cause to do and end up having to write the script again. You may need to change the 'dbhome_1' to 'db_1' depending on how you installed.
#!/bin/sh
#
# This script lists all sessions connected to the specified hostname in the query.
# It ignores the SYS, DBSNMP and PUBLIC users
#
# Hard code the ORACLE_HOME and a valid SID, and the host_name
#
export ORACLE_HOME=/u01/app/oracle/product/11.2.0.4/dbhome_1
export ORACLE_SID=PROD1
export PATH=$ORACLE_HOME/bin:$PATH
rm sessions.lst
db_name=`grep dbhome_1 /etc/oratab | cut -f 1 -d ':' | grep "[1-9]\b" | grep -v "#"`
for db in $db_name
do
export ORACLE_SID=$db
sqlplus -s "/ as sysdba" <
col inst_id form 99
col sid form 99999
col serial# form 9999999
col program form a40
col username form a20
col host_name form a10
set lines 160
set feedback off
spool ${db}_sessions.txt
select instance_name from v\$instance;
set feedback on
select a.inst_id,b.host_name,a.sid,a.serial#,a.program,a.username
from gv\$session a, gv\$instance b where username is not null and a.inst_id=b.inst_id and b.host_name='xu1db06' and username
not in ('SYS','DBSNMP','PUBLIC') order by 1;
spool off
exit
EOF
done
for f in `ls *.txt`
do
cat $f >> sessions.lst
done
rm *sessions.txt
#
# End of Script
#
While I'm at it, here's a script that sets up the patching process - it lists the services running, and generates the scripts to stop the services, the instances, and then restart them at the end.
#!/bin/sh
#
# This script lists all services running on the node
# and generates scripts to stop and start them
#
# Hard code the node name in the line below where it says "NODENAME"
#
db_name=`grep dbhome_1 /etc/oratab | cut -f 1 -d ':' | grep -v "[1-9]\b" | grep -v "#"`
echo "Make sure you've changed the node name in the script - ctrl-c to exit this if you didn't, otherwise hit Enter"
read input
rm service_status.lst
rm stop_services.sh
rm services_before_patch.lst
echo Scanning for Services...will take a minute
for db in $db_name
do
srvctl status service -d $db >> service_status.tmp
tail -1 service_status.tmp
done
cp service_status.tmp services_before_patch.lst
grep -v not service_status.tmp > service_status.lst
rm service_status.tmp
echo
echo
echo "This has been saved in services_before_patch.lst"
#
# Hard code the node name here (replace NODENAME)
awk '{print $2" "$7}' service_status.lst | awk -F, '{print $1}' | sed 's/[0-9]$//' | awk '{print "srvctl stop service -d "$2" -s "$1" -n NODENAME "}' > stop_services.sh
sed 's/stop/start/g' stop_services.sh > start_services.sh
inst_name=`ps -ef | grep pmon | grep -v grep | grep -v perl |awk '{print $8}' | grep -v ASM | cut -c 10-`
for i_name in $inst_name
do
echo "srvctl stop instance -d "$i_name" -i "$i_name >> temp_si
done
cat temp_si | sed 's/[0-9]//' > temp_si_1
grep -v XUFSDB temp_si_1 > stop_instances.sh
rm temp_si temp_si_1
sed 's/stop/start/g' stop_instances.sh > start_instances.sh
echo
echo "You need to do the following:"
echo
echo "chmod +x stop_services.sh"
echo
echo "chmod +x start_services.sh"
echo
echo "chmod +x stop_instances.sh"
echo
echo "chmod +x start_instances.sh"
echo
echo "Then run them in this order once the application owners have been advised:"
echo
echo "./stop_services.sh"
echo
echo "./stop_instances.sh"
echo
echo "Stop the DBFS mount service and the XU1FSDB instance"
echo
echo "APPLY THE PATCH"
echo
echo "./start_services.sh"
echo
echo "Restart the DBFS mount service"
echo
echo "./start_instances.sh"
echo
echo "Compare the running services and instances before and after the stop/start"
#
# End of Script
#
Sunday, September 7, 2014
Applying a rolling patch on an Exadata box
Another day, another new thing to do.
Never applied a rolling patch on RAC. Never applied a patch on Exadata.
So. The first thing to do, obviously, is to read the aptly named "README.txt" file.
Looked quite straightforward, no nasty surprises in store (I hoped).
One passage did confuse me:
For a RAC environment, shut down all the services (database, ASM, listeners, nodeapps, and CRS daemons) running from the Oracle home of the node you want to patch. After you patch this node, start the services on this node.Repeat this process for each of the other nodes of the Oracle RAC system. OPatch is used on only one node at a time.
Never applied a rolling patch on RAC. Never applied a patch on Exadata.
So. The first thing to do, obviously, is to read the aptly named "README.txt" file.
Looked quite straightforward, no nasty surprises in store (I hoped).
One passage did confuse me:
For a RAC environment, shut down all the services (database, ASM, listeners, nodeapps, and CRS daemons) running from the Oracle home of the node you want to patch. After you patch this node, start the services on this node.Repeat this process for each of the other nodes of the Oracle RAC system. OPatch is used on only one node at a time.
So did this mean I needed to shutdown ASM, listeners and the CRS on the host? Or just the databases in the HOME I was patching?
I decided to just do the databases in the HOME that was to be patched since it would fail if it wanted everything shut down and I'd revisit it if that was the case.
Here we go.
Check the databases in the ORACLE_HOME to be patched:
cat /etc/oratab | grep home_1
FSDB1:/u01/app/oracle/product/11.2.0.4/dbhome_1:N
TESTD1:/u01/app/oracle/product/11.2.0.4/dbhome_1:N
DEVD1:/u01/app/oracle/product/11.2.0.4/dbhome_1:N
Check to see if they are running
ps -ef | grep pmon
oracle 13742 1 0 Jun13 ? 00:22:19 asm_pmon_+ASM1
oracle 38331 1 0 Jun13 ? 00:21:28 ora_pmon_TESTD1
oracle 38343 1 0 Jun13 ? 00:27:10 ora_pmon_FSDB1
oracle 52291 1 0 Aug15 ? 00:07:28 ora_pmon_DEVD1
Stop the databases on the first node
srvctl stop instance -d TESTD -i TESTD1
srvctl stop instance -d DEVD -i DEVD1
srvctl stop instance -d FSDB -i FSDB1
PRCR-1133 : Failed to stop database FSDB and its running services
PRCR-1132 : Failed to stop resources using a filter
CRS-2529: Unable to act on 'ora.fsdb.db' because that would require stopping or relocating 'dbfs_mount', but the force option was not specified
CRS-2529: Unable to act on 'ora.fsdb.db' because that would require stopping or relocating 'dbfs_mount', but the force option was not specified
Ah. At this point I put the keyboard down and backed away slowly.
I checked with my colleagues, they said this is normal on an Exadata - there is a shared file system that is mounted on every node and this FSDB database controls it (or something)
Apparently it's OK to stop it on the nodes as long as nothing is using it.
/sbin/fuser /dbfs_direct/
If this comes back blank, all is OK.
You need to set the environment to the ASM database, then unmount the dbfs
ORACLE_SID = [DEVD1] ? +ASM1
oracle@xd1db01[+ASM1]/home/oracle> crsctl status resource dbfs_mount -t
--------------------------------------------------------------------------------
NAME TARGET STATE SERVER STATE_DETAILS
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
dbfs_mount
ONLINE ONLINE xd1db01
ONLINE ONLINE xd1db02
ONLINE ONLINE xd1db03
ONLINE ONLINE xd1db04
oracle@xd1db01[+ASM1]/home/oracle> crsctl stop resource dbfs_mount -n xd1db01
CRS-2673: Attempting to stop 'dbfs_mount' on 'xd1db01'
CRS-2677: Stop of 'dbfs_mount' on 'xd1db01' succeeded
oracle@xd1db01[+ASM1]/home/oracle> crsctl status resource dbfs_mount -t
--------------------------------------------------------------------------------
NAME TARGET STATE SERVER STATE_DETAILS
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
dbfs_mount
OFFLINE OFFLINE xd1db01
ONLINE ONLINE xd1db02
ONLINE ONLINE xd1db03
ONLINE ONLINE xd1db04
Try stopping the FSDB1 instance again
oracle@xd1db01[+ASM1]/home/oracle> . oraenv
ORACLE_SID = [+ASM1] ? FSDB1
oracle@xd1db01[FSDB1]/home/oracle> srvctl stop instance -d FSDB -i FSDB1
oracle@xd1db01[FSDB1]/home/oracle>
Check the oracle executable in the HOME isn't being used
cd $ORACLE_HOME/bin
/sbin/fuser oracle
This should come back blank
Set the PATH to include OPATCH
export PATH=/u01/app/oracle/product/11.2.0.4/dbhome_1/OPatch:$PATH
run opatch lsinventory so you can compare after the patch
opatch lsinventory
Oracle Interim Patch Installer version 11.2.0.3.4
Copyright (c) 2012, Oracle Corporation. All rights reserved.
Oracle Home : /u01/app/oracle/product/11.2.0.4/dbhome_1
Central Inventory : /u01/app/oraInventory
from : /u01/app/oracle/product/11.2.0.4/dbhome_1/oraInst.loc
OPatch version : 11.2.0.3.4
OUI version : 11.2.0.4.0
Log file location : /u01/app/oracle/product/11.2.0.4/dbhome_1/cfgtoollogs/opatch/opatch2014-09-08_08-46-27AM_1.log
Lsinventory Output file location : /u01/app/oracle/product/11.2.0.4/dbhome_1/cfgtoollogs/opatch/lsinv/lsinventory2014-09-08_08-46-27AM.txt
--------------------------------------------------------------------------------
Installed Top-level Products (1):
Oracle Database 11g 11.2.0.4.0
There are 1 products installed in this Oracle Home.
Interim patches (3) :
Patch 17839474 : applied on Thu Feb 27 09:22:39 EST 2014
Unique Patch ID: 17071278
Patch description: "DISKMON PATCH FOR EXADATA (JAN2014 - 11.2.0.4.3) : (17839474)"
Created on 22 Dec 2013, 21:51:42 hrs PST8PDT
Bugs fixed:
17839474
Patch 17984784 : applied on Thu Feb 27 09:22:21 EST 2014
Unique Patch ID: 17154638
Patch description: "CRS PATCH FOR EXADATA (JAN2014 - 11.2.0.4.3) : (17984784)"
Created on 8 Jan 2014, 22:53:15 hrs PST8PDT
Bugs fixed:
16613232, 14525998, 17065496, 17551223, 16346413
Patch 17943261 : applied on Thu Feb 27 09:21:42 EST 2014
Unique Patch ID: 17071278
Patch description: "DATABASE PATCH FOR EXADATA (JAN2014 - 11.2.0.4.3) : (17943261)"
Created on 1 Jan 2014, 21:20:56 hrs PST8PDT
Sub-patch 17741631; "DATABASE PATCH FOR EXADATA (DEC 2013 - 11.2.0.4.2) : (17741631)"
Sub-patch 17628006; "DATABASE PATCH FOR EXADATA (NOV 2013 - 11.2.0.4.1) : (17628006)"
Bugs fixed:
17288409, 13944971, 16450169, 17265217, 16180763, 16220077, 17465741
17614227, 16069901, 14010183, 16285691, 17726838, 13364795, 17088068
17612828, 17443671, 17080436, 17761775, 16721594, 16043574, 16837842
17446237, 16863422, 17332800, 13609098, 17610798, 17501491, 17239687
17468141, 17752121, 17602269, 16850630, 17346671, 17313525, 14852021
17783588, 17437634, 13866822, 12905058, 17546761
Rac system comprising of multiple nodes
Local node = xd1db01
Remote node = xd1db02
Remote node = xd1db03
Remote node = xd1db04
This assumes you've already extracted the patch to a directory and are in that directory
Check the patch doesn't conflict
opatch prereq CheckConflictAgainstOHWithDetail -ph ./
Oracle Interim Patch Installer version 11.2.0.3.4
Copyright (c) 2012, Oracle Corporation. All rights reserved.
PREREQ session
Oracle Home : /u01/app/oracle/product/11.2.0.4/dbhome_1
Central Inventory : /u01/app/oraInventory
from : /u01/app/oracle/product/11.2.0.4/dbhome_1/oraInst.loc
OPatch version : 11.2.0.3.4
OUI version : 11.2.0.4.0
Log file location : /u01/app/oracle/product/11.2.0.4/dbhome_1/cfgtoollogs/opatch/opatch2014-09-08_08-47-04AM_1.log
Invoking prereq "checkconflictagainstohwithdetail"
Prereq "checkConflictAgainstOHWithDetail" passed.
OPatch succeeded.
Looks good to go - apply the patch
opatch apply
Oracle Interim Patch Installer version 11.2.0.3.4
Copyright (c) 2012, Oracle Corporation. All rights reserved.
Oracle Home : /u01/app/oracle/product/11.2.0.4/dbhome_1
Central Inventory : /u01/app/oraInventory
from : /u01/app/oracle/product/11.2.0.4/dbhome_1/oraInst.loc
OPatch version : 11.2.0.3.4
OUI version : 11.2.0.4.0
Log file location : /u01/app/oracle/product/11.2.0.4/dbhome_1/cfgtoollogs/opatch/18841764_Sep_08_2014_08_47_26/apply2014-09-08_08-47-26AM_1.log
Applying interim patch '18841764' to OH '/u01/app/oracle/product/11.2.0.4/dbhome_1'
Verifying environment and performing prerequisite checks...
All checks passed.
Provide your email address to be informed of security issues, install and
initiate Oracle Configuration Manager. Easier for you if you use your My
Oracle Support Email address/User Name.
Visit http://www.oracle.com/support/policies.html for details.
Email address/User Name:
You have not provided an email address for notification of security issues.
Do you wish to remain uninformed of security issues ([Y]es, [N]o) [N]: Y
This node is part of an Oracle Real Application Cluster.
Remote nodes: 'xd1db02' 'xd1db03' 'xd1db04'
Local node: 'xd1db01'
Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
(Oracle Home = '/u01/app/oracle/product/11.2.0.4/dbhome_1')
Is the local system ready for patching? [y|n]
OK, enter 'Y' to apply the patch
y
User Responded with: Y
Backing up files...
Patching component oracle.network.rsf, 11.2.0.4.0...
Patching component oracle.rdbms.rsf, 11.2.0.4.0...
Patching component oracle.rdbms, 11.2.0.4.0...
Verifying the update...
The local system has been patched. You can restart Oracle instances on it.
Patching in rolling mode.
Remaining nodes to be patched:
'xd1db02' 'xd1db03' 'xd1db04'
What is the next node to be patched?
OK, that was painless
I left this session open and opened another putty session to the first node and restarted the databases
oracle@xd1db01[FSDB1]/home/oracle> . oraenv
ORACLE_SID = [FSDB1] ? +ASM1
oracle@xd1db01[+ASM1]/home/oracle>
oracle@xd1db01[+ASM1]/home/oracle> crsctl start resource dbfs_mount -n xd1db01
CRS-2672: Attempting to start 'ora.fsdb.db' on 'xd1db01'
CRS-2676: Start of 'ora.fsdb.db' on 'xd1db01' succeeded
CRS-2672: Attempting to start 'dbfs_mount' on 'xd1db01'
CRS-2676: Start of 'dbfs_mount' on 'xd1db01' succeeded
oracle@xd1db01[+ASM1]/home/oracle>
oracle@xd1db01[+ASM1]/home/oracle> srvctl start instance -d FSDB -i FSDB1
PRCC-1015 : FSDB was already running on xd1db01
PRCR-1004 : Resource ora.fsdb.db is already running
oracle@xd1db01[+ASM1]/home/oracle> crsctl status resource dbfs_mount -t
--------------------------------------------------------------------------------
NAME TARGET STATE SERVER STATE_DETAILS
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
dbfs_mount
ONLINE ONLINE xd1db01
ONLINE ONLINE xd1db02
ONLINE ONLINE xd1db03
ONLINE ONLINE xd1db04
oracle@xd1db01[DEVD1]/home/oracle>
oracle@xd1db01[DEVD1]/home/oracle> srvctl start instance -d DEVD -i DEVD1
oracle@xd1db01[DEVD1]/home/oracle> srvctl start instance -d TESTD -i TESTD1
All started OK.
Opened a session on the second node to be patched and shut down the databases there
oracle@xd1db02[+ASM2]/home/oracle> crsctl stop resource dbfs_mount -n xd1db02
CRS-2673: Attempting to stop 'dbfs_mount' on 'xd1db02'
CRS-2677: Stop of 'dbfs_mount' on 'xd1db02' succeeded
oracle@xd1db02[+ASM2]/home/oracle> crsctl status resource dbfs_mount -t
--------------------------------------------------------------------------------
NAME TARGET STATE SERVER STATE_DETAILS
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
dbfs_mount
ONLINE ONLINE xd1db01
OFFLINE OFFLINE xd1db02
ONLINE ONLINE xd1db03
ONLINE ONLINE xd1db04
oracle@xd1db02[+ASM2]/home/oracle>
oracle@xd1db02[DEVD2]/home/oracle> srvctl stop instance -d DEVD -i DEVD2
oracle@xd1db02[DEVD2]/home/oracle> srvctl stop instance -d FSDB -i FSDB2
oracle@xd1db02[DEVD2]/home/oracle> srvctl stop instance -d TESTD -i TESTD2
All stopped OK, back to the session on the first node where the patch apply is waiting for a response
Remaining nodes to be patched:
'xd1db02' 'xd1db03' 'xd1db04'
What is the next node to be patched?
xd1db02
You have selected 'xd1db02' from 'xd1db02' 'xd1db03' 'xd1db04'
The node 'xd1db02' will be patched next.
Please shutdown Oracle instances running out of this ORACLE_HOME on 'xd1db02'.
(Oracle Home = '/u01/app/oracle/product/11.2.0.4/dbhome_1')
Is the node ready for patching? [y|n]
y
User Responded with: Y
Updating nodes 'xd1db02'
Apply-related files are:
FP = "/u01/app/oracle/product/11.2.0.4/dbhome_1/.patch_storage/18841764_Jun_26_2014_03_07_07/rac/copy_files.txt"
DP = "/u01/app/oracle/product/11.2.0.4/dbhome_1/.patch_storage/18841764_Jun_26_2014_03_07_07/rac/copy_dirs.txt"
MP = "/u01/app/oracle/product/11.2.0.4/dbhome_1/.patch_storage/18841764_Jun_26_2014_03_07_07/rac/make_cmds.txt"
RC = "/u01/app/oracle/product/11.2.0.4/dbhome_1/.patch_storage/18841764_Jun_26_2014_03_07_07/rac/remote_cmds.txt"
Instantiating the file "/u01/app/oracle/product/11.2.0.4/dbhome_1/.patch_storage/18841764_Jun_26_2014_03_07_07/rac/copy_files.txt.instantiated" by replacing $ORACLE_HOME in "/u01/app/oracle/product/11.2.0.4/dbhome_1/.patch_storage/18841764_Jun_26_2014_03_07_07/rac/copy_files.txt" with actual path.
Propagating files to remote nodes...
Instantiating the file "/u01/app/oracle/product/11.2.0.4/dbhome_1/.patch_storage/18841764_Jun_26_2014_03_07_07/rac/copy_dirs.txt.instantiated" by replacing $ORACLE_HOME in "/u01/app/oracle/product/11.2.0.4/dbhome_1/.patch_storage/18841764_Jun_26_2014_03_07_07/rac/copy_dirs.txt" with actual path.
Propagating directories to remote nodes...
Instantiating the file "/u01/app/oracle/product/11.2.0.4/dbhome_1/.patch_storage/18841764_Jun_26_2014_03_07_07/rac/make_cmds.txt.instantiated" by replacing $ORACLE_HOME in "/u01/app/oracle/product/11.2.0.4/dbhome_1/.patch_storage/18841764_Jun_26_2014_03_07_07/rac/make_cmds.txt" with actual path.
Running command on remote node 'xd1db02':
cd /u01/app/oracle/product/11.2.0.4/dbhome_1/network/lib; /usr/bin/make -f ins_net_client.mk client_sharedlib ORACLE_HOME=/u01/app/oracle/product/11.2.0.4/dbhome_1 || echo REMOTE_MAKE_FAILED::>&2
Running command on remote node 'xd1db02':
cd /u01/app/oracle/product/11.2.0.4/dbhome_1/rdbms/lib; /usr/bin/make -f ins_rdbms.mk liborasdksh ORACLE_HOME=/u01/app/oracle/product/11.2.0.4/dbhome_1 || echo REMOTE_MAKE_FAILED::>&2
Running command on remote node 'xd1db02':
cd /u01/app/oracle/product/11.2.0.4/dbhome_1/rdbms/lib; /usr/bin/make -f ins_rdbms.mk client_sharedlib ORACLE_HOME=/u01/app/oracle/product/11.2.0.4/dbhome_1 || echo REMOTE_MAKE_FAILED::>&2
Running command on remote node 'xd1db02':
cd /u01/app/oracle/product/11.2.0.4/dbhome_1/rdbms/lib; /usr/bin/make -f ins_rdbms.mk ioracle ORACLE_HOME=/u01/app/oracle/product/11.2.0.4/dbhome_1 || echo REMOTE_MAKE_FAILED::>&2
The node 'xd1db02' has been patched. You can restart Oracle instances on it.
Not sure what these "REMOTE_MAKE_FAILED" messages were.
Went back to the second node and restarted the databases
oracle@xd1db02[+ASM2]/home/oracle> crsctl start resource dbfs_mount -n xd1db02
CRS-2672: Attempting to start 'ora.fsdb.db' on 'xd1db02'
CRS-2676: Start of 'ora.fsdb.db' on 'xd1db02' succeeded
CRS-2672: Attempting to start 'dbfs_mount' on 'xd1db02'
CRS-2676: Start of 'dbfs_mount' on 'xd1db02' succeeded
oracle@xd1db02[+ASM2]/home/oracle> crsctl status resource dbfs_mount -t
--------------------------------------------------------------------------------
NAME TARGET STATE SERVER STATE_DETAILS
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
dbfs_mount
ONLINE ONLINE xd1db01
ONLINE ONLINE xd1db02
ONLINE ONLINE xd1db03
ONLINE ONLINE xd1db04
oracle@xd1db02[+ASM2]/home/oracle> srvctl start instance -d DEVD -i DEVD2
oracle@xd1db02[+ASM2]/home/oracle> srvctl start instance -d TESTD -i TESTD2
oracle@xd1db02[+ASM2]/home/oracle> ps -ef | grep pmon
oracle 13690 1 0 Jun24 ? 00:19:37 asm_pmon_+ASM2
oracle 23768 1 0 Aug13 ? 00:09:43 ora_pmon_DEVD2
oracle 41299 1 0 Aug15 ? 00:08:13 ora_pmon_TESTD2
Back to the first node
Remaining nodes to be patched:
'xd1db03' 'xd1db04'
What is the next node to be patched?
I don't want to patch these nodes, so did a ctrl-c to exit
OPatch failed with error code 130
Ignored this.
Check the patches were applied
opatch lsinventory on both nodes
xd1db01:
Patch 18841764 : applied on Mon Sep 08 08:47:50 EST 2014
Unique Patch ID: 17779414
Created on 26 Jun 2014, 03:07:07 hrs PST8PDT
Bugs fixed:
18841764
xd1db02:
Patch 18841764 : applied on Mon Sep 08 08:47:50 EST 2014
Unique Patch ID: 17779414
Created on 26 Jun 2014, 03:07:07 hrs PST8PDT
Bugs fixed:
18841764
So I'm not sure what the "REMOTE_MAKE_FAILED" messages were referring to - the patch applied successfully on the second node.
That was it, not too hard. If I'd known about the FSDB before it wouldn't have thrown me.
Thursday, September 4, 2014
Creating a duplicate database on the same host
I was asked to do this, and after my first abortive attempt that some how crashed the source database (see the last post) tried again. This time it worked, very easily, and I have no idea why the first attempt failed.
This was on RAC, so ignore the srvctl commands for the listener and just do a lsnrctl stop and lsnrctl start if you're not on RAC.
This was on RAC, so ignore the srvctl commands for the listener and just do a lsnrctl stop and lsnrctl start if you're not on RAC.
DUPDB creation from SRCDB on the same host
Add entry to /etc/oratab (or /var/opt/oracle/oratab)
DUPDB:/u01/app/oracle/product/11.2.0.4/dbhome_1:N
Create basic init ora file
.
oraenv
DUPDB
cd
$ORACLE_HOME/dbs
vi
initDUPDB.ora
DB_NAME=DUPDB
control_files='+DATA/DUPDB/CONTROLFILE/DUPDB.ctl'
db_file_name_convert='+DATA/SRCDB','+DATA/DUPDB'
log_file_name_convert='+REDO/SRCDB','+REDO/DUPDB'
log_file_dest_1='LOCATION=+RECO'
log_file_dest_1='LOCATION=+RECO'
compatible=11.2.0.4
db_unique_name='DUPDB'
compatible is needed otherwise it won’t find the ASM disk
group.
ORA-19504: failed
to create file "+DATA/dupdb/controlfile/dupdb.ctl"
ORA-17502:
ksfdcre:3 Failed to create file +DATA/dupdb/controlfile/dupdb.ctl
ORA-15001:
diskgroup "DATA" does not exist or is not mounted
ORA-15204:
database version 11.2.0.0.0 is incompatible with diskgroup DATA
Create audit dest
oracle@devsrv[DUPDB]/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs>
mkdir /u01/app/oracle/admin/DUPDB
oracle@devsrv[DUPDB]/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs>
mkdir /u01/app/oracle/admin/DUPDB/adump
Backup listener.ora and add entry for new database
(SID_DESC =
(SID_NAME = DUPDB) (GLOBAL_DBNAME=DUPDB) (ORACLE_HOME =
/u01/app/oracle/product/11.2.0.4/dbhome_1))
Stop and restart the listener
srvctl stop listener
-n devsrv -l listener
srvctl start
listener -n devsrv -l listener
or
lsnrctl stop
lsnrctl start
Backup the source database (when I tried to do it from the
active database it overwrote the source control file and I had to recreate it
and recover the database). You will use this backup to create the document.
. oraenv
SRCDB
rman
connect target /
run
{
allocate channel d1 device type disk format '/backup/%U.bkp';
backup database include current controlfile;
backup archivelog all;
release channel d1;
}
. oraenv
DUPDB
Start the database in nomount mode
sys.DUPDB[DUPDB]>
startup nomount
ORACLE instance
started.
Total System
Global Area 634753024 bytes
Fixed Size 2255752 bytes
Variable
Size 486540408 bytes
Database Buffers 134217728 bytes
Redo Buffers 11739136 bytes
Connect to rman and run the duplicate command
oracle@devsrv[DUPDB]>
rman
connected
to target database: SRCDB (DBID=4454375200)
connected
to auxiliary database: DUPDB (not mounted)
RMAN>
duplicate target database to DUPDB;
Starting
Duplicate Db at 04-SEP-14
using
target database control file instead of recovery catalog
allocated
channel: ORA_AUX_DISK_1
channel
ORA_AUX_DISK_1: SID=64 device type=DISK
contents
of Memory Script:
{
sql clone "create spfile from
memory";
}
executing
Memory Script
sql
statement: create spfile from memory
contents
of Memory Script:
{
shutdown clone immediate;
startup clone nomount;
}
executing
Memory Script
Oracle
instance shut down
connected
to auxiliary database (not started)
Oracle
instance started
Total
System Global Area 634753024 bytes
Fixed
Size 2255752 bytes
Variable
Size 486540408 bytes
Database
Buffers 134217728 bytes
Redo
Buffers 11739136 bytes
contents
of Memory Script:
{
sql clone "alter system set db_name =
''SRCDB'' comment=
''Modified by RMAN duplicate'' scope=spfile";
sql clone "alter system set db_unique_name =
''DUPDB'' comment=
''Modified by RMAN duplicate''
scope=spfile";
shutdown clone immediate;
startup clone force nomount
restore clone primary controlfile;
alter clone database mount;
}
executing
Memory Script
sql
statement: alter system set db_name
= ''SRCDB'' comment= ''Modified by RMAN
duplicate'' scope=spfile
sql
statement: alter system set
db_unique_name = ''DUPDB''
comment= ''Modified by RMAN duplicate'' scope=spfile
Oracle
instance shut down
Oracle
instance started
Total
System Global Area 634753024 bytes
Fixed
Size 2255752 bytes
Variable
Size 486540408 bytes
Database
Buffers 134217728 bytes
Redo
Buffers 11739136 bytes
Starting
restore at 04-SEP-14
allocated
channel: ORA_AUX_DISK_1
channel
ORA_AUX_DISK_1: SID=64 device type=DISK
channel
ORA_AUX_DISK_1: starting datafile backup set restore
channel
ORA_AUX_DISK_1: restoring control file
channel
ORA_AUX_DISK_1: reading from backup piece /backup/02phlndl_1_1.bkp
channel
ORA_AUX_DISK_1: piece handle=/backup/02phlndl_1_1.bkp
tag=TAG20140904T141003
channel
ORA_AUX_DISK_1: restored backup piece 1
channel
ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
output
file name=+DATA/dupdb/controlfile/dupdb.ctl
Finished
restore at 04-SEP-14
database
mounted
RMAN-05529:
WARNING: DB_FILE_NAME_CONVERT resulted in invalid ASM names; names changed to
disk group only.
contents
of Memory Script:
{
set until scn 7668683392612;
set newname for datafile 1 to
"+data";
set newname for datafile 2 to
"+data";
set newname for datafile 3 to
"+data";
.
.
channel
ORA_AUX_DISK_1: starting datafile backup set restore
channel
ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel
ORA_AUX_DISK_1: restoring datafile 00001 to +data
.
.
channel
ORA_AUX_DISK_1: reading from backup piece /backup/01phlmts_1_1.bkp
Duplicate will continue
Run asmcmd –p and check the files are getting written:
ASMCMD [+DATA/DUPDB/DATAFILE]
> ls
ARCHIVE_DEV.3229.857401553
DATA_FIX_TAB01.3228.857401627
OMS_DATA.3237.857401553
RMAN duplicate finished
datafile 41
switched to datafile copy
input datafile copy
RECID=40 STAMP=857402281 file name=+DATA/dupdb/datafile/po_simulation.3271.857401757
datafile 42
switched to datafile copy
input datafile
copy RECID=41 STAMP=857402281 file name=+DATA/dupdb/datafile/po_simulation_idx.3272.857401757
datafile 43
switched to datafile copy
input datafile
copy RECID=42 STAMP=857402281 file name=+DATA/dupdb/datafile/streams_data.3273.857401757
datafile 44
switched to datafile copy
input datafile
copy RECID=43 STAMP=857402281 file name=+DATA/dupdb/datafile/ulog_tab01.3274.857401757
datafile 45
switched to datafile copy
input datafile
copy RECID=44 STAMP=857402281 file name=+DATA/dupdb/datafile/xdb.3275.857401757
contents of
Memory Script:
{
Alter clone database open resetlogs;
}
executing Memory
Script
database opened
Finished
Duplicate Db at 04-SEP-14
Put the listener.ora back to remove the static entry and
restart
cp
listener.ora20140904 listener.ora
srvctl stop listener
-n devsrv -l listener
srvctl start
listener -n devsrv -l listener
or
lsnrctl stop
lsnrctl start
lsnrctl stat
Service "DUPDB"
has 1 instance(s).
Instance "DUPDB", status READY,
has 1 handler(s) for this service...
Service "DUPDBXDB"
has 1 instance(s).
Instance "DUPDB", status READY,
has 1 handler(s) for this service...
Copy the SRCDB spfile to a pfile and update the DUPDB
parameters
. oraenv
SRCDB
Sqlplus “/ as
sysdba”
Create pfile=/tmp/initDUPDB.ora
from spfile;
Edit the file and change all the SRCDB to DUPDB and remove
references to RAC instances if present:
*.archive_lag_target=900
*.audit_file_dest='/u01/app/oracle/admin/DUPDB/adump'
*.audit_trail='none'
*.cluster_database=FALSE
*.compatible='11.2.0.4.0'
*.control_files='+DATA/DUPDB/controlfile/current.3233.857401533'
*.cursor_sharing='similar'
*.db_block_checksum='true'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_file_multiblock_read_count=30
*.db_name='DUPDB'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP)
(SERVICE=DUPDBXDB)'
*.fast_start_mttr_target=180
*.filesystemio_options='none'
*.global_names=false
*.job_queue_processes=0
*.log_archive_dest_1='LOCATION=+RECO'
*.log_archive_format='%t_%s_%r.arc'
*.open_cursors=300
*.optimizer_index_caching=80
*.optimizer_index_cost_adj=20
*.pga_aggregate_target=419430400
*.processes=1000
*.remote_listener='xd1-scan1:1521'
*.remote_login_passwordfile='exclusive'
*.session_cached_cursors=60
*.sessions=1105
*.sga_target=2000m
*.shared_pool_reserved_size=19293798
*.timed_os_statistics=10
*.undo_retention=3600
Stop and restart the database and implement the new spfile
NOLOG> startup
pfile=/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/initDUPDB.ora
ORACLE instance
started.
NOLOG> create
spfile='+DATA/DUPDB/PARAMETERFILE/spfileDUPDB.ora' from pfile;
File created.
NOLOG>
shutdown immediate;
Database closed.
Database
dismounted.
ORACLE instance
shut down.
Edit the initDUPDB.ora file to point to the spfile:
SPFILE='+DATA/DUPDB/PARAMETERFILE/spfileDUPDB.ora'
NOLOG> startup
ORACLE instance
started.
Total System
Global Area 2087780352 bytes
Fixed Size 2254824 bytes
Variable
Size 822085656 bytes
Database
Buffers 1241513984 bytes
Redo Buffers 21925888 bytes
Database mounted.
Database opened.
sys.DUPDB[DUPDB]>
show parameter spfile
NAME TYPE VALUE
------------------------------------
-------------------------------- ------------------------------
spfile string +DATA/dupdb/parameterfile/spfiledupdb.ora
Make sure the archived logs are pointing to the right
location
NOLOG> archive
log list
Database log
mode Archive Mode
Automatic
archival Enabled
Archive
destination +RECO
Oldest online log
sequence 86
Next log sequence
to archive 87
Current log
sequence 87
All done!
Subscribe to:
Posts (Atom)