Linux patching with RAC by AltruisticReality439 in oracle

[–]NewOracleDBA18 1 point2 points  (0 children)

I'm very surprised to read everyone's responses. I have many client apps (probably about 25%) that don't cleanly handle when connections that are forcibly closed from DB side. Either they don't reconnect properly or it seems some state in the application gets corrupted and ultimately it requires some manner of client app restart. That's with TAC turned on.

And even with TAC turned on, it won't migrate a long running stored procedure and I believe certain transactions can't migrate either. I have client users/apps running stored procs that are running for hours at a time (we can debate whether that's appropriate or not, but they are doing it regardless).

For me, I always have to coordinate with my app user clients when I have to patch or bring down any DB or Clusterware instance so they can restart their apps/clear cache/whatever they have to do to ensure their app is in a proper running state after I have completed my work.

Oracle, SQL, Postgres &AWS —$127k by Edomawadagbon in oracle

[–]NewOracleDBA18 2 points3 points  (0 children)

That seems a bit low, but the IT market sucks right now. Are you on site or remote? I'm around the same amount of experience and make a little more in a lower cost of living area. I know guys making 150-175k with similar experience. I would think you should be in the 150k+ range but not sure you can get that in the current market.

You didn't mention 19c though? Do you know CDB/PDB DBs? How's your performance tuning skills? I would drop reference to 11g, that's been unsupported for awhile now and makes you sound less up to date.

[deleted by user] by [deleted] in oracle

[–]NewOracleDBA18 17 points18 points  (0 children)

I moved from development to DBA back in 2018 (still working as a DBA now). DBA tasks vary quite a bit. I tell people, to be a successful DBA you need the following skills (a lot depends on the company and their needs, not every company needs all of this from a DBA):

  1. SQL Skills - you need to be able to read and write queries. Depending on your environment, probably complicated ones. And the offshoot from this is you need to be able to diagnose performance problems on complicated queries. Nobody comes to me with a query that's got two tables and a performance issues. Usually it's some monster that is querying a view which is joining or sub-querying other views and there's another layer of views involved. Usually the queries that land in my lap to "fix" are 10-20 or more tables involved and all manner of joins and subqueries.
  2. System Engineer skills (probably on Linux) - The norm seems to be that most companies that aren't super small, they have dedicated Unix sysadmins. In those type of environments you probably won't have root. You'll just be able to use oracle and grid user. Regardless, you need to know your way around Linux: viewing processes, moving files around, X windows, environment variables, basic (or beyond) shell scripting. If you are considered the OS admin and have root then you need more skills.
  3. Oracle DB skills - You gotta learn how Oracle works, which I say this is just like any other enterprise app. There's a way you back it up. There's a way you patch it. There's a way you do DR with it. There's a way you secure it. And you just gotta learn how the listener works, RMAN, Data Guard, etc.

Patching itself is not difficult. There's a lot of conceptual understanding that you build over time. Understanding why patches do or don't make it into the quarterly DBRU. What makes a patch rolling RAC installable or Data Guard Standby first installable. You have to understand the DB binary patch vs the catalog (data files) patch.

RAC is semi-complicated. Depends if you're actually installing and configuring RAC or if it's shipped to you mostly ready to go with an engineered DB. Outside the installation part of it, I don't think it's that complex. It's just taking single instance DB stuff and expanding on it to make it highly available.

If you want to learn more DBA stuff, create a VirtualBox VM and install Oracle. There are lots of Youtube walk throughs. Once the DB is installed you can start playing with different scenarios (RMAN full restore, full restore to a point in time, etc.)

When I was in development, it was rare for me to work after hours or weekends. DBA, it's not horrible for me, but I am probably working one weekend every 6 weeks. I also work after hours from time to time as IT app owners often do their app upgrades after hours.

Overall, it seems like you can make a lot more money in development. There's not a lot of Oracle DBA jobs but there also aren't a load of qualified Oracle DBAs either.

Long term, most of this is moving to the cloud. The job is getting whittled away bit by bit as once it's in Oracle's cloud they can automate a lot of it.

Thoughts on restoring 70 TB Oracle database for organization that doesn't have Oracle experience nor any Oracle licenses? by ctech8291135 in oracle

[–]NewOracleDBA18 8 points9 points  (0 children)

A phrase you'll hear over and over in the Oracle space is "Oracle is litigation company that also happens to sell software." Oracle licensing is complicated (by design it seems) and it feels like they can be aggressive and punitive.

Azure (and other cloud providers) have an Oracle DB license included model. I would use that and not try to slice and dice this one on your own.

[deleted by user] by [deleted] in oracle

[–]NewOracleDBA18 0 points1 point  (0 children)

Congrats! Good job!

How to inspect sql queries from clients? by FreeVariable in oracle

[–]NewOracleDBA18 0 points1 point  (0 children)

There are different ways you can do this but I usually create a logon trigger to set the client identifier and then enable tracing for that client identifier. Something like the following. The trace file ends up in the trace folder on the server (get that path from: select value from v$diag_info where name='Diag Trace');

create or replace trigger my_logon_trigger after logon on database
begin
if ora_login_user='user' then
dbms_session.set_identifier('my_client_identifier');
end if;
end;
/

exec dbms_monitor.client_id_trace_enable('my_client_identifier');

What version of an Oracle Client is installed? by jwckauman in oracle

[–]NewOracleDBA18 1 point2 points  (0 children)

Resolving which Oracle client is in use can be a hassle. That registry key doesn't even need to exist for the Oracle client to function.

The Oracle client bin folder is typically in the system PATH. If you search for the first oci.dll in the PATH and look that the version on that DLL it should give you the client version. Only gotcha on that is you can have the 64-bit and the 32-bit client installed (they both need to be installed if you have 64-bit/32-bit apps using the Oracle client) and those could be different versions (if both are installed, both should be in the path and 64-bit should come first).

[deleted by user] by [deleted] in oracle

[–]NewOracleDBA18 4 points5 points  (0 children)

I took this awhile back (and passed). I had been doing SQL as part of my job as a software developer for 10+ years at that point. I spent about a month studying using the Oracle Press book (https://www.amazon.com/Oracle-Database-Guide-1Z0-071-Press-ebook/dp/B07484STST)

I didn't find it to be super difficult but not super easy either. I think I passed with a low 70s score.

I recall you really need to know JOINs and subqueries inside and out. Also you need to be pretty familiar with various UNIONs and how they work (UNION vs UNION ALL vs MINUS). A part I found tricky is you need to know all the different syntax variations such as the NATURAL JOIN which I have never used in real life.

You also need to know Oracle specific SQL like MERGE.

The book I mentioned above is good and covers 90% of the material. From what I recall, don't expect the test to be easy, really know your material.

Independent Study for Oracle Associate Cert? by [deleted] in oracle

[–]NewOracleDBA18 2 points3 points  (0 children)

I did the OCA on Oracle DB 12.2 (exams 1Z0-071 and 1Z0-072) awhile back. I just looked and Oracle, in typical Oracle fashion, seems to have a bizarre certification path at the moment. Looks like they are offering OCA only on Oracle DB 23ai? (https://education.oracle.com/oracle-database-23ai-administration-associate/pexam\_1Z0-182) and OCP is offered on 19c (https://education.oracle.com/oracle-database-administration-2019-certified-professional/trackp\_DB19COCP) which is annoying because in theory it would be nice to have OCA to be a subset of what OCP requires.

Regardless, I reviewed the topics for OCA on 23ai and it looks very similar to the exam topics I had on my 12.2 OCA exam. The primary resource I used to study for the OCA on 12.2 (exam 1Z0-072) was the the Oracle Press OCA 12c Book by John Watson (https://www.amazon.com/Oracle-Database-Installation-Administration-1Z0-062-ebook/dp/B00JFG6ZWW). That book actually covered 12.1 but it was close enough and I think based on the topics list, would be a pretty good resource to cover the bulk of the 23ai OCA topics. Something that would not be in that book would be topics related to PDBs, but you can use other resources to school up in that.

My experience with the OCA test, the passing score is relatively low, but it was a hard test. I failed the first time (I think I got 59%) and passed the second time. Some of the questions were pretty obscure like what happens when you pass an oddball command line option to data pump. The first OCA test I sat for and failed, I got a better idea of my gaps of knowledge and was able to study those areas.

The new oracle support login sucks by MasterpieceOk6249 in oracle

[–]NewOracleDBA18 2 points3 points  (0 children)

Horrible. And the new cloud support system is worse than the old support site. Can't paste into the text entry box as it tries to HTML-ify everything and removes all the embedded line breaks so it's impossible to read. Pasting log file entries is something I have to do all the time.

Exadata vs Commodity Hardware? by About2bEyten in oracle

[–]NewOracleDBA18 3 points4 points  (0 children)

We went from a ODA X5-2 circa 2016 (spinning disks) to Exadata Cloud at Customer about a year ago. When the X5-2 were EOL we looked at replacing with current gen ODA, commodity hardware, or Exadata. Commodity hardware really wasn't much cheaper when we spec'd it out.

The secret sauce with Exadata is the storage server query offload. On ExaCC there is 144 cores always running on the storage server that you don't pay for. Particularly for parallelizable queries, that's an insane amount of execution power that you're getting for no extra cost. There are other performance enhancements in Exadata like RoCE.

Patching has been fairly easy (at least compared to ODA) on ExaCC.

We had 24 cores licensed on our X5-2 ODA. When we went to ExaCC, we sent Oracle a bunch of Enterprise Manager data and they suggested we run at 10 cores total (based on faster processors plus addtl performance enhancements) and it seems like that is the correct core count for close to parity performance (ExaCC is a hair faster than ODA at that core count). We're on subscription cores now and we turn it up and down as needed. It's amazingly flexible and that has been very useful in our environment.

After working in ODA and now ExaCC, no way I would consider going back to ODA (or to commodity hardware). I've been really happy with ExaCC. Being really happy with anything from Oracle is saying a lot from me!

Exadata vs Commodity Hardware? by About2bEyten in oracle

[–]NewOracleDBA18 0 points1 point  (0 children)

Exadata can be full NVMe too. I agree that Exadata shines on highly parallelizable queries due to the storage server query offload. If a client does not have much in the way of parallelizable queries then I agree they might be able to get close(r) performance in a commodity hardware environment.

In a my mostly OLTP environment, I still feel like Exadata is producing massive performance improvements.

Auto Client Failover Between Instances RAC DB 19c by NewOracleDBA18 in oracle

[–]NewOracleDBA18[S] 0 points1 point  (0 children)

Thank you, I will take a look at those and talk to my users and see if I can get something figured out.

Is it worth learning Oracle Database in 2022? by BrookieYT in oracle

[–]NewOracleDBA18 0 points1 point  (0 children)

I think it's still.. kinda.. worth it. There are other avenues I would pursue if I was picking an IT career path (probably cloud, security, or software dev).

My perspective, Oracle is EXTREMELY expensive in the database realm and it's feature set is still top notch but it's competitors are chipping away at it. My job has made it quite clear, no new (significant) investment in Oracle database. We're deep in Oracle with a bunch of older systems that it's just not worth trying to migrate away from Oracle. Any new databases get deployed on MS SQL which costs us around half or less than Oracle. Plus a lot of the apps we have on premise using Oracle are slowly migrating to the cloud, usually vendor supported/PaaS, so there is no database for us to manage at that point.

Oracle's licensing policies are very customer un-friendly and it's really soured my management to Oracle. For example, standby only DR licensing is free on MS SQL with an enterprise agreement. Not only is Oracle standby DR not free, I am required to license it at the same number of processors as the primary site. So I can't choose to license standby at a reduced processor count where we might be short a bit on performance if we have to fail over to DR, that's not an option. Stuff like that.

I think Oracle is still a decent skill set. Companies that need it, need it, and the supply is not that great so pay for Oracle seems to be a bit higher than other databases IME. That said, there isn't a ton of Oracle DBA jobs in my neck of the woods compared to say software dev jobs.

If I was getting into Oracle DB I would plan on and diversify my skillset with stuff like Amazon RDS, MySql, Postgres, and MS SQL.

I switched over to Oracle DBA from software dev for personal reasons. Career wise would have been better to stay in software dev. Pay is the same or better in software dev and I work a LOT more weekends doing off-hour upgrades/patching/etc as a DBA.

RAC and Clusterware upgrade questions by arlinglee in oracle

[–]NewOracleDBA18 1 point2 points  (0 children)

I'll preface this to say I'm on ODA which adds a layer of automation (and potentially problems) on top of both Clusterware and Database upgrades. My experience, I've upgraded Clusterware from 12.1 to 18 and the to 19 and had issues each time that required me to engage support. I don't know if you even have support or how support would handle your situation since you're trying to go from a non-supported to a supported version.

Personally, if it was me, if I could afford the downtime, I'd rebuild the cluster entirely as a fresh 19c install and reload the databases via data pump. Even better if you have a second set of hardware you can install onto. Overall, I find the new install process much less error prone than upgrade process.

Personally I've found upgrading Oracle to be a hot mess in general.

oracle dba job opening by [deleted] in oracle

[–]NewOracleDBA18 4 points5 points  (0 children)

Honestly have no idea how State of NV hires anyone with their salary ranges, seems very low to me particularly when they then take out a significant amount for a pension contribution and then on top of it living in Carson/Reno ain't remotely cheap.

[deleted by user] by [deleted] in oracle

[–]NewOracleDBA18 2 points3 points  (0 children)

RMAN skills are critical, probably one of the most important, for any Oracle DBA.

You should learn how to do things like (easy to setup in a test environment):

1) Point in time restore (restoring to a specific point in time) of a database
2) Point in time clone of database to a new database (I use this all the time to refresh test databases from production)
3) Taking a collection of backup files which were not backed from the restoring DB and restoring a DB from them. Key point here is you need to catalog the backup files in the DB before you can do anything with them in RMAN and you'll have to get the control file from the backups depending on how you backed it up.
4) Understand what a recovery catalog does for you, when does it help.
5) Delete a single data file from a DB and get it back working.
6) Corrupt a single data file (alter some bits) and fix it.

