Got it!
Melissa, May 09, 2005 - 11:39 am UTC
Hi, Tom. Thanks a million! As usual, I found the answer I am looking for from you! BTW, this is the first time I actually was able to post a question. Whats more, I got the answer so quick even faster than metalink! Really appreciate your help!
RMAN and backing up a tablespace
Bob, September 28, 2006 - 1:11 pm UTC
Hi Tom,
I'm trying to back up our SYSTEM tablespace which is in NOARCHIVELOG mode using RMAN.
So I am entering BACKUP SYSTEM TABLESPACE; at the RMAN prompt. And I get the error it's in noarchivelog mode.
So I log in as sys/sysdba and issue shutdown immediate;
When I try: startup mount exclusive - I get
ERROR: ORA-01031: insufficient privileges
What I am trying to do is:
SQL> CONNECT sys AS SYSDBA
SQL> STARTUP MOUNT EXCLUSEVE;
SQL> ALTER DATABASE ARCHIVELOG;
SQL> ARCHIVE LOG START;
SQL> ALTER DATABASE OPEN;
Then if I go into RMAN I can backup the SYSTEM tablespace.
Any ideas what I am doing wrong?
Thanks
September 29, 2006 - 7:49 am UTC
are you trying to do a hot backup in noarchivelog mode? that does not work.
Changing database to ARCHIVELOG
Bob, September 28, 2006 - 7:18 pm UTC
I've realized what I have done wrong: it works if you're
are signed into the Oracle (UNIX user account) - which I guess is a member of the dba group.
What if I dont mind loosing a day's worth of data?
Raghav, October 02, 2006 - 9:10 am UTC
Hi Tom,
You may find this a bit silly but I thought its worth a try.
We have a 1.5Tb Data warehouse. The ETL starts at 2100 and finishes at 0700 next morning. Now our database is in archivelog mode (although some of the tables are in NOLOGGING mode).
I would like to know whether I can tell our DBAs to do a RMAN hot backup (level 0/1) with the DB in noarchivelog mode. The database has no changes after 0700 so if we are prepared to loose 1 days ETL (because we can obviously extract it again from a source should it be needed).
Our current RMAN hotbackups start at 0900 and finish at 1300. So if we are prepared to loose 1 day's ETL, can we simply take a hot backup using RMAN whilst the DB is in noarchivelog mode?
Thanks for your help in advance
October 02, 2006 - 10:03 am UTC
You do not backup hot in noarchivelog mode, no such facility exists. The only hot backup is done in ARCHIVELOG mode.
If you tell your DBA's to do that, they might make fun of you :)
Need advice in starting backup routines
Scott, October 16, 2006 - 5:48 pm UTC
Tom,
I've been at my job for a little over 3 months now. Our current backups consist of schema exports (all our app data resides in 1 schema). I rolled my eyes when I saw that, and mentioned how bad it is to my boss. Low and behold, I found out mentioning better methods on things is volunteering to do it.
We currently have many clients with many databases. Since, our clients don't have DBAs, our databases are running in NOARCHIVELOG mode, which I feel is not good, considering we run an ERP solution on the database, and some of our clients are very data oriented. I looked at this thread and am trying to start some backup procedures. A few ideas I had was to create a script that runs in the middle of the night, shuts down the db, backs it up, and then restarts the database. A friend of mine also informed me that there is a way for rman to create a backup, and then delete old archive logs after the backup is made.
I've read a good portion of the recovery manuals, but am not sure which way I should reccomend to my company. Any advice would be greatly appreciated.
Thanks,
Scott
October 16, 2006 - 5:53 pm UTC
you mean you have no backups, export isn't a backup tool (hope they were consistent=y AND NOT RUN AS SYS/SYSDBA!!!!! at the very very least)
so, since your clients don't have DBA's they all signed a piece of paper stating in no uncertain terms "we will lose all data since our last (non) backup some day, not might, not could, but WILL. we realize this and have no problem with that whatsoever. Further more, since we never actually TEST the dmp files - we realize we will most likely lose all of our data someday - and that is perfectly OK"
I don't understand the lack of a DBA and noarchivelog tie in? clients either
a) want data
b) don't care about the data
most fall into a) :)
Any chance you can work with a mentor anywhere in your company? Or hire some outside expertise for a couple of days (or at least get training??)
recovery is the only thing a DBA cannot mess up, we can fix anything else. This is IMPORTANT stuff.
unattended shutdown - startups, that is a recipe for disaster :)
Good news for our backup procedures
Scott, October 30, 2006 - 1:15 pm UTC
Tom,
I looked over what you said, and read some Oracle backup documentation as going to a class isn't really an option. Then, my boss told me her husband does Oracle Backups (I asked why he didn't work here, but that was another can of worms). So, he's going to come in and help us set up a full backup procedure.
There is an option in OEM for deleting archivelogs when a backup is created. It seems most people ignore this option, and do a manual archivelog deletion. Is there some reason that this option is typically not used? Aside from job security, it would seem that this would help to make a DBA's life easier.
Thanks for your advice,
Scott
October 30, 2006 - 3:11 pm UTC
many people would prefer to keep the archives on disk as well as in the backup sets for as long as possible....
so as to NOT have to restore them if they don't have to, as long as you have room, why not keep them on the system as long as possible (rather then have to copy them onto disk to apply when you recover)
sys/sysdba exports
A reader, December 11, 2007 - 11:52 pm UTC
Tom, in reference to your response above "(hope they were consistent=y AND NOT RUN
AS SYS/SYSDBA!!!!! at the very very least)"
Can you please elaborate on the SYS/SYSDBA part? Is it because if you are going to rely on an export as a backup (silly as that may be) then you don't want the SYS schema when reimporting into a new database?
Thanks
December 12, 2007 - 8:11 am UTC
no, it is the fact that consistent=y is IGNORED when you run as sys/sysdba
because sys/sysdba cannot use flashback, cannot be read only - they are special.
so, you have no chance of getting a consistent export with them and likely you would never be able to import the data fully (it is not consistent, each table is as of a different point in time).