Create public database link in oracle database

Create public database link in oracle database
================================================

ORCL TNS alias entry should be added in tnsnames.ora file and tnsping working fine


SQL> !tnsping orcl

TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 17-MAR-2020 15:09:24

Copyright (c) 1997, 2013, Oracle.  All rights reserved.

Used parameter files:
/u01/app/oracle/product/11.2.0/dbhome_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = xxx.ctttttb0k1yfde.as-sou-2.amazonaws.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME =ORCL)))
OK (1340 msec)



SQL> create public database link AWS_RDS connect to admin identified by admin1234567890  using 'ORCL' ;

Database link created


using TNS
==============

create public database link  AWS_RDS_TNS connect to admin identified by admin1234567890
USING '(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = xxx.ctttttb0k1yfde.as-sou-2.amazonaws.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME =ORCL)))';


SQL> create public database link  AWS_RDS_TNS connect to admin identified by admin1234567890
USING '(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = xxx.cttttb0k1yfde.as-so-3.amazonaws.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME =ORCL)))';
  2
Database link created.

SQL> select * from dual@AWS_RDS_TNS;

D
-
X

SQL>


Drop public database link
=============================

SQL> drop public database link AWS_RDS_TNS;

Database link dropped.


SQL>

Create Directory on AWS RDS

Create Directory on AWS RDS
============================

Prerequisite: AWS RDS access required.



Command:-
============
exec rdsadmin.rdsadmin_util.create_directory(p_directory_name => 'AWS_RDS_DIR');

output:
==========
SQL> exec rdsadmin.rdsadmin_util.create_directory(p_directory_name => 'AWS_RDS_DIR');

PL/SQL procedure successfully completed.

SQL>

Query:-
=========

select DIRECTORY_NAME,DIRECTORY_PATH,OWNER from dba_directories;

output:
==========

SQL> select DIRECTORY_NAME,DIRECTORY_PATH,OWNER from dba_directories;

DIRECTORY_NAME                 DIRECTORY_PATH                                                                   OWNER
------------------------------ -------------------------------------------------------------------------------- ----------
OPATCH_INST_DIR                /rdsdbbin/oracle.12.2.0.1.ru-2020-01.rur-2020-01.r1.EE.3/OPatch                  SYS
DATA_PUMP_DIR                  /rdsdbdata/datapump                                                              SYS
AWS_RDS_DIR                    /rdsdbdata/userdirs/01                                                           SYS
BDUMP                          /rdsdbdata/log/diag/rdbms/orcl_a/ORCL/trace                                      SYS
OPATCH_SCRIPT_DIR              /rdsdbbin/oracle.12.2.0.1.ru-2020-01.rur-2020-01.r1.EE.3/QOpatch                 SYS
OPATCH_LOG_DIR                 /rdsdbbin/oracle.12.2.0.1.ru-2020-01.rur-2020-01.r1.EE.3/QOpatch                 SYS
ADUMP                          /rdsdbdata/admin/ORCL/adump                                                      SYS

7 rows selected.

SQL>

List of Files in an AWS RDS Oracle Database Directory

Listing of Files in an AWS RDS Oracle Database Directory
==========================================================


Query:-
==========

select * from table(RDSADMIN.RDS_FILE_UTIL.LISTDIR('DATA_PUMP_DIR'))
order by mtime;

Query :-
==========

select * from table(rdsadmin.rds_file_util.listdir(p_directory => '&MyOracleDatabaseDirectoryName'));


SQL> select * from table(RDSADMIN.RDS_FILE_UTIL.LISTDIR('DATA_PUMP_DIR'))
order by mtime;  2

FILENAME                                                     TYPE         FILESIZE MTIME
------------------------------------------------------------ ---------- ---------- ---------
oracl_rds_full_db.dmp                                        file          2248704 17-MAR-20
oracl_rds_full_db.log                                        file             7082 17-MAR-20
test1_full_db.dmp                                            file        167448576 17-MAR-20
test1_anurag_db.log                                          file             2217 17-MAR-20
test1_anurag_db1.log                                         file             1474 17-MAR-20
datapump/                                                    directory        4096 17-MAR-20
test1_anurag_db2.log                                         file           993408 17-MAR-20

