OCP factory training notes (Day 5)

Whole database = full database + control file + archive log file
Instance recovery (system power loss)
Media recovery (delete file, damaged media, DBA intervention required)

Database crash:
All the Members in the redo group are corrupted and crash is triggered only when the process is accessed.
The control file is lost, and the data file is also lost.
Undo or system tablespace loss
Keeping a database open
After the instance is open, it fails in the case of
Loss:
• Any control file
• A data file belonging to the system or undo
Tablespaces
• An entire redo log Group. As long as at least one
Member of the group is available, the instance
Remains open

Files can be forcibly deleted in Unix. Windows files cannot be forcibly deleted because of locks.
After the file is deleted, the system will report an error after a while.
In this case, shutdown abort is used to close the database,
If shutdown immediate is used, the database will hang there and report an error (dbwr write-back failure)

If a member in a log file group is lost and the lost log group has been archived,
You can recreate the log group and recreate the lost files.
Clear, which is equivalent to clearing the log file and recreating it.
When the log file of the connected machine is deleted, alert. log will report an error alert.
Loss of a redo log file
If a member of a redo log file group is lost, as long
The Group still has at least one member, then:
• The normal operation of the instance is not
Affected.
• You receive a message in the Alert Log policying
You that a member cannot be found.
• You can restore the missing log file by dropping
The lost redo log member and adding a new
Member.
• If the group with the missing log file has been
Archived, you can clear the log group to re-create
The missing file.

Q: Why alter system switch logfile after killing the log file,
The system still does not report an error.

The instructor replied: Oracle has a mechanism called lazy write, which is suspected to be the cause of database problems.

The control file can be copied only when the file is not in the Mount status.

Online Log File status:
Current: indicates that lgwr is writing this file.
Active: indicates that the dirty data corresponding to the log is not written back to the data file. This log is used for the next instance recovery.

Database status: nomount, mount, open;
Corresponding instance status: started, mount, open (select status from V $ instance)

Flash back is used to solve logic errors (user errors)
Flash Back Type
Flash back database, flash back table,

 

 

Flash back Library:
You can open the database in read-only mode. (read only) to check whether the data has been restored to the desired status.
In the Mount status, we turn on the flash back function, restore to the desired state, and finally restore the database in the alter database open resetlogs mode.
Flash back is the opposite direction of recover.

The following situations are not supported by flashback.
Control File recovered or rebuilt
The tablespace has been deleted.
The data file has been reduced.
You cannot use flashback database in the following
Situations:
• The control file has been restored or re-created.
• A tablespace has been dropped.
• A data file has been shrunk.

The in-place operation does not affect the database size,
When a table is flashed back, the row movement must be enabled (the database Default table does not open the row movement) and returns to a time.
Logminer can mine logs, archive or connect logs to view the time

When a table is flashed back, the database adds a DML lock to the table.
Flash Back table
Cannot span DDL statements (fore example)
You cannot modify the system table.
Will generate undo and redo.

Exercise:
Alter session set nls_date_format = 'yyyy-mm-dd hh24: MI: ss ';
Flash table employees to_timestamp ('1970 _ 08_21 11:33:37 ', 'yy-mm-dd hh24: MI: ss ');
Alter table employees enable row movement;
Updated HR. Employees set salary = 2000;
Flashback table HR. Employees to timestamp (systimestamp-3/1440); returns to three minutes ago.

View All indexes of a user and recreate these indexes.
Select index_name from user_indexes;
Alter index emp_emp_id_pk rebuild;

The flash back table uses the recycle bin. The following situations cannot flash back.
1. The flash job cannot be in the system tablespace.
2. dictionary-type tablespace
3. The Purge table cannot be returned.

Exercise:
SQL> conn HR/hr
SQL> drop table Dept
SQL> drop table dept constraints cascade;
SQL> show recyclebin
SQL> flashback table dept before drop;
When the table is flashed back, the related foreign keys are not obtained.

