Management of Oracle and use of EXP and IMP

Oracle Management, Backup and recovery

The user who manages the database is the SYS, system, which can compare SYS to the chairman and the system to the general manager, the difference between the following

1, the most important difference, the importance of storing data is not the same

SYS: the base tables and views of all Oracle data dictionaries are stored in the SYS user, the base tables and views for the operation of Oracle

Is critical and is maintained by the database itself and cannot be changed manually by any user. SYS user has DBA, SYSDBA, Sysoper

Roles or permissions, which are the most user with Oracle permissions

System: Used to hold sub-level internal data, such as some of Oracle's features or tools for managing information. System users have

DBA, SYSDBA role, or system permissions

2, the second difference, the authority of the different

SYS user must be logged in as SYSDBA or as sysoper and cannot log in to the database in the normal way

If the system is logged on normally, it is actually a normal DBA user, but if as SYSDBA is logged in, the result is actually logged in as SYS user

You can see it from the login information.

SYSDBA and Sysoper are the same and different, none is not, that is, different

Startup Database Startup

Shutdown database shutdown

ALTER DATABASE Open/mount/backup ALTER DATABASE Open/mount/backup

Change character set None

Create DATABASE (creates databases) None

Drop database (delete databases) None

Create SPFile Create SPFile

ALTER DATABASE Archivelog (archive log) ALTER DATABASE Archivelog (archive log)

ALTER DATABASE recovery recovery databases can only be fully recovered and cannot perform incomplete recovery

have restricted session limit to have restricted session limit

Allows the user to connect as a SYS user for some basic operations, but cannot view user data

User is public after login after user is SYS login

User with DBA Authority

DBA user refers to a database user with DBA role, a privileged user can perform special operations such as launching an instance, shutting down an instance, etc.

DBA users can perform various administrative operations only after starting the database

Managing Initialization parameters

Display initialization parameters

1, show parameter display system parameters

Sql> show parameter;

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

O7_dictionary_accessibility Boolean FALSE

Active_instance_count integer

Aq_tm_processes integer 0

Archive_lag_target integer 0

Asm_diskgroups string

Asm_diskstring string

Asm_power_limit Integer 1

Backup and Recovery of Oracle database

A logical backup is the process of exporting the data object's structure and data using the tools export (data export to disk), logical recovery

Refers to the process of importing a data object into a database using a backup file when the database object is damaged by mistake.

Physical backups can be performed in the open state of the database or after the database is closed, but logical backups and restores can only be performed in open state

Export:

Export is divided into: export User A table, export the user schema, export the entire database three ways

Export is done using the EXP command, the following are common options

UserID: Used to specify the user name, password, connection string to perform the export operation

Tables: Table for specifying export operations

Owner: Specifies the scenario to perform the export operation

Full=y: Used to specify the database to perform the export operation

Inctype: Used to specify the increment type to perform the export operation

Rows: Used to specify whether the export operation is to export data from the table

File: Used to specify the export file name

To export a table:

1. Export your own table (table data)

[Email protected]_test oracle]$ exp userid=scott/[email protected] tables=emp file=/oracle/test/scott_emp.dmp

Export:release 11.2.0.1.0-production on Mon Sep 26 14:33:45 2016

Copyright (c) 1982, the Oracle and/or its affiliates. All rights reserved.

Exp-00056:oracle Error 12154 encountered

Ora-12154:tns:could not resolve the connect identifier specified

Exp-00000:export terminated unsuccessfully

Export an error, and then make sure that the Oracle instance is test and view

[Email protected]_test admin]$ Cat Tnsnames.ora

# Tnsnames.ora Network Configuration File:/u01/app/product/11.2.0/dbhome_1/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

orcl11g =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP) (HOST = aliyun_test) (PORT = 1521))

(Connect_data =

(SERVER = dedicated)

(service_name = orcl11g.us.oracle.com)

)

)

Oracle instance is orcl11g and then re-exported

[Email protected]_test oracle]$ exp userid=scott/[email protected] tables=emp file=/oracle/test/scott_emp.dmp

Export:release 11.2.0.1.0-production on Mon Sep 26 14:38:11 2016

Copyright (c) 1982, the Oracle and/or its affiliates. All rights reserved.

Connected to:oracle Database 11g Enterprise Edition Release 11.2.0.1.0-64bit Production

