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

Backup and recovery of Oracle database

Let's take a look at some simple examples:

Various methods of physical backup of Oracle database.

Use RMAN tools to backup and restore the database.

Data export and import operations.

(1) close the BOOKSALES database and make a full cold backup.

Select file_name from dba_data_files

Select member from v$logfile

Select value from v$parameter where name='control_files'

Shutdown immediate

/ / copy all data files, online redo log files and control files to backup disk

Startup

(2) after starting the database, create a table called cold in the data and insert the data to change the state of the database.

Create table cold (id number (510) primary key)

Table COLD has been created.

Insert into cold values (1)

1 row has been inserted.

Select * from cold

(3) use the cold backup of the database to restore the state of the BOOKSALES database to the backup time, and check whether there is a cold table after the restore.

/ / close the database

Shutdown immediate

/ / restore all backup data files, control files, and online redo log files to their original location.

/ / restart the database

Startup

(4) set the BOOKSALES database to archive mode.

/ / close the database

Shutdown immediate

/ / set the archive destination

Select name,value from v$parameter where name='db_recovery_file_dest'

Alter system set log_archive_dest='D:\ Oracle\ backup\ archive'

Alter system set log_archive_duplex='D:\ Oracle\ backup\ archive'

/ / Boot the database to the loaded state

Startup mount

/ / change the database to archive mode

Alter database archivelog

/ / Open the database

Alter database open

(5) make a hot backup of the BOOKSALES database.

Select tablespace_name,file_name from dba_data_files order by tablespace_name

Alter tablespace users begin backup

The Tablespace USERS has changed.

/ / copy all data files in the tablespace to the backup disk

Alter tablespace users end backup

The Tablespace USERS has changed.

(6) create a table called hot in the database and insert data to change the state of the database.

Create table hot (id number primary key, name varchar2 (25)) tablespace users

Table HOT has been created.

Insert into hot values (33, 'xushicheng')

1 row has been inserted.

(7) assuming that the data file saving the hot table is damaged, use hot backup to restore the database.

Shutdown abort

Startup mount

Recover datafile'D:\ Oracle\ app\ administrators\ oradata\ BOOKSALES\ users002.dbf'

Alter database datafile'D:\ Oracle\ app\ administrators\ oradata\ BOOKSALES\ users002.dbf' online

Alter database open

(8) after the database is restored, verify the status of the hottable and its data.

Select * from hot

(9) using the hot backup of the database to carry out time-based, SCN-based and CANCEL-based incomplete recovery respectively.

Shutdown abort

Startup mount

Recover database until time '2019-6-1'

Set time on

Alter system switch logfile

The System SWITCH has changed.

Alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS'

The Session has changed.

Select recid,stamp,sequence#,first_change#,first_time,next_change# from v$log_history

Shutdown abort

Startup mount

Recover database until change 1344396

(10) in order to back up and restore the BOOKSALES database using the RMAN tool, configure the automatic channel allocation of RMAN.

RMAN target sys/admin@BOOKSALES

RMAN > configure default device type to sbt

Replace the recovery directory with the target database control file

New RMAN configuration parameters:

CONFIGURE DEFAULT DEVICE TYPE TO 'SBT_TAPE'

New RMAN configuration parameters have been successfully stored

RMAN > configure device type disk parallelism 2

New RMAN configuration parameters:

CONFIGURE DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TO BACKUPSET

New RMAN configuration parameters have been successfully stored

RMAN > configure device type disk parallelism 3

Old RMAN configuration parameters:

CONFIGURE DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TO BACKUPSET

New RMAN configuration parameters:

CONFIGURE DEVICE TYPE DISK PARALLELISM 3 BACKUP TYPE TO BACKUPSET

New RMAN configuration parameters have been successfully stored

RMAN > configure channel 3 device type disk maxpiecesize=50m

New RMAN configuration parameters:

CONFIGURE CHANNEL 3 DEVICE TYPE DISK MAXPIECESIZE 50 M

New RMAN configuration parameters have been successfully stored

(11) use RMAN tool to back up BOOKSALES database completely.

Shutdown immediate

Alter system set log_archive_dest_1='location=D:\ Oracle\ backup\ ARCHIVE MANDATORY'

The System SET has changed.

Startup mount

Alter database archivelog

RMAN > backup database format'd:\ Oracle\ backup\% U.BKP'

Start backup in 03-June-19

Replace the recovery directory with the target database control file

RMAN-00571: =

RMAN-00569: = ERROR MESSAGE STACK FOLLOWS =

RMAN-00571: =

RMAN-03002: the backup command (on 00:21:49 on 06 / 03 / 2009) failed

ORA-19554: error configuring device, device type: SBT_TAPE, device name:

