.log`.
The Alert Log is the first place to look when troubleshooting or investigating errors, failures, and other messages indicating a potential database problem. Common events logged in the Alert Log include:
+ Database startup or shutdown.
+ Database redo log switch.
+ Database errors and warnings, which begin with `ORA-` followed by an Oracle error number.
+ Network and connection issues.
+ Links for a detailed trace files about specific database events.
The Oracle Alert Log can be found inside the database Automatic Diagnostics Repository (ADR), which is a hierarchical file-based repository for diagnostic information: `$ADR_BASE/diag/rdbms/{DB-name}/{SID}/trace`.
In addition, several other Oracle server components have unique log files such as the database listener and the Automatic Storage Manager (ASM).
**Examples**
The following screenshot displays partial contents of the Oracle database Alert Log File.
![\[Contents of the Oracle database Alert Log File\]](http://docs.aws.amazon.com/dms/latest/oracle-to-aurora-postgresql-migration-playbook/images/pb-oracle-database-alert-log-file.png)
For more information, see [Monitoring Errors and Alerts](https://docs.oracle.com/en/database/oracle/oracle-database/19/admin/monitoring-the-database.html#GUID-E5F89E8E-7FBC-47DD-BA5D-96AFD9CE4BC7) in the *Oracle documentation*.
## PostgreSQL usage
PostgreSQL provides detailed logging and reporting of errors that occur during the database and connected sessions lifecycle. In an Amazon Aurora deployment, these informational and error messages are accessible using the Amazon RDS console.
### PostgreSQL and Oracle error codes
| Oracle | PostgreSQL |
| --- | --- |
| ORA-00001: unique constraint (string.string) violated | SQLSTATE[23505]: Unique violation: 7 ERROR: duplicate key value violates unique constraint "constraint\$1name" |
For more information, see [PostgreSQL Error Codes](https://www.postgresql.org/docs/10/errcodes-appendix.html) in the *PostgreSQL documentation*.
### PostgreSQL error log types
| Log type | Information written to log |
| --- | --- |
| DEBUG1…DEBUG5 | Provides successively-more-detailed information for use by developers |
| INFO | Provides information implicitly requested by the user |
| NOTICE | Provides information that might be helpful to users |
| WARNING | Provides warnings of likely problems |
| ERROR | Reports an error that caused the current command to abort |
| LOG | Reports information of interest to administrators |
| FATAL | Reports an error that caused the current session to abort |
| PANIC | Reports an error that caused all database sessions to abort |
For more information, see [Error Reporting and Logging](https://www.postgresql.org/docs/13/runtime-config-logging.html) in the *PostgreSQL documentation*.
**Examples**
Access the PostgreSQL error log using the Amazon RDS or Aurora management console.
1. Sign in to your AWS console and choose **RDS**.
1. Choose **Databases** and select your database.
1. Choose **Logs & events**.
![\[Logs and events tab\]](http://docs.aws.amazon.com/dms/latest/oracle-to-aurora-postgresql-migration-playbook/images/pb-logs-events.png)
1. Scroll down to the Logs section and select the log to inspect. For example, select the log during the hour the data was experiencing problems. The following screen shot displays partial contents of a PostgreSQL database error log as viewed from the Amazon RDS Management Console.
![\[Logs\]](http://docs.aws.amazon.com/dms/latest/oracle-to-aurora-postgresql-migration-playbook/images/pb-logs.png)
1. Choose one of the logs.
![\[View log\]](http://docs.aws.amazon.com/dms/latest/oracle-to-aurora-postgresql-migration-playbook/images/pb-view-log.png)
### PostgreSQL error log configuration
The following tables shows parameters that control how and where PostgreSQL log and errors files will be placed.
| Parameter | Description |
| --- | --- |
| `log_filename` | Sets the file name pattern for log files. Modifiable by an Aurora Database Parameter Group. |
| `log_rotation_age` | (min) Automatic log file rotation will occur after N minutes. Modifiable by an Aurora Database Parameter Group. |
| `log_rotation_size` | (kB) Automatic log file rotation will occur after N kilobytes. Modifiable by an Aurora Database Parameter Group. |
| `log_min_messages` | Sets the message levels that are logged (`DEBUG`, `ERROR`, `INFO`, and so on). Modifiable by an Aurora Database Parameter Group |
| `log_min_error_statement` | Causes all statements generating error at or above this level to be logged (`DEBUG`, `ERROR`, `INFO`, and so on). Modifiable by an Aurora Database Parameter Group. |
| `log_min_duration_statement` | Sets the minimum run time above which statements will be logged (ms). Modifiable by an Aurora Database Parameter Group |
Modifications to certain parameters, such as `log_directory` (which sets the destination directory for log files) or `logging_collector` (which start a subprocess to capture stderr output and/or csvlogs into log files) are disabled for Aurora PostgreSQL instances.
# Oracle SGA and PGA memory sizing and PostgreSQL memory buffers
With AWS DMS, you can optimize database performance by properly sizing memory components like Oracle’s System Global Area (SGA) and Program Global Area (PGA), as well as PostgreSQL’s memory buffers.
| Feature compatibility | AWS SCT / AWS DMS automation level | AWS SCT action code index | Key differences |
| --- | --- | --- | --- |
| ![\[Two star feature compatibility\]](http://docs.aws.amazon.com/dms/latest/oracle-to-aurora-postgresql-migration-playbook/images/pb-compatibility-2.png) | N/A | N/A | Different cache names, similar usage |
## Oracle usage
An Oracle instance allocates several individual “pools” of server RAM used as various caches for the database. These include the Buffer Cache, Redo Buffer, Java Pool, Shared Pool, Large Pool, and others. The caches reside in the System Global Area (SGA) and are shared across all Oracle sessions.
In addition to the SGA, each Oracle session is granted an additional area of memory for session-private operations (sorting, private SQL cursors elements, and so on) called the Private Global Area (PGA).
Cache size can be controlled for individual caches or globally, and automatically, by an Oracle database. Setting a unified “memory size” parameter enables Oracle to automatically manage individual cache sizes.
+ All Oracle memory parameters are set using the `ALTER SYSTEM` command.
+ Some changes to memory parameters require an instance restart.
Some of the common Oracle parameters that control memory allocations include:
+ `db_cache_size` — The size of the cache used for database data.
+ `log_buffer` — The cache used to store Oracle redo log buffers until they are written to disk.
+ `shared_pool_size` — The cache used to store shared cursors, stored procedures, control structures, and other structures.
+ `large_pool_size` — The cache used for parallel queries and RMAN backup/restore operations.
+ `Java_pool_size` — The cache used to store Java code and JVM context.
While these parameters can be configured individually, most database administrators choose to let Oracle automatically manage RAM. Database administrators configure the overall size of the SGA, and Oracle sizes individual caches based on workload characteristics.
+ `sga_max_size` — Specifies the hard-limit maximum size of the SGA.
+ `sga_target` — Sets the required soft-limit for the SGA and the individual caches within it.
Oracle also allows control over how much private memory is dedicated for each session. Database Administrators configure the total size of memory available for all connecting sessions, and Oracle allocates individual dedicated chunks from the total amount of available memory for each session.
+ `pga_aggregate_target` — A soft-limit controlling the total amount of memory available for all sessions combined.
+ `pga_aggregate_limit` — A hard-limit for the total amount of memory available for all sessions combined (Oracle 12c only).
In addition, instead of manually configuring the SGA and PGA memory areas, you can also configure one overall memory limit for both the SGA and PGA and let Oracle automatically balance memory between the various memory pools. This behavior is enabled using the `memory_target` and `memory_max_target` parameters.
For more information, see [Memory Architecture](https://docs.oracle.com/en/database/oracle/oracle-database/19/cncpt/memory-architecture.html#GUID-913335DF-050A-479A-A653-68A064DCCA41) and [Database Memory Allocation](https://docs.oracle.com/en/database/oracle/oracle-database/19/tgdba/database-memory-allocation.html#GUID-E9265077-B296-485A-BC2C-0AF55762D1EC) in the *Oracle documentation*.
## PostgreSQL usage
PostgreSQL provides us with control over how server RAM is allocated. The following table includes some of the most important PostgreSQL memory parameters.
| Memory pool parameter | Description |
| --- | --- |
| `shared_buffers` | Used to cache database data read from disk. Approximate Oracle Database Buffer Cache equivalent. |
| `wal_buffers` | Used to store WAL (Write-Ahead-Log) records before they are written to disk. Approximate Oracle Redo Log Buffer equivalent. |
| `work_mem` | Used for parallel queries and SQL sort operations. Approximate Oracle PGA equivalent and/or the Large Pool (for parallel workloads). |
| `maintenance_work_mem` | Memory used for certain backend database operations such as `VACUUM`, `CREATE INDEX`, `ALTER TABLE ADD FOREIGN KEY`. |
| `temp_buffers` | Memory buffers used by each database session for reading data from temporary tables. |
| Total memory available for PostgreSQL cluster | Controlled by choosing the **DB Instance Class** during instance creation. ![\[Instance creation\]](http://docs.aws.amazon.com/dms/latest/oracle-to-aurora-postgresql-migration-playbook/images/pb-db-instance-class.png) |
Cluster level parameters, such as `shared_buffers` and `wal_buffers`, are configured using parameter groups in the Amazon RDS Management Console.
**Examples**
View the configured values for database parameters.
```
show shared_buffers
show work_mem
show temp_buffers
```
View the configured values for all database parameters.
```
select * from pg_settings;
```
Use of the `SET SESSION` command to modify the value of parameters that support session-specific settings. Changing the value using the `SET SESSION` command for one session will have no effect on other sessions.
```
SET SESSION work_mem='100MB';
```
If a `SET SESSION` command is issued within a transaction that is aborted or rolled back, the effects of the `SET SESSION` command disappear. Once the transaction is committed, the effects will become persistent until the end of the session, unless overridden by another execution of `SET SESSION`.
Use of the `SET LOCAL` command to modify the current value of those parameters that can be set locally to a single transaction. Changing the value using the `SET LOCAL` command for one transaction will have no subsequent effect on other transactions from the same session. After issuing a `COMMIT` or `ROLLBACK`, the session-level settings will take effect.
```
SET LOCAL work_mem='100MB';
```
Reset a value of a run-time parameter to its default value.
```
RESET work_mem;
```
Changing parameter values can also be done with a direct update to the `pg_settings` table.
```
UPDATE pg_settings SET setting = '100MB' WHERE name = 'work_mem';
```
## Summary
Use the following table as a general reference only. Functionality may not be identical across Oracle and PostgreSQL.
| Description | Oracle | PostgreSQL |
| --- | --- | --- |
| Memory for caching table data | db\$1cache\$1size | shared\$1buffers |
| Memory for transaction log records | log\$1buffer | wal\$1buffers |
| Memory for parallel queries | large\$1pool\$1size | work\$1mem |
| Java code and JVM | Java\$1pool\$1size | N/A |
| Maximum amount of physical memory available for the instance | sga\$1max\$1size or memory\$1max\$1size | Configured by the Amazon RDS/Aurora instance class For example: db.r3.large: 15.25GB
db.r3.xlarge: 30.5GB
|
| Total amount of private memory for all sessions | pga\$1aggregate\$1target and pga\$1aggregate\$1limit | temp\$1buffers (for reading data from temp tables), work\$1mem (for sorts) |
| View values for all database parameters | SELECT * FROM v$parameter;
| Select * from pg_settings;
|
| Configure a session-level parameter | ALTER SESSION SET ...
| SET SESSION ...
|
| Configure instance-level parameter | ALTER SYSTEM SET ...
| Configured by parameter groups in the Amazon RDS Management Console. |
For more information, see [Write Ahead Log](https://www.postgresql.org/docs/13/runtime-config-wal.html) and [Resource Consumption](https://www.postgresql.org/docs/13/runtime-config-resource.html) in the *PostgreSQL documentation*.
# Oracle instance parameters and Amazon RDS parameter groups
With AWS DMS, you can configure Oracle instance parameters and Amazon RDS parameter groups to optimize database performance, security, and resource utilization. Oracle instance parameters control various aspects of an Oracle database instance, such as memory allocation, logging, and backup settings. Amazon RDS parameter groups act as a container for engine configuration values that can be applied to one or more Amazon RDS database instances.
| Feature compatibility | AWS SCT / AWS DMS automation level | AWS SCT action code index | Key differences |
| --- | --- | --- | --- |
| ![\[One star feature compatibility\]](http://docs.aws.amazon.com/dms/latest/oracle-to-aurora-postgresql-migration-playbook/images/pb-compatibility-1.png) | N/A | N/A | Use Cluster and Database/Cluster parameters. |
## Oracle usage
Oracle instance and database-level parameters can be configured using the `ALTER SYSTEM` command. Certain parameters can be configured dynamically and take immediate effect while other parameters require an instance restart.
+ All Oracle instance and database-level parameters are stored in a binary file known as the Server Parameter file (SPFILE).
+ The binary SPFILE can be exported to a text file using the following command:
```
CREATE PFILE = 'my_init.ora' FROM SPFILE = 's_params.ora';
```
When modifying parameters, you can choose the persistence of the changed values with one of the three following options:
+ Make the change applicable only after a restart by specifying `scope=spfile`.
+ Make the change dynamically, but not persistent, after a restart by specifying `scope=memory`.
+ Make the change both dynamically and persistent by specifying `scope=both`.
**Examples**
Use the `ALTER SYSTEM SET` command to configure a value for an Oracle parameter.
```
ALTER SYSTEM SET QUERY_REWRITE_ENABLED = TRUE SCOPE=BOTH;
```
For more information, see [Initialization Parameters](https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/initialization-parameters-2.html#GUID-FD266F6F-D047-4EBB-8D96-B51B1DCA2D61) and [Changing Parameter Values in a Parameter File](https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/changing-parameter-values-in-a-parameter-file.html#GUID-4C578B21-DE2B-4210-8EB7-EF28D36CC1CB) in the *Oracle documentation*.
## PostgreSQL usage
When running PostgreSQL databases as Amazon Aurora Clusters, Parameter Groups are used to change to cluster-level and database-level parameters.
Most of the PostgreSQL parameters are configurable in an Amazon Aurora PostgreSQL cluster, but some are disabled and can’t be modified. Since Amazon Aurora clusters restrict access to the underlying operating system, modification to PostgreSQL parameters must be made using Parameter Groups.
Amazon Aurora is a cluster of database instances and, as a direct result, some of the PostgreSQL parameters apply to the entire cluster while other parameters apply only to a particular database instance.
| Aurora PostgreSQL parameter class | Controlled by |
| --- | --- |
| **Cluster-level parameters** Single cluster parameter group for each Amazon Aurora cluster. | Managed by cluster parameter groups. For example, [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/dms/latest/oracle-to-aurora-postgresql-migration-playbook/chap-oracle-aurora-pg.configuration.parameters.html) |
| **Database instance-level parameters** Every instance in an Amazon Aurora cluster can be associated with a unique database parameter group. | Managed by database parameter groups For example, [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/dms/latest/oracle-to-aurora-postgresql-migration-playbook/chap-oracle-aurora-pg.configuration.parameters.html) |
PostgreSQL 10 introduces the following new parameters:
+ `enable_gathermerge` — enable run plan gather merge.
+ `max_parallel_workers` — maximum number of parallel workers process.
+ `max_sync_workers_per_subscription` — maximum number of synchronous workers for subscription.
+ `wal_consistency_checking` — check consistency of WAL on the standby instance (can’t be set in Aurora PostgreSQL).
+ `max_logical_replication_workers` — maximum number of logical replication worker process.
+ `max_pred_locks_per_relation` — Maximum number of records that can be predicate-lock before locking the entire relation (signup).
+ `max_pred_locks_per_page` — Maximum number of records that can be predicate-lock before locking the entire page.
+ `min_parallel_table_scan_size` — minimum table size to consider parallel table scan.
+ `min_parallel_index_scan_size` — minimum table size to consider parallel index scan.
**Examples**
Follow the following steps to create and configure Amazon Aurora database and cluster parameter groups.
1. Sign in to the AWS Management Console and choose **RDS**.
1. Choose **Parameter groups** and choose **Create parameter group**.
**Example**
You can’t edit the default parameter group. Create a custom parameter group to apply changes to your Amazon Aurora cluster and its database instances.
1. For **Parameter group family**, choose the database family.
1. For **Type**, choose **DB Parameter Group**.
1. Choose **Create**.
Follow the following steps to modify an existing parameter group.
1. Sign in to the AWS Management Console and choose **RDS**.
1. Choose **Parameter groups** and choose the name of the parameter to edit.
1. For **Parameter group actions**, choose **Edit**.
1. Change parameter values and choose **Save changes**.
For more information, see [SET](https://www.postgresql.org/docs/13/sql-set.html) in the *PostgreSQL documentation*.
# Oracle and PostgreSQL session parameters
With AWS DMS, you can configure session parameters for Oracle and PostgreSQL databases to optimize performance and customize behavior during migration tasks. Session parameters are special configuration options that influence how the database engine operates and processes data.
| Feature compatibility | AWS SCT / AWS DMS automation level | AWS SCT action code index | Key differences |
| --- | --- | --- | --- |
| ![\[One star feature compatibility\]](http://docs.aws.amazon.com/dms/latest/oracle-to-aurora-postgresql-migration-playbook/images/pb-compatibility-1.png) | N/A | N/A | SET options are significantly different in PostgreSQL. |
## Oracle usage
Certain Oracle database parameters and configuration options are modifiable at the session level using the `ALTER SESSION` command. However, not all Oracle configuration options and parameters can be modified on a per-session basis. To view a list of all configurable parameters that can be set for the scope of a specific session, query the `v$parameter` view as shown in the following example.
```
SELECT NAME, VALUE FROM V$PARAMETER WHERE ISSES_MODIFIABLE='TRUE';
```
**Examples**
Change the `NLS_LANAUGE` codepage parameter of the current session.
```
alter session set nls_language='SPANISH'
Sesi≤n modificada.
alter session set nls_language='ENGLISH';
Session altered.
alter session set nls_language='FRENCH';
Session modifiΘe.
alter session set nls_language='GERMAN';
Session wurde geΣndert.
```
Specify the format of date values returned from the database using the `NLS_DATE_FORMAT` session parameter.
```
select sysdate from dual;
SYSDATE
SEP-09-17
alter session set nls_date_format='DD-MON-RR';
Session altered.
select sysdate from dual;
SYSDATE
09-SEP-17
alter session set nls_date_format='MM-DD-YYYY';
Session altered.
select sysdate from dual;
SYSDATE
09-09-2017
alter session set nls_date_format='DAY-MON-RR';
Session altered.
```
For more information, see [Changing Parameter Values in a Parameter File](https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/changing-parameter-values-in-a-parameter-file.html#GUID-4C578B21-DE2B-4210-8EB7-EF28D36CC1CB) in the *Oracle documentation*.
## PostgreSQL usage
PostgreSQL provides session-modifiable parameters that are configured using the `SET SESSION` command. Configuration of parameters using `SET SESSION` will only be applicable in the current session. To view the list of parameters that can be set with `SET SESSION`, you can query `pg_settings`.
```
SELECT * FROM pg_settings where context = 'user';
```
Find the commonly used session parameters following:
+ `client_encoding` configures the connected client character set.
+ `force_parallel_mode` forces use of parallel query for the session.
+ `lock_timeout` sets the maximum allowed duration of time to wait for a database lock to release.
+ `search_path` sets the schema search order for object names that are not schema-qualified.
+ `transaction_isolation` sets the current Transaction Isolation Level for the session.
**Examples**
Change the Time zone of the connected session.
```
set session DateStyle to POSTGRES, DMY;
SET
select now();
now
Sat 09 Sep 11:03:43.597202 2017 UTC
(1 row)
set session DateStyle to ISO, MDY;
SET
select now();
now
2017-09-09 11:04:01.3859+00
(1 row)
```
## Summary
The following table includes a partial list of parameters and is meant to highlight various session-level configuration parameters in both Oracle and PostgreSQL. Not all parameters are directly comparable.
| Parameter purpose | Oracle | PostgreSQL |
| --- | --- | --- |
| Configure time and date format | ALTER SESSION
SET nls_date_format =
'dd/mm/yyyy hh24:mi:ss';
| SET SESSION
datestyle to 'SQL, DMY';
|
| Configure the current default schema or database | ALTER SESSION
SET current schema='schema_name'
| SET SESSION
SEARCH_PATH TO schemaname;
|
| Generate traces for specific errors | ALTER SESSION SET
events '10053 trace name
context forever';
| N/A |
| Run trace for a SQL statement | ALTER SESSION
SET sql_trace=TRUE;
ALTER SYSTEM
SET EVENTS 'sql_trace
[sql:&&sql_id]
bindd=true, wait=true';
| N/A |
| Modify query optimizer cost for index access | ALTER SESSION
SET optimizer_index_cost_adj = 50
| SET SESSION random_page_cost TO 6;
|
| Modify query optimizer row access strategy | ALTER SESSION
SET optimizer_mode=all_rows;
| N/A |
| Memory allocated to sort operations | ALTER SESSION
SET sort_area_size=6321;
| SET SESSION work_mem TO '6MB';
|
| Memory allocated to hash joins | ALTER SESSION
SET hash_area_size=1048576000;
| SET SESSION work_mem TO '6MB';
|
For more information, see [SET](https://www.postgresql.org/docs/13/sql-set.html) in the *PostgreSQL documentation*.