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

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

script to ddl for table--metadata

 export ORACLE_SID="$1"

export ORACLE_HOME=/u01/db/oracle19c/oraHome_1
export PATH=$PATH:$ORACLE_HOME/bin
export LD_LIBRARY_PATH=/u01/db/oracle19c/oraHome_1/lib

 $ORACLE_HOME/bin/sqlplus /nolog << End_SQL >> r_tables.log

 connect / as sysdba

 SET head OFF

SET feed OFF
SET trimspool ON
SET linesize 32767
SET pagesize 32767
SET echo OFF
SET termout OFF
SET verify OFF

 

@r_tables.sql

 

End_SQL

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

more r_tables.sql

spool readwrite_tables.sql
select 'alter table ' || owner || '.' || table_name || ' read write;' from dba_tables where read_only='YES';
spool off

 

spool readonly_tables.sql
select 'alter table ' || owner || '.' || table_name || ' read only;' from dba_tables where read_only='YES';
spool off

create the public synonyms in the target database.

 

1. Prepare the script to create the public synonyms in the target database.

cat header.txt > 13_b_get_public_synonym.pomdir01.sql.out

cat 13_b_get_public_synonym. pomdir01.sql.out | grep "CREATE PUBLIC SYNONYM" | grep -v SELECT  >> create the 13_create_public_synonym.sql

echo "quit" >> create the 13_create_public_synonym.sql

Huge page script

 

awk '/Hugepagesize:/{p=$2} / 0 /{next} / kB$/{v[sprintf("%9d GB %-s",int($2/1024/1024),$0)]=$2;next} {h[$0]=$2} /HugePages_Total/{hpt=$2} /HugePages_Free/{hpf=$2} {h["HugePages Used (Total-Free)"]=hpt-hpf} END{for(k in v) print sprintf("%-60s %10d",k,v[k]/p); for (k in h) print sprintf("%9d GB %-s",p*h[k]/1024/1024,k)}' /proc/meminfo|sort -nr|grep --color=auto -iE "^|( HugePage)[^:]*"

Analyze a particular SQL ID and see the trends for the past day

 Analyse a particular SQL ID and see the trends for the past day

set pages 50000 lines 32767

select
s.snap_id,
to_char(s.begin_interval_time,'HH24:MI') c1,
sql.executions_delta c2,
sql.buffer_gets_delta c3,
sql.disk_reads_delta c4,
sql.iowait_delta c5,
sql.cpu_time_delta c6,
sql.elapsed_time_delta c7
from
dba_hist_sqlstat sql,
dba_hist_snapshot s
where
s.snap_id = sql.snap_id
and s.begin_interval_time > sysdate -1
and
sql.sql_id='&sqlid'
order by c7
/

Do we have multiple plan hash values for the same SQL ID – in that case may be changed plan is causing bad performance

set pages 50000 lines 32767

select
SQL_ID
, PLAN_HASH_VALUE
, sum(EXECUTIONS_DELTA) EXECUTIONS
, sum(ROWS_PROCESSED_DELTA) CROWS
, trunc(sum(CPU_TIME_DELTA)/1000000/60) CPU_MINS
, trunc(sum(ELAPSED_TIME_DELTA)/1000000/60)  ELA_MINS
from DBA_HIST_SQLSTAT
where SQL_ID in (
'&sqlid')
group by SQL_ID , PLAN_HASH_VALUE
order by SQL_ID, CPU_MINS
/