Quick & Easy Dbase Backup ?

you need to use rman (recovery utility), here is a simple introduction:

http://www.cuddletech.com/articles/oracle/node59.html

hi acton, good to see you.  how do i check if listener is up?  I've been able to launch the Enterprise Manager Console gui and connect to the dbase thru there tho.

use "lsnrctl start" to start up service or go to services to start up (if in the windows).

use "lsnrctl status" to check if it is running.

the ocopy.exe option sounds simple but i have no clue on what  to enter in as paramters.  Just in case the oracle dbase is not on the local coputer but a remote server which i connect to from the Enterprise GUI Console.

I tried RMAN but received the error:
RMAN-00554: initialization of internal recovery manager package failed
RMAN-04005: error from target database:
ORA-12560: TNS:protocol adapter error

Ocopy will only work locally on the server, and only if the server is Windows-based.  It takes about the same parameters as a normal DOS/Windows "copy" or "xcopy" command: that is the source file(s) and the destination directory.

Some setup and configuration work is need for RMAN before it can be used.  You may have to spend some time reading the documentation for RMAN if you want to use it.

did you start the TNS serveice as I posted?  Also you need to enter your database name instead of "target" which is the db name the tutorial was using...

i am not sure about ocopy, never used it. but rman is supposed to be a tool to be used to backup and recovery your db. read the tutorial and oracle doc and it is not that hard. need a little practice.

acton, I searched the local HDD for "lsnrctl" but no luck. Checking under Windows 2000 services.msc reveals nothing about oracle in there.  Could it be under a different name?

ok i got my hands on a copy of the "lsnrctl.exe" file and ran it.  I got the error:

(lsnrctl.exe start)

Failed to start service, error 2.
TNS-12560: TNS:protocol adapter error
 TNS-00530: Protocol adapter error

(lsnrctl.exe status)
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
TNS-12541: TNS:no listener
 TNS-12560: TNS:protocol adapter error
  TNS-00511: No listener
   32-bit Windows Error: 61: Unknown error

oh..

are you on the oracle server ?

Before the listener can be started, it must be configured.  Usually the installer will do this for you.

If you are not on the server, you won't be able to (and don't need to) run a copy of the listener locally.  You will need to configure your tnsnames.ora file though for the listener that should be running on your server.  You can use a wizard for this, or you can edit the file manually.

Im not on the actual Oracle server.  I connect to it remotely.  Can backups still be made (similar to SQL Server 2000) ?   I did locate the tnsnames.ora file under C:\oracle\product\10.2.0\client_1\NETWORK\ADMIN.   It appears to be filled in with the details i use while within the GUI console:

# tnsnames.ora Network Configuration File: C:\oracle\product\10.2.0\client_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.

REMOTEDEV =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = firefly)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = remotedbase.home.ca)
    )
  )

I found Net Manager in the start menu and added a listener:
Address 1
Host: Firefly
Port: 1521

I didnt see any location to specify a dbase or provide a login/pass to connect to the dbase

On running rman i still get the same ORA-12560  however using lsnrctl status  i get TNS-01189 The listener could not authenticate the user

"I'm not on the actual Oracle server"  - That could be a problem.

"I connect to it remotely" - How?  Via Remote Desktop, or VNC or something similar?  Or just via mapped network drives?

"Can backups still be made?" - Certainly!

"similar to SQL Server 2000?" - Probably not, but I don't know since I don't use SQL Server.

If you are used to SQL Server, but are new to Oracle, please remember:
1. SQL Server is designed for only one O/S and is tightly-integrated with the O/S, Oracle is platform-independent.
2. SQL Server comes from a company known for making intuitive, GUI (usually) user-friendly tools.
3. Oracle comes from a company known for a high-performance, scalable, stable database.  They also makes some software tools, but that isn't what Oracle does best!

I connect to other dbases thru the Enterprise Manager COnsole.  It asks for dbase names and so i provide the host names which are on the LAN but not locally on my PC.

BTW in the meantime, what is the SQL query to copy the records from one table over into another?  The two tables belong to seperate dbases.

I am not an expert with Enterprise Manager.  I usually do my DBA tasks either directly on the server or connected via Remote Desktop.  I usually use either the DOS command line, or SQL*Plus, which is a command-line interface to Oracle.

