OCP factory training notes (day 6)

 
Instructor name: Liu tie
@ Oracle.com
MSN:
@ Hotmail.com

About the OCP examination,
SQL 70 questions: dbai 88 questions (69%) passed
OCP: Pass OCA certification, test workshop2, need to register: Pass the registration number of the factory training
Dba2: 64 questions (61%) 1 hour and a half
OCM: participate in at least two factory training courses, advanced (get OCP 10g certification)
Beijing/Shanghai/Guangzhou
DBA 1/DBA 2 Performance tunning, SQL tunning, gridcontrol, backup and recover, RAC, dataguard, stream
Security (VPD, FGA) dataware House: refresh Materialized View

DataWarehouse: refresh the materialized view (two days, 17 questions, all operation questions). The final score is determined based on the information provided by the dictionary view.
There are not many, and there are only 200 OCM (130 in China) in the world to grasp, understand, and operate.

Learning direction
Digest OCP: two years of work experience required

1. Official Website:
Otn.oracle.com
Document PLSQL, Tab reference
2. Software Download
Metalink.oracle.com
3. asktom.oracle.com
PLSQL: From getting started to proficient (SQL regular expressions)
4.itpub.net Forum (Oracle investigator website in China)
5.eagle.com
6. Oracle 8i backup and restoration manual
The backup principle has not changed from 8 to 10 to 11.

Backup and recovery manual
1. RMAN (manual backup, command line mode)
ERP: Manage Resoure controls resources
Job Schedule: scheduled tasks

Exercises and progress
Create: Schema: a collection of objects (tables, indexes) under a user)
Account: User Role Permissions

New features starting with Oracle 10 GB: ASM
Portalbal cluster: RS: Cluster ready Service
Resource Manager: Controls resource allocation (it is used to manage resource allocation of user groups)
Oracle stream: the Advanced Replication and materialized views of a stream are different.
Database Control: one-to-one, independent installation port number (an independent port number must be used for access. If a server
There are two databases, there are two independent port numbers)
Grid contol: (one port, 4889, managing all dB information, installing grid control requires a separate disc)
Oracle 10 Gb self-management: Oracle tunning advisor, undo Advisor
Memory: The physical zone loaded on the physical memory. One part is called SGA and the other part is called the original process.

Key database files: Database Control Files, data files, and redo
Other key datafiles: backup file, TRC file, spfile

Shared Pool: parses SQL statements and stores SQL statements.
Database Buffer: data block buffer
Java pool: required by JVM. External stored procedure is called.
Large pool: Backup Recovery
Stream poll: Advanced Replication (available in Oracle 10)
Pga_aggregate: the PGA of each session is a part of the total PGA,

In dedicated scenarios, user_process and server_process are one-to-one,
The PGA size is about 1 MB: The saved information includes the user name, login, cursor information, and PGA
Set PGA: It can be large (larger than the memory, because PGA initialization only requires 90 MB space)

 

 

For example, it is an SQL statement parsing and analysis process.

1> the user establishes a connection to the server, connect, and connect to the listener. At this time, the listener will fork a process server process,
This server process is not equal to the background process. (Shadow process, as said by instructor Yao)
Update EMP set sal = 1000 where ....
2> sever process accepts users' SQL queries and puts them into the library cache,
First, check the library cache for its syntax errors, and then put it into the dictionary cache to check whether the permission is correct. if the user has this permission
Put the statement into the library cache for further parsing (PARSE). At this time, Oracle will compare and select the optimal one. An execution plan is generated,
(Explain Plan), and finally transfer the database execution plan to the database buffer.
3> Search for data in the buffer cache and find two columns. One is LRU and the other is Checkpoint queue.
If no such data exists, read the data from the disk and buffer it to LRU. (Old imgage is placed on this LRU)
4> copy an image
Copy the image from LRU to the current image (the previous image will become old IMG) and write it to undo instead (old IMG will write it to undo)
5> modify current image and block content + SCN
6> record to Checkpoint queue, (current image ?), All blocks not written to the data file are put here (current IMG records all relevant information)
7> dbwr writes back the physical block, and redo log buffer writes the redo log file. (when dbwr writes back the physical block, the data in the related checkpoint queue is cleared)

