Determine and Set Parameters for Sizing Database Structures

1. Documentation -> Masters Book List -> Administrator’s Guide -> 2 Creating and Configuring an Oracle Database -> Specifying Initialization Parameters

2. First of all, let’s see how to locate and view the different parameters of the database.

That, Although all parameters we will see structures do not Affect the database, it is interesting to give an overview

# All these exercises make them in the CMO Virtual Machine Oracle user

[Oracle @ ocm ~] $ sqlplus / as sysdba

- We can see the parameters that are in effect in the current session
show parameters

- We can also see the value of one or more specific parameters
show parameters db_recovery

- This command uses below the V $ PARAMETER view
- It is equivalent to the following query
COL NAME FORMAT A35
COL VALUE FORMAT A30
SELECT NAME, VALUE FROM V $ PARAMETER WHERE LOWER (NAME) LIKE '% db_recovery%';

- We can see if we have starting the instance SPFILE or PFILE
- This parameter gives the location of the SPFILE or will be empty if we raise the BD with PFILE
show parameter spfile

- Since we are using SPFILE, we can see the values ​​as follows
show spparameters db_recovery

- The above query uses V $ SPPARAMETER, so you can use the following SQL
SELECT NAME, VALUE FROM V $ SPPARAMETER WHERE LOWER (NAME) LIKE '% db_recovery%';

3. Parameters DB_NAME and DB_DOMAIN.
The name of the database (DB_NAME) can not exceed 8 characters. It is defined in the CONTROLFILE and if different from the parameter value will not raise the instance. The DB_DOMAIN parameter is optional and can be useful in distributed environments where there DATABASE LINKS. The global name of the database (DATABASE GLOBAL NAME) will consist of both parameters.

– We see the value of both parameters and the global name of the database
show parameter db_name
show parameter db_domain
SELECT PROPERTY_NAME, property_value FROM WHERE DATABASE_PROPERTIES PROPERTY_NAME = ‘GLOBAL_DB_NAME’;

4. FAST RECOVERY AREA configuration (formerly known as Flash Recovery Area)

– Key parameters are db_recovery_file_dest and DB_RECOVERY_FILE_DEST_SIZE
show parameter db_recovery

– They are modifiable without restarting the instance
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 5G SCOPE = BOTH;
– Validate the change
show parameter DB_RECOVERY_FILE_DEST_SIZE

– Oracle automatically manages the space at this location
– Can we see the occupation as a percentage of the types of files it contains
SELECT * FROM V $ FLASH_RECOVERY_AREA_USAGE;

5. The parameter CONTROL_FILES location of CONTROLFILE defined.

– We see the current value of CONTROL_FILES
show parameter control_files
– We can also get the location using the V $ CONTROLFILE
SELECT NAME FROM V $ CONTROLFILE;

Suppose we want to add a third copy of CONTROLFILE

– We stopped the BD
IMMEDIATE SHUTDOWN

# Copy one of the CONTROLFILE to the location where you want the new
cp -p /u01/app/oracle/oradata/OCM/control01.ctl /u01/app/oracle/product/11.2.0/dbhome_1/dbs/control03.ctl

– We put the BD in NOMOUNT mode and modify the parameter CONTROL_FILES
STARTUP NOMOUNT
ALTER SYSTEM SET CONTROL_FILES = ‘/ u01 / app / oracle / oradata / COM / control01.ctl’
‘/u01/app/oracle/flash_recovery_area/OCM/control02.ctl’
‘/u01/app/oracle/product/11.2.0/dbhome_1/dbs/control03.ctl’ SCOPE = SPFILE;

– We stopped, we started the database and validate the change
IMMEDIATE SHUTDOWN
STARTUP
show parameter control_files

– Back out changes
IMMEDIATE SHUTDOWN
STARTUP NOMOUNT
ALTER SYSTEM SET CONTROL_FILES = ‘/ u01 / app / oracle / oradata / COM / control01.ctl’
‘/u01/app/oracle/flash_recovery_area/OCM/control02.ctl’ SCOPE = SPFILE;
STARTUP FORCE

– A couple of tips that can rid you of a scare and even a large layoff
– 1. DO NOT EVER overwrite one CONTROLFILE twice after verifying that you have an alternative backup
– 2. NO NEVER overwrite a backup CONTROLFILE (repeat, never, NEVER EVER)

6. Consider the importance of the block size of the database (DB_BLOCK_SIZE).
DB default parameter sets in 8k (DB_BLOCK_SIZE = 8192)

– Consider setting block size BD
show parameter db_block_size

– However, we can create using different size TABLESPACES
– This can be useful if you want to move TBS between databases with different block size (TBS transportable)
– It is also useful for FACT TABLES (we will see in the section Data Warehouse Management)

– We will create a sample TBS 32k
– We need to first enable the BUFFER CACHE 32k
DB_32K_CACHE_SIZE ALTER SYSTEM SET SCOPE = BOTH = 100M;

– Now we add a test and validate TABLESPACE that was created with a size of 32K
CREATE TABLESPACE TESTDW DATAFILE ‘/u01/app/oracle/oradata/OCM/testdw01.dbf’ BLOCKSIZE SIZE 100M 32K;
TABLESPACE_NAME SELECT, FROM BLOCK_SIZE DBA_TABLESPACES;

– We clean the environment
DROP TABLESPACE TESTDW INCLUDING CONTENTS AND DATAFILES;
ALTER SYSTEM SET DB_32K_CACHE_SIZE SCOPE = MEMORY = 0;
ALTER SYSTEM RESET DB_32_CACHE_SIZE SCOPE = SPFILE;

