What is written on the Oracle DBA work list?

Major work of the database administrator (DBA)

Start and close a database

1) Start the database
$ Svrmgrl
Svrmgr> connect internal (instance startup)
Svrmgr> startup

2) shut down the database
$ Svrmgrl
Svrmgr> connect internal
Svrmgr> shutdown [immediate/abort]
Immediate: the database is closed only after the session accessing the database is completely terminated and the resources are released in sequence.
Abort: the session is terminated immediately and the database is closed immediately.

Backup and recovery

1) logical backup and recovery (that is, unloading and loading databases)
1. Unload Database: Export
Without parameters:
$ CD $ ORACLE_HOME/bin
$ Exp

Username: cwadmin
Password:
Connected to: oracle8 Enterprise Edition Release 8.0.4.0.0-Production
PL/SQL release 8.0.4.0.0-Production
Enter array fetch buffer size: 4096>
Export File: expdat. DMP> pzexdat. dmp

(1) E (ntire database), (2) U (SERS), or (3) T (Ables): (2) U> 1
Export grants (yes/no): Yes> Y
Export table data (yes/no): Yes> Y
Compress extents (yes/no): Yes> Y
The export operation starts automatically and finally appears:
Export terminated successfully without warnings.

With parameters. The optional parameters are as follows (available in Exp help = y ):

Keyword description (default) keyword description (default)
Bytes ---------------------------------------------------------------------------------------------------
Userid username/password full export entire file (N)
BUFFER size of data buffer OWNER list of owner usernames
FILE output file (EXPDAT. DMP) TABLES list of table names
COMPRESS import into one extent (Y) RECORDLENGTH length of IO record
GRANTS export grants (Y) INCTYPE incremental export type
INDEXES export indexes (Y) RECORD track incr. export (Y)
ROWS export data rows (Y) PARFILE parameter filename
CONSTRAINTS export constraints (Y) CONSISTENT cross-table consistency
LOG log file of screen output STATISTICS analyze objects (ESTIMATE)
DIRECT direct path (N)
FEEDBACK display progress every x rows (0)
POINT_IN_TIME_RECOVER Tablespace Point-in-time Recovery (N)
RECOVERY_TABLESPACES List of tablespace names to recover
VOLSIZE number of bytes to write to each tape volume

Example: $ exp userid = cwadmin/cwadmin tables = '(sys_cwxx, sys_menu)' file = pzexport. dmp

2. installation:

Without Parameters
$ Cd $ ORACLE_HOME/bin
$ Imp

Username: cwadmin
Password:
Connected to: Oracle8 Enterprise Edition Release 8.0.4.0.0-Production
PL/SQL Release 8.0.4.0.0-Production
Import file: expdat. dmp> pzexdat. dmp
Enter insert buffer size (minimum is 4096) 30720> 10240

Export file created by EXPORT: V08.00.04 via conventional path
List contents of import file only (yes/no): no>

Ignore create error due to object existence (yes/no): no> y
Import grants (yes/no): yes> y
Import table data (yes/no): yes> y
Import entire export file (yes/no): no> y
The import Task starts automatically and finally appears:
Import terminated successfully with warnings.

With parameters. The optional parameters are as follows (can be obtained using imp help = y ):

Keyword Description (Default)
Bytes ---------------------------------------------------------------------------------------------------
USERID username/password FULL import entire file (N)
BUFFER size of data buffer FROMUSER list of owner usernames
FILE input file (EXPDAT. DMP) TOUSER list of usernames
SHOW just list file contents (N) TABLES list of table names
IGNORE ignore create errors (N) RECORDLENGTH length of IO record
GRANTS import grants (Y) INCTYPE incremental import type
INDEXES import indexes (Y) COMMIT commit array insert (N)
ROWS import data rows (Y) PARFILE parameter filename
LOG log file of screen output
DESTROY overwrite tablespace data file (N)
INDEXFILE write table/index info to specified file
CHARSET character set of export file (NLS_LANG)
POINT_IN_TIME_RECOVER Tablespace Point-in-time Recovery (N)
SKIP_UNUSABLE_INDEXES skip maintenance of unusable indexes (N)
Analyze execute analyze statements in dump file (y)
Feedback Display progress every X rows (0)
Volsize number of bytes in file on each volume of a file on tape

Example: $ imp userid = cwadmin/cwadmin tables = '(sys_dwxx, sys_menu)' file = pzexdat. dmp

2) physical backup and recovery
1. Cold backup and Hot Backup
Cold backup
When the database is closed and in progress. Copy all data files, repeat log files, and control files to the disk. After idle time, move the backup to the tape.
(1) You can use: svrmgr> select * from V $ logfile;
Select * from V $ dbfile;
Select * from V $ control. file;
These statements are used to understand the location and name of the data file, repeat log file, and control file.
(2) Use the $ CP command to copy:
Example: $ CP/u01/u02/pz_ts.ora/dbfile_ B/

Hot Backup
The database must work in the archivelog mode.
You can use the svrmgr> archive log list statement to query the archived log status.

If the conversion is performed in the noarchivelog mode
Svrmgr> connect internal
Svrmgr> shutdown immediate --- Close the database ---
Svrmgr> startup Mount --- prepare a database for the conversion of the paused log ---
Svrmgr> alter database archivelog; --- conversion ---
Svrmgr> alter database open; --- Open Database ---
 
Copy
1> set a tablespace as a backup mode
Svrmgr> alter tablespace pzts begin backup;
2> copy
Svrmgr> $ CP/u01/u02/pz_ts.ora/dbfile_ B/
3> Cancel backup of the table
Svrmgr> alter tablespace pzts end backup;
Use the preceding three steps to back up data files in all tablespaces.
4> copy Control Files
5> copy the archived repeat log

2. Restore
If the disk fails, the database is shut down.
(1) copy the backup that resides on the disk to another disk or tape.
(2) Execution
Svrmgr> connect internal
Svrmgr> startup Mount
Svrmgr> alter database open;
An error message is displayed, prompting you to restore it.
(3) Execution
Svrmgr> recover Database
Automatic recovery as prompted
(4) Execution
SVRMGR> alter database open
After the restoration is completed, the database is opened.