An instance has only one lgwr process.

All the operations performed will be recorded in the checkpoint queue, and all the data read by the SELECT statement will be placed on the LRU. (Confirmed by the teacher)

What role does PGA play? The user_name and cursor information will be cached. The cursor here mainly refers to the cursor information cached in the user session, which is inconsistent with the PGA information cached in SQL.
Ckpt: Process
After checking the checkpoint, the checkpoint queue will be checked, and all dirty data will be written back to the data file.

 

Modifying the control file header also modifies the file header.

Large pool (RMAN) allocates memory areas and stores read data (if this is not set, RMAN data will be exported to the share memory)
Stream pool (Stream Replication Use: capture, propagate, apply)

Pmon: the lock that rolls back data quickly
SMON: When the instance is down, Oracle will make a complete comparison process. SMON will compare the last successful complete check point (if the redo SCN is greater than the SCN of the control block,
Rollback is required.

During dump, session-level dump will use the alert file,

Use OMF to create a file. By default, MB of files will be created.

OS block: 512 bytes
Oracle datablock (2 k, 4 K, 8 K, 16 K, 32 K)

Extent and segment
Extent: a set of continuous blocks. It cannot span tablespaces or datafiles.
Segment: several tables, partition, and segments can span tablespaces and different files

For the member created for the log group, the two member sizes in a group must be the same.

Iot: overflow segment. index groups can span tablespaces.

For big tablespace:
Alter datafile = alter tablespace

(Administrator and concept)

When using RMAN for backup, use the control file to compare with the use of catalog:
Different control files and catalog store backup information:
1> the control file information is automatically cleared, and catalog does not
2> scripts can be stored in the catalog Library (scripts)

RMAN. Only sysdba-authorized users can perform

Catalog creation process
Create user RMAN identified by RMAN default tablespace tbs1;
Grant resource to RMAN;
Grant connect to RMAN;
Grant recovery_catalog_owner to RMAN;
RMAN target/catalog rman/RMAN
RMAN> Create catalog
RMAN> Register database (register synchronous database)

Em MANAGER: view the current percentage (P17)

View backup logs that are no longer in use:
Report obsolete
Delete backup logs that are no longer in use
Delete Obsolete

Back up the flash back File
RMAN> Backup recovery files;
Select to back up the backup file to the tape drive.

Automatic Management and automatic clearing
Control_file_record_keep_time: the time when the control information is stored in the control file (seven days by default)

Db_recovery_file_dest: Location of the Archive

Nls_date_format: Date Format
Nls_lang: Language/region, Character Set
Garbled characters are generated when environment variables and database languages do not match.

Join RMAN: Auxiliary, recovery catalog, target database
The secondary database is very useful for dataguard:

Sqlplus/As sysdba
'/' Indicates logging in with an operating system user

RMAN target sys/Oracle log = $ home/oradata/U03/RMAN. Log append
RMAN target sys/Oracle partition file = $ home/scripts/scr. RCV

Backup optimization:
Compared with the previous data block, if it is the same as before, it will not be backed up.
RMAN/automatic backup control file

Show all: Backup rules are stored in the control file. If these default rules are modified, they are stored in the dictionary view.

Configure backupset: Binary Compression
Configure copy: copy from one file to another

Parallelism: Degree of Parallelism
/U01/APP/Oracle/% u _ % P _ % C
'% U _ % P _ % C' =' % U'
% U 8-bit random number
% P Piece number
% C: Number of copies

The backup result set contains multiple data files. The data is compressed to the original 50% when the empty blocks are exceeded.

Select IMG: copy the image (change the database to normal by switching the log to copy the image)
Image copy cannot be used for copying to a tape

What is a backup set and what is a backup slice?
Backup slices can form a backup set (one backup set can be divided into multiple backup piece)

Ensure that the number of copies cannot exceed 128 MB,
(In the old version, the database file backup cannot exceed 4 GB, otherwise it will not be recognized, so we need to back up the slices to form a large backup set)
Degree of Parallelism
Drive C, % C = 1
Disk D, % C = 2

The control file data file can be placed in a backup set.
Archive cannot be stored in a backup set with other backup types.

If you select the optimize parameter, the next backup will match the previous backup. If it remains unchanged, no backup will be performed. (This is not recommended in the production database because the data in the production database is always changing)

Recovery window: As a clearing standard, it determines whether the backup result set is obsolete,
Redundancy: if it is set to 2, the first backup set is 1 and the last one is 2. Only the last two backup sets are retained.

RMAN> show exclude (which files are excluded from full-Database Backup)

Channels can be automatically allocated.
Automatic channel allocation: Configure Default device type to SBT;
RMAN> run {
Allocate channel C1 device type disk; (manually allocate channels)
Backup datafile
}

Execute parallel Backup Using Three Parallel Channels
Parallism = 3

Delete input (after the archive file is backed up, the archived Log File backed up is deleted from the system)

RMAN> report scheme; data files that can be backed up in the current database
RMAN> Backup spfile;
RMAN> Backup archivelog all;

Run {
Backup as backupset
Format '/u01/db01/backup/% d _ % S _ % P'
Duration 10: 00 minimize Load
(Database );
SQL 'alter system archivelog current ';
}

The above statement 'duration minimize load'
This means that the backup work is completed evenly within 10 hours, reducing excessive Io consumption on the production system.

Crosscheck: checks which files exist and those files do not exist,
When this operation is performed, the archive files in the records of the control files are compared with the actual archive files,
If there is no archived log in the actual location, mark the non-existing archived log as expired. (test) (yes)

List expired archivelog all;
You can view all invalid archive logs.

Crosscheck archivelog all;

 

Obsolete: A backup set that exceeds the retention policy
Expired: It is good to control the file record, but the file has been physically deleted (delete expired)

(For more information, see Oracle 9i fundamental1)

In noarchive mode, you must back up the database files in the Mount state or offline mode (but immediate offline does not work ),
Select the offline normal mode. (The test proves yes)
Flash back, flash back (test)
Backup as compressed backupset database; this method is suitable for disk backup. The original backup size is 50% of the former size)

