Shulou(Shulou.com)06/01 Report--

Oracle management, backup and recovery

The users who manage the database are sys and system. Sys can be compared to the chairman and system to the general manager. There are the following differences between them.

1. The most important difference is that the importance of stored data is different.

Sys: the base tables and views of all oracle data dictionaries are stored in sys users, and these base tables and views run for oracle

Is critical and is maintained by the database itself and cannot be changed manually by any user. Sys users own dba, sysdba, sysoper

Roles or permissions, which are the users with the highest privileges in oracle

System: used to store secondary internal data, such as management information about some features or tools of oracle. Owned by system users

Dba, sysdba role, or system permissions

2. The second difference is the difference of authority.

Sys users must log in as as sysdba or as sysoper and cannot log in to the database in a normal way

If system logs in normally, it is actually an ordinary dba user, but if as sysdba logs in, the result is actually logged in as a sys user

You can see it from the login information.

Sysdba and sysoper are the same or 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 (create database) none

Drop database (delete database) none

Create spfile create spfile

Alter database archivelog (archive log) alter database archivelog (archive log)

Alter database recovery recovery database can only be fully recovered, not incomplete recovery

Have restricted session session limit have restricted session session limit

Allows the user to perform some basic operations as a sys user connection, but cannot view user data

After login, the user is sys. After login, the user is public.

Users with dba permissions

Dba users refer to database users with the role of dba. Privileged users can perform special operations such as starting and shutting down instances.

Dba users can only perform various administrative operations after starting the database.

Manage initialization parameters

Show initialization parameters

1. Show parameter displays 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

Logical backup refers to the process of using the tool export to export the structure of the data object and the data to the file (data is exported to disk) and logical recovery.

It refers to the process of using the tool import to import data objects into the database using backup files when the database objects are damaged by misoperation.

Physical backup can be done either in database open or after database shutdown, but logical backup and recovery can only be done in open state.

Export:

Export can be divided into three ways: exporting a user's table, exporting each user's schema, and exporting the whole database.

Export is done using the exp command, and here are the common options

Userid: used to specify the user name, password, and connection string to perform the export operation

Tables: table used to specify the export operation

Owner: used to specify the scheme for performing the export operation

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

Inctype: used to specify the type of increment to perform the export operation

Rows: used to specify whether to export the data in the table to perform the export operation

File: used to specify the export file name

Export table:

1. Export your own table (table data)

[oracle@aliyun_test oracle] $exp userid=scott/redhat@test 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, 2009, 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 and report an error, and then determine whether the Oracle instance is test. Check

[oracle@aliyun_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)

)

)

The Oracle instance is ORCL11G, so re-export

[oracle@aliyun_test oracle] $exp userid=scott/redhat@orcl11g 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, 2009, 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 14 rows exported

EXP-00091: Exporting questionable statistics.

EXP-00091: Exporting questionable statistics.

Export terminated successfully with warnings.

[oracle@aliyun_test oracle] $ll / oracle/test/scott_emp.dmp

-rw-r--r-- 1 oracle oinstall 16384 Sep 26 14:38 / oracle/test/scott_emp.dmp

Export multiple tables

[oracle@aliyun_test oracle] $exp userid=scott/redhat@orcl11g 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, 2009, 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 14 rows exported

EXP-00091: Exporting questionable statistics.

EXP-00091: Exporting questionable statistics.

. . Exporting table DEPT 4 rows exported

EXP-00091: Exporting questionable statistics. There is a warning message here, which finds out that it is a problem with the character set.

EXP-00091: Exporting questionable statistics.

Export terminated successfully with warnings.

If the character set does not match, first query the character set of the database

SQL > select sys_context ('userenv','language') from dual

SYS_CONTEXT ('USERENV','LANGUAGE')

AMERICAN_AMERICA.AL32UTF8

And then set the same character set

[oracle@aliyun_test oracle] $export NLS_LANG= "AMERICAN_AMERICA.AL32UTF8"

Then export the data of multiple tables

[oracle@aliyun_test oracle] $exp userid=scott/redhat@orcl11g 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, 2009, 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 14 rows exported

. . Exporting table DEPT 4 rows exported

Export terminated successfully without warnings.

Found that the warning disappeared and successfully resolved the warning message.

The above execution is for Scott users to export their own tables. Here is how to export scott users' tables with system users.

[oracle@aliyun_test oracle] $exp userid=system/redhat@orcl11g 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, 2009, 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 14 rows exported

Export terminated successfully without warnings.

You only need to modify the corresponding table information

Export the structure of the table, only the structure

3. Export the structure of the table

Exp userid=scott/redhat@orcl11g tables=emp file=/oracle/test/emp_table.dmp rows=n

4. Use direct export method

Exp userid=scott/redhat@ocrl11g tables=emp file=/oracle/test/emp_direct.dmp direct=y

This method is faster than the default conventional method, and can be considered when the amount of data is large.

This method requires that the character set of the database is exactly the same as that of the client, otherwise an error will be reported.

Export scheme

Exporting a scheme means using the export tool to export all objects (tables, views, indexes, etc.) and data in one or more scenarios and store it in a file

1. Export your own plan

[oracle@aliyun_test oracle] $exp userid=scott/redhat@orcl11g 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, 2009, 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 14 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 schemes

If the user wants to export other scenarios, the permissions of dba or exp_full_database are required, such as using the system user

You can export any scheme.

Exp userid=system/redhat@orcl11g owner=system,scott file=/oracle/test/system_scott.dmp

The example indicates that users using system cannot export the schema of sys

Export the entire database

Export database refers to the use of export to export objects and data in all databases, requiring the user to have the right to dba

Or exp_full_database permission.

Exp userid=system/redhat@orcl11g full=y inctype=comlete file=/oracle/test/full_oracle.dmp

Import tabl

1. Import table

Imp userid=scott/redhat@orcl11g tables=emp file=

Simulate deleting a table and then exporting the data?

[oracle@oracle11g ~] $exp userid=scott/redhat@orcl11g tables=student

File=/oracle/test/student.dmp

SQL > drop table student

Table dropped

SQL > desc student

Object student does not exist

Then imp imports the data

$imp userid=scott/redhat@orcl11g tables=student file=/oracle/test/student.dmp

Finally, check whether the table was successfully imported?

SQL > desc student

Name Type Nullable Default Comments

--

XH NUMBER (4) Y

XM VARCHAR2 (20) Y

SEX CHAR (2) Y

SAL NUMBER (7 dint 2) Y

BIRTHDAY DATE Y

Successfully import table data

2. Use system to import data

Simulation deletion

SQL > drop table student

Table dropped

Then import the table data

$imp userid=system/redhat@orcl11g tables=student file=/oracle/test/student.dmp touser=scott

And then check

SQL > desc student

Name Type Nullable Default Comments

--

XH NUMBER (4) Y

XM VARCHAR2 (20) Y

SEX CHAR (2) Y

SAL NUMBER (7 dint 2) Y

BIRTHDAY DATE Y

3. The structure of the import table

Import only the structure of the table and not the data

Imp userid=scott/redhat@orcl11g tables=emp file= rows=n

4. Import data

If the object of the table already exists, you can import only the data, not the object.

Imp userid=scott/redhat@orcl11g tables=emp file= ignore=y

Import scheme

An import scheme refers to importing objects and data from a file into one or more scenarios using the import tool

If you want to import another user's scheme, the user is required to have the permission of dba or imp_full_database.

1. Import your own solution

Imp userid=scott/redhat@orcl11g file=/oracle/test/schema_scott.dmp

2. Import the scheme of other users (use system users to operate)

Imp userid=system/redhat@orcl11g 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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.