ORA-27211: failed to load media management library

(12) use RMAN tool to back up the initialization parameter file and control file of BOOKSALES database.

RMAN > backup spfile format'd:\ Oracle\ app\ administrators\ oradata\ BOOKSALES\% U'

Start backup in 03-June-19

RMAN-00571: =

RMAN-00569: = ERROR MESSAGE STACK FOLLOWS =

RMAN-00571: =

RMAN-03002: the backup command (on 00:23:19 on 06 / 03 / 2009) failed

ORA-19554: error configuring device, device type: SBT_TAPE, device name:

ORA-27211: failed to load media management library

RMAN > backup current controlfile format'D:\ Oracle\ app\ administrators\ oradata\ BOOKSALES\% U.CTL'

Start backup in 03-June-19

RMAN-00571: =

RMAN-00569: = ERROR MESSAGE STACK FOLLOWS =

RMAN-00571: =

RMAN-03002: the backup command (on 00:24:58 on 06 / 03 / 2009) failed

ORA-19554: error configuring device, device type: SBT_TAPE, device name:

ORA-27211: failed to load media management library

(13) use RMAN tool to back up USERS table space and BOOKTBS1 table space.

RMAN > backup tablespace users,booktbs1 format'D:\ Oracle\ app\ administrators\ oradata\ BOOKSALES\% U.BKP'

Start backup in 03-June-19

RMAN-00571: =

RMAN-00569: = ERROR MESSAGE STACK FOLLOWS =

RMAN-00571: =

RMAN-03002: the backup command (on 00:26:39 on 06 / 03 / 2009) failed

ORA-19554: error configuring device, device type: SBT_TAPE, device name:

ORA-27211: failed to load media management library

(14) use RMAN tool to back up the data files users01.dbf and users02.dbf of BOOKSALES database.

RMAN > backup datafile'D:\ Oracle\ app\ administrators\ oradata\ BOOKSALES\ users01.dbf' format'D:\ Oracle\ app\ administrators\ oradata\ BOOKSALES\% u'

Start backup in 03-June-19

RMAN-00571: =

RMAN-00569: = ERROR MESSAGE STACK FOLLOWS =

RMAN-00571: =

RMAN-03002: the backup command (on 00:29:14 on 06 / 03 / 2009) failed

ORA-19554: error configuring device, device type: SBT_TAPE, device name:

ORA-27211: failed to load media management library

RMAN > backup datafile'D:\ Oracle\ app\ administrators\ oradata\ BOOKSALES\ users02.dbf' format'D:\ Oracle\ app\ administrators\ oradata\ BOOKSALES\% u'

Start backup in 03-June-19

RMAN-00571: =

RMAN-00569: = ERROR MESSAGE STACK FOLLOWS =

RMAN-00571: =

RMAN-03002: the backup command (on 00:30:49 on 06 / 03 / 2009) failed

ORA-19554: error configuring device, device type: SBT_TAPE, device name:

ORA-27211: failed to load media management library

(15) use RMAN tool to back up the control files of BOOKSALES database.

RMAN > backup current controlfile format'D:\ Oracle\ app\ administrators\ oradata\ BOOKSALES\% U.ctl'

Start backup in 03-June-19

RMAN-00571: =

RMAN-00569: = ERROR MESSAGE STACK FOLLOWS =

RMAN-00571: =

RMAN-03002: the backup command (on 00:31:50 on 06 / 03 / 2009) failed

ORA-19554: error configuring device, device type: SBT_TAPE, device name:

ORA-27211: failed to load media management library

(16) use RMAN tool to back up the archive files of BOOKSALES database.

RMAN > backup archivelog all

Start backup in 03-June-19

The current log is archived

RMAN-00571: =

RMAN-00569: = ERROR MESSAGE STACK FOLLOWS =

RMAN-00571: =

RMAN-03002: the backup command (on 00:32:37 on 06 / 03 / 2009) failed

ORA-19554: error configuring device, device type: SBT_TAPE, device name:

ORA-27211: failed to load media management library

RMAN > backup database plus archivelog format'D:\ Oracle\ app\ administrators\ oradata\ BOOKSALES\% u'

Start backup in 03-June-19

The current log is archived

RMAN-00571: =

RMAN-00569: = ERROR MESSAGE STACK FOLLOWS =

RMAN-00571: =

RMAN-03002: the backup plus archivelog command (on 00:33:14 on 06 / 03 / 2009) failed

ORA-19554: error configuring device, device type: SBT_TAPE, device name:

ORA-27211: failed to load media management library

(17) use RMAN tool to back up the backup set formed by BOOKSALES database and restore the database.

RMAN > shutdown immediate

The database is closed

Database has been uninstalled