Backup as copy datafile '/oradata/users_01_db01.dbf'
Format '/backup/users01.dbf ';
The backup SCN value is not equal to the value of the locked data file.

Report list: the existing (stored scipts) will be taken out. This is only suitable for archiving databases.

Backup as copy
Datafile '/oradata/users_01_db01.dbf'
Format '/backup/users01.dbf ';
The SCN value of the locked backup is not equal to the value of the locked data file.

When Oracle recovers, tags can be specified as result sets.

During RMAN backup, the system automatically checks the bad parts of the backed up file and reports which file is broken. RMAN can even check for logical corruption.
For example, the index should be unique, but if two unique indexes exist in one table, RMAN can also check.

Validate: Verify the backup and verify the input file.

Back up archive files (online logs are automatically switched before archiving) (test)
In this case, what does this command mean after backup? SQL 'alter system archivelog current ';?
(A: This is only a continuation of the 9i RMAN script and has no practical significance)

Failover can appear during Backup archiving.

 

 

 

For example, if the source database cannot find logs with a sequence of 50,
RMAN will automatically go to the standby database.
(Can this be done in stream replication? The instructor is not sure)

After the backup is complete, the archived logs backed up are automatically deleted.

RMAN> run {
Backup
Format '/disk1/backup/arc _ % T _ % S _ % P'
Archivelog from sequence = 234
Delete input;
} (The test) successfully deleted the latest one and deleted the relevant content in the file system.

RMAN backup
For T-level backup, Incremental Backup can be performed every day after full-database backup, reducing the time required for backup
Two types: cumulative and a different level

 

Cumulative)
Search for n-1 or smaller

 

 

Difference
Search for N or less

The default value is different. If you need to back up incremental data, you need to add a cumulative parameter.
RMAN> Backup incremental level 0 database;
RMAN> Backup incremental Level 1 database;
RMAN> Backup incremental Level 1 Cumulative database;
Cumulative backup and differential backup can coexist in the backup set of a database.

Block change backup:
Normally, if there is 1 TB of data in the backup, if only 20 GB is changed, Oracle also needs to scan 1 TB of Block During the RMAN backup, to avoid this waste of resources, we use the block change file. Block change tracking is used as follows:

 

 

 

