Skip to main content

Posts

ORA-27046: file size is not a multiple of logical block size and SP2-0714: invalid combination of STARTUP options

My DB Configuration : Proddb : Source GoldenGate Database Testdb : Target GoldenGate Database DB Backups : None SPFILE/PFILE Backup : None What Happened: The SPFILE in the source database (Proddb) got corrupted. I attempted to increase sga_max_size and streams_pool_size . After making these changes and issuing a STARTUP FORCE command to bounce the database, my Proddb (source) database failed to start. Unfortunately, there were no backups of the database, SPFILE, or PFILE. Solution (What I Did): Since the target server (Testdb) had valid parameter values and was similar to Proddb: I generated a PFILE from the SPFILE on the Testdb server. Transferred the PFILE to the Proddb server. Edited the PFILE with proddb values and Started the Proddb database using the PFILE. Created a new SPFILE from the PFILE. Shut down the database and started it again using the newly created SPFILE. The database is now up and running with the newly created SPFILE. STEPS AND COMMANDS USED :  Source Server ...
Post a Comment
Read more
Recent posts

ORACLE DATA GUARD COMMANDS

Hey Guys!! I have provided some important and useful commands that is used during monitoring and troubleshooting Oracle Data Guard related Issues. PFB. TO CHECK THE STATUS OF THE DATABASE - PRIMARY AND STANDBY: ========================================================== SET LINESIZE 189 PAGES 2000 COL NAME FOR A10 COL HOST_NAME FOR A30 SELECT NAME, INSTANCE_NAME, DB_UNIQUE_NAME, HOST_NAME, STATUS, OPEN_MODE, DATABASE_ROLE AS DB_ROLE, LOGINS FROM V$DATABASE, GV$INSTANCE; TO CHECK STANDBY INFO : ======================= SET LINESIZE 189 PAGES 2000 COL NAME FOR A10 COL HOST_NAME FOR A20 COL DB_UNIQUE_NAME FOR A20 COL OPEN_MODE FOR A10 COL DG_BROKER FOR A15 SELECT NAME, DB_UNIQUE_NAME, HOST_NAME, DATABASE_ROLE AS DB_ROLE, OPEN_MODE, PROTECTION_MODE AS PROTECT_MODE, PROTECTION_LEVEL AS PROTECT_LEVEL, DATAGUARD_BROKER AS DG_BROKER, SWITCHOVER_STATUS FROM V$DATABASE, GV$INSTANCE; TO CHECK CURRENT DG CONFIGURATION : =================================== SHOW PARAMETER LOG_ARCHIVE_CONFIG; SHOW PARA...
Post a Comment
Read more
HOW TO START A SINGLE INSTANCE OR ALL NODES OF A RAC DATABASE ? DEFINITION: IN RAC (REAL APPLICATION CLUSTERS), INSTEAD OF THE USUAL SETUP WITH ONE INSTANCE AND ONE DATABASE, WE HAVE MULTIPLE INSTANCES CONNECTING TO A SINGLE DATABASE. USAGE: LOAD BALANCING, HIGH AVAILABILITY ASSUMPTION : ASM AND CRS SERVICES ARE ALREADY UP AND RUNNING 1). STARTING ONLY ONE INSTANCE OF A RAC DATABASE SCENARIO :  ASM and CRS SERVICES ARE UP, DATABASE AND INSTANCES ARE DOWN               su - grid (or) su - root               cd $GRID_HOME/bin               ./crsctl check crs                    su - oracle                   srvctl status database -d db_name               srvctl start instance -d db_name -i instance_name     ...
2 comments
Read more

HOW TO CREATE A NEW TABLESPACE AND USER/SCHEMA IN ORACLE DATABASE (ASM) ?

 Hi everyone, Suppose you receive a request from a client to create a new user/schema in the Oracle Database. Here are the steps you need to follow after connecting to Database as sys user: STEP 1: Determine the storage type of the database (file system, ASM, etc.). STEP 2: Identify the location of the datafiles. STEP 3: Create a tablespace and allocate an appropriate size for its datafile. STEP 4: Create the user and associate the newly created tablespace with the user. STEP 5: Grant necessary permissions to the user. STEP 6: Verify connectivity in database. By following these steps, you will be able to successfully create a new user in the Oracle Database. STEP 1: Determine the storage type of the database (file system, ASM, etc.). STEP 2: Identify the location of the datafiles. set linesize 189 pages 2000 col tabelspace_name for a30 col file_name for a50 col bytes for a30 col bytes for 999999 select tablespace_name, file_name, bytes/1024/1024 as TOTAL_SIZE_IN_MB from dba_data_fi...
Post a Comment
Read more

HOW TO CREATE A NEW TABLESPACE AND USER/SCHEMA IN ORACLE DATABASE (FILE SYSTEM) ?

 Hi everyone, Suppose you receive a request from a client to create a new user/schema in the Oracle Database. Here are the steps you need to follow after connecting to Database as sys user: STEP 1: Determine the storage type of the database (file system, ASM, etc.). STEP 2: Identify the location of the datafiles. STEP 3: Create a tablespace and allocate an appropriate size for its datafile. STEP 4: Create the user and associate the newly created tablespace with the user. STEP 5: Grant necessary permissions to the user. STEP 6: Verify connectivity in database. By following these steps, you will be able to successfully create a new user in the Oracle Database. STEP 1: Determine the storage type of the database (file system, ASM, etc.). STEP 2: Identify the location of the datafiles. set linesize 189 pages 2000 col tabelspace_name for a30 col file_name for a50 col bytes for a30 col bytes for 999999 select tablespace_name, file_name, bytes/1024/1024 as TOTAL_SIZE_IN_MB from dba_data_fi...
Post a Comment
Read more

HELLO DBAs

Hello everyone,   Welcome to my blog! I'm Ravi Kiran, an Oracle Database Administrator. I have created this blog to share my knowledge of Oracle Database with fellow DBAs. In this blog, you will find valuable information about Oracle DB, Oracle Data Guard, Oracle RAC, and more. I encourage you to leave your comments and thoughts, and feel free to point out anything I may have missed. Let's come together and create an amazing DB community where we can support and learn from each other.   Thank you for visiting, and I look forward to our journey together.   Best regards, Ravi Kiran
Post a Comment
Read more