Maaz Anjum's Blog

Wednesday, October 30, 2013

SRVCTL: Add Preferred Instances

Dedicated to an old friend and colleague.

1. Add the service. Please take note, the instance names are in UPPER case.

[oowner@gxtbdb01 ~]$ srvctl status service -d utldb

[oowner@gxtbdb01 ~]$ srvctl add service -d utldb -s test -r UTLDB1

2. Start it

[oowner@gxtbdb01 ~]$ srvctl status service -d utldb

Service test is not running.

[oowner@gxtbdb01 ~]$ srvctl start service -d utldb -s test

[oowner@gxtbdb01 ~]$ srvctl status service -d utldb

Service test is running on instance(s) UTLDB1

3. Modify it to add a new instance to the preferred list

[oowner@gxtbdb01 ~]$ srvctl modify service -d utldb -s test -n -i UTLDB1,UTLDB2

[oowner@gxtbdb01 ~]$ srvctl status service -d utldb

Service test is running on instance(s) UTLDB1

4. Service has to be restarted for the changes to take into effect

[oowner@gxtbdb01 ~]$ srvctl stop service -d utldb -s test

[oowner@gxtbdb01 ~]$ srvctl start service -d utldb -s test

[oowner@gxtbdb01 ~]$ srvctl status service -d utldb

Service test is running on instance(s) UTLDB1,UTLDB2

Cheers!

continue reading "SRVCTL: Add Preferred Instances"

Add Your Opinion 0 Opinions

Sunday, October 20, 2013

Oracle Golden Gate 12.1.2

I've only just caught up on the new OGG release news! Stepping through just the documentation, I'm excited about trying it out in the next few weeks.

Edited December 19, 2013: It took me a while to get around it, but I did write a post on installation/upgrade to this version here.

NewImage

http://docs.oracle.com/goldengate/1212/gg-winux/index.html

Cheers!

continue reading "Oracle Golden Gate 12.1.2"

Add Your Opinion 0 Opinions

Tuesday, October 15, 2013

How to Resume an RMAN Duplicate on Failure - Just Re-Run it!

If you find yourself at the with an error message like below during an RMAN Duplicate, don’t worry about it. As I found out from dumb luck, it’s quite easy!

RMAN-03002: failure of recover command at 10/07/2013 15:11:47
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 53302 and starting SCN of 143193979270

The requirement was to clone production, without causing additional load on it, to a new environment that I would use for Golden Gate replication.

Database: 11.1.0.7.0 64bit

Server: Solaris 5.10 x86 64bit

Backup Location: Data Domain

Let me show the duplicate script first:

RUN
{
        ALLOCATE auxiliary CHANNEL CH01 TYPE 'SBT_TAPE' parms "ENV=(NB_ORA_POLICY=ORACLE_RAC01_PRD1_FULL,NB_ORA_CLIENT=RAC01,NB_ORA_SCHED=Default-Application-Backup)";
        ALLOCATE auxiliary CHANNEL CH02 TYPE 'SBT_TAPE' parms "ENV=(NB_ORA_POLICY=ORACLE_RAC01_PRD1_FULL,NB_ORA_CLIENT=RAC01,NB_ORA_SCHED=Default-Application-Backup)";
        ALLOCATE auxiliary CHANNEL CH03 TYPE 'SBT_TAPE' parms "ENV=(NB_ORA_POLICY=ORACLE_RAC01_PRD1_FULL,NB_ORA_CLIENT=RAC01,NB_ORA_SCHED=Default-Application-Backup)";
        ALLOCATE auxiliary CHANNEL CH04 TYPE 'SBT_TAPE' parms "ENV=(NB_ORA_POLICY=ORACLE_RAC01_PRD1_FULL,NB_ORA_CLIENT=RAC01,NB_ORA_SCHED=Default-Application-Backup)";
        ALLOCATE auxiliary CHANNEL CH05 TYPE 'SBT_TAPE' parms "ENV=(NB_ORA_POLICY=ORACLE_RAC01_PRD1_FULL,NB_ORA_CLIENT=RAC01,NB_ORA_SCHED=Default-Application-Backup)";
        ALLOCATE auxiliary CHANNEL CH06 TYPE 'SBT_TAPE' parms "ENV=(NB_ORA_POLICY=ORACLE_RAC01_PRD1_FULL,NB_ORA_CLIENT=RAC01,NB_ORA_SCHED=Default-Application-Backup)";

        SET NEWNAME FOR DATAFILE 54 TO '/u03/oradata/RPTPRD/data_01.365.668441285';
        SET NEWNAME FOR DATAFILE 54 TO '/u03/oradata/RPTPRD/data_01.365.668441285';
        . . .
        . . .
        SET NEWNAME FOR DATAFILE 54 TO '/u03/oradata/RPTPRD/data_01.365.668441285';

        DUPLICATE TARGET DATABASE TO RPTPRD
                UNTIL SCN 143197743846
                LOGFILE GROUP 1 ('/r01/oradata/RPTPRD/redo01a.log',
                                '/r02/oradata/RPTPRD/redo01b.log') SIZE 500M REUSE,
                        GROUP 2 ('/r01/oradata/RPTPRD/redo02a.log',
                                '/r02/oradata/RPTPRD/redo02b.log') SIZE 500M REUSE,
                        GROUP 3 ('/r01/oradata/RPTPRD/redo03a.log',
                                '/r02/oradata/RPTPRD/redo03b.log') SIZE 500M REUSE
                NOFILENAMECHECK
                SPFILE
                PARAMETER_VALUE_CONVERT 'PRD','RPTPRD'
                SET CONTROL_FILES='/u01/oradata/RPTPRD/control01.ctl','/u02/oradata/RPTPRD/control02.ctl','/u03/oradata/RPTPRD/control03.ctl'
                SET DB_CREATE_FILE_DEST='/u03/oradata/RPTPRD'
                SET AUDIT_FILE_DEST='/u01/app/oracle/admin/RPTPRD/adump'
                SET DB_RECOVERY_FILE_DEST='/a01/flash_recovery_area'
                set DIAGNOSTIC_DEST='/u01/app/oracle/'
                SET DB_RECOVERY_FILE_DEST_SIZE='20G'
                SET local_listener='(address=(protocol=TCP)(host=apvmrdb01)(port=1529))'
                SET FAL_CLIENT=''
                SET FAL_SERVER=''
                SET log_archive_dest_2=''
                SET dg_broker_start='FALSE'
                SET CLUSTER_DATABASE='FALSE'
                SET log_archive_dest_1='LOCATION=/a01/flash_recovery_area'
                SET db_create_online_log_dest_2='/r02/oradata/RPTPRD'
                SET db_create_online_log_dest_1='/r01/oradata/RPTPRD'
                SET standby_archive_dest='';
}

The actual run error is below.

Starting recover at 07-OCT-13

starting media recovery

unable to find archived log
archived log thread=1 sequence=53302
Oracle Error:
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/u03/oradata/RPTPRD/system.326.665261191'

released channel: CH01
released channel: CH02
released channel: CH03
released channel: CH04
released channel: CH05
released channel: CH06
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 10/07/2013 15:11:47
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 53302 and starting SCN of 143193979270

I knew that Data Domain had the backups, so it couldn’t be a case of missing pieces. By the way, the failure actually happened because my log_archive_dest_1 parameter was set to a location that didn’t exist on my auxiliary server.

So what now? I thought maybe I should drop the database and start from scratch. But, curiosity sometimes works in my favor. I simply restarted the script and bingo! It worked!!

Relevant excerpt from duplicate log:

contents of Memory Script:
{
   set until scn  143197743846;
   recover
   clone database
    delete archivelog
   ;
}
executing Memory Script

executing command: SET until clause

Starting recover at 07-OCT-13

starting media recovery