With the partitioning, OLAP, Data Mining and Real application testing options

Export done in Us7ascii character set and UTF8 NCHAR character set

Server uses Al32utf8 character set (possible charset conversion)

About to export specified tables via conventional Path ...

. . Exporting table EMP rows exported

Exp-00091:exporting questionable statistics.

Exp-00091:exporting questionable statistics.

Export terminated successfully with warnings.

[Email protected]_test oracle]$ ll/oracle/test/scott_emp.dmp

-rw-r--r--1 Oracle oinstall 16384 SEP 14:38/oracle/test/scott_emp.dmp

Export more than one table

[Email protected]_test oracle]$ exp userid=scott/[email protected] tables=emp,dept File=/oracle/test/scott_emp_ Dept.dmp

Export:release 11.2.0.1.0-production on Mon Sep 26 15:10:05 2016

Copyright (c) 1982, the Oracle and/or its affiliates. All rights reserved.

Connected to:oracle Database 11g Enterprise Edition Release 11.2.0.1.0-64bit Production

With the partitioning, OLAP, Data Mining and Real application testing options

Export done in Us7ascii character set and UTF8 NCHAR character set

Server uses Al32utf8 character set (possible charset conversion)

About to export specified tables via conventional Path ...

. . Exporting table EMP rows exported

Exp-00091:exporting questionable statistics.

Exp-00091:exporting questionable statistics.

. . Exporting table DEPT 4 rows exported

Exp-00091:exporting questionable statistics. A warning message appears, identifying the problem with the character set.

Exp-00091:exporting questionable statistics.

Export terminated successfully with warnings.

Character set mismatch causes the database character set to be queried first

Sql> Select Sys_context (' Userenv ', ' language ') from dual;

Sys_context (' USERENV ', ' LANGUAGE ')

--------------------------------------------------------------------------------

American_america. Al32utf8

And then in the same set of characters

[Email protected]_test oracle]$ export nls_lang= american_america. Al32utf8 "

And then export data from multiple tables

[Email protected]_test oracle]$ exp userid=scott/[email protected] tables=emp,dept File=/oracle/test/scott_emp_ Dept.dmp

Export:release 11.2.0.1.0-production on Mon Sep 26 15:11:56 2016

Copyright (c) 1982, the Oracle and/or its affiliates. All rights reserved.

Connected to:oracle Database 11g Enterprise Edition Release 11.2.0.1.0-64bit Production

With the partitioning, OLAP, Data Mining and Real application testing options

Export done in Al32utf8 character set and UTF8 NCHAR character set

About to export specified tables via conventional Path ...

. . Exporting table EMP rows exported

. . Exporting table DEPT 4 rows exported

Export terminated successfully without warnings.

Discovery warning disappears, successfully resolves warning message

The above is performed by the Scott user to export their own tables, the following describes the use of system users to export the Scott User's table

[Email protected]_test oracle]$ exp userid=system/[email protected] tables=scott.emp file=/oracle/test/system_ Emp.dmp

Export:release 11.2.0.1.0-production on Mon Sep 26 15:17:54 2016

Copyright (c) 1982, the Oracle and/or its affiliates. All rights reserved.

Connected to:oracle Database 11g Enterprise Edition Release 11.2.0.1.0-64bit Production

With the partitioning, OLAP, Data Mining and Real application testing options

Export done in Al32utf8 character set and UTF8 NCHAR character set

About to export specified tables via conventional Path ...

Current user changed to SCOTT

. . Exporting table EMP rows exported

Export terminated successfully without warnings.

You just need to modify the appropriate table information.

Export the structure of a table, export only the structure

3, the structure of the export table

Exp Userid=scott/[email protected] tables=emp file=/oracle/test/emp_table.dmp rows=n

4. Using direct Export method

Exp Userid=scott/[email protected] tables=emp file=/oracle/test/emp_direct.dmp direct=y

This is faster than the default normal way, and when the data volume is large, consider using this method

This method requires the database's character set and the client's character set exactly the same, otherwise it will error

Export scenarios

Export scenarios refer to exporting a scenario or all objects in multiple scenarios (tables, views, indexes, and so on) and data and storing them in a file using the Export tool

1. Export your own solution