Sys cannot view the table deleted under the HR account.
SQL> Conn/As sysdba
SQL> show recyclebin
Invisible.

Flashback query: flashback Query
Returns the content queried by all versions.

Example:
Select employee_id, salary from employees as of Timestamp T1;

Query the version of the flashback row:

Transaction: 1, 2, 3
Search for the thing number 2,

Flashback versions: aims to find the transaction ID, flash back transaction query, flashback-transaction-querey

Flashback VERSION Query cannot be performed across DDL languages, external tables, DDL languages, and segment shrink ),
Query version-> Find the transaction ID-> Find Undo-> Find statement-> redo

 

T1-T2-T3, 11g
Analyze the impact of Undo transactions. automatically because T1 is deleted, how does T2, T3 need to be changed so that the entire transaction is not affected.

Mobile Data:
Sqlload: external data homes come internally
DataPump: External tables transmit data through DataPump

Expdp is automatically selected
External table API or direct path API
External table or direct path API.

Previous import and export can be placed on the client (exp, IMP)
The new backup method is host-based rman, and expdp backups are stored on the database server (not on the client ).

 

Create directory object extab) log_dir
SQL _load
DataPump must have directory objects

Define how to load data files to segments and rows by controlling the file, and convert failed data files into bad files.
Discarded: Filter statements that do not meet the conditions.
Data files, control files, and Database Control files are not a concept. They are all in the ASCII format.
Example of Control File

SQL * loader control file (continued)
1 -- this is a sample control file
2 load data
3 infile 'sample. dat'
4 badfile 'sample. bad'
5 discardfile 'sample. DSC'
6 append
7 into Table EMP
8 When (57) = '.'
9 trailing nullcols
10 (hiredate sysdate,
Deptno position () integer external (3)
Nullif deptno = blanks,
Job position (7: 14) Char terminated by whitespace
Nullif job = blanks "upper (: Job )",
Mgr position (28:31) integer external
Terminated by whitespace, nullif Mgr = blanks,
Ename position (34: 41) Char
Terminated by whitespace "upper (: ename )",
Empno position (45) integer external
Terminated by whitespace,
Sal position (51) Char terminated by whitespace
"To_number (: Sal, '$99,999.99 ')",
Comm integer external enclosed by '(' and '%'
": Comm * 100"
)

The default control file is insert (the table must be empty)
So change to append (additional method)

The direct path method is faster than the conventional load method and has poor functions.

 

 

 

 

Sqlldr inventory/verysecure control = lab_18_05.ctl

DataPump:
DataPump calls the following package
Expdp impdp web-based interface

DataPump has powerful functions, such as traditional Import and Export
Expdp must specify the directory object. This parameter must be used for both the backup source and destination.

Remap: remap_datafile = .....
Transmission property: object, advanced code

Expdp HR/hr full = y
Dumpfile = dp_dir1: full1 % u. DMP, dp_dir2: full2 % u. dmp
Filesize = 2G parallel = 3
Logfile = dp_dir1: expfull. Log job_name = expfull

Filesize: the maximum size of the specified file is 2 GB.
Several files are generated by default.

External table:
Oracle has two types of external tables
Load in and use sqlloader to temporarily load data.
There are no real rows in the External table, and the rows are placed outside.
If you use the following command, select * from...; the background will call sqlldr to load the table.
Create Table as select * from external table;
You only want to get the statistical value and do not need all records. In this case, you can store the data in an external table.

 

External table objective: ETL (partitioning, conversion, and loading)

9i, sqlloader loading, does not exist unload
10 Gb, can be detached from the source, but 9i sqlload does not have this function
The External table only has the table name.

Exercise:
Create directory D1 as '/home/Oracle ';
Grant all on direcotry D1 to system;
Export the HR object to all users.
Data cannot be dumped as sysdba.
Must be imported and exported in normal mode.

 

In Windows, you must grant the batch login permission to sqlloader statements.