channel CH01: starting archived log restore to default destination
channel CH01: restoring archived log
archived log thread=1 sequence=53302
channel CH01: reading from backup piece al_180608_1_828139086
channel CH02: starting archived log restore to default destination
channel CH02: restoring archived log
archived log thread=2 sequence=46813
channel CH02: restoring archived log
archived log thread=1 sequence=53303
channel CH02: reading from backup piece al_180607_1_828139086
channel CH03: starting archived log restore to default destination
channel CH03: restoring archived log
archived log thread=2 sequence=46814
channel CH03: restoring archived log
archived log thread=2 sequence=46815
channel CH03: reading from backup piece al_180609_1_828139086
channel CH04: starting archived log restore to default destination
channel CH04: restoring archived log
archived log thread=1 sequence=53304
channel CH04: reading from backup piece al_180612_1_828145845
channel CH05: starting archived log restore to default destination
channel CH05: restoring archived log
archived log thread=2 sequence=46816
channel CH05: reading from backup piece al_180611_1_828145845
channel CH06: starting archived log restore to default destination
channel CH06: restoring archived log
archived log thread=1 sequence=53305
channel CH06: reading from backup piece al_180614_1_828153049
channel CH01: piece handle=al_180608_1_828139086 tag=TAG20131006T223805
channel CH01: restored backup piece 1
channel CH01: restore complete, elapsed time: 00:00:25
archived log file name=/a01/flash_recovery_area/1_53302_663959504.dbf thread=1 sequence=53302
channel CH01: starting archived log restore to default destination
channel CH01: restoring archived log
archived log thread=2 sequence=46817
channel CH01: reading from backup piece al_180616_1_828153049
channel CH03: piece handle=al_180609_1_828139086 tag=TAG20131006T223805
channel CH03: restored backup piece 1
channel CH03: restore complete, elapsed time: 00:00:25
channel CH02: piece handle=al_180607_1_828139086 tag=TAG20131006T223805
channel CH02: restored backup piece 1
channel CH02: restore complete, elapsed time: 00:00:40
archived log file name=/a01/flash_recovery_area/2_46813_663959504.dbf thread=2 sequence=0
channel clone_default: deleting archived log(s)
archived log file name=/a01/flash_recovery_area/2_46813_663959504.dbf RECID=7 STAMP=828199660
archived log file name=/a01/flash_recovery_area/2_46814_663959504.dbf thread=2 sequence=46814
channel clone_default: deleting archived log(s)
archived log file name=/a01/flash_recovery_area/2_46814_663959504.dbf RECID=4 STAMP=828199644
archived log file name=/a01/flash_recovery_area/2_46815_663959504.dbf thread=2 sequence=46815
channel clone_default: deleting archived log(s)
archived log file name=/a01/flash_recovery_area/1_53302_663959504.dbf RECID=3 STAMP=828199644
archived log file name=/a01/flash_recovery_area/1_53303_663959504.dbf thread=1 sequence=53303
channel clone_default: deleting archived log(s)
archived log file name=/a01/flash_recovery_area/1_53303_663959504.dbf RECID=2 STAMP=828199643
channel CH04: piece handle=al_180612_1_828145845 tag=TAG20131007T003044
channel CH04: restored backup piece 1
channel CH04: restore complete, elapsed time: 00:01:00
archived log file name=/a01/flash_recovery_area/1_53304_663959504.dbf thread=1 sequence=53304
channel clone_default: deleting archived log(s)
archived log file name=/a01/flash_recovery_area/2_46815_663959504.dbf RECID=1 STAMP=828199642
channel CH05: piece handle=al_180611_1_828145845 tag=TAG20131007T003044
channel CH05: restored backup piece 1
channel CH05: restore complete, elapsed time: 00:01:00
archived log file name=/a01/flash_recovery_area/2_46816_663959504.dbf thread=2 sequence=46816
channel clone_default: deleting archived log(s)
archived log file name=/a01/flash_recovery_area/1_53304_663959504.dbf RECID=5 STAMP=828199657
channel CH01: piece handle=al_180616_1_828153049 tag=TAG20131007T023048
channel CH01: restored backup piece 1
channel CH01: restore complete, elapsed time: 00:01:03
channel CH06: piece handle=al_180614_1_828153049 tag=TAG20131007T023048
channel CH06: restored backup piece 1
channel CH06: restore complete, elapsed time: 00:01:28
archived log file name=/a01/flash_recovery_area/1_53305_663959504.dbf thread=1 sequence=53305
channel clone_default: deleting archived log(s)
archived log file name=/a01/flash_recovery_area/2_46816_663959504.dbf RECID=6 STAMP=828199659
archived log file name=/a01/flash_recovery_area/2_46817_663959504.dbf thread=2 sequence=46817
channel clone_default: deleting archived log(s)
archived log file name=/a01/flash_recovery_area/1_53305_663959504.dbf RECID=8 STAMP=828199666
channel clone_default: deleting archived log(s)
archived log file name=/a01/flash_recovery_area/2_46817_663959504.dbf RECID=9 STAMP=828199670
media recovery complete, elapsed time: 00:00:08
Finished recover at 07-OCT-13

I’m a big fan of crediting others work, after I a re-ran the duplicate command, I google’d for “resume rman duplicate” and found April C. Sim’s blog on it!

Hope this helps.

Cheers!

continue reading "How to Resume an RMAN Duplicate on Failure - Just Re-Run it!"

Add Your Opinion 0 Opinions

Friday, September 27, 2013