Alter database enable block change tracking
Using File '/mydir/rman_change_track.f'
Reuse; (test)
You can view the V $ block_change_tracking view.

SQL> alter database enable block change tracking
Using File '/home/Oracle/rman_change_track.f'
Reuse;
SQL> alter database enable block change tracking;
SQL> select * from V $ block_change_tracking;

Incremental copy,
Incremental updating backups
Incremental copy.

Steps for restoring a database using incremental copy
1> image copy (original)
2> incremental image copy (incremental, there is always only one)
3> recovery (from original and incremental recovery)
Recover copy of datafile {n | 'n '}
With tag 'initpd _ df1 ';

How to view the SCN of a block: Dump

File SCN: View v $ datafile

| |
Expand this question: view the itpub discussion post:
For a DBA, it should be clear whether the system has to restore the media.
The above judgment indicates whether the restoration of media or instance is based on the concept of SCN number.

If the system SCN is the same as the data file SCN and the start SCN of the data file, no media recovery is required.
If there is a difference, the media needs to be restored.

The start scn and end SCN of a normally closed database are the same
If the two items are different, the instance needs to be restored.

In addition, LZ reports the following error:
ORA-01194: File 1 needs more recovery to be consistent
It is obvious that more logs are needed for instance recovery.

Check the Oracle File status:
Select
A. Name,
A. checkpoint_change #,
B. checkpoint_change #,
Case
When (A. checkpoint_change #-B. checkpoint_change #) = 0) Then 'startup normal'
When (A. checkpoint_change #-B. checkpoint_change #)> 0) Then 'Media recovery'
When (A. checkpoint_change #-B. checkpoint_change #) <0) Then 'old control file'
Else 'What? '
End status
From v $ datafile A, -- control file SCN for datafile
V $ datafile_header B -- datafile header SCN
Where a. File # = B. File #;

Tip

The V $ datafile_header view uses the physical datafile on disk as its source. The V $ datafile View
Uses the control file as its source.

| |

BS key: Backup set
You can delete file content.
Delete backup; Set 156;

File BP key (Backup status)
Status :( available) whether the restoration is valid
Piece handle: name of the backup part

RMAN> report need backup (check which files need to be backed up)
For example
RMAN> report need backup incremental 3;
RMAN> report need backup days 3;
Delete backup set: Delete backup information + Delete backup files
RMAN> report obsolete redundancy 2;

Monitor the RMAN status,
SET command ID
V $ session_longops: Number of reads and number of reads (test)
Total
Monitoring RMAN backups
To correlate a process with a channel during a backup, perform the following steps:
1. Start recovery manager and connect to the target database and, optionally, the recovery
Catalog.
2. Set the command ID parameter after allocating the channels and then copy the desired
Object. The string specified by the set command id command is entered into
V $ session. client_info column of all allocated channels.
Run {
Allocate channel T1 type disk;
SET command ID to 'rman ';
Backup datafile 1;
Release Channel T1 ;}
3. query the V $ process and V $ session Views to get the session identifier (SID) and
Operating system process identifier (spid) for the channels using the previusly specified
Command ID string.
Select Sid, spid, client_info
From v $ PROCESS p, V $ session s
Where p. ADDR = S. paddr
And client_info like '% id = RMAN % ';
4. query the V $ session_longops view to get the status of the copy.
Select Sid, serial #, context, SOFAR, totalwork,
Round (SOFAR/totalwork * 100,2) "% complete"
From v $ session_longops
Where opname like 'rman: %'
And opname not like 'rman: Aggregate %'
And totalwork! = 0;
5. If you use a channel of Type SBT and the copy process appears to hang, query
V $ session_wait by using the SID obtained in step 3 to determine whether RMAN is
Waiting for a media manager function call to complete.
Select * from V $ session_wait where event like '% SBT % ';

Do not back up a tablespace:
RMAN> Configure exclude for tablespace users;

If the RMAN backup policy of the data is modified, it will be recorded in the dictionary,
Start RMAN next time. View v $ rman_configuration will be modified.
Control file. (Overwrite the default settings in the control file)
Select * from V $ rman_configuration; (test)
Conf # NAME value
1 controlfile autobackup on
2 retention policy to redundancy 2
Flash back database operations (test)