Be careful with the term "databases"!  That has a very different meaning in an Oracle environment than in a SQL Server environment!  In Oracle, a "database" is the entire system, that typically includes one (or more) user schemas that contain tables, indexes, views, procedures etc. for a particular application.  An Oracle "database" also typically contains lots of user accounts for people who use the system, or the application may be set up with only one actual Oracle user ID that is shared by all users of the application.
  I'm not an expert on SQL Server, but I understand that one SQL Server system will typically contain multiple "databases" including a "tempdb" and others.

Are your two tables actually in separate Oracle databases (usually there is only one Oracle database per Windows server) or are they just in separate schemas that are part of the same database?  If they are actually in separate databases, you will have to use a database link to get from one to the other.  If they are just in separate schemas, you just need to allow (grant) the one schema owner (the privilege) to select from the table in the other schema.

Here is a link to another question I responded to a couple months ago on this site that covered many of the differences between SQL Server and Oracle.  Please check this out.  It may be quite helpful or at least informative:
https://www.experts-exchange.com/questions/21701397/EASY-basic-informations-about-Oracle-8-9.html

>>SQL query to copy the records from one table over into another?  The two tables belong to seperate dbases.
     sqlplus has a "copy" command which let you transfer data between 2 databases.

     Mark is right. Oracle is entirely different from MS SQL in a lot of aspects. If those two tables just belong to different 2 users (schemas), they are treated as still in the same database , then you would just issue as following:

      insert into table1 select ... from table2;

acton

That syntax ("insert into table1 select ... from table2") will only work if both of these conditions are true:
1. The schema owner for table2 has done:
grant select on table2 to [schema_owner_of_table1]
or: and less likely, the owner of table1 has the "select any table" system privilege.
2. There is a public synonym for table2, or the owner of table1 has a private synonym "table2" pointing to table2 in the other schema.

If there are no synonyms, you will need this syntax when logged in as the owner of table1:
(assuming that table2 is owned by "Mary")
insert into table1 select ... from mary.table2;

If you have DBA privilege, you can do it like this, assuming that table1 is owned by "John", and that the columns are identical:
insert into john.table1 select * from mary.table2;

yup... thx, mark.

The Enterprise GUI has a node entitled DATABASE.  Underneath that are 2 entries, the Development Dbase, and the Production Dbase.  Both databases are located on different hosting PCs (neither on my local pc).  Perhaps it would be easier if there was a way to simply get an export of the table structures and data, procedures, functions, etc saved as SQL query commands.  I recall being able to do this with myPHPAdmin.  Is there an equiv for Oracle?  I'd even consider writing C# code to query the dbase for all its tables and contents if I knew the command to perform such reflections on what is within the dbase.  Perhaps the "exp"ort feature mentioned earlier would be better?

Again Im still having issues with this  TNS-12560: TNS:protocol adapter error despite my feable attempts of using the Net Manager tool.

OK, so you have two different Oracle databases on your network, each on a different PC (or server).  What that means for your local PC is that you must get SQL*Net configured properly on your PC to allow your PC to connect to both of them.  You don't need (and shouldn't have) an Oracle listener running locally on your PC, if you don't have a database running locally on your PC.

Do you have one Oracle_home on your local PC or multiple?  That means, do you have just one installation of the Oracle client software on your local PC, or do you possibly have two (or more) different, separate installations of Oracle software on your local PC?  If you have multiple, separate Oracle installations, you will have to configure SQL*Net in both (or all) of them.  If you are not sure, run RegEdit, open: HkeyLocalMachine, Software, Oracle and check the AllHomes node.  Does that show one entry, or more than one?

Since you are not physically on the server, that limits what you can do, since the Oracle utilites work best (and in some cases only work) if you are directly on the server, or have a Remote Desktop or VNC connection to the server, as if you were on the server.

Every Oracle database will allow you to extract the table structures, data, procedures, etc. *IF YOU HAVE APPROPRIATE SECURITY* through simple SQL queries of the Oracle data dictionary (Oracle's internal tables that keep track of all database objects, like: users, tables, indexes, views, procedures, etc.) or from the tables themselves to get the data.  No, you don't need to use C# or any other tool for this, simple SQL queries can gather all this information if you know where to look and if you have the permissions.

if you want to connect to remote db, you need to set up a correct entry in your <oracle>\network\admin\tnsnames.ora as :

<entryname>=
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST =< remoteservername>)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = <DEDICATED or SHARED>)
      (SERVICE_NAME = <your db name>)
    )
  )

It assumes that you set up net service in your server as TCP/IP 1521, look at  listener.ora in the server to see if it is already set up.

