Oracle Database 19c Maintenance and Monitoring Standard Operating Procedure (SOP)

Oracle Database 19c Maintenance and Monitoring Standard Operating Procedure

Purpose:

Ensure the reliability, performance, and security of the Oracle Database 19c environment.

Scope:

This SOP applies to all Oracle Database 19c instances.

Responsibilities:

- Database Administrators (DBAs)

- System Administrators

Maintenance Tasks:

- Daily:

    - Check database alert logs for errors.

    - Verify backup completion.

    - Monitor system resources (CPU, memory, disk space).

- Weekly:

    - Run database backups (RMAN).

    - Perform database maintenance tasks (e.g., rebuild indexes, update statistics).

    - Review database performance metrics.

- Monthly:

    - Perform operating system updates.

    - Review database security settings.

Monitoring Tasks:

- Database Performance:

    - Monitor CPU utilization.

    - Monitor disk I/O.

    - Monitor memory utilization.

- Database Logs:

    - Monitor alert logs.

    - Monitor trace files.

- Backup Monitoring:

    - Verify backup completion.

    - Verify backup integrity.

Log and Trace Purging:

- ADRCI Commands:

    - adrci exec="SHOW ALERT" to view alert logs.

    - adrci exec="SHOW TRACE" to view trace files.

    - adrci exec="PURGE ALERT" to purge alert logs.

    - adrci exec="PURGE TRACE" to purge trace files.

Backup Script:

```

bash

#!/bin/bash

rman target=/ <<EOF

BACKUP DATABASE;

EOF

```

Schedule:

Daily, weekly, and monthly tasks should be scheduled using a scheduling tool (e.g., cron).

Documentation:

- Database documentation should include:

    - Database configuration.

    - Backup schedules.

    - Maintenance schedules.

    - Performance metrics.

How to clear flashback log in oracle database

 Here are the steps to clear flashback logs in Oracle without causing downtime ¹:

- Check the size of the recovery area with the command "show parameter db_recovery"

- Check how much space is used and free with the command "select space_used/(1024_1024_1024),space_limit/(1024_1024_1024) from v$recovery_file_dest"

- Set the parameter "log_archive_min_succeed_dest" with the command "show parameter log_archive_min_succeed_dest"

- Set "log_archive_dest_1" to a different location with the command "alter system set log_archive_dest_1='LOCATION=/home/oracle/temp_archivelog' scope=memory"

- Defer the default "DB_RECOVERY_FILE_DESTINATION" with the command "alter system set log_archive_dest_state_10=defer scope=memory"

- Switch logfiles with the command "alter system switch logfile"

- Make "db_recovery_file_size" the same as the actual usage with the command "alter system set db_recovery_file_dest_size=55G scope=memory"

- Check for errors and manually remove archive log files from the OS and run "RMAN> crosscheck archivelog all" and "RMAN> delete expired"

- Put log_archive_destination back with the commands "alter system set log_archive_dest_1=' ' scope=memory" and "alter system set log_archive_dest_10='LOCATION=USE_DB_RECOVERY_FILE_DEST' scope=memory"

- Make sure "flash_recovery_area" has enough space with the command "alter system set db_recovery_file_dest_size=50G scope=spemory"

Procedure to manage Oracle Physical Standby Database using DGMGRL for Oracle 19c

Procedure to manage Oracle Physical Standby Database using DGMGRL for Oracle 19c:

*Step 1: Create a Standby Database*

- Create a physical standby database using RMAN duplicate command.

- Ensure the standby database is in recovery mode.

*Step 2: Configure DGMGRL*

- Connect to the primary database using DGMGRL.

- Configure the standby database using the following commands:

```

DGMGRL> CREATE CONFIGURATION 'standby_config';

DGMGRL> ADD DATABASE 'standby_db' AS 'physical_standby';

```

*Step 3: Enable DGMGRL*

- Enable DGMGRL using the following command:

```

DGMGRL> ENABLE CONFIGURATION;

```

*Step 4: Monitor Standby Database*

- Monitor the standby database using the following command:

```

DGMGRL> SHOW DATABASE 'standby_db';

```

*Step 5: Perform Switchover*

- Perform a switchover to the standby database using the following command:

```

DGMGRL> SWITCHOVER TO 'standby_db';

```

*Step 6: Perform Failover*

- Perform a failover to the standby database using the following command:

```

DGMGRL> FAILOVER TO 'standby_db';

```

