TechDBA_Future

1 year ago | [YT] | 1

TechDBA_Future

System Global Area

• The SGA is a read/write memory area that stores information shared by all database processes and by all users of the database (sometimes it is called the Shared Global Area).
• This information includes both organizational data and control information used by the Oracle Server.
• The SGA is allocated in memory and virtual memory.
• The size of the SGA can be established by a DBA by assigning a value to the parameter SGA_MAX_SIZE in the parameter file—this is an optional parameter.

The SGA is allocated when an Oracle instance (database) is started up based on values specified in the initialization parameter file (either PFILE or SPFILE).

The SGA has the following mandatory memory structures:
• Database Buffer Cache
• Redo Log Buffer
• Java Pool
• Streams Pool
Shared Pool – includes two components:
• Library Cache
• Data Dictionary Cache
Other structures (for example, lock and latch management, statistical data)

Additional optional memory structures in the SGA include:
• Large Pool

The SHOW SGA SQL command will show you the SGA memory allocations.
• This is a recent clip of the SGA for the DBORCL database at SIUE.
• In order to execute SHOW SGA, you must relate to the special privilege SYSDBA (which is only available to user accounts that are members of the DBA Linux group).

Early versions of Oracle used a Static SGA. This meant that if modifications to memory management were required, the database had to be shutdown, modifications were made to the init.ora parameter file, and then the database had to be restarted.

Oracle 11g uses a Dynamic SGA. Memory configurations for the system global area can be made without shutting down the database instance. The DBA can resize the Database Buffer Cache and Shared Pool dynamically.

Several initialization parameters are set that affect the amount of random-access memory dedicated to the SGA of an Oracle Instance. These are:

• SGA_MAX_SIZE: This optional parameter is used to set a limit on the amount of virtual memory allocated to the SGA – a typical setting might be 1 GB; however, if the value for SGA_MAX_SIZE in the initialization parameter file or server parameter file is less than the sum the memory allocated for all components, either explicitly in the parameter file or by default, at the time the instance is initialized, then the database ignores the setting for SGA_MAX_SIZE. For optimal performance, the entire SGA should fit in real memory to eliminate paging to/from disk by the operating system.
• DB_CACHE_SIZE: This optional parameter is used to tune the amount memory allocated to the Database Buffer Cache in standard database blocks. Block sizes vary among operating systems. The DBORCL database uses 8 KB blocks. The total blocks in the cache defaults to 48 MB on LINUX/UNIX and 52 MB on Windows operating systems.
• LOG_BUFFER: This optional parameter specifies the number of bytes allocated for the Redo Log Buffer.
• SHARED_POOL_SIZE: This optional parameter specifies the number of bytes of memory allocated to shared SQL and PL/SQL. The default is 16 MB. If the operating system is based on a 64-bit configuration, then the default size is 64 MB.
• LARGE_POOL_SIZE: This is an optional memory object – the size of the Large Pool defaults to zero. If the init.ora parameter PARALLEL_AUTOMATIC_TUNING is set to TRUE, then the default size is automatically calculated.
• JAVA_POOL_SIZE: This is another optional memory object. The default is 24 MB of memory.

The size of the SGA cannot exceed the parameter SGA_MAX_SIZE minus the combination of the size of the additional parameters, DB_CACHE_SIZE, LOG_BUFFER, SHARED_POOL_SIZE, LARGE_POOL_SIZE, and JAVA_POOL_SIZE.

Memory is allocated to the SGA as contiguous virtual memory in units termed granules. Granule size depends on the estimated total size of the SGA, which as was noted above, depends on the SGA_MAX_SIZE parameter. Granules are sized as follows:
· If the SGA is less than 1 GB in total, each granule is 4 MB.
· If the SGA is greater than 1 GB in total, each granule is 16 MB.

Granules are assigned to the Database Buffer Cache, Shared Pool, Java Pool, and other memory structures, and these memory components can dynamically grow and shrink. Using contiguous memory improves system performance. The actual number of granules assigned to one of these memory components can be determined by querying the database view named V$BUFFER_POOL.

