Backup
Backup Control File
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SYS> alter database backup controlfile to '/home/oracle/control.ctl';
Database altered.
SYS>
Backup Control File to Trace
When control file gone
use user trace script set #1 to recreate control file
When control and redo are both gone
use user trace script set #2
Using Data Pump Export and Import
Export Full Database
SYS> create directory DPXFER as '/home/oracle/oracleExport';
Directory created.
[oracle@localhost oracleExport]$ pwd
/home/oracle/oracleExport
[oracle@localhost oracleExport]$ expdp system/oracle directory=DPXFER DUMPFILE=hr.dmp FULL=y LOGFILE=expfull.log
Export Schema
Schema Export Mode The schema export mode is invoked using the SCHEMAS parameter. If you have no EXP_FULL_DATABASE role, you can only export your own schema. If you have EXP_FULL_DATABASE role, you can export several schemas in one go. Optionally, you can include the system privilege grants as well.
$ expdp hr/hr DIRECTORY=exp_dir DUMPFILE=schema_exp.dmp SCHEMAS=hr,sh,oe
[oracle@localhost oracleExport]$ expdp system/oracle DIRECTORY=dpxfer DUMPFILE=schema_exp.dmp schemas-hr,sh,oe LOGFILE=schema_exp.log
Table Export
This export mode is specified using the TABLES parameter. In this mode, only the specified tables, partitions and their dependents are exported. If you do not have the EXP_FULL_DATABASE role, you can export only tables in your own schema. You can only specify tables in the same schema.
$ expdp hr/hr DIRECTORY=exp_dir DUMPFILE=tables_exp.dmp TABLES=employees,jobs,departments
[oracle@localhost oracleExport]$ expdp system/oracle DIRECTORY=dpxfer DUMPFILE=tables_exp.dmp TABLES=hr.employees,hr.jobs,hr.departments LOGFILE=tables_exp.log
Export: Release 10.2.0.1.0 - Production on Tuesday, 07 April, 2009 23:33:32
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "SYSTEM"."SYS_EXPORT_TABLE_01": system/******** DIRECTORY=dpxfer DUMPFILE=tables_exp.dmp TABLES=hr.employees,hr.jobs,hr.departments LOGFILE=tables_exp.log
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 192 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/TRIGGER
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "HR"."DEPARTMENTS" 6.632 KB 27 rows
. . exported "HR"."EMPLOYEES" 15.76 KB 107 rows
. . exported "HR"."JOBS" 6.609 KB 19 rows
Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:
/home/oracle/oracleExport/tables_exp.dmp
Job "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at 23:33:40
Full Import Mode
The full import mode loads the entire contents of the source (export) dump file to the target database. However, you must have been granted the IMP_FULL_DATABASE role on the target database. The data pump import is invoked using the impdp command in the command line with the FULL parameter specified in the same command line.
$ impdp system/
Schema Import Mode
The schema import mode is invoked using the SCHEMAS parameter. Only the contents of the specified schemas are load into the target database. The source dump file can be a full, schema-mode, table, or tablespace mode export files. If you have a IMP_FULL_DATABASE role, you can specify a list of schemas to load into the target database.
$ impdp hr/hr DIRECTORY=exp_dir DUMPFILE=expfull.dmp SCHEMAS=hr,sh,oe
Table Import Mode
This export mode is specified using the TABLES parameter. In this mode, only the specified tables, partitions and their dependents are exported. If you do not have the EXP_FULL_DATABASE role, you can import only tables in your own schema.
$ impdp hr/hr DIRECTORY=exp_dir DUMPFILE=expfull.dmp TABLES=employees,jobs,departments
Recovery
Find All files that need recovery
SYS> select name, error from v$datafile JOIN v$recover_file USING (file#);
no rows selected
Loss of Control File
Shutdown instance by SHUTDOWN ABORT
Copy the remaining control files to the missing file's location. Or copy to another location and update the instance's parameter file to point to the new location. Or, you can delete the missing control file from the init parameter file
Start the instance
Loss of Redo Log File
Determine whether there is a missing log file by examining the alert log
Restore the missing file by copying one of the remaining files from the same group
If the media failure is due to loss of disk drive, rename the missing file
If the group has already been archived, or if you're in NOARCHIVELOG mode, you may choose to solve the problem by clearing the log group to re-create the missing files. Select the appropriate group and select the Clear Logfile action. You can also clear the affected group manually with the following command:
SYS> alter database clear logfile group 1;
Database altered
Loss of Data File in NOARCHIVELOG Mode
The loss of any data file in NOARCHIVELOG mode requires complete restoration of the database, including control files and all data files
Shut down the instance if it is not already down
Click Perform Recovery on the Maintenance properties page
Select Whole Database as the type of recovery
Loss of Noncritical Data File in ARCHIVELOG Mode
Click Perform Recovery on the Maintenance properties page
Select “Datafiles” as the recovery type, and select “Restore to current time”
Add all data files that need recovery
Determine whether you want to restore the files to the default location or to a new location
Submit the RMAN job to restore and recover the missing files
Loss of System-critical Data File in ARCHIVELOG Mode
Shut down the instance if it is not already down
Mount the database
Click Perform Recovery on the Maintenance properties page
Select “Datafiles” as the recovery type, and select “Restore to current time”
Add all data files that need recovery
Determine whether you want to restore the files to the default location or to a new location
Submit the RMAN job to restore and recover the missing files
Open the database. Users are not required to reenter data because the recovery is up to the time of the last commit
V$RECOVER_FILE
You can often use the table V$RECOVER_FILE to determine which files to recover. This table lists all files that need to be recovered, and explains why they need to be recovered.NoteThe table is not useful if the control file currently in use is a restored backup or a new control file created since the media failure occurred. A restored or re-created control file does not contain the information Oracle needs to fill V$RECOVER_FILE accurately.
The following query displays the file ID numbers of datafiles that require recovery:
SELECT file#, online, error
FROM v$recover_file;
No comments:
Post a Comment