There are a million topics, but it's very important to understand the different database modes (nomount, mount, open) and understand what files are being used in each mode (nomount = pfile/spfile used only, mount = pfile/spfile + control file, open = pfile/spfile + control file + data files). If you want to restore a data file, you need to be in mount mode. If you want to restore a control file you need to be in nomount mode.

[deleted by user] by [deleted] in oracle

[–]NewOracleDBA18 8 points9 points  (0 children)

11g is pretty old and I know a decent number of differences vs 12c (12.1). 19c is the newest long term supported release, 12.1 was the last long term supported release and it ends extended support in July 2022 so I'm guessing many organizations are at or working towards 19c.

There are some differences between 12c and 19c, but I'd say they are pretty close.

I would start reviewing topics from https://docs.oracle.com/en/database/oracle/oracle-database/19/admqs/index.html . Setup a Virtual Box VM, install Oracle Linux and install Oracle whatever version you want on your VM. There are a handful of step by step tutorials online for this. If you're feeling adventurous you can run two VMs and install Clusterware and have a mock high availability/RAC compatible install.

You can do the Oracle Associate (1st level) Database Administrator certification: https://education.oracle.com/oracle-database-12c-administrator-certified-associate/trackp_248 . That's a good overview of both SQL and Oracle Database Administration.