Granules are allocated when the Oracle server starts a database instance in order to provide memory addressing space to meet the SGA_MAX_SIZE parameter. The minimum is 3 granules: one each for the fixed SGA, Database Buffer Cache, and Shared Pool. In practice, you'll find the SGA is allocated much more memory than this. The SELECT statement shown below shows a current_size of 1,152 granules.

SELECT name, block_size, current_size, prev_size, prev_buffers
FROM v$buffer_pool;

NAME BLOCK_SIZE CURRENT_SIZE PREV_SIZE PREV_BUFFERS
-------------------- ---------- ------------ ---------- ------------
DEFAULT 8192 560 576 71244




Program Global Area (PGA)

A PGA is:
• a nonshared memory region that contains data and control information exclusively for use by an Oracle process.
• A PGA is created by Oracle Database when an Oracle process is started.
• One PGA exists for each Server Process and each Background Process. It stores data and control information for a single Server Process or a single Background Process.
• It is allocated when a process is created, and the memory is scavenged by the operating system when the process terminates. This is NOT a shared part of memory – one PGA to each process only.
• The collection of individual PGAs is the total instance PGA, or instance PGA.
• Database initialization parameters set the size of the instance PGA, not individual PGAs.

The Program Global Area is also termed the Process Global Area (PGA) and is a part of memory allocated that is outside of the Oracle Instance

1 year ago | [YT] | 3

TechDBA_Future

Benefits of Using Oracle Database: -


The reasons you should use Oracle DBMS are extensive. Some of the greatest benefits of Oracle DBMS include its:
• Pricing
• Database performance
• Hybrid cloud environment
• Data security
• Data governance
• Scalability
• Backup options
• …and more.

• Scalability: Oracle DBMS can handle massive amounts of data and accommodate the growth of your organization, ensuring your database can scale as your business expands.
• High performance: Oracle DBMS is designed to deliver optimal performance, enabling faster query processing and data retrieval, enhancing overall system efficiency.
• Data security: Oracle DBMS incorporates advanced security features to protect your sensitive data, including encryption, access controls, and auditing capabilities, ensuring data confidentiality and integrity.
• Reliability and availability: Oracle DBMS is known for its high availability and fault-tolerant architecture, minimizing downtime and ensuring continuous access to critical data.
• Advanced analytics: Oracle DBMS provides powerful analytics and reporting capabilities, allowing organizations to gain valuable insights from their data and make informed business decisions.
• Oracle DBMS supports seamless integration with various applications and systems.

1 year ago | [YT] | 2

TechDBA_Future

1 year ago | [YT] | 3

TechDBA_Future

command to use is command is used for removing a table and all its data from the database?

1 year ago | [YT] | 2

TechDBA_Future

Which of the following is not a type of SQL statement?

1 year ago | [YT] | 4

TechDBA_Future

Which is the smallest unit of storage in an Oracle database?

1 year ago | [YT] | 2

TechDBA_Future

Useful Srvctl Commands - Part 1
===========================================

SRVCTL is known as server control utility, which is used to add, remove,relocate and manage different crs services or components in RAC database.

1. STOP DATABASE :
---------------------------
SYNTAX – srvctl stop database -d db_name [-o stop_options] where stop_options is normal/immediate(default)/transactional/abort

e.g

srvctl stop database -d PRODB -o normal
srvctl stop database -d PRODB -o immediate
srvctl stop database -d PRODB -o transactional
srvctl stop database -d PRODB -o abort

2. START DATABASE
-------------------------------
SYNTAX – srvctl start database -d db_name [-o start_options] where start_option is nomount/mount/open(default)

e.g

srvctl start database -d PRODB -o nomount
srvctl start database -d PRODB -o mount
srvctl start database -d PRODB -o open


3. STOP AN INSTANCE
-------------------------------------------
SYNTAX – srvctl stop instance -d db_unique_name [-i “instance_name_list”]} [-o stop_options] [-f]
e.g

srvctl stop instance -d PRODB -i PRODB1


4. START AN INSTANCE
-------------------------------------------------

SYNTAX – srvctl start instance -d db_unique_name [-i “instance_name_list”} [-o start_options]
e.g

srvctl start instance -d PRODB -i PRODB1


