duplicate db from standy to other server

 Duplicate Testuat

 

$ export ORACLE_SID=Testuat3

$ sqlplus '/as sysdba'

Testuat3 SQL> alter system set cluster_database=FALSE scope=spfile ;

 System altered.

 Testuat3 SQL> exit

 

$ srvctl stop database -d DRTestuat

 Switch to grid and from asmcmd remove all DRTestuat and leave parameter file and password file on ASM and exit to oracle

 $ sqlplus '/as sysdba'

 Testuat3 SQL>startup nomount

 rman target  sys/****@192.156.0.14:1521/DRNTEST auxiliary sys/****@192.156.0.54:1521/Testuat3

 

RMAN> duplicate target database to Testuat from active database;

---

Finished Duplicate Db at 03-OCT-24

RMAN> exit

 

$ sqlplus '/as sysdba'

Testuat3 SQL> alter system set cluster_database=TRUE scope=spfile ;

 

System altered.

 Testuat3 SQL> shut immediate

  

$ srvctl start database -d DRTestuat

select name,open_mode from gv$database;

creating pluggable database 19c

-- CREATE pluggable DATABASE --

[oracle@testdbslvd01 ~]$ . oraenv

ORACLE_SID = [stgcont1] ? testcont1

[oracle@testdbslvd01 ~]$ sqlplus / as sysdba

SYS@ testcont1 SQL> create pluggable database TESTDEV admin user PDBADMIN identified by ********;

Pluggable database created.

SYS@ testcont1 SQL> alter pluggable database TESTDEV open instances=all;

Pluggable database altered.

SYS@ testcont1 SQL> alter session set container = TESTDEV;

Session altered.

SYS@ testcont1 SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------

        15 TESTDEV                         READ WRITE NO

SYS@ testcont1 SQL> exit

-- CREATE SERVICE NAME --

[oracle@testdbslvd01 ~]$ srvctl add service -d tevcont-s test_test_srv -pdb TESTDEV -r testcont1,testcont2 -m BASIC -e SELECT -z 180 -w 5

[oracle@testdbslvd01 ~]$ srvctl start service -s test_test_srv-d testcont

[oracle@testdbslvd01 ~]$ srvctl status service -s test_test_srv -d testcont

Service test_test_srv is running on instance(s) testcont1,testcont2

[oracle@testdbslvd01 ~]$

-- CREATE TABLESPACE & USER --

create tablespace USERS;

create tablespace TEST_TBS;

create user TEST_DEV identified by test_test1144;

grant connect to TEST_DEV;

grant resource to TEST_DEV;

grant unlimited tablespace TO TEST_DEV;

alter user TEST_DEV default tablespace TEST_TBS;

alter user TEST_DEV quota unlimited on TEST_TBS;

GRANT CREATE VIEW TO TEST_DEV;

GRANT CREATE TRIGGER TO TEST_DEV;

-- grant access rights to resolve lock issues

GRANT EXECUTE ON DBMS_LOCK TO TEST_DEV;

-- grant access rights to resolve XA related issues:

GRANT SELECT ON PENDING_TRANS$ TO TEST_DEV;

GRANT SELECT ON DBA_2PC_PENDING TO TEST_DEV;

GRANT SELECT ON DBA_PENDING_TRANSACTIONS TO TEST_DEV;

GRANT EXECUTE ON DBMS_XA TO TEST_DEV;

alter profile default LIMIT PASSWORD_LIFE_TIME UNLIMITED;

Postgres commads

 [oracle@Tesdb ~]$ systemctl status postgresql-15

● postgresql-15.service - PostgreSQL 15 database server

   Loaded: loaded (/usr/lib/systemd/system/postgresql-15.service; enabled; vendor preset: disabled)

   Active: active (running) since Thu 2024-02-01 17:45:15 +03; 3 days ago

     Docs: https://www.postgresql.org/docs/15/static/

  Process: 33491 ExecStartPre=/usr/pgsql-15/bin/postgresql-15-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)

 Main PID: 33500 (postmaster)

   CGroup: /system.slice/postgresql-15.service

           ├─33500 /usr/pgsql-15/bin/postmaster -D /var/lib/pgsql/15/data/

           ├─33503 postgres: logger

           ├─33505 postgres: checkpointer

           ├─33506 postgres: background writer

           ├─33508 postgres: walwriter

           ├─33509 postgres: autovacuum launcher

           ├─33510 postgres: logical replication launcher

           ├─33541 postgres: postgres Tesdb [local] idle

           ├─65961 postgres: postgres Tesdb 172.16.162.240(61004) idle

           ├─69716 postgres: postgres Tesdb 172.16.162.240(63935) idle

           ├─69816 postgres: postgres Tesdb 172.16.162.240(64108) idle

           ├─71388 postgres: postgres postgres 10.28.68.209(49286) idle

           ├─71411 postgres: postgres Tesdb 172.16.162.240(49191) idle

           └─71552 postgres: postgres postgres 10.28.68.215(44922) idle

[oracle@Tesdb ~]$

[oracle@Tesdb ~]$ sudo -i -u postgres

-bash-4.2$

-bash-4.2$

-bash-4.2$ id

uid=26(postgres) gid=26(postgres) groups=26(postgres)

[oracle@Tesdb ~]$ sudo -i -u postgres

-bash-4.2$

postgres-# \du

                                   List of roles

 Role name |                         Attributes                         | Member of

-----------+------------------------------------------------------------+-----------

 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

postgres-# \c Tesdb

You are now connected to database "Tesdb" as user "postgres".

Tesdb-#

Tesdb-#

Tesdb-#

Tesdb-# \l

                                                 List of databases

   Name    |  Owner   | Encoding |   Collate   |    Ctype    | ICU Locale | Locale Provider |   Access privileges

-----------+----------+----------+-------------+-------------+------------+-----------------+-----------------------

 Tesdb    | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            |

 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            |

 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            | =c/postgres          +

           |          |          |             |             |            |                 | postgres=CTc/postgres

 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            | =c/postgres          +

           |          |          |             |             |            |                 | postgres=CTc/postgres

(4 rows)

Tesdb-# \c

You are now connected to database "Tesdb" as user "postgres".

Tesdb-#

Tesdb-#

Tesdb-# \du

                                   List of roles

 Role name |                         Attributes                         | Member of

-----------+------------------------------------------------------------+-----------

 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

Tesdb=# \dt

               List of relations

 Schema |       Name        | Type  |  Owner

--------+-------------------+-------+----------

 public | chat_conversation | table | postgres

 public | employees         | table | postgres

 public | orgnaization      | table | postgres

 public | user              | table | postgres

(4 rows)

Tesdb=# \dt+ employees

                                       List of relations

 Schema |   Name    | Type  |  Owner   | Persistence | Access method |    Size    | Description

--------+-----------+-------+----------+-------------+---------------+------------+-------------

 public | employees | table | postgres | permanent   | heap          | 8192 bytes |

(1 row)

Tesdb=# select schemaname,relname from pg_stat_user_Tables;

 schemaname |      relname

------------+-------------------

 public     | employees

 public     | user

 public     | chat_conversation

 public     | orgnaization

Tesdb=# select datname,oid from pg_database;

  datname  |  oid

-----------+-------

 postgres  |     5

 Tesdb    | 16535

 template1 |     1

 template0 |     4

(4 rows)

how to connect database 

-bash-4.2$ psql -U postgres Tesdb

psql (15.5)

Type "help" for help.

Tesdb=#

Tesdb=#

Tesdb=# \conninfo

You are connected to database "Tesdb" as user "postgres" via socket in "/var/run/postgresql" at port "5432".

creating os user:

+++++++++++++++++++

h-#

Tesdb-#

Tesdb-# \du

                                   List of roles

 Role name |                         Attributes                         | Member of

-----------+------------------------------------------------------------+-----------

 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

Tesdb-# \q

-bash-4.2$

-bash-4.2$

-bash-4.2$ createuser test

-bash-4.2$ psql

psql (15.5)

Type "help" for help.

postgres=# \du

                                   List of roles

 Role name |                         Attributes                         | Member of

-----------+------------------------------------------------------------+-----------

 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

 test      |  

 

 

date and time

select now() -interval '2 hours 30 minutes' as deduct_time;

          deduct_time

-------------------------------

 2024-02-06 07:39:10.676531+03

(1 row)

select now() as current;

            current

-------------------------------