Oracle Physical System related query statements

1. View the SGA and PGA

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/44/EF/wKioL1PjM8qgspO8AACxtSBsIFY007.jpg "style=" float: none; "title=" 02.png "alt=" Wkiol1pjm8qgspo8aacxtsbsify007.jpg "/>

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/44/EF/wKioL1PjM8qDRwE_AAB80IJ6B5U411.jpg "style=" float: none; "title=" 03.png "alt=" Wkiol1pjm8qdrwe_aab80ij6b5u411.jpg "/>

The SGA and PGA size 0 are shown here because the Memory_target parameter is introduced in Oracle 11g, and the memory specified by the Memory_target parameter automatically allocates memory to the SGA and PGA;

View Memory_target;

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/44/EF/wKiom1PjNHDDelW0AADOW924Fa0187.jpg "title=" 04.png "alt=" Wkiom1pjnhddelw0aadow924fa0187.jpg "/>

sga_max_size=3200m indicates that when the database is running memory that is larger than Sga_target, the OS OS can allocate and redistribute memory to the SGA, but the size cannot exceed 3200M;

2. Viewing shared pools and data buffers

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/44/EF/wKiom1PjNEvgOsjhAADj9vA05YI466.jpg "title=" 05.png "alt=" Wkiom1pjnevgosjhaadj9va05yi466.jpg "/>

The shared pool and data buffer size is 0 because Oracle is set to automatically manage the SGA, and the size of the shared pool and data buffer is determined by sga_target;

3. View Log Buffers

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/44/EF/wKiom1PjNPKQNwOZAABd996KAQA175.jpg "title=" 06.png "alt=" Wkiom1pjnpkqnwozaabd996kaqa175.jpg "/>

Because Log_buffer every 1M to write once, the allocation is too large optimization effect is not obvious, the general 15M can meet the conditions;

Log_buffer must be manually assigned and adjusted. The Modify database Parameters command is as follows:

Alter system set <parameter_name>=<value> Scope=memory|spfile|both [Sid=<sid_name>]

Scope Parameter 3 selectable values

Memory: Only change the current instance run, restart the database after the failure;

SPFile: Only changes the SPFile settings, does not change the current instance to run, after restarting the database takes effect;

Both: Simultaneously changes the instance and the SPFile, the current change takes effect immediately, restarts the database to still take effect;

For RAC environments, you can also specify SID parameters and set different instances differently;

If the current instance uses pfile instead of SPFile, then scope=spfile and both will produce an error;

The instance starts with Pfile, scope defaults to memory, starts with SPFile, scope defaults to both;

Some parameters can only be restarted after the effective, such as log_buffer; memory and both parameters are error;

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/44/F1/wKioL1PjOLeySmbgAAI9NdSfVt0848.jpg "title=" 07.png "alt=" Wkiol1pjoleysmbgaai9ndsfvt0848.jpg "/>

4. View the instance Name

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/44/F0/wKiom1PjN-SzKGOLAABbne9KuN0340.jpg "title=" 08.png "alt=" Wkiom1pjn-szkgolaabbne9kun0340.jpg "/>

5. View the DB instance process Ps-ef|grep Ora_

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/44/F1/wKioL1PjOSbynV9UAAMurU3jlGA110.jpg "title=" 09.png "alt=" Wkiol1pjosbynv9uaamuru3jlga110.jpg "/>

which (Local=no) means that the background process is non-Oracle itself, which is accessed by other users through listening and connecting to the database;

6. Check whether the archive is turned on

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/44/F1/wKioL1PjOafzIJlcAACwnFiKFdQ355.jpg "title=" 10.png "alt=" Wkiol1pjoafzijlcaacwnfikfdq355.jpg "/>

NO Archive mode indicates that the current database is non-archived;

Opening the archive requires restarting the database, starting with the database in the Mount State;

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/44/F1/wKiom1PjOO7TL2VRAAEPjlaT0Wc337.jpg "title=" 11.png "alt=" Wkiom1pjoo7tl2vraaepjlat0wc337.jpg "/>

