My RMAN script of full backup

[Big George]

Hello,
I have a doubt about my RMAN script of full backup, which run every
Sunday.

run {
allocate channel c1 type disk maxpiecesize=4G;
CROSSCHECK archivelog all;
backup
incremental level 0
format '\\192.168.12.19\g$\SRV4-arclogs\MTDPROD\RMAN\FULL
\FULL_%d_%T_%s_%p.bak'
filesperset 8
spfile format '\\192.168.12.19\g$\SRV4-arclogs\MTDPROD\RMAN
\FULL\spfile_%d_%s_%T_%p.ora'
database ;
backup
current controlfile format '\\192.168.12.19\g$\SRV4-arclogs
\MTDPROD\RMAN\FULL\ctl_%d_%s_%T_%p.ctl' ;
SQL 'alter system archive log current';
backup
format '\\192.168.12.19\g$\SRV4-arclogs\MTDPROD\RMAN\FULL\ARC_
%d_%T_%s_%p.bak'
archivelog all delete all input;
restore spfile to pfile '\\192.168.12.19\g$\SRV4-arclogs
\MTDPROD\RMAN\FULL\pfile_mtdprod.ora';
}


As you see, I backup my level 0 backuppieces and my controlfile
separately.

I backup controlfile separately because it would be easier to identify
it and move it to a new host, in case we have to restore database from
a disaster recovery.

Is that correct? Or could I have problems with that controlfile when
restoring my full backup?

[joel garry]

Not entirely, see http://docs.oracle.com/cd/B19306_01/backup.102/b14192/bkup003.htm#i1009642

A manually backed up controlfile does not get automatically restored.
Note that the controlfile gets backed up automatically when you backup
file 1, but that is considered a manual backup. You should set
CONFIGURE CONTROLFILE AUTOBACKUP ON

One viewpoint is you can't have too many controlfile backups, another
is you can get confused and shoot yourself in the foot.

It ain't a backup procedure unless it has successfully restored. You
need to test various scenarios, and understand enough to deal with
anything reality throws at you. You need to understand when to back
off and let Oracle do the right thing automatically.

jg
--
@home.com is bogus.
Redwood City, huh? http://www.utsandiego.com/news/2012/apr/27/nassco-built-spy-vessel-sale/

[John Hurley]

Joel:

# You need to understand when to back off and let Oracle do the right
thing automatically.

Ummm ... never?

Oracle restart for example obviously one of the worst concepts ever.
Server reboots for some reason and things startup before the system is
checked out?

I am a firm believer in scripting all the rman backup steps myself
( including getting extra copies of control file and spfile ).

Letting oracle make decisions on restoring anything ... not this dba.

Testing every single step of all recovery actions periodically ... you
got my vote there dude!

[Noons]

John Hurley wrote,on my timestamp of 29/04/2012 11:17 PM:

> Oracle restart for example obviously one of the worst concepts ever.
> Server reboots for some reason and things startup before the system is
> checked out?

Well, you know how it goes: gotta replace all those "expensive dbas" who dare
show professionalism and think about such eventualities!
Preferably with something that generates lots of consultancy revenue and sounds
"kewl"...

[joel garry]

On Apr 29, 6:17 am, John Hurley <johnthehur...@gmail.com> wrote:
> Joel:
>
> # You need to understand when to back off and let Oracle do the right
> thing automatically.
>
> Ummm ... never?

A reasonable viewpoint, considering the consequences of losing data.

>
> Oracle restart for example obviously one of the worst concepts ever.
> Server reboots for some reason and things startup before the system is
> checked out?

Yes, I've seen the downside of automatonism, though I've never worked
with that product.

>
> I am a firm believer in scripting all the rman backup steps myself
> ( including getting extra copies of control file and spfile ).

Used to do that, but I'm trying to be a good dba 2.0 and use dbconsole
for backups. In reality, I take what it generates and make a couple
of paranoid mods. Of course I also have other redundancies, including
all the old stuff I did myself, for when dbconsole decides to suck a
gun.

>
> Letting oracle make decisions on restoring anything ... not this dba.

Remember, most dba's are less experienced, and many will shoot their
own feet. In the end, it is a dba decision, in concert with
management SLA's.

I find it encouraging that the OP would at least ask.

>
> Testing every single step of all recovery actions periodically ... you
> got my vote there dude!

jg
--
@home.com is bogus.

"You are receiving this message because your email address is
associated with an unmigrated legacy Blogger account." - In an email
to one of my spamtrap addresses.

[Mladen Gogala]

On Fri, 27 Apr 2012 14:23:27 -0700, joel garry wrote:

> A manually backed up controlfile does not get automatically restored.