5. REMOVING DB FROM CRS:
---------------------------------------------
SYNTAX – srvctl remove database -d db_unique_name [-f] [-y] [-v]
e.g

srvctl remove database -d PRODB -f -y


6. ADDING DB IN CRS :
--------------------------------------------------
SYNTAX – srvctl add database -d db_unique_name -o ORACLE_HOME [-p spfile]
e.g

srvctl add database -d PRODB -o /u01/app/oracle/product/12.1.0.2/dbhome_1 -p +DATA/PRODDB/parameterfile/spfilePRODB.ora


7. REMOVING AN INSTANCE FROM CRS:
-----------------------------------------------
SYNTAX – srvctl remove instance -d DB_UNIQUE_NAME -i INSTANCE_NAME
e.g

srvctl remove instance -d PRODB - I PRODB1


8.ADDING AN INSTANCE TO CRS:
----------------------------------------------
SYNTAX – srvctl add instance –d db_unique_name –i inst_name -n node_name
e.g

srvctl add instance -d PRODB - i PRODB1 -n rachost1


9. Enable/disable auto restart of the instance
--------------------------------------------------
srvctl enable instance -d DB_UNIQUE_NAME-i INSTANCE_NAME
srvctl disable instance -d DB_UNIQUE_NAME-i INSTANCE_NAME


10. Enable/disable auto restart of the database
--------------------------------------------------
srvctl enable database -d DB_UNIQUE_NAME
srvctl disable database -d DB_UNIQUE_NAME

1 year ago | [YT] | 2

TechDBA_Future

Useful Srvctl Commands Part-2
===========================================
11. ADDING A SERVICE:
----------------------------
SYNTAX – srvctl add servicec -d {DB_NAME} -s {SERVICE_NAME} -r {“preferred_list”} -a {“available_list”} [-P {BASIC | NONE | PRECONNECT}]

e.g

srvctl add service -d PREDB -s PRDB_SRV -r "PREDB1, PREDB2" -a "PREDB2" -P BASIC


12.REMOVING A SERVICE:
---------------------------------
SYNTAX – srvctl remove service -d {DB_NAME} -s {SERVICE_NAME}
e.g


srvctl remove service -d PREDB -s PRDB_SRV


13. START A SERVICE
-----------------------------------
SYNTAX– srvctl start servicec -d {DB_NAME} -s {SERVICE_NAME}
e.g


srvctl start service -d PREDB -s PRDB_SRV


14. STOP A SERVICE
----------------------------------------------
SYNTAX– srvctl stop servicec -d {DB_NAME} -s {SERVICE_NAME}
e.g

srvctl stop service -d PREDB -s PRDB_SRV


15. RELOCATE A SERVICE
------------------------------------------------
SYNTAX – srvctl relocate service -d {database_name} -s {service_name} -i {old_inst_name} -r {new_inst_name}

EXAMPLE: (Relocating service PRDB_SRV from PREDB2 to PREDB1)

srvctl relocate service -d PREDB -s PRDB_SVC -i PREDB2 -t PREDB1


16. Check the status of service
---------------------------------------------------
SYNTAX – srvctl status service -d {database_name} -s {service_name}

srvctl status service -d PREDB -s PRDB_SVC


17. Check the configuration of service
----------------------------------------------------
SYNTAX – srvctl config service -d {database_name} -s {service_name}


srvctl config service -d PREDB -s PRDB_SVC


18. Check scan listener configuration
--------------------------------------------------
srvctl config scan_listener

SCAN Listener LISTENER_SCAN1 exists. Port: TCP:1522
Registration invited nodes:
Registration invited subnets:
SCAN Listener is enabled.
SCAN Listener is individually enabled on nodes:
SCAN Listener is individually disabled on nodes:
SCAN Listener LISTENER_SCAN2 exists. Port: TCP:1522
Registration invited nodes:
Registration invited subnets:
SCAN Listener is enabled.
SCAN Listener is individually enabled on nodes:
SCAN Listener is individually disabled on nodes:
SCAN Listener LISTENER_SCAN3 exists. Port: TCP:1522
Registration invited nodes:
Registration invited subnets:
SCAN Listener is enabled.
SCAN Listener is individually enabled on nodes:
SCAN Listener is individually disabled on nodes:


19. Modify scan_listener port:
---------------------------------------------------

srvctl modify scan_listener -p {new-SCAN-port}

srvctl modify scan_listener -p 1523


$GRID_HOME/bin/srvctl stop scan_listener
$GRID_HOME/bin/srvctl start scan_listener

Alter system set remote_listener='orcl-scan.stc.com.sa:1523' scope=both sid='*';


20. Manage MGMTDB in oracle 12c:
-----------------------------------------
srvctl status mgmtdb
Database is enabled
Instance -MGMTDB is running on node node12-1



-- stop and start MGMT db.

srvctl stop mgmtdb
srvctl start mgmtdb

1 year ago | [YT] | 1

TechDBA_Future

Useful Dataguard commands Part-1
≈====≈========================

1. Setup DG broker in the standby setup. (Run on both primary and standby)

- For standalone db :

ALTER SYSTEM SET dg_broker_config_file1 = '\U01\oradata\dr1node.dat' scope=both sid='*';
ALTER SYSTEM SET dg_broker_config_file2 = '\U01\oradata\dr2node.dat' scope=both sid='*';

-- For oracle RAC/ASM file system.

ALTER SYSTEM SET dg_broker_config_file1 = '+DATA/broker/dr1node.dat' scope=both sid='*';
ALTER SYSTEM SET dg_broker_config_file2 = '+DATA/broker/dr2node.dat' scope=both sid='*';


ALTER SYSTEM SET DG_BROKER_START=TRUE scope=both sid='*';

2. Create configuration in dgbroker:

-- on primary
$dgmgl

DGMGRL> CONNECT sys/;
Connected.

-- create configuration with primary db_unique_name and its service name.

DGMGRL> CREATE CONFIGURATION 'PROD_DG' AS PRIMARY DATABASE IS 'PRIMDB' CONNECT IDENTIFIER IS PRIMDB;

Configuration "PRIMDB" created with primary database "PRIMDB"

--- Add standby in the configuration:

DGMGRL> ADD DATABASE 'STYDB' AS CONNECT IDENTIFIER IS STYDB MAINTAINED AS PHYSICAL;

Database "STYDB" added
3. Enable the configuration


DGMGRL> ENABLE CONFIGURATION;
Enabled.

At this stage our dg broker setup is completed.

4. View configuration of dgbroker:


DGMGRL> show configuration

DGMGRL> show configuration verbose
5. view database informations:

-- Here PRIMDB and STYDB are db_unique_name of primary and standby db


DGMGRL> show database 'PRIMDB'
DGMGRL > show database 'STYDB'
DGMGRL> show database verbose 'PRIMDB'


6. View statusreport of databases

-- Here PRIMDB and STYDB are db_unique_name of primary and standby db

show database PRIMDB statusreport

7. View database inconsistent properties

-- Here PRIMDB and STYDB are db_unique_name of primary and standby db

show database PRIMDB InconsistentProperties
show database PRIMDB InconsistentLogXptProps

show database STYDB InconsistentProperties
show database STYDB InconsistentLogXptProps


8. Check whether all logfiles are archived or not(on primary)

show database PRIMDB send entries


PRIMARY_SEND_QUEUE
STANDBY_NAME STATUS RESETLOGS_ID THREAD LOG_SEQ TIME_GENERATED TIME_COMPLETED FIRST_CHANGE# NEXT_CHANGE# SIZE (KBs)
CURRENT 1022762318 1 294 10/30/2019 11:09:26 12298130044308 274219

9. Check information of received log sequence (not applied) (Run for standby)

DGMGRL>show database STYDB recvqentries

STANDBY_RECEIVE_QUEUE
STATUS RESETLOGS_ID THREAD LOG_SEQ TIME_GENERATED TIME_COMPLETED FIRST_CHANGE# NEXT_CHANGE# SIZE (KBs)
NOT_APPLIED 1022762318 1 293 10/30/2019 10:03:06 10/30/2019 11:09:26 12298109948824 12298130044308 3487164

10. Check database wait events:

DGMGRL>show database PRIMDB topwaitevents

1 year ago | [YT] | 1