do you have toad? toad has a very straightforward menu option to let you extract table structures and as such ...

In case you aren't familar with TOAD, that name is an acronym for: Tool for Oracle Application Developers.  It is available from Quest Software.  (Remember what I said earlier about Oracle being a dataabse company, not a tools company?  This is a good GUI tool from a third party company that is better in many ways than what Oracle offers.)  I think they still offer a free version, but that may have a reduced feature set.

I only have one Oracle Home.  As for SQL*Net the closest i could find in the start menu was Oracle Net Manager.  Currently it has listed:
Oracle Net Configuration
  + Local
      - Profile
      + Service Naming
            - Dbase1
            - Dbase2
            - Dbase3
      + Listeners
             - LISTENER
                   
I inserted LISTENER only ysterday but no effect.  Checking under Service Naming, all seems good.   Each Dbase has a Serviuce Name, Connection Type: Database Default, Protocol TCP, Port 1521, and Host Name Dbase1 (or Dbase2, or Dbase3).  Address configuration only has one tab.

from your client machine, do you have a entry for the remote db? You can use net configuration assistant tool to configure and test it.

I've always used either the Net Configuration Assistant or manually edited the tnsnames.ora and sqlnet.ora file, rather than using Net Manager, so I'm not familiar with Net Manager.  Who set up and manages the Oracle databases on your network?  Can they (or that person) give you any assistance with configuring your client to connect to those servers?

The others around me are just as clueless when installing/using oracle so no assistace for me.  I have the Net Configuration Assistant up and it is giving me 4 choices: Listener Configuration, Naming Method Config, Local Net Service Name, and Directory Usage.

Under Listener, i checked out reconfigure and a listing for LISTENER is provided, Protocol TCP, Port 1521.  For Local Net Service, I selected reconfigure, selected the net servuice name, and priovied all the details.  Test was successful.

You don't need a listener on a local PC!  A listener is only meaningful on a server that has an Oracle database running on it.

I always use the Local Net Service Name which will configure your: tnsnames.ora and your sqlnet.ora files for you.  These are in your Oracle_home\Network\Admin directory.  Choose: Add, then enter the "Service Name" of the database you want to connect to.  This is usually a three-part value in the form: [SID].[organization].com, where [SID] is the database name on the server.
If your organization is: ABC Inc., and your database name (SID) is: "PROD", then you would enter this for the "Service name":
PROD.ABC.com  
(As far as I know, this field is not case-sensitive.)

Then choose the default protocol (TCP), then enter the name or IP address of your Oracle server for the "Host name".  Use the default port value (1521) unless your server's listener has been configured with a non-default port value.  At that point you will be ready to test the connection.  If it succeeds, then give this a meaningful name like: "Prod" or "Test" and save it.  (This is the value that you will need every time for third line of a typical Oracle login screen.)

Ok i will successful with the Local Net Service as you said.  

I tried "exp" from the console window and used the login/pass as what i entered for the Local Net Service entry but received the error:
EXP-00056: ORACLE error 12560 encountered
ORA-12560: TNS:protocol adapter error
EXP-00000: Export terminated unsuccessfully

The tnsnames.ora  file's contents appear similar to the TNS descriptor as presented in the Enterprise GUI, and thru the GUI i connect fine.  The sqlnet.ora is:

SQLNET.AUTHENTICATION_SERVICES= (NTS)

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)


Is this ok?  This has not been modified in any way (default settings during install).

Also i tried RMAN:

ORACLE_HOME=C:\oracle\product\10.2.0\client_1
ORACLE_SID=dbase1

C:\Documents and Settings\> rman nocatalog target /

Recovery Manager: Release 10.2.0.1.0 - Production on Wed May 3 16:59:14 2006

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00554: initialization of internal recovery manager package failed
RMAN-04005: error from target database:
ORA-12560: TNS:protocol adapter error

What happened in Net Configuration Assistant when you tested the connection?  Did it work, or report an error?  If you didn't test it, you can either go back into that wizard and choose "Reconfigure" then select your entry and test it.  Or, from a DOS prompt, try:
tnsping [your tns_alias name]

for example, if you had named it "Prod", try:
tnsping prod

or:
tnsping prod.[organization_name].com

If that works, you will see a multi-line response with "OK" on the last line.

RMAN, export and all other Oracle utilities will only work from your client *AFTER* you get SQL*Net properly configured.  If you don't have a working SQL*Net connection, nothing else in Oracle will work from your PC.