Unless there is a catalog which is searched for all control file backups.
But given that Oracle gets DBID from the control file, things can get a
bit complicated in that case and the DBA will either have to know the DBID
of the database that needs a restore or will have to browse through the
RMAN catalog tables. DBID is the first column of the V$DATABASE and is
the primary key for RMAN tables.



--
http://mgogala.byethost5.com

[Mladen Gogala]

On Sun, 29 Apr 2012 23:45:38 +1000, Noons wrote:

> Well, you know how it goes: gotta replace all those "expensive dbas" who
> dare show professionalism and think about such eventualities!

Replacing an expensive DBA with a less expensive one is not a sacrilege,
it's a rational business decision. Fortunately for both of us, large
databases that support thousands of concurrent connections are
exceptionally complex beasts which do require a DBA with a great deal of
knowledge. It's easy to manage a database when everything works as
expected, but databases are very complex beasts and if everything looks
right, you've overlooked something. When the main production database
loses an instance in the middle of the night, say at 3:15 AM, you want to
be really sure that the DBA on the other end of the phone line knows what
to do and how to restore the service in the minimum time. That's not the
time or place to start clicking on the OEM, which takes forever to load.
My Xubuntu 12.04 virtual machine boots faster than I can connect to OEM
12c.

The legend of the database that doesn't need a DBA is just a legend.
Status of technology is such that every serious production database needs
one and it will stay so for some time. However, there will be a change in
the future. RAM sizes are getting much bigger, SSD are getting much more
common, and MVC frameworks like Hibernate, Zend or Django are becoming
prevalent. Database algorithms will change, automatic tuning will slowly
become prevalent, both in the database arena and in the OS arena. At that
point, SQL tuning, which now comprises a large part of my job, will be
done by a computer. When Skynet becomes self aware, the DBA 1.0 personnel
will become obsolete and will be tehminated.



--
http://mgogala.byethost5.com

[Noons]

On Apr 30, 7:07 am, Mladen Gogala <gogala.mla...@gmail.com> wrote:

> The legend of the database that doesn't need a DBA is just a legend.

Ah yes, that. Good joke. Whoever came up with that at Oracle had a
sense of humour.

> the future. RAM sizes are getting much bigger, SSD are getting much more
> common, and MVC frameworks like Hibernate, Zend or Django are becoming
> prevalent. Database algorithms will change, automatic tuning will slowly
> become prevalent, both in the database arena and in the OS arena. At that
> point, SQL tuning, which now comprises a large part of my job, will be
> done by a computer.

I've actually been like that for a while (except for Peoplesoft...).
Most of the SQL tuning here is done by developers on copies of
production, using either Toad or OEM. They run dbms_stats at the
appropriate schedule slots in prod - they know much better than I what
those are - for objects of wild volatility.
I ensure that once an average SQL goes from dev to prod, its plan
doesn't change for the worse. As well as collect stats regularly on
low volatility objects. Can't remember the last time we got a
"runaway" prod performance problem caused by wild SQL plans - other
than the odd bu^H^Hfeature. And that's about it. Got better things to
do with my time than "death-by-sql-tuning". KISS. Like it or not.

Of course: I'm not on con-sultancy rates. So I do mostly things like
tuning the OS/DB interface, move data based on usage patterns, take
advantage of SAN tools and features, monitor and analyze usage trends
for capacity planning - CPU, mem, and disk space - and design, test
and configure new servers as well as plug holes in MS/SQL here and
there. A much better use of the time I'm being paid for.

> When Skynet becomes self aware, the DBA 1.0 personnel
> will become obsolete and will be tehminated.

Tell that to the hordes of "con-sultants" who tried to get me
outsourced for the last 5 years... Apparently, upfront we had a
"performance problem". Even though no one has been able to pinpont
exactly what that problem is. I'm sure as soon as some got in, they'd
find "heaps of problems" - at a price. Heck: we don't run complicated
RAC/ASM setups, so there M-U-S-T be a problem somewhere, right? Just
like the security auditors who come in every 6 months and found
nothing "broken" in the last 4 years.

Ah well, Skynet can be fun! ;-)

[Mladen Gogala]

On Sun, 29 Apr 2012 22:16:52 -0700, Noons wrote:

> I ensure that once an average SQL goes from dev to prod, its plan
> doesn't change for the worse. As well as collect stats regularly on low
> volatility objects. Can't remember the last time we got a "runaway"
> prod performance problem caused by wild SQL plans - other than the odd
> bu^H^Hfeature. And that's about it. Got better things to do with my
> time than "death-by-sql-tuning". KISS. Like it or not.

