Recovery of DBPaaS RDS Oracle database on the Amazon EC2 Oracle instance

Supported OS:
Linux-x86-64 bit platform.

Steps need to perform to get the required restore script:
1.Go to Veritas download Center or goodies location(/usr/openv/netbackup/ext/db_ext/oracle/samples/rman/) and download the script from master or media.
2.Copy restore script to EC2 instance or on-prem VM.
3.Run aws_rds_oracle_rman_clone_restore_script_2.0.sh to perform restore operation.

Prerequisite:

1. Oracle instance should be installed and running on EC2 instance or on-prem VM.
Note: We recommend using the same version of binaries for the restore with
the same or higher Patch Set Update (PSU) or Release Update (RU) level used
by the Backed up RDS Oracle instance
Reference: Please refer 'Prepare the target platform for the restore' topic for more details.
https://aws.amazon.com/blogs/database/restore-an-amazon-rds-for-oracle-instance-to-a-self-managed-instance/

2. For EC2-user: Oracle user should have sudo access to download aws-cli as oracle user.
To provide sudo access to oracle user , please add below entry in /etc/sudoers file:
[ec2-user@ip-XX-XX-XX-XXX ~]$ sudo cat /etc/sudoers | grep oracle
oracle           ALL=(ALL)       ALL

3. For EC2-user: Assign password to oracle user once oracle instance is installed on ec2 user.
[ec2-user@ip-XX-XX-XX-XXX ~]$ sudo passwd oracle
Changing password for user oracle.
New password:

4. Access oracle user and access temp folder:
[ec2-user@ip-XX-XX-XX-XXX ~]su - oracle
[oracle@ip-XX-XX-XX-XXX ~]cd /tmp

5. To download data from S3 to EC2 instance or on-prem VM, need to install aws-cli, and also need to configure aws.
For installation steps please refer below steps or document:
[oracle@ip-XX-XX-XX-XXX ~] curl "https://awscli.amazonaws.com/awscli-exe-linux-x86_64.zip" -o "awscliv2.zip"
[oracle@ip-XX-XX-XX-XXX ~]unzip awscliv2.zip
[oracle@ip-XX-XX-XX-XXX ~]sudo ./aws/install
[oracle@ip-XX-XX-XX-XXX ~]aws configure
AWS Access Key ID [None]: XXXXXXXXXXXX
AWS Secret Access Key [None]: XXXXXXXXXXXXX
Default region name [None]: XXXXXXX
Default output format [None]:
https://docs.aws.amazon.com/cli/latest/userguide/getting-started-install.html

6. Create a folder backup with oracle user and download all files from s3:
[oracle@ip-XX-XX-XX-XXX ~]mkdir -p /home/oracle/backup
[oracle@ip-XX-XX-XX-XXX ~]cd /home/oracle/backup
[oracle@ip-XX-XX-XX-XXX ~]aws s3 cp s3://s3_bucket_name/s3_folder_name . --recursive
Please refer below document
https://docs.aws.amazon.com/AmazonS3/latest/userguide/download-objects.html#download-multiple-objects

7.Change the owner as oracle and group as dba for backup files by running below commands:
[oracle@ip-XX-XX-XX-XXX ~]chown oracle:dba *
http://www.rampant-books.com/op_unix_209_change_file_owners.htm

8. Copy the script, at location /home/oracle, set permission and run the script as oracle user:
[oracle@ip-XX-XX-XX-XXX ~]chmod 777 aws_rds_oracle_rman_clone_restore_script_1.0.sh
[oracle@ip-XX-XX-XX-XXX ~]./aws_rds_oracle_rman_clone_restore_script_1.0.sh

9. Once the script is recovered successfully,
check the data by setting ORACLE_SID.
for PDB, export ORACLE_SID=DB_NAME
for CDB, export ORACLE_SID=RDSCDB

10. Connect using command:
SQL>sqlplus / as sysdba
SQL>select name, open_mode from v$database;