Patch and Provision with EM12c: #3 Database Provisioning - Using Profiles

With my previous post on creating Profiles with OEM12c completed, I wanted to use them for actual deployments. If you recall, Mr. Green wants to make sure that his deployments are consistent across each environment.

What I'd to achieve today

Provision RDBMS software and Create a Database using a procreated profile

Let's Begin
1. Find your custom profiles by navigating to the Database Provisioning homepage.
NewImage

2. Highlight the Profile you'd like to use and click "Provision".
NewImage

3. On the next page, the profile you selected in the previous screen is already populated. Click the "Add" button, and select the correct host. Note, that you can select multiple hosts, but unfortunately I only have a single one to test with.
NewImage

Configuration
4. On the next screen, as we have seen with earlier posts, the Configuration screens require a step-through of the Host, Software, Database, and Compliance areas.
4.1 Click on Setup Hosts
NewImage

4.2 Select the correct values from the next couple of screens.
NewImage

4.3 Click on "Deploy Software".
NewImage

4.4 Enter the Oracle Base and Home directories.
NewImage

4.5 Time for "Create Database".
NewImage

4.6 Configure Database settings accordingly.
NewImage

4.7 I usually just skip the Compliance setup. Clicked through it to get ahead.
NewImage

4.8 Getting close to the end now!
NewImage

4.9 Submit the task!
NewImage

Progress
5. The procedure activity screen displays periodically refreshed status updates.
5.1 Let's start with the first one.
NewImage

5.2 Bingo, we're done!
NewImage

6. Upon searching for the container database, I find it.
NewImage

Conclusion
The story doesn't end here, I could go a few steps further to provision the GI, RDBMS, and Database with a single profile. I'd like to save that for another time :)

I do understand, that in a test/lab environment the results aren't as critical as in a production one. The aim of these exercises is to show you that it is possible to leverage profiles to provision software plus database quite easily.

Cheers!

continue reading "Patch and Provision with EM12c: #3 Database Provisioning - Using Profiles"

Add Your Opinion 0 Opinions

Thursday, September 26, 2013

How long did your Database Upgrade actually take?

This might be old news, but it became new news for me after I discovered it last week. As far as I know, there's no way to determine the length of a database upgrade. There are, however, ways to influence the length by gathering dictionary stats, truncate AUD$ and FGA_LOG$. At the end of the upgrade process, either by DBUA, or via Manually I'd want to know the actual time taken by the scripts.

With a recent upgrade from 11.1.0.7 t o 11.2.0.3 on Solaris, I ran into this issue that disrupted the upgrade process and my (inadequate mental) timer. After reviewing the scripts called internally by catupgd.sql, I found this one that I thought was quite helpful.

SQL> @?/rdbms/admin/utlu112s.sql

Oracle Database 11.2 Post-Upgrade Status Tool 09-20-2013 14:53:05

Component Current Version Elapsed Time

Name Status Number HH:MM:SS

Oracle Server

. VALID 11.2.0.3.0 00:15:13

JServer JAVA Virtual Machine

. VALID 11.2.0.3.0 00:03:39

Oracle Real Application Clusters

. VALID 11.2.0.3.0 00:00:00

Oracle Workspace Manager

. VALID 11.2.0.3.0 00:00:50

OLAP Analytic Workspace

. VALID 11.2.0.3.0 00:00:23

OLAP Catalog

. VALID 11.2.0.3.0 00:00:59

Oracle OLAP API

. VALID 11.2.0.3.0 00:00:25

Oracle Enterprise Manager

. VALID 11.2.0.3.0 00:04:56

Oracle XDK

. VALID 11.2.0.3.0 00:00:44

Oracle Text

. VALID 11.2.0.3.0 00:00:46

Oracle XML Database

. VALID 11.2.0.3.0 00:04:48

Oracle Database Java Packages

. VALID 11.2.0.3.0 00:00:26

Oracle Multimedia

. VALID 11.2.0.3.0 00:10:05

Spatial

. VALID 11.2.0.3.0 00:05:34

Oracle Expression Filter

. VALID 11.2.0.3.0 00:00:10

Oracle Rules Manager

. VALID 11.2.0.3.0 00:00:09

Gathering Statistics

. 00:05:56

Total Upgrade Time: 00:55:14 <--

PL/SQL procedure successfully completed.

As it turns out, Tim Hall has blogged about it within his DB12c upgrade steps. The script in 12c is simply called utlu121s.sql.

Cheers!

