Initial contact Oracle database, FAQ and solution (Turn)

Initial contact Oracle database, FAQ and solution

Just contact Oracle database and get used to the easy way to get started with SQL Server?????????????????????

1. Using the cmd login Sqlplus method

> S qlplus sys/12345678@ordb1 as Sysdba

O RDB1 is the name of the database server

2. Use Oracle's Sqlplus login Sqlplus

3. Remote connection to Oracle database

Method One:

1. Open/network/admin/tnsnames.ora file;//This records a connection name corresponding to the server IP, port number and other information

2. Add the following code to the copy modification:

ORCL1 =

(DESCRIPTION =

(address = (PROTOCOL = TCP) (HOST = 192.168.1.102) (PORT = 1521))

(Connect_data =

(SERVER = dedicated)

(service_name = ORCL)

)

)

Where, ORCL1 is the server database instance name, 192.168.1.102 is the server address, 1521 is the port, Service_Name is the name of the database server instance (not the server's computer name), generally only modifies these four data.

3. Test:

At the command line, enter: Ping the DB server to see if you can ping it.

At the command line, enter: Tnsping DB instance name to see if you can successfully connect to the database.

4. Finally, through the database instance name, user name, password access to the database.

Method Two:

Is the Management configuration Tool "Net Manager" using Oracle.

Add service naming,

In fact, the modification of this tool will also be saved into the "Tnsnames.ora" file, the same as the effect of direct operation "Tnsnames.ora" file.

4. Oracle Enterprise Manager Console configuration

Before using Oracle Enterprise Manager Console, you first start the oracledbconsoleor< database instance service name >windows service.

If you forget the URL for EM, you can reconfigure them by applying the >emca-config Dbcontrol DB command, or you can open the:p ortlist.ini file on the Oracle/product/10.2.0/db_1/install directory For viewing.

5. Create users, assign permissions

Method One:

Login EM (Enterprise Manager control, such as Http://192.168.1.65:5500/em), where the user is created in the configuration;

Method Two:

Apply SQL statements, such as

CREATE USER "TESTUSER1" Profile "DEFAULT" identified by the "*******" account UNLOCK
GRANT "CONNECT" to "TESTUSER1" 6. Forget username, password

Log on to the server,

>sqlplus/as SYSDBA or CONN Sys/pass_word as SYSDBA;

Use the following statement to modify the user's password (except SYS, system, System and SYS to perform alter modification using the other's permissions),

ALTER USER < username > identified by < new pass >;

If sys, SYSTEM's password is forgotten, you can apply orapwd. EXE tool to modify the password. Instructions are as follows,

Orapwd file=< file path + filename > password=< new password

Where file is located is the password-saving files, such as/oracle/product/10.2.0/db_1/database/pwdordb1.ora, and the filename is composed of the "Pwd+ database service name." The location of the password file is in the/database directory under the Oracle_home directory.

This password is the password that modifies the SYS user. Passwords for other users except SYS and system will not change.

7. Oracle's Log

/oracle/product/10.2.0/admin/ordb1/bdump, records the conversion of redo logs, database startup and shutdown, database structure changes, rollback section changes, deadlocks, internal errors and other information.

8. Execute more than one SQL statement at the same time

Method One:

Execute the SQL file with the command, that is, write multiple SQL statements in a SQL file, such as a a.sql file in the D-packing directory, and the A.sql content is as follows:

Insert into table values (...);

Insert into table values (...);

Insert into table values (...);

Insert into table values (...);

We can use the cmd command to login sqlplus, login after the successful input: @d:/a.sql; Can.

Method Two:

In Toad, after entering more than one SQL statement, click Execute as script in the upper left corner of the toolbar.

9. Backup, RESTORE Database

Method One:

Application EM (Enterprise Manager console);

When you need to host authentication, you need to do the following before

Administration Tools > Local Security Policy > Local Policy > User Rights Assignment > is logged in as a batch job, adding the operating system user to the, and then using the operating system user to log on.

Method two, Application exp, IMP,

In the cmd download, input exp, login, you can follow the prompts to backup;

After the backup, the application of IMP can restore the database;

User name Sys as SYSDBA at logon. When backing up database table row data, note the prefix, and prefix, for each table. The name of the table.

Method three, cold backup

After you close the database service, you will control files, data files, log files for backup, by default, these files are saved in a directory, such as D:/ORACLE/PRODUCT/10.2.0/ORADATA/ORDB1.

References, database backup and recovery, http://v.blog.sohu.com/u/vw/2102943

Sqlplus The statement that is committed after the database is manipulated

Commit;

11. Move a database instance from one computer to another computer

Implemented by using a cold backup recovery method.

Target if yes, move the Oracle database instance from host A to host B.

1 on Host B, establish a database instance with the same name as the database instance on host A;

2 to close all Oracle services on host A and B;

3 from Host a copy the Oracle database instance's control files, data files, log files to host B, by default these files will be saved in the same directory, for $oracleRoot/oradata/< database service name >;

4 to start the Oracle service on Host B;

5) complete.