7 rows selected.

SQL>

How to change Database name of Oracle Databases

Change database name 
============================

As prerequisite we should do below things

• take existing spfile/pfile backup
• take database backup
• list down where db name use 
• After db name change network entries 

SQL> select dbid,name,open_mode from v$database;

      DBID NAME      OPEN_MODE
---------- --------- --------------------
2332867595 TEST      READ WRITE

SQL>
SQL>
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 1620115456 bytes
Fixed Size                  2253704 bytes
Variable Size             989858936 bytes
Database Buffers          620756992 bytes
Redo Buffers                7245824 bytes
Database mounted.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@localhost admin]$
[oracle@localhost admin]$ nid TARGET=sys/sys@test DBNAME=test1 SETNAME=YES

DBNEWID: Release 11.2.0.4.0 - Production on Tue Mar 10 22:18:08 2020

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to database TEST (DBID=2332867595)

Connected to server version 11.2.0

Control Files in database:
    /u01/app/oracle/oradata/test/control01.ctl
    /u01/app/oracle/fast_recovery_area/test/control02.ctl

Change database name of database TEST to TEST1? (Y/[N]) => Y

Proceeding with operation
Changing database name from TEST to TEST1
    Control File /u01/app/oracle/oradata/test/control01.ctl - modified
    Control File /u01/app/oracle/fast_recovery_area/test/control02.ctl - modified
    Datafile /u01/app/oracle/oradata/test/system01.db - wrote new name
    Datafile /u01/app/oracle/oradata/test/sysaux01.db - wrote new name
    Datafile /u01/app/oracle/oradata/test/undotbs01.db - wrote new name
    Datafile /u01/app/oracle/oradata/test/users01.db - wrote new name
    Datafile /u01/app/oracle/oradata/test/example01.db - wrote new name
    Datafile /u01/app/oracle/oradata/test/temp01.db - wrote new name
    Control File /u01/app/oracle/oradata/test/control01.ctl - wrote new name
    Control File /u01/app/oracle/fast_recovery_area/test/control02.ctl - wrote new name
    Instance shut down

Database name changed to TEST1.
Modify parameter file and generate a new password file before restarting.
Succesfully changed database name.
DBNEWID - Completed succesfully.

[oracle@localhost admin]$

=====================================


[oracle@localhost ~]$ . oraenv
ORACLE_SID = [test1] ? test
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@localhost ~]$
[oracle@localhost ~]$ !sq
sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.4.0 Production on Tue Mar 10 22:20:11 2020

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

Connected to an idle instance.

SQL> startup mount
ORACLE instance started.

Total System Global Area 1620115456 bytes
Fixed Size                  2253704 bytes
Variable Size             989858936 bytes
Database Buffers          620756992 bytes
Redo Buffers                7245824 bytes
ORA-01103: database name 'TEST1' in control file is not 'TEST'


SQL>
[oracle@localhost tmp]$ !sq
sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.4.0 Production on Tue Mar 10 22:22:10 2020

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

Connected to an idle instance.

SQL> startup mount pfile=/tmp/test1.ora
ORACLE instance started.

Total System Global Area 1620115456 bytes
Fixed Size                  2253704 bytes
Variable Size             989858936 bytes
Database Buffers          620756992 bytes
Redo Buffers                7245824 bytes
Database mounted.
SQL> create spfile from pfile='/tmp/test1.ora';

File created.

SQL> shut immediate;
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 1620115456 bytes
Fixed Size                  2253704 bytes
Variable Size             989858936 bytes
Database Buffers          620756992 bytes
Redo Buffers                7245824 bytes
Database mounted.
SQL> select dbid,name,open_mode from v$database;

      DBID NAME      OPEN_MODE
---------- --------- --------------------
2332867595 TEST1     MOUNTED

SQL> alter database open;

Database altered.

SQL> select dbid,name,open_mode from v$database;

      DBID NAME      OPEN_MODE
---------- --------- --------------------
2332867595 TEST1     READ WRITE

SQL>