Open Archive

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/44/F1/wKioL1PjOiHRfKExAAEOLr_8Pxc612.jpg "title=" 12.png "alt=" Wkiol1pjoihrfkexaaeolr_8pxc612.jpg "/>

Closing the archive also needs to be in the database Mount State;

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/44/F1/wKioL1PjOl-B2DXlAAEJBkWRpCY124.jpg "title=" 13.png "alt=" Wkiol1pjol-b2dxlaaejbkwrpcy124.jpg "/>

7. Database Start-Stop

Database start-up is divided into 3 stages Nomount, mount, open, you can start the startup directly, can also be launched in three steps;

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/44/F1/wKiom1PjOejTNhvQAAFYKbm5Qnw599.jpg "title=" 15.png "alt=" Wkiom1pjoejtnhvqaafykbm5qnw599.jpg "/>

The database boot to nomount state needs to read the parameter file (spfile\pfile), allocate the memory area according to the parameter file, and start the background process;

Database boot to mount state requires reading control file, control file, data file, log file, checkpoint information, etc.

The database boot to open state needs to ensure that all data files and log files are equal to the records in the control file;

8. View SPFile File location

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/44/F2/wKioL1PjPKTBVpHOAACTuJ0yvio341.jpg "title=" 14.png "alt=" Wkiol1pjpktbvphoaactuj0yvio341.jpg "/>

9. View Control File Location

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/44/F2/wKioL1PjPMzT5hCxAAEU-QQapPA679.jpg "title=" 16.png "alt=" Wkiol1pjpmzt5hcxaaeu-qqappa679.jpg "/>

10. View Data File location

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/44/F3/wKioL1PjPQXCJQhOAAGvQYfu2gE775.jpg "title=" 17.png "alt=" Wkiol1pjpqxcjqhoaagvqyfu2ge775.jpg "/>

11. View Log File location

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/44/F3/wKioL1PjPX-QjIf9AAF09djPdI0423.jpg "title=" 18.png "alt=" Wkiol1pjpx-qjif9aaf09djpdi0423.jpg "/>

12. View the archive file location

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/44/F3/wKioL1PjPaKTP17yAADKVUmepUc002.jpg "title=" 19.png "alt=" Wkiol1pjpaktp17yaadkvumepuc002.jpg "/>

13. View the location of the alarm log file

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/44/F3/wKiom1PjPL3QrUmOAAJkiSawmiU702.jpg "title=" 20.png "alt=" Wkiom1pjpl3qrumoaajkisawmiu702.jpg "/>

Alarm Log for Bdump directory, alert beginning of the file;

14. Monitoring

Check the listening status;

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/44/F4/wKioL1PjPjaDprcNAAMs9j5X0yA334.jpg "title=" 21.png "alt=" Wkiol1pjpjadprcnaams9j5x0ya334.jpg "/>

Turn off monitoring;

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/44/F3/wKiom1PjPTXQELkXAADzrblQp1o498.jpg "title=" 22.png "alt=" Wkiom1pjptxqelkxaadzrblqp1o498.jpg "/>

Turn on monitoring;

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/44/F4/wKioL1PjPmChSWurAAOoUv4qfZE959.jpg "title=" 23.png "alt=" Wkiol1pjpmchswuraaoouv4qfze959.jpg "/>

Compared to the beginning of the Lsnrctl status diagram can be found that the monitoring opened, but the database service is not registered in;

Register the database service;

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/44/F4/wKioL1PjPqXCUR15AAAmsSF7wLc993.jpg "title=" 24.png "alt=" Wkiol1pjpqxcur15aaamssf7wlc993.jpg "/>

Check the database again for normal listening;

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/44/F3/wKiom1PjPaKBjDbnAAM2431P4as875.jpg "title=" 25.png "alt=" Wkiom1pjpakbjdbnaam2431p4as875.jpg "/>

This article is from "Jabbar Superman" blog, please be sure to keep this source http://chopper.blog.51cto.com/3946170/1537033