Issue:
we got Error on oracle database version 19c :ORA-22923 WHEN INSERTING INTO NCLOB COLUMNS when Application doing some inserts .
Root cause:
This is bug on the Oracle database version 19.6 * which cause this error
ORA-22923: Amount Of Data Specified In Streaming LOB Write Is 0
Solution:
Oracle provided patch to resolve this issue :
Please apply the patch for the BUG 30919691.
After applying this patch the issue resolved for us.
1.Acrhitecture/Design
ü
Redshift
Hosted on top of Linux OS cluster ,below is the architectural
Diagram.
ü
Redshift
Consists of 1 leader node (min) and many Compute nodes
ü
Leader
node used to communicate with End users thru ODBC/JDBC
ü
Redshift
data stored in Compute node. It has 2 types Dense compute & Dense
storage.
ü
Redshift
has data slices (considered as disk) where it used to store the data
with mirroring
ü
Redshift
Back end database is PostgreSQL
ü
Redshift
Version based on Cluster version and PostgreSQL version.
ü
Amazon
Redshift data is stored in a columnar fashion which drastically reduces the I/O
on disks .
ü
While
launching the Redshift cluster ,we can connect it using Endpoint or
cluster names,LN & CN cant be accessed from the users.
ü
AWS
charge only for Compute node ,not for Leader Node.
ü
Use
AWS cluster endpoint to connect to AWS cluster.
2. DB administration
ü
Data
loading and unloading To & from to AWS redshift. It needs S3 bucket
access.
ü
we
need access_key_ID & secret_access_key for the user to load & unload
the data.
ü
We
can use the ARN for authentication to
use copy & unload the data from S3
ü
Each
Redshift cluster comes with Master username/password ,like Super user.
ü
We
need to provide the cluster name each Redshift cluster ,which we
can represent the APP name
ü
We
can use the tag to notify the APP name
3. Backup/Restore methods/Process
ü
In
Redshift we can take the snapshot of the entire cluster, from this we can
restore the database and specific tables.
ü
If
we need we can restore specific tables from the snapshot.
We can customize the retention for Redshift database snapshots
4. Maintenance Tasks needed
ü
We
need stats collection job needed to improve the performance of the queries.
ü
We
need Vaccum job ,its kind of defragmentation activity on Redshift
that’s available ,both Online & offline
ü
WLM-work
load management schedule based on the usage.
ü
Based
on the storage usage ,it will alert the usage of disk/server
ü
We
can setup Cloud watch monitoring for AWS redshift Clusters to get the Alarm.
ü
We
can create the Event for monitoring and provide the ARN and email to you.
5. Encryption
ü We can encrypt the Redshift
cluster using KMS
6. Tools that used for Administration.
ü
We
can do the administration work thru AWS console
ü
By
default, AWS console has SQL editor which we can use for DB
administration.
ü
Query
editor has limitation time to run the query below 10 mins execution.
ü
Aginity
work Bench for Redshift , tool to work with AWS redshift
ü
We
can install the PostgreSQL client on other nodes and make connection to
Redshift database.
Below mentioned are the steps that need to perform during any application upgrade activities for disabling data guard and archive log mode at same time.
We need to execute all below steps on primary server only.
Take backup of primary archive log files with backup script threshold set to Zero.
Take backup of spfile/pfile, sqlnet.ora, listener.ora and tnsnames.ora files.
Take backup of /oracle/DB1/112_64/dbs/dr1DB1_ P.dat and /oracle/DB1/112_64/dbs/dr2DB1_ P.dat files. (DG broker config files backup.)
Put the DB in mount mode and execute below commands to disable broker and data guard.
SHUT IMMEDIATE;
STARTUP MOUNT;
## Disable Broker ##
ALTER SYSTEM SET DG_BROKER_START=FALSE SCOPE=BOTH;
ALTER SYSTEM SET dg_broker_config_file1=’’ SCOPE=BOTH;
ALTER SYSTEM SET dg_broker_config_file2=’’ SCOPE=BOTH;
## Disable Data Guard ##
ALTER SYSTEM SET LOG_ARCHIVE_CONFIG=’’ SCOPE=BOTH;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2=’’ SCOPE=BOTH;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=’DEFER’ SCOPE=BOTH;
ALTER SYSTEM SET standby_file_management=MANUAL SCOPE=BOTH;
ALTER SYSTEM SET FAL_SERVER=’’ SCOPE=BOTH;
ALTER SYTEM SET LOG_FILE_NAME_CONVERT=’’ SCOPE=BOTH;
## Disable Force logging and Flash back ##
ALTER DATABASE NO FORCE LOGGING;
ALTER DATABASE FLASHBACK OFF;
## Disable archive log mode ##
ALTER DATABASE NOARCHIVELOG;
## Bounce the database ##
SHUT IMMEDIATE;
STARTUP;
CREATE PFILE FROM SPFILE;
Verify above changes are in effect and data guard is disabled.
Disable below script on primary server.
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; - RMAN configuration to change archive deletion policy without data guard. To be executed on primary.
Do not terminate DR server.
Login to DR SERVER and then stop redo apply and bring down DB1 DR instance and listener.
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