– If you are curious, you can see how Oracle memory moves at the V $ SGA_RESIZE_OPS
– You can see the 100M move between the BUFFER POOL (db_cache_size) and 32k BUFFER (db_32k_cache_size)
– SELECT * FROM V $ SGA_RESIZE_OPS ORDER BY END_TIME;

7. Maximum number of processes or the same, PROCESSES parameter.
By default has a value of 100. This value increase is usually recommended, as it is often insufficient

– Default PROCESSES = 100 but as we created the database with DBCA, it sets the default value of 150
show parameter Processes

– We will upload it to 300 and restart the database as it is a static parameter

ALTER SYSTEM SET PROCESSES = 300 SCOPE = SPFILE;
IMMEDIATE SHUTDOWN
Startup

8. In 11g they have introduced a new parameter, DDL_LOCK_TIMEOUT to allow the DDL statements wait for LOCK.

– Consider the value in effect for our session DDL_LOCK_TIMEOUT
– Although we can also set the value at the instance level
show parameter ddl_lock_timeout

- We can set the number of seconds you want (eg 10 seconds.)
ALTER SESSION SET DDL_LOCK_TIMEOUT = 10;

- From another session we can generate a lock on a table
CREATE TABLE TEST (COL1 VARCHAR2 (10));
INSERT INTO TEST VALUES ('X');

- Go back to the first session and try to delete the table
- We'll see how waits 10 seconds before releasing the error ORA-00054
SET TIMING ON
DROP TABLE TEST;

- We clean the environment in the second session
ROLLBACK;
DROP TABLE TEST;
- We set the value to 0 in the first session
ALTER SESSION SET DDL_LOCK_TIMEOUT = 0;

9. You may ever bring any any static parameter in the SPFILE intentáis then restart the instance and fail for some reason. We will practice this recovery scenario, which is more common than we imagine.

- To force a decision on the restart we will raise the MEMORY_TARGET parameter. Eg. 200G
ALTER SYSTEM SET MEMORY_TARGET =200G SCOPE=SPFILE ;

- We stopped and tried to lift the body
Unless that you have more than 200G free: - the error should see "MEMORY_TARGET on This system not supported ORA-00845"
IMMEDIATE SHUTDOWN
STARTUP

- As the instance has not even reached the NOMOUNT state, we can not change the SPFILE with SQL * Plus
- But if we can create a PFILE from the SPFILE but we have opened the BD
CREATE PFILE = '/ tmp / initOCM.ora' FROM SPFILE;

- Now we have to edit the file and modify /tmp/initOCM.ora line MEMORY_TARGET to leave it as it was
- Vi /tmp/initOCM.ora
- We changed MEMORY_TARGET = 500M

- Now we got the instance in MOUNT mode using the modified PFILE
NOMOUNT STARTUP PFILE = '/ tmp / initOCM.ora'

- We restore the SPFILE from PFILE modified and restart the instance
CREATE SPFILE FROM PFILE = '/ tmp / initOCM.ora'
IMMEDIATE SHUTDOWN
STARTUP

10. There is a faster way to view the contents of a binary file is to use the SPFILE and “string” command

# The string command extracts plain text strings of at least 4 characters in a binary file
strings /u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileOCM.ora

# This also serves to generate a PFILE
# Although it is possible that you have to correct the long lines (In the example below you have to concatenate the parameter control_files)
strings /u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileOCM.ora > /tmp/initOCM.ora

11. Let’s look at the parameters that we have in our DB CMO with a brief comment to each

* .audit_file_dest = '/U01/app/oracle/admin/COM/adump' # route system audits. If filled not walk into the BD
* .audit_trail = 'Db' # Specific audits are written only in BD
* .compatible = '11 .2.0.0.0 '# Default to use the functionality of the 11gR2
* .control_files = '/ U01 / app / oracle / oradata / COM / control01.ctl' # Utilizad least two copies, Recommended 3
                '/u01/app/oracle/flash_recovery_area/OCM/control02.ctl'
* .db_block_size = 8192 # 8192 = 8k is the standard size for OLTP databases
* .db_domain = '# Optional, used in distributed environments
* .db_name = 'OCM' # This is the only required parameter to raise a BD
* .db_recovery_file_dest='/U01/app/oracle/flash_recovery_area'#Location FRA. It is recommended to have another disk to the BD
* .db_recovery_file_dest_size = 5368709120 # Size min. Recommended = SIZE DATAFILES + ARCH. GENERATED IN A DAY
* .diagnostic_dest = '/U01/app/oracle' # Location ADR "Automatic Diagnostic Recovery"
* .dispatchers = '(PROTOCOL = TCP) (SERVICE = OCMXDB)' # Dispatcher default settings for the service XML DB
* .log_archive_format = "% T_% s_% r.dbf '# archivelogs default format (% thread_% sequence_% resetlogsID)
* .memory_target = 500M # AMM = ASMM use more memory movement between PGA and SGA
* .open_cursors = 300 # Maximum number of open courses
* .processes = 300 # We have increased this value to 300. This value is very variable.
* .remote_login_passwordfile = 'EXCLUSIVE' # The password file (orapwOCM) can only be used by BD
* .undo_tablespace = 'UNDOTBS1' # tablespace UNDO (If it is omitted, the database would use the first UNDO TBS)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.