Results 1 to 13 of 13
  1. #1
    Join Date
    Aug 2004
    Location
    Manchester, UK
    Posts
    143

    Unanswered: Export as a backup

    Hello,

    Is it me, or is the export utility not the best way to back up your database? My theory is that export only takes the datafiles, and does not back up the parameter file, control files, or redo logs. So if you had some sort of disaster you would actually have to rebuild your oracle database from scratch, and then import, rather than just copying all your backed up control files etc into the relevant areas and starting up your db.

    Am I right? (Or can you use export to do a thorough back up of all relevant files?)

    Many thanks,

    Paula.

  2. #2
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    You're right.

  3. #3
    Join Date
    Aug 2004
    Location
    Manchester, UK
    Posts
    143
    Thanks for that. I've never used the export but an external company today have told me that I should use it to back up the database relating to their product.

    The problem is that they do not know what they changed the sys password to, and so I cannot connect to the database to shut it down and perform a full cold backup. The only user they can provide is system, which is just not good enough.

    Can anyone recommend a better solution than export? Is there any other way to backup the database without using sysdba user to shut it down first?

    Thanks,

    Paula.

  4. #4
    Join Date
    Jul 2003
    Posts
    2,296
    Export only backs up your data (logical backup) and you will not be able to recover/restore your actual database.

    Read up on rman which will allow you to recover and restore your database in the event of any errors, file corruption, disk failures, user-error, or any other phenomenon which would affect your db.

    9i rman User Guide:
    PHP Code:
    http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96566/toc.htm 
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  5. #5
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Never use export as a backup for an oracle database!! Export is point in time copy of the data and can't be used to recover your data. For example, even if the database is in archive mode and you have a crash, export could NOT be used to recover. I will illustrate. Say you have done a Full export at 8am and your database crashes at noon. You would have to fully rebuild the database and restore the export. After the restore your database would be current as of 8AM, not noon. However if you perform a true hot or cold backup of the database then you could quickly restore your database to the last transaction that was committed at noon. See the following links for information on backup and recovery.

    To setup your own backup/recovery see
    http://download-west.oracle.com/docs...a96572/toc.htm

    to use the oracle rman processor see
    http://download-west.oracle.com/docs...a96566/toc.htm


    By the way, if you can get into the system user, you can change the database on the sys user by issuing the alter user sys identified by new_password; where new password is the password that you want to assign. It's also possible that the sys account is simply locked, which is the default on newer versions of the database. To connect as a sysdba, which you can do even if the database is locked, log into the database server as the oracle user (or the user oracle was installed as), and issue the following command

    sqlplus "/ as sysdba"
    Last edited by beilstwh; 02-22-06 at 11:25.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  6. #6
    Join Date
    Jul 2003
    Posts
    2,296
    If you can log into the host then you don't need the SYS password.
    log into the host server as oracle, then issue:
    PHP Code:
    bash > . oraenv
    ORACLE_SID 
    = [dbsid] ? dbsid
    bash 
    sqlplus "/as sysdba"

    SQL*PlusRelease 9.2.0.4.0 Production on Wed Feb 22 15:19:09 2006

    Copyright 
    (c19822002Oracle Corporation.  All rights reserved.


    Connected to:
    Oracle9i Enterprise Edition Release 9.2.0.4.0 64bit Production
    With the Partitioning option
    JServer Release 9.2.0.4.0 
    Production

    sys
    @dbsid
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  7. #7
    Join Date
    Aug 2004
    Location
    Manchester, UK
    Posts
    143
    It is a windows server, which is used for many things. I tried logging on to it as oracle with the default password but nothing.

    I presume your example is Unix?

    Any other thoughts?

    Many thanks

  8. #8
    Join Date
    Dec 2003
    Posts
    1,074
    We use a combination of export and rman. We export the database in full prior to nightly production, as we have the luxury of having a small enough system that we can currently export everything in under an hour. Following nightly production, we perform a full backup using rman.

    This way, we have up-to-the-minute recovery, as well as the logical backup. We've set things up this way, as we have many systems running in the same database instance instance. If a nightly production job screws up the data in one system, we cannot use rman to recover everything to a point just prior to the error, as that rolls *everything* in the database back (we've had no luck with TSPITR - tablespace point in time recovery - errors we haven't been able to get around). But, we can restore the tables in one system to it's pre-production state using expdp/impdp, leaving all other systems untouched.

    -Chuck

  9. #9
    Join Date
    Aug 2004
    Location
    Manchester, UK
    Posts
    143
    By the way, if you can get into the system user, you can change the database on the sys user by issuing the alter user sys identified by new_password; where new password is the password that you want to assign. It's also possible that the sys account is simply locked, which is the default on newer versions of the database. To connect as a sysdba, which you can do even if the database is locked, log into the database server as the oracle user (or the user oracle was installed as), and issue the following command

    sqlplus "/ as sysdba"
    Sorry, missed this bit first time round. I have now changed the sys password, and will now set up a full cold backup to run every night. I didn't realise that you could change sys whilst logged in as system - didn't think system had enough permissions. But it does. So that's great!

    Many thanks all!

  10. #10
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    It is not OS specific. If you are logged on as the oracle user (member of the dba group) then in a command window (CMD) you will be able to issue the sqlplus "/ as sysdba" login. If your environment is not setup correctly, then you have to manually set it up. In the command window do the following

    1) Change to the bin directory in the oracle software package.
    2) SET ORACLE_HOME=... (set ORACLE_HOME to the full path just under the oracle bin directory.
    3) SET ORACLE_SID=xxxx (Assign the correct oracle sid for your database)

    4) issue the sqlplus "/ as sysdba" account.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  11. #11
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    They control their _product_ behaivour, YOU control your DB behaivour. Don't let them tell you how to do YOUR job. Let your boss know what things could happen if you take the way they want you to take. I never worry about sys's password, I tend to always use external OS authentication to do DBA jobs.

  12. #12
    Join Date
    Jul 2003
    Posts
    2,296
    Quote Originally Posted by Polly013
    It is a windows server, which is used for many things. I tried logging on to it as oracle with the default password but nothing.

    I presume your example is Unix?

    Any other thoughts?

    Many thanks
    you can do this on Windows db Host server in a command prompt.
    1. open a command window (start, run, type in "cmd" then enter)
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  13. #13
    Join Date
    Aug 2004
    Location
    Manchester, UK
    Posts
    143
    Now that I have a sys password I fully intend to do things my way. Whilst I couldn't get onto the system to shut it down, my hands were a bit tied.

    However, I have now come across another strange problem. I can log in to sqlplus using sys/pass@sadas as sysdba. I can then perform 'shutdown immediate' with no problems.

    But then when I attempt 'startup' I get told: ORA-0012 - you are not logged in. So then I exit out of sqlplus and log in again as sys. I then attempt 'startup' again, and get the message 'database cannot be mounted in exclusive mode'.

    I really can't make out what is happening, I am simply trying to bounce the server in the most basic way.

    The contractor has told me to use the following script to do this, but I still can't get past the fact that I should be able to do a basic shutdown as described above!

    Contractor's script!

    Code:
    call c:\APPS\oracle\ora81\bin\oradim.exe -shutdown -sid SADAS -usrpwd ORACLE -shuttype srvc,inst -shutmode i
    net stop OracleORAHOME81TNSListener
    net start OracleOraHOME81TNSListener
    ORADIM -STARTUP -SID SADAS -USRPWD ORACLE -STARTTYPE SRVC -PFILE D:\SADAS\ORACLE\ADMIN\PFILE\SADASINIT.ORA
    I shouldn't have to shut the listener down to shut the database as far as I know. I don't do that on all my other oracle dbs.

    And finally, I have tried to log into the os using the oracle user, but yet again I cannot get the password right. Although the contractor swears they have kept to defaults, that has certainly not been the case with sys and oracle users. Any thoughts on how I can change this one?

    Many thanks!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •