Golden gate 19c all in one approach

 

GGSCI> info extract extract_name, showch ;

copy the SCN number from above output. 

select name, thread#, sequence#, status, first_time, next_time, first_change#, next_change# from  v$archived_log where 25772732953522 between first_change# and next_change#;

select name, thread#, sequence#, status, first_time, next_time, first_change#, next_change# from  v$archived_log where <scn> between first_change# and next_change#;

Path/to/archivelog/1_8322558_956503891.arc

Linux

systemctl start service_name

systemctl stop service_name

systemctl enable service_name

systemctl disable service_name

systemctl status service_name

sudo chkconfig service_name on

sudo chkconfig service_name off

sudo chkconfig service_name status

bash --noprofile --norc starts a completely clean, bare-bones Bash shell without running any of your normal startup scripts.

---

What it does

When you start Bash normally, it loads several configuration files, such as:

• /etc/profile → system-wide environment variables

• ~/.bash_profile, ~/.bash_login, ~/.profile → user login scripts

• ~/.bashrc → aliases, functions, prompt settings, etc.

These can set your PATH, prompt, aliases, etc.
Sometimes you don’t want that — especially when testing or running untrusted scripts.

The flags:

• --noprofile → skip /etc/profile and any personal profile files.

• --norc → skip reading ~/.bashrc.

Errors while running "yum update or dnf update" :- Errors during downloading metadata for repository 'pgdg-common':

 PostgreSQL common RPMs for RHEL / Rocky 8 - x86_64                                                                          353  B/s | 659  B     00:01

PostgreSQL common RPMs for RHEL / Rocky 8 - x86_64                                                                          0.0  B/s |   0  B     00:00

Errors during downloading metadata for repository 'pgdg-common':

  - Curl error (37): Couldn't read a file:// file for file:///etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG [Couldn't open file /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG]

Error: Failed to retrieve GPG key for repo 'pgdg-common': Curl error (37): Couldn't read a file:// file for file:///etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG [Couldn't open file /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG]

[oracle@pm ~]$ cd /etc/yum.repos.d/

sudo rm pg* 

-- Note :- just remove the package which having error as above and then you're good to go --

# mkdir /var/lib/rpm/backup
# cp -a /var/lib/rpm/__db* /var/lib/rpm/backup/
# rm -f /var/lib/rpm/__db.[0-9][0-9]*
# rpm --quiet -qa
# rpm --rebuilddb
# yum clean all

Triggers in oracle

 -- Creating a table for demonstration purposes

CREATE TABLE your_table (

    id NUMBER PRIMARY KEY,

    data VARCHAR2(50),

    last_updated TIMESTAMP

);

-- BEFORE INSERT Trigger

CREATE OR REPLACE TRIGGER before_insert_trigger

BEFORE INSERT ON your_table

FOR EACH ROW

BEGIN

    -- Code to be executed before insert

    -- For example, setting a default value

    :NEW.last_updated := SYSTIMESTAMP;

END;

/

-- AFTER INSERT Trigger

CREATE OR REPLACE TRIGGER after_insert_trigger

AFTER INSERT ON your_table

FOR EACH ROW

BEGIN

    -- Code to be executed after insert

    -- For example, logging the insertion

    INSERT INTO log_table (log_message) VALUES ('New row inserted');

END;

/

-- BEFORE UPDATE Trigger

CREATE OR REPLACE TRIGGER before_update_trigger

BEFORE UPDATE ON your_table

FOR EACH ROW

BEGIN

    -- Code to be executed before update

    -- For example, preventing updates under certain conditions

    IF :NEW.data = 'restricted' THEN

        RAISE_APPLICATION_ERROR(-20001, 'Updates to "restricted" data are not allowed.');

    END IF;

END;

/

-- AFTER UPDATE Trigger

CREATE OR REPLACE TRIGGER after_update_trigger

AFTER UPDATE ON your_table

FOR EACH ROW

BEGIN

    -- Code to be executed after update

    -- For example, updating related records in another table

    UPDATE related_table

    SET related_data = :NEW.data

    WHERE related_id = :NEW.id;

END;

/

-- BEFORE DELETE Trigger

CREATE OR REPLACE TRIGGER before_delete_trigger

BEFORE DELETE ON your_table

FOR EACH ROW

BEGIN

    -- Code to be executed before delete

    -- For example, preventing deletion of specific records

    IF :OLD.data = 'protected' THEN

        RAISE_APPLICATION_ERROR(-20002, 'Deletion of "protected" records is not allowed.');

    END IF;

END;

/

-- AFTER DELETE Trigger

CREATE OR REPLACE TRIGGER after_delete_trigger

AFTER DELETE ON your_table

FOR EACH ROW

BEGIN

    -- Code to be executed after delete

    -- For example, logging the deletion

    INSERT INTO log_table (log_message) VALUES ('Row deleted');

END;

/

GRANT & REVOKE Commands for the new_user on Oracle 19c Database ?

---------- Grant Commands ------------

GRANT CREATE CLUSTER, CREATE ANY CLUSTER, ALTER ANY CLUSTER, DROP ANY CLUSTER TO &new_user;
GRANT ALTER DATABASE, ALTER SYSTEM, AUDIT SYSTEM TO &new_user;
GRANT CREATE ANY INDEX, ALTER ANY INDEX, DROP ANY INDEX TO &new_user;
GRANT CREATE PROFILE, ALTER PROFILE, DROP PROFILE TO &new_user;
GRANT CREATE ROLE, ALTER ANY ROLE, DROP ANY ROLE, GRANT ANY ROLE TO &new_user;
GRANT CREATE ROLLBACK SEGMENT, ALTER ROLLBACK SEGMENT, DROP ROLLBACK SEGMENT TO &new_user;
GRANT CREATE USER, ALTER USER, DROP USER, CREATE SESSION TO &new_user;
GRANT CREATE VIEW, CREATE ANY VIEW, DROP ANY VIEW TO &new_user;
GRANT CREATE SYNONYM, CREATE ANY SYNONYM, CREATE PUBLIC SYNONYM, DROP ANY SYNONYM, DROP PUBLIC SYNONYM TO &new_user;
GRANT CREATE SESSION, ALTER SESSION, RESTRICTED SESSION, ALTER RESOURCE COST TO &new_user;
GRANT CREATE TABLE, CREATE ANY TABLE, ALTER ANY TABLE, DROP ANY TABLE, SELECT ANY TABLE, INSERT ANY TABLE, UPDATE ANY TABLE, DELETE ANY TABLE, LOCK ANY TABLE TO &new_user;
GRANT CREATE TABLESPACE, ALTER TABLESPACE, DROP TABLESPACE, MANAGE TABLESPACE TO &new_user;

----- Revoke Commands -----

REVOKE CREATE CLUSTER, CREATE ANY CLUSTER, ALTER ANY CLUSTER, DROP ANY CLUSTER FROM &new_user;

REVOKE ALTER DATABASE, ALTER SYSTEM, AUDIT SYSTEM FROM &new_user;

REVOKE CREATE ANY INDEX, ALTER ANY INDEX, DROP ANY INDEX FROM &new_user;

REVOKE CREATE PROFILE, ALTER PROFILE, DROP PROFILE FROM &new_user;

REVOKE CREATE ROLE, ALTER ANY ROLE, DROP ANY ROLE, GRANT ANY ROLE FROM &new_user;

REVOKE CREATE ROLLBACK SEGMENT, ALTER ROLLBACK SEGMENT, DROP ROLLBACK SEGMENT FROM &new_user;

REVOKE CREATE USER, ALTER USER, DROP USER, CREATE SESSION FROM &new_user;

REVOKE CREATE VIEW, CREATE ANY VIEW, DROP ANY VIEW FROM &new_user;

REVOKE CREATE SYNONYM, CREATE ANY SYNONYM, CREATE PUBLIC SYNONYM, DROP ANY SYNONYM, DROP PUBLIC SYNONYM FROM &new_user;

REVOKE CREATE SESSION, ALTER SESSION, RESTRICTED SESSION, ALTER RESOURCE COST FROM &new_user;

REVOKE CREATE TABLE, CREATE ANY TABLE, ALTER ANY TABLE, DROP ANY TABLE, SELECT ANY TABLE, INSERT ANY TABLE, UPDATE ANY TABLE, DELETE ANY TABLE, LOCK ANY TABLE FROM &new_user;

REVOKE CREATE TABLESPACE, ALTER TABLESPACE, DROP TABLESPACE, MANAGE TABLESPACE FROM &new_user;

ALTER USER &new_user QUOTA 100M ON &tablespace;

GRANT UNLIMITED TABLESPACE TO &new_user;

Oracle 19c Architecture Explanation ?

Library cache:-

stores SQL and PL/SQL statements along with their execution plans.

Data Dictionary Cache:-

stores info about database schema such as tables, columns, definitions, indexes, privileges. 

Shared Pool:-

shared memory structures for SQL and PL/SQL execution.

it contains frequently executed SQL statements, execution plans, data dictionary information.

--

buffer memory :- i/o operations happen.

cache memory :- read/write operations happen.

