Take backup of Oracle database without SYSDBA and SYSOPER login in system?

by Vishal Kolekar
(Mumbai)

I have question about taking backup of database in ORACLE 10g. In my project I created one application in my mobile which only accepts user name and password and sends it to the server and after authenticating moves to DBA command screen in mobile for further commands. Since from login screen there is no text box for SYSDBA and SYSOPER

so my question is as below.

1> without login as "SYSDBA" and "SYSOPER" can SYSTEM user take backup? Is there any solution for it that is without login as "SYSDBA" or "SYSOPER"?

2) please tell me 3-4 simplest command of DBA to take backup of database in ORACLE 10g

Vishal Kolekar

If we go back to basics there are several ways to make backups of your Oracle database:
  • you can use the Export utility or Datapump in Oracle 10g onwards to perform a logical backup and for that you don't need sysdba or sysoper priviliges. You can also use PL/SQL procedures to make the backup if you're using Datapump;
  • you can take a cold backup by making a copy of the all the database files using operating system commands (you do need sysdba/sysoper privileges to shutdown the Oracle database and re-start it but not to do the backup);
  • you can take hot backups with the database open (if it's running in archivelog mode) by using RMAN (Oracle Recovery Manager)
  • you can also take hot backups of the database by issuing the command

    alter tablespace begin backup;

    in SQL*Plus and then using the operating system to make backups of the data files and take the tablespace out of backup mode by issuing the SQL command

    alter tablespace end backup;

    Again the Oracle database has to be running in archivelog mode
Neither of these last 2 methods requires sysoper or sysdba privileges.

The short answer to the first question then is that unless you need to shutdown or startup your Oracle database you don't need sysdba or sysoper privileges.

The converse is that if you do need to shutdown/startup your Oracle database via your mobile application you will need one or other of those privileges so your application should be able to accept the extra few characters (" as sysdba") and pass them on the server along with the username and password.

As for question 2 - that can't be answered completely without knowing what type of backup you want - physical (cold, hot) or logical and whether you want to back up the whole of your Oracle database or just part(s) of it.

The simplest form of backup is the cold backup - shutdown the database and use the operating system to make a copy of all the files. That's fine if you don't mind losing data, if you need to ensure no data loss then you need to perform a hot backup. Going one step further you could consider a standby database using Oracle DataGuard or using advanced replication or Oracle Streams to propagate data changes to another database.

Full details of backup and recovery options, Oracle DataGuard and Streams are in the Oracle database documentation. Details on the Oracle DataPump and Export utilities can be found in the Oracle Database Utilities manual.

For instructor-led Oracle training courses see our partners Smartsoft Computing for Oracle training in the UK or Skillibuilders for training in the New York/Boston area and for classroom quality online training see GoGo Training and remember to contact us before booking to ensure you get our special rates for the training.

Comments for Take backup of Oracle database without SYSDBA and SYSOPER login in system?

Average Rating starstarstarstarstar

Click here to add your own comments

Dec 03, 2011
Rating
starstarstarstarstar
database
by: Mayank kumar

how to connect database without sysdba & sysoper?
Connecting to Oracle as a normal user is just a case of providing the username and password. For example in SQL*Plus the command is:
SQL> connect hr/monday@xe

where "hr" is the username, "monday" is the password and "xe" is the database name.

May 02, 2011
Rating
starstarstarstarstar
How to connect Oracle Database using JDBC for SYSDBA login?
by: Vishal D. Kolekar

In my project I am using JDBC code for connection to Oracle database for DBA purpose. I posted the part of code which I am going to used in project. I am developing the application in Netbeans 6.9.

In my project connection will be made to the database through mobile so it is a remote connection to the database. By using this code I can only login as simple System user like other user. Can anybody tell me the how I change the code so that I can get SYSDBA login! without this SYSDBA or SYSOPER role I can't use DBA's backup option.

Please tell me some modification or any other way to solve this situation.


String driver="jdbc:oracle:thin:";
String connStr="@"+Address+":"+port+":"+SID;
public Connection Connect(String UserName,String Password){
try{
if(checkLogin(UserName, Password)){
Class.forName("oracle.jdbc.OracleDriver").newInstance();
// Load the JDBC driver
java.sql.DriverManager.registerDriver (new oracle.jdbc.OracleDriver());
//System.out.println(driver+UserName+"/"+Password +connStr);
return java.sql.DriverManager.getConnection (driver+UserName+"/"+Password +connStr);
}
else
return null;
}catch(Exception e){
System.out.println("Error: " +e.getMessage());
return null;
}
}


Possibly a better solution than connecting "as sysdba" would be to create a command file on the host server that would perform the backup for you. That way you could run the Oracle Recovery Manager (RMAN) which automatically keeps track of what backups have been made and makes baking up and recovering Oracle databases much easier.

If the operating system user is a member of the OSDBA group, and if the Oracle SID is set, then RMAN can connect to the database with SYSDBA privileges without having to specify a username and password with the command

rman target / .

You could either invoke the command file directly or schedule it using DBMS_SCHEDULER.

Apr 28, 2011
Rating
starstarstarstarstar
Show some example of backup for Oracle
by: Vishal D. Kolekar

Please can you show me 3-4 example of backup(hot,cold,archived) some command for Oracle 10g with appropriate syntax.

Performing a cold backup in Oracle is very simple (this example is for Oracle 10g XE on Windows):
  1. connect as a user with SYDBA privilege
    SQL> connect / as sysdba
  2. shutdown the database
    SQL> shutdown
  3. copy the files using operating system commands
    SQL> $copy c:\oraclexe\oradata\XE\*.dbf c:\oraclexe\oradata\backup
  4. start the database
    SQL> startup

Performing an Oracle hot backup is slightly more complicated. You can either use RMAN or plain SQL statements but either way the database must be in archiving the online redo logs (i.e. in archive log mode). The steps to put the database into archivelog mode are as follows:-
  1. shutdown the database
    SQL> shutdown immediate
  2. start the database in mount mode (i.e. don't open the database files)
    SQL> startup mount
  3. put the database into archivelog mode
    SQL> alter database archivelog;
  4. open the database
    SQL> alter database open;
  5. confirm the database is in archivelog mode
    SQL> select log_mode from v$database;
    and that the archiver is running
    SQL> select archiver from v$instance;
  6. force a log switch to cause the redo log file to be archived
    SQL> alter system switch logfile;
  7. Check that the log file has been archived
    SQL> select name from archived_logs;

Once your Oracle database is running in archivelog mode, performing a hot backup using RMAN (the Oracle Recovery Manager) is very straightforward.
  1. start RMAN (issue the command rman from the operating system prompt)
  2. from RMAN connect to the target database (the database to be backed up)
    RMAN> connect target /
  3. backup the database
    RMAN> backup dataabse;
  4. optionally backup the controlfile
    RMAN> backup current controlfile; RMAN has many commands and options which affect how the backup and recovery is done and where files are stored. See the Oracle Database Backup and Recovery User's Guide for the details. For in-depth training Contact us for details of our instructor-led or online training on Oracle backup and recovery.

Click here to add your own comments

Join in and write your own page! It's easy to do. How? Simply click here to return to Oracle Questions.