*Step 7: Reinstate Primary Database*

- Reinstate the primary database using the following command:

```

DGMGRL> REINSTATE 'primary_db';

```

*Step 8: Remove Standby Database*

- Remove the standby database using the following command:

```

DGMGRL> REMOVE DATABASE 'standby_db';

```

*Additional Commands*

- To check the configuration: `DGMGRL> SHOW CONFIGURATION;`

- To check the database status: `DGMGRL> SHOW DATABASE <db_name>;`

- To check the switchover status: `DGMGRL> SHOW SWITCHOVER STATUS;`

Note: Ensure you have the necessary privileges and credentials to perform these operations.

Also, please note that this is a general procedure and you should consult the Oracle documentation and your organization's policies and procedures for specific instructions and guidelines on managing Oracle Physical Standby Database using DGMGRL.

Oracle “SYS” Credentials Update

 To change the Oracle sys password in a primary database and physical standby database, follow these steps:

*Primary Database:*

1. Connect to the primary database as sysdba:

```

sqlplus / as sysdba

```

1. Change the sys password:

```

ALTER USER SYS IDENTIFIED BY <new_password>;

```

*Physical Standby Database:*

1. Connect to the physical standby database as sysdba:

```

sqlplus / as sysdba

```

1. Change the sys password:

```

ALTER USER SYS IDENTIFIED BY <new_password> CONTAINER=ALL;

```

Note: The `CONTAINER=ALL` clause is required to change the password for all containers (root and PDBs) in a multitenant environment.

*Synchronize Passwords:*

To synchronize the sys password between the primary and physical standby databases, use the following command on the primary database:

```

ALTER SYSTEM SET STANDBY_PASSWORD = <new_password> SCOPE=BOTH;

```

This command updates the standby password in the primary database and propagates the change to the physical standby database.

*Verify Password Change:*

Verify that the sys password has been changed successfully by connecting to both databases with the new password:

```

sqlplus sys/<new_password>@<primary_db>

sqlplus sys/<new_password>@<physical_standby_db>

```

Remember to update any scripts, applications, or tools that use the sys password to connect to the databases.

Migrating Fusion Middleware from Solaris to Linux

 Migrating Fusion Middleware from Solaris to Linux involves several steps. Here's a general outline:

*Exporting the domain and configuration from Solaris:*

1. *Backup the domain*: Use the WebLogic Server Console or WLST commands to backup the entire domain, including all configurations, applications, and security settings.

2. *Export the domain configuration*: Use the `pack` command to export the domain configuration to a jar file.

    - `pack -domain=domain_name -template=domain_template.jar`

3. *Export individual components*: Export individual components like SOA Suite, Oracle Service Bus (OSB), or Oracle Enterprise Manager (EM) using their respective tools or WLST commands.

4. *Export database schemas*: Export the database schemas used by Fusion Middleware components using the database vendor's tools (e.g., Oracle Data Pump).

*Migrating to Linux:*

1. *Install Linux*: Install the chosen Linux distribution on the target server.

2. *Install Fusion Middleware*: Install the same version of Fusion Middleware on Linux as was on Solaris.

3. *Create a new domain*: Create a new domain on Linux using the WebLogic Server Configuration Wizard.

4. *Import the domain configuration*: Use the `unpack` command to import the domain configuration from the jar file.

    - `unpack -domain=domain_name -template=domain_template.jar`

5. *Configure individual components*: Configure individual components like SOA Suite, OSB, or EM using their respective tools or WLST commands.

6. *Update database connections*: Update the database connections and credentials in the Fusion Middleware configuration.

7. *Test and validate*: Perform thorough testing to ensure all components and applications are functioning correctly.

Additional tips:

- Use the same version of Fusion Middleware on Linux as was on Solaris to ensure compatibility.

- Ensure the Linux environment is properly configured and patched.

- Use Oracle's official documentation and support resources for guidance specific to your Fusion Middleware version and environment.

- Consider engaging Oracle consulting services or a certified partner for complex migrations.

Some useful WLST commands for exporting and importing domain configurations:

- `exportDomain`: Exports the entire domain configuration to a jar file.

- `importDomain`: Imports the domain configuration from a jar file.

- `exportComponent`: Exports a specific component's configuration (e.g., SOA Suite, OSB).

- `importComponent`: Imports a component's configuration from a jar file.

Remember to consult Oracle's official documentation and support resources for more detailed instructions and guidance specific to your Fusion Middleware version and environment.