[Email protected]_test oracle]$ exp userid=scott/[email protected] Owner=scott file=/oracle/test/schema_scott.dmp

Export:release 11.2.0.1.0-production on Mon Sep 26 15:22:27 2016

Copyright (c) 1982, the Oracle and/or its affiliates. All rights reserved.

Connected to:oracle Database 11g Enterprise Edition Release 11.2.0.1.0-64bit Production

With the partitioning, OLAP, Data Mining and Real application testing options

Export done in Al32utf8 character set and UTF8 NCHAR character set

. Exporting Pre-schema procedural objects and actions

. Exporting foreign function library names for user SCOTT

. Exporting public type synonyms

. Exporting private type synonyms

. Exporting object type definitions for user SCOTT

About to export SCOTT ' s objects ...

. Exporting database Links

. Exporting sequence Numbers

. Exporting cluster definitions

. About to export SCOTT ' s tables via conventional Path ...

. . Exporting table DEPT 4 rows exported

. . Exporting table EMP rows exported

. . Exporting table Salgrade 5 rows exported

. . Exporting table STUDENT 0 rows exported

. Exporting synonyms

. Exporting views

. Exporting stored Procedures

. Exporting operators

. Exporting referential integrity constraints

. Exporting triggers

. Exporting indextypes

. Exporting bitmap, functional and extensible indexes

. Exporting Posttables Actions

. Exporting materialized views

. Exporting snapshot logs

. Exporting job queues

. Exporting refresh groups and children

. Exporting dimensions

. Exporting Post-Schema procedural objects and actions

. Exporting statistics

Export terminated successfully without warnings.

2. Export other programs

If the user wants to export other scenarios, they need DBA authority or exp_full_database permissions, such as using the system user

You can export any scenario

Exp Userid=system/[email protected] Owner=system,scott file=/oracle/test/system_scott.dmp

Example indicates a schema that uses the system user cannot export SYS

Export the entire database

Exporting a database means exporting objects and data from all databases with export, requiring that user to have DBA authority

or a exp_full_database permission.

Exp Userid=system/[email protected] full=y inctype=comlete file=/oracle/test/full_oracle.dmp

Import Table

1. Import Table

Imp userid=scott/[email protected] tables=emp file=

Simulate deleting a table and then exporting the data?

[[Email protected] ~]$ exp userid=scott/[email protected] Tables=student

File=/oracle/test/student.dmp

sql> drop table student;

Table dropped

sql> desc Student;

Object student does not exist

The IMP then imports the data

$ imp userid=scott/[email protected] tables=student file=/oracle/test/student.dmp

Finally see if the table was imported successfully?

sql> desc Student;

Name Type Nullable Default Comments

-------- ------------ -------- ------- --------

XH Number (4) Y

XM VARCHAR2 (Y)

SEX CHAR (2) Y

SAL number (7,2) Y

BIRTHDAY DATE Y

Successfully imported table data

2. Import data with System execution

Simulate Delete

sql> drop table student;

Table dropped

Then import the table data

$ imp userid=system/[email protected] tables=student file=/oracle/test/student.dmp Touser=scott

And then view

sql> desc Student;

Name Type Nullable Default Comments

-------- ------------ -------- ------- --------

XH Number (4) Y

XM VARCHAR2 (Y)

SEX CHAR (2) Y

SAL number (7,2) Y

BIRTHDAY DATE Y

3, the structure of the import table

Import only the structure of a table without importing data

Imp userid=scott/[email protected] tables=emp file= rows=n

4. Import data

If the object of the table already exists, you can import only the data without importing the object

Imp userid=scott/[email protected] tables=emp file= ignore=y

Import Scenarios

Import scenario refers to importing objects and data from a file into one or more scenarios using the Import tool

If you want to import a scenario for another user, ask the user to have DBA authority, or Imp_full_database permissions

1, the introduction of their own solutions

Imp Userid=scott/[email protected] File=/oracle/test/schema_scott.dmp

2. Scenarios for importing other users (operating with system users)

Imp userid=system/[email protected] file=/oracle/test/schema_system_scott.dmp Fromuser=system Touser=scott

Import Database

By default, when you import a database, all structures and data are imported

Imp userid=system/redhat full=y file=/oracle/test/database.dmp

Management of Oracle and use of EXP and IMP