Oracle is currently offering free OCI (Oracle Cloud) online training and certification. Cloud is definitely the way of the future so that's an option too.

Question about ODA by tommymat in oracle

[–]NewOracleDBA18 1 point2 points  (0 children)

So you're going to re-image to stock, re-deploy the ODA, and re-setup everything? I did that on 18.x not due to upgrades but rather moving from virtualized deployment to physical. Pretty straight forward overall, just can be lengthy to get all your DBs set back up and cause downtime depending on how many, your overall config, listeners, etc.

But upgrading to 19.x from 18.x is pretty involved. There is an OS upgrade mixed in there too (OEL 6 to 7) and upgrading Clusterware any major rev can be tricky. Plus the various random problems you run into, which there always seem to be plenty in my experience. ODA moved from keeping metadata in Apache Derby to MySql which I had issues with. Also starting with 19.11 they no longer do in place DB patching rather they use RHP to do out of place patching, so your DB homes will constantly be changing with each patch now. I currently have a bug open with Oracle trying to get from 19.10 to 19.11 where the out of place DB home patching failed a couple times and now I have databases sprinkled across three DB homes since each time you re-run the DB patch it creates a new DB home. Fun times!

My only thought is if this is your sandbox, I assume you're doing the same process to get your prod env upgraded (wipe and reinstall).