continue reading "How long did your Database Upgrade actually take?"

Add Your Opinion 0 Opinions

Saturday, September 21, 2013

ORA-00119 During a Database Upgrade

I consider myself a reasonably detail oriented person – reasonably… Well, every now and then, more often than not, I tend to overlook documentation. Either that, or my dyslexia kicks in.

Before I go into my story, let me explain the configuration, which led me to the setup on which I had a problem.

11.1.0.7.0 CRS & ASM upgraded to 11.2.0.3.0 GI.
11.2.0.3.0 RDBMS Software Installed.

During an upgrade of a few 11.1.0.7.0 Databases to 11.2.0.3.0 on Solaris 10 deployed on a two node RAC, I encountered the error below.

CRS-2674: Start of 'ora.freedom.db' on 'jellybean1' failed

CRS-2632: There are no more servers to try to place resource 'ora.freedom.db' on that would satisfy its placement policy

CRS-5017: The resouce action "orac.freedom.db start" encountered the following error

ORA-00119: invalid specification for system parameter LOCAL_LISTENER
ORA000132: syntax error inresolved network name 'LISTENER_FREEDOM1'

CRS-2674: Start of 'ora.freedom.db' on 'jellybean2' failed

CRS-2632: There are no more servers to try to place resource 'ora.freedom.db' on that would satisfy its placement policy

CRS-5017: The resouce action "orac.freedom.db start" encountered the following error

ORA-00119: invalid specification for system parameter LOCAL_LISTENER
ORA000132: syntax error inresolved network name 'LISTENER_FREEDOM2'

The error to note is the ORA-00119. It’s basically telling me the issue, CRS is unable to start the resource!

I found a MOS note which talks about my situation. “DBUA failed to start instance (ORA-00119) when upgrading from pre 11.2 database to 11.2 (Doc ID 1552530.1)”.

It simply said, to set the LOCAL_LISTENER parameters to the actual address prior to running the DBUA.

For example (excuse my plagiarism from MOS, I’m too lazy to change the names).

alter system set local_listener='(ADDRESS = (PROTOCOL = TCP)(HOST = xxxx-vip)(PORT = 1521))' scope=spfile sid='xxx1';
alter system set local_listener='(ADDRESS = (PROTOCOL = TCP)(HOST = xxxx-vip)(PORT = 1521))' scope=spfile sid='xxx2';

Again, unfortunately I did not create flashback restore point, but fortunately the error only happens with the Post Upgrade Steps. My fix, for the currently database, was to startup the database and set the parameter correctly. That “seemed” to fix the issue.

For the next database, I set the parameter before the upgrade. But to my surprise, the LOCAL_LISTENER parameter was reset to LISTENER_<SID>!!

Okay, at this point, I had to find out what was going on. On my third instance, I actually set the parameter as per the recommendation above and bounced it. Voila, the parameter is set because the instance dynamically registers it. But, where is it even getting the value??

After a little creative digging around, it turns out that I missed a step after installing the RDBMS software. I will outline the steps for the upgrade process and follow up with what actually happened.

Shutdown Database (from 11.1 RDBMS home)
Startup Upgrade one instance on (from 11.2 RDBMS home)
Upgrade Database Components
Post Upgrade

a. Shutdown instance

b. Startup Database from 11.2 GI

c. Error with ORA-00119

It took me a few minutes to put the pieces together. I was able to start the database via server control (srvctl) on the 11.1 home, but not from srvctl on 11.2 home. What was missing? Wait, where is the LOCAL_LISTENER parameter getting its default value? Think man think! Oh shoot, the TNSNAMES.ORA file!

After I copied the TNSNAMES.ORA file to the new 11.2 RDBMS home, the next upgrade worked without a glitch.

So, if you’ve found your way to this post, I sincerely hope it helps you.

Cheers!

continue reading "ORA-00119 During a Database Upgrade"

Add Your Opinion 0 Opinions

Subscribe to: Posts ( Atom )

Categories

Wednesday, October 30, 2013

SRVCTL: Add Preferred Instances

Sunday, October 20, 2013

Oracle Golden Gate 12.1.2

Tuesday, October 15, 2013

How to Resume an RMAN Duplicate on Failure - Just Re-Run it!

Friday, September 27, 2013

Patch and Provision with EM12c: #3 Database Provisioning - Using Profiles

Thursday, September 26, 2013

How long did your Database Upgrade actually take?

Saturday, September 21, 2013

ORA-00119 During a Database Upgrade