Yes, getting SQL*Net configured correctly is probably the biggest pain of any new Oracle install.

the tnsping dbase1  worked fine and Net Config Assistant tested connection ok.  All was successful in that respect.  

Now for SQL Net, exactly where is that tool located?

My start menu under Windows 2000 reveals:
[Application Development]
  Oracle Data Provider for .NET Readme.lnk
  Oracle Object for OLE Class Library Help.
  Oracle Object for OLE Help.lnk
  Oracle Object for OLE Readme.lnk
  Oracle ODBC Help.lnk
  OraOLEDB Readme.lnk
  SQL Plus.lnk
  SQLPlus Worksheet.lnk
[Configuration and Migration Tools]
Administration Assistant for Windows.lnk
Microsoft ODBC Administrator.lnk
Net Configuration Assistant.lnk
Net Manager.lnk

[Enterprise Management Packs]

[Integrated Management Tools]
Enterprise Security Manager.lnk
OLAP Analytic Workspace Manager and Works

Oracle Directory Manager.lnk
Policy Manager.lnk
SQLPlus Worksheet.lnk
Wallet Manager.lnk

[Oracle Installation Products]
Universal Installer Concepts Guide.lnk
Universal Installer.lnk

Enterprise Manager Console

SQL Net is a generic term for Oracle's client connectivity software, it isn't the exact name of a particular program or shortcut.  You can configure it either via:
1. Net Configuration assistant (my recommendation)
2. Net Manager
3. hand editing the tnsnames.ora and sqlnet.ora files (only for the experienced!)

If you have that working, then all of the Oracle tools and utilities that you listed above should work *IF* in fact you do have only one "Oracle_home" directory on your PC.  If you have multiple Oracle_homes, you need to configure SQL*Net in each one.  The easiest way to do that is to just copy tnsnames.ora and sqlnet.ora files from a directory where they work, to another \Network\Admin directory where they are needed.

Of course the listener and database have to be running on the server.

Does SQL*Plus work for you?  When you start that, it should give you a three-line login box where you enter:
1. a valid Oracle user name
2. the password for that user
3. your SQL*Net database alias (dbase1)

If that works, then you will be at an "SQL>" prompt where you can type, then run any SQL statement you like.  Try this one to get a list of all of the pre-built objects in Oracle's "data dictionary":
select * from dictionary;

Use [Ctrl]-C to stop it if you don't want to wait for it to get to the end.

You may want to change the default formatting, something like this:
column comments format a60

then repeat:
select * from dictionary;

SQL*Plus is the powerful, native, command-line programming interface for Oracle.  I do almost all of my DBA work via SQL*Plus.
That can easily execute *.SQL scripts for repetitve tasks, so I can use a text editor to write a script, try it in our test system, then run it in production.

>> the tnsping dbase1  worked fine and Net Config Assistant tested connection ok.  All was successful in that respect.  

     great.  you can use either OEM (oracle enterprise manager console) or sql*plus to connect to your db.
     in OEM, you use "dbase1" to configure a  node to your  db.
     in sql*plus command line, you simplely type in :
      sqlplus  <user>/<pass>@dbase1;



acton

    Seems we go a long way from your original topic :)

Yup im connected to SQL Plus fine.  The dicitonary statement revealed lots.  I had to type in the user/pass and dbase name.  All is good.  Now backing up...

With my current functionality what would you guys suggest?  I've tried using "exp" and "rman" from the CUI console window but still get an adapter error.

I only have one oracle_home and installation on this PC.

BTW thanks for the persistence so far guys.  

Oracle backups and/or exports are not typically done from a client machine.  They are usually done from the server.  An export should work on a client if you really want to do it that way, and if the client is the same verion of Oracle as the server, but it will be much slower than directly on the server because of the network overhead.

RMAN may allow you to initiate backups from a client, but I haven't mastered RMAN yet.  (I plan to in the next three months.)

I think we have gotten your basic connectivity to work (which was the main part of your original question) and have given you a lot more help (or at least information) than is typical for a single question here.

Now you need to decide if you really want to do backups or exports regularly from a client.  I don't recommend that.  Then you may need to read the documentation on RMAN or post another question for RMAN, now that we have your basic connectivity working.

agree.

i never did backup on client side or export, usually they are done in server side.
 you want to spend sometime on oracle's doc which is very detailed and informative and then go back to do it...

Great job guys. Thanks.