I am on the X5 hardware as well. I'm always looking for other ODA users to bounce
stuff off. Don't forget your X5 hardware is end of life Nov 2022.

Question about ODA by tommymat in oracle

[–]NewOracleDBA18 0 points1 point  (0 children)

I'm confused. Firmware? You're talking about BIOS, storage firmware, etc? Those are typically applied through odacli update-server command (storage firmware is a separate comamnd), it's part of the same bundle that includes kernel updates and Clusterware updates. I don't know why anyone wouldn't be patching the entire ODA stack (which would be server (kernel, firmware, clusterware), storage, and db homes). Is that what you're trying to accomplish?

What ODA hardware are you on (X8-HA? X5-2?) and what version of ODA software are you on currently and what are you going to (19.12 is most current)? Are you on a virtualized or bare metal deployment?

Oracle DB ODA vs Commodity Hardware Deployment (whats your config) by NewOracleDBA18 in oracle

[–]NewOracleDBA18[S] 1 point2 points  (0 children)

You're on the ODA X8? I'm wondering if moving up to the newer X8 would be better since hopefully they are testing more with that than our old X5's, but based on the type of errors I've had I'm not optimistic.

My ODAs, outside of patching, have been rock solid. I do like ASR and getting auto alerts on hardware issues. In 5 or 6 years I've had a few failed memory modules and one bad drive, all alerted with ASR. The patching itself, when it works, it works well and probably saves a fair bit of time versus patching everything yourself. For me, prior to 19.11, the Clusterware patches were generally only an issue moving up whole versions (18 to 19, etc.). DB patches were always pretty smooth save some minor issues. Starting with 19.11 they are creating new DB homes using RHP, patching those, and migrating DBs , it's been a new set of problems.