Oracle instance is closed

RMAN > startup mount

Connected to the target database (not started)

Oracle instance has been started

The database is mounted

The total system global area is 3373858816 bytes

Fixed Size 2180424 byt

Variable Size 1845496504 byt

Database Buffers 1509949440 byt

Redo Buffers 16232448 byt

RMAN > restore database

Start restore in 03-June-19

Assigned channel: ORA_DISK_1

Channel ORA_DISK_1: SID=191 device type = DISK

Assigned channel: ORA_DISK_2

Channel ORA_DISK_2: SID=129 device type = DISK

Assigned channel: ORA_DISK_3

Channel ORA_DISK_3: SID=192 device type = DISK

RMAN-00571: =

RMAN-00569: = ERROR MESSAGE STACK FOLLOWS =

RMAN-00571: =

RMAN-03002: the restore command (on 00:34:44 on 06 / 03 / 2009) failed

RMAN-06026: some targets have not been found-abort restore

RMAN-06023: no copy restore of data file 4 was found

RMAN-06023: no copy restore of data file 3 was found

RMAN-06023: no copy restore of data file 2 was found

RMAN-06023: no copy restore of data file 1 was found

RMAN > recover database

Start recover in 03-June-19

Use channel ORA_DISK_1

Use channel ORA_DISK_2

Use channel ORA_DISK_3

RMAN-00571: =

RMAN-00569: = ERROR MESSAGE STACK FOLLOWS =

RMAN-00571: =

RMAN-03002: the recover command (on 00:35:37 on 06 / 03 / 2009) failed

RMAN-06094: data file 4 must be re-stored

RMAN > alter database open

The database is open

RMAN > alter database open resetlogs

RMAN-00571: =

RMAN-00569: = ERROR MESSAGE STACK FOLLOWS =

RMAN-00571: =

RMAN-03002: the alter db command (on 00:36:19 on 06 / 03 / 2009) failed

ORA-01531: the instance has opened the database

(18) use EXPDP tool to export the whole database of BOOKSALES database.

Create or replace directory dumpdir as'D:\ Oracle\ backup'

Directory DUMPDIR has been created.

Grant read,write on directory dumpdir to system

Grant was successful. Wuxi × × Hospital http://mobile.zzchnk.com/

Grant exp_full_database, imp_full_database to system

Grant was successful.

Expdp system/admin directory=dum_dir dumpfile=expfull.dmp full=yes nologfile=yes

(19) using EXPDP tool to export the USERS tablespace of EXPDP database.

C:\ Users

Export: Release 11.2.0.1.0-Production on Monday June 3 00:49:22 2019

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

Connect 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

ORA-39001: invalid parameter value

ORA-39000: dump file description error

ORA-39087: invalid directory name DUM_DIR

(20) use EXPDP tool to export publisher table and books table of BOOKSALES database.

C:\ Users\

Export: Release 11.2.0.1.0-Production on Monday June 3 00:50:14 2019

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

Connect 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

ORA-39002: invalid operation

ORA-39070: unable to open log file.

ORA-39087: invalid directory name DUMP_DIR

(21) use EXPDP tool to export all database objects and data in bs mode in BOOKSALES database.

C:\ Users\

Export: Release 11.2.0.1.0-Production on Monday June 3 00:51:05 2019

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

Connect 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

ORA-39006: internal error

ORA-39068: the master table data is invalid in the row of PROCESS_ORDER=-3

ORA-01658: unable to create an INITIAL zone for segments in tablespace USERS

ORA-39097: unexpected error occurred in data pump job-1658

(22) Delete the orderitem table and order table in the BOOKSALES database, use the dump file, and use the IMPDP tool to restore.

Drop table orderitem

Drop table order

C:\ Users\

Import: Release 11.2.0.1.0-Production on Monday June 3 00:54:29 2019

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

UDI-28000: operation generated ORACLE error 28000

ORA-28000: the account is locked

User name: sys as sysdba

Password:

Connect 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

ORA-39001: invalid parameter value

ORA-39000: dump file description error

ORA-39087: invalid directory name DPUMP_DIR

(23) Delete the USERS tablespace in the BOOKSALES database, use the dump file, and use the IMPDP tool to restore.

Drop users

C:\ Users\

Import: Release 11.2.0.1.0-Production on Monday June 3 00:56:42 2019

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

UDI-28000: operation generated ORACLE error 28000

ORA-28000: the account is locked

User name: sys as sysdba

Password:

Connect 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

ORA-39001: invalid parameter value

ORA-39000: dump file description error

ORA-39087: invalid directory name DPUMP_DIR

Many of the above operations have actually failed, the specific reason I do not know, and has not been resolved, but the relevant commands should be correct.

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.