Well, when I said "SQL Tuning", I had in mind help to developers.
Applications grow, objects are added, data models change, rarely for the
better, things get messy. I am usually a go-to person when there is a
problem like this: this query runs for 3 minutes and it needs to be put
into a user facing web application. A user will not wait for 3 minutes,
period. Can you make it complete in 10 seconds or less? Tricks like
clustering and result cache come in handy. A long time ago, the most
expensive statement on the system was, believe it or not, "SELECT 1 FROM
DUAL". That was a "keep alive SQL" executed by a gazillion of application
servers: WL, JBoss and Tomcat. Also, caching calls to the address tables
containing state, also made a big difference. Those tables are mostly read
only and result cache can cut down load on the system drastically, at the
expense of some memory. That type of thing still cannot be done by
computers.

>
> Of course: I'm not on con-sultancy rates. So I do mostly things like
> tuning the OS/DB interface, move data based on usage patterns, take
> advantage of SAN tools and features, monitor and analyze usage trends
> for capacity planning - CPU, mem, and disk space - and design, test and
> configure new servers as well as plug holes in MS/SQL here and there. A
> much better use of the time I'm being paid for.
>
>> When Skynet becomes self aware, the DBA 1.0 personnel will become
>> obsolete and will be tehminated.
>
> Tell that to the hordes of "con-sultants" who tried to get me outsourced
> for the last 5 years... Apparently, upfront we had a "performance
> problem". Even though no one has been able to pinpont exactly what that
> problem is. I'm sure as soon as some got in, they'd find "heaps of
> problems" - at a price. Heck: we don't run complicated RAC/ASM setups,
> so there M-U-S-T be a problem somewhere, right? Just like the security
> auditors who come in every 6 months and found nothing "broken" in the
> last 4 years.
>
> Ah well, Skynet can be fun! ;-)

There are quite a few unscrupulous consultants around. Pushing for RAC/ASM
in every situation is probably the best warning sign. I love asking
consultants for references. If I don't get a number of their clients, I
know that there is a problem. As for Skynet, I like that chick that was
playing T3. Keeping a face like that must be hard.


--
http://mgogala.freehostia.com

[Noons]

On May 3, 7:53 am, Mladen Gogala <gogala.REMOVETHISmla...@google.com>
wrote:

> Well, when I said "SQL Tuning", I had in mind help to developers.
> Applications grow, objects are added, data models change, rarely for the
> better, things get messy. I am usually a go-to person when there is a
> problem like this: this query runs for 3 minutes and it needs to be put
> into a user facing web application. A user will not wait for 3 minutes,
> period. Can you make it complete in 10 seconds or less? Tricks like
> clustering and result cache come in handy.

Indeed. Same here. Most of the time the developers get away with the
tuning features in Toad and SQLDeveloper. Actually, the last few
dozen development SQL problems I got involved with were caused by our
release of Toad doing by default "ANALYZE TABLE" instead of "exec
dbms_stats.gather_table_stats". I've now asked Oracle to find me an
event trap to disable the ANALYZE command completely: it's at the root
of most SQL tuning problems I get involved with!

> A long time ago, the most
> expensive statement on the system was, believe it or not, "SELECT 1 FROM
> DUAL".

Got one of those as well. Third party BI tool that uses a PL/SQL
timer 1 second delay all over the place. So, what's the longest wait
event in my AWR and statspack reports, by a country mile? The PL/SQL
timer, of course! (groan)
I'm interested in hearing what "auto-everything-to-replace-the-dba"
would do with that?...

> There are quite a few unscrupulous consultants around. Pushing for RAC/ASM
> in every situation is probably the best warning sign. I love asking
> consultants for references. If I don't get a number of their clients, I

Too right. I wish I had a dollar for every bloody idiot that has
waltzed in here in the last 5 years claiming we should be on RAC/ASM/
OCM/bloody-whatever - sight unseen and without even asking how/what do
we run or to see a statspack/awr report...

> know that there is a problem. As for Skynet, I like that chick that was
> playing T3. Keeping a face like that must be hard.

Hehehe! Too right!

[joel garry]

On May 2, 6:22 pm, Noons <wizofo...@gmail.com> wrote:

>
> Got one of those as well.  Third party BI tool that uses a PL/SQL
> timer 1 second delay all over the place.  So, what's the longest wait
> event in my AWR and statspack reports, by a country mile?  The PL/SQL
> timer, of course!  (groan)
> I'm interested in hearing what "auto-everything-to-replace-the-dba"
> would do with that?...

Sometimes the OMS just goes bonkers because some hefty process used
lots of lgwr a few minutes before <sigh>

OK quick, don't look, how many background processes can you name in
11gR2?

Now go look at the appendix in the ref manual.

jg
--
@home.com is bogus.

Lawsuit lotto: http://www.utsandiego.com/news/2012/may/01/man-abandoned-dea-cell-steps-forward/

[Noons]

joel garry wrote,on my timestamp of 4/05/2012 1:57 AM:


> OK quick, don't look, how many background processes can you name in
> 11gR2?
> Now go look at the appendix in the ref manual.

Belive me: it would be the last thing in my to-do list!
:-)