for CDB, SQL> ALTER SESSION SET CONTAINER=DB_NAME;

Below steps are optional, If user wants to verify metadata object like stored procedures, functions, triggers, views

11. [oracle@ip-XX-XX-XX-XXX ~]$ vi /u01/app/oracle/product/XX.0.0/dbhome_1/network/admin/tnsnames.ora
=>ADD PDB (ORCL is the PDB on the source db instance) entry as shared below and save the file.
[oracle@-XX-XX-XX-XXX admin]$ cat tnsnames.ora
CDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = hostname)(PORT = XXXX))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = cdb1)
    )
  )

ORCL =
(DESCRIPTION =
   (ADDRESS = (PROTOCOL = TCP) (HOST = hostname)(PORT = XXXX))
   (CONNECT_DATA =
     (SERVER = DEDICATED)
     (SERVICE_NAME = ORCL)
   )
)

cd /u01/app/oracle/product/XX.0.0/dbhome_1/bin .
Run below command to register the service in the listener.ora 

12. [[oracle@ip-XX-XX-XX-XXX admin]$ export DISPLAY=XX.XX.XX.XX:0.0 (IP is of the VM/laptop from where the mobax session is created)

13. [oracle@ip-XX-XX-XX-XXX bin]$ netca

Oracle Net Services Configuration:
Error: null
Check the trace file for details: /u01/app/oracle/cfgtoollogs/netca/trace_OraDBXXHome1-2402055AM2200.log
Oracle Net Services configuration failed.  The exit code is 1.

If Above command fails, Create listener.ora file.Copy /u01/app/oracle/product/XX.0.0/dbhome_1/network/admin/samples/listener.ora  in the same path as tnsnames.ora file .Uncomment below entry and update as follow

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = hostname)(PORT = XXXX))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROCXXXX))
    )
  )

14. Restart the listener service.

[oracle@ip-XX-XX-XX-XXX admin]$ lsnrctl stop

[oracle@ip-XX-XX-XX-XXX admin]$ lsnrctl start

15. [oracle@ip-XX-XX-XX-XXX admin]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Feb 5 05:29:25 2024
Version 19.10.0.0.0

Copyright (c) 1982, 2020, Oracle.  All rights reserved.

Connected to an idle instance.

==Instance is idle ,hence started it using startup command.

SQL> startup;

SQL> conn admin@ORCL
Enter password:
Connected.

SQL> SELECT object_name FROM user_objects WHERE object_type = 'VIEW';

SQL> SELECT object_name FROM user_objects WHERE object_type = 'TRIGGER';

SQL> SELECT object_name FROM user_objects WHERE object_type = 'PROCEDURE';

SQL> SELECT object_name FROM user_objects WHERE object_type = 'FUNCTION';

Recovery:
Run the restore script to perform recovery operation.

Input:
Valid S3 path(Generated as part of restore job)

Output:
Customer should be able to restore oracle instance using local rman with provided backup information.

Post Recovery:
User need to drop and recreate temp tablespace after restoring the AWS RDS Oracle database.
https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=438717376824234&id=2197640.1&_afrWindowMode=0&_adf.ctrl-state=umyl7sieu_4
https://dbaclass.com/article/how-to-drop-and-recreate-temp-tablespace-in-oracle/

Manual Restore steps:

Restore steps:
1.To Change DBID Using NID Utility:
https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=422209525273857&id=863800.1&_afrWindowMode=0&_adf.ctrl-state=13ho85sauf_4
https://docs.oracle.com/en/database/oracle/oracle-database/19/sutil/oracle-dbnewid-utility.html#GUID-24AAE1B4-34DA-4746-ADCB-D068FD7DFD8D

2.To recreate the temp file in a temp tablespace:
https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=428275198232818&id=2197640.1&_afrWindowMode=0&_adf.ctrl-state=ackcjxy1w_53
https://dbaclass.com/article/how-to-drop-and-recreate-temp-tablespace-in-oracle/

Note: Source and target database name should be same, and user can run restore operation only once to same target database.