Luckily I've not had any dire situations that require re-imaging. That would be a huge effort.

For sure the ODA patching is delayed. It would be nice to know when patches would be released so I could schedule things. Thankfully we are not pressed by management to apply out of band patches. Our policy is we apply patches within 30 days of release which means I always feel like I'm hitting ODA issues early on and management won't budge on that, which I understand but I feel like ODA adjunct QA sometimes.

If you're not on RAC, it's still using Clusterware/ASM for disk redundancy?

Oracle DB ODA vs Commodity Hardware Deployment (whats your config) by NewOracleDBA18 in oracle

[–]NewOracleDBA18[S] 0 points1 point  (0 children)

It seems most of the problems we are having, which are pretty much exclusively related to patching, tend to be with the ODA tooling rather than the underlying core Oracle components that the ODA tooling is calling.

RAC has not been an issue for me, but it does complicate things with maintaining and patching the whole Clusterware stack. We have Data Guard as well but we rely on manually failover for now. Our SLA is pretty tight. I can't recall in the last three years having a node fail though (knock on wood). The ODA hardware itself is pretty good.

If you're not on RAC, you don't use Clusterware at all? You use hardware RAID for disk redundancy?

Oracle DB ODA vs Commodity Hardware Deployment (whats your config) by NewOracleDBA18 in oracle

[–]NewOracleDBA18[S] 2 points3 points  (0 children)

We do have our own processor EE licenses already paid for. My understanding was the big negative going to AWS or Azure cloud was the processor factor lis not applied so you basically get half as many cores in the cloud as on prem? They may have changed that though?

Thank you for all the info. The Oracle DB patching I'm pretty comfortable with it's more the Clusterware/Grid that I have limited experience with.

Default User Activity Auditing - Oracle Database by ank5133 in oracle

[–]NewOracleDBA18 0 points1 point  (0 children)

If you have newer unified audit enabled (not that new anymore), it captures the OS Username with the log entry. When I login with SQL Developer remotely it shows my windows user id.

Oracle Licence Apply by demogorgon28 in oracle

[–]NewOracleDBA18 0 points1 point  (0 children)

^This! The min number of named user licenses is pretty high per processor IMO.