filesystem handling in Oracle 19c RAC ?

 1. verify ACFS installation.

2. Create an ACFS File System:-

srvctl add filesystem -d <device> -m <mount_point>

eg. srvctl add filesystem -d /dev/asm/acfs-vol1-123 -m /acfs_file

3. Start the ACFS File System:-

srvctl start filesystem -d <device>

eg. srvctl start filesystem -d /dev/asm/acfs-vol1-123

4. Mount the ACFS File System:

mount -t acfs <device> <mount_point>

eg. mount -t acfs /dev/asm/acfs-vol1-123 /acfs_file

5. Verify ACFS Status:

srvctl status filesystem -d <device>

eg. srvctl status filesystem -d /dev/asm/acfs-vol1-123

6. Resize ACFS File System :-

acfsutil size +<size> <mount_point>

eg. acfsutil size +100G /acfs_file 

7. List all ACFS file systems:

acfsutil info fs

8. Display information about a specific ACFS file system:

acfsutil info fs -o volume <mount_point>

eg. acfsutil info fs -o volume  /acfs_file

9. Stop  an ACFS FILE SYSTEM:

srvctl stop filesystem -d <device>

eg. srvctl stop filesystem -d /dev/asm/acfs-vol1-123

10. Remove an ACFS file system:

srvctl remove filesystem -d <device>

eg. srvctl remove filesystem -d /dev/asm/acfs-vol1-123

11. Display ACFS resource dependency:

srvctl config fs -d <device>

eg. srvctl config fs -d /dev/asm/acfs-vol1-123

12. 

optimizer parameters in oracle 19c db

begin

for x in (select name from v$parameter where name like 'optimizer%')

loop

dbms_output.put_line(x.name);

end loop;

end;

-------------------------------------------------------
optimizer_features_enable
optimizer_mode
optimizer_index_cost_adj
optimizer_index_caching
optimizer_dynamic_sampling
optimizer_ignore_hints
optimizer_secure_view_merging
optimizer_use_pending_statistics
optimizer_capture_sql_plan_baselines
optimizer_use_sql_plan_baselines
optimizer_use_invisible_indexes
optimizer_adaptive_reporting_only
optimizer_adaptive_plans
optimizer_inmemory_aware
optimizer_adaptive_statistics
optimizer_ignore_parallel_hints

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

-- Enable timing of SQL statements

SET TIMING ON;

-- Enable autotrace for detailed execution plans

SET AUTOTRACE ON;

-- Set the threshold for identifying slow queries (adjust as needed)

ALTER SESSION SET STATISTICS_LEVEL = ALL;

ALTER SESSION SET TIMED_STATISTICS = TRUE;

ALTER SESSION SET SQL_TRACE = TRUE;

-- Your SQL query goes here

SELECT *

FROM your_table

WHERE your_condition;

-- Reset session settings

ALTER SESSION SET STATISTICS_LEVEL = TYPICAL;

ALTER SESSION SET TIMED_STATISTICS = FALSE;

ALTER SESSION SET SQL_TRACE = FALSE;

-- Disable timing of SQL statements

SET TIMING OFF;

-- Display execution plan

-- Uncomment the line below if you want to see the execution plan

-- SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));

> /dev/null 2>&1 to prevent errors

> /dev/null : Redirects standard output to /dev/null, discarding it.

2>&1 : Redirects standard error to the same location as standard output. This ensures that both

standard output and standard error are discarded.

script.sh > script.log 2>&1

to prevent errors :-

command or script > /dev/null 2>&1

echo -e "something" > filename enable backlash escape

 echo -e "india\nmaharashtra" > india.txt

cat india.txt

india

maharashtra 

#!/usr/bin/expect to automate Linux commands ?

 #!/usr/bin/expect

spawn some_command   # Replace some_command with the command you want to automate

expect "ExpectedPrompt"   # Replace ExpectedPrompt with the prompt you expect

send "your_input\r"   # Replace your_input with the input you want to provide

interact   # Allows you to interact with the spawned process manually if needed

chmod 777 example.exp 

./example.exp

example.: 

#!/usr/bin/expect

spawn su - goldengate

expect "Password: \r"

send "your_password\r"

expect eof    # wait for spawn processes to finish

\r :- carriage return simulate "Enter" 

\e or \x1B :- for escape key

Ctrl-a : - control plus a 

[root@localhost ~]# cat send.sh

#!/bin/bash

 

 

expect<<!!

spawn vim ./file02.txt

expect "insert mode:\r"

send "i\r"

expect "type some content:\r"

send "I am living in Pune.\r"

expect "save exit\r"

send "\x1B"

send ":wq\r"

expect eof

!!