[ORACLE] Create OCI DB System using the console.

Hello everyone!

I hope you're doing well.

Today, I going to show you how to create a new DB system in OCI.

Let's get started.

 

1) Select Oracle Database -> Oracle Base Database Service in the OCI console and click on "Create DB system".

 

 

 

2) Enter the compartment and DB system names.

 

3) Click on "Change Shape" and select your preferred shape.

 

 

4) Configure the storage. To do this, click on "Change Storage".

 

Select the storage management software, and configure the storage performance and size.

5) Configure the Oracle Database software edition and the DB system node count.

 

We have 4 database software edition options: 

• Standard Edition
• Enterprise Edition
• Enterprise Edition High Performance
• Enterprise Edition Extreme Performance.

 

6) Add the ssh key and select whether to use your own licences or the license included model.

 

7) Now, specify the VCN and subnet to want to use.

 

 

The private IP will be created automatically, if you don't configure it.

 

 

8) Click "Next".

 

 

9) Now let's configure the database information.

 

Click "Change database image".

10) Select the database image.

 

 

11) Configure the SYS and TDE passwords, as well as the backup service.

Finally, click on “Create DB system”.

After 90 minutes (average), you will have a DB system ready to use.

And that's it. I hope it helps you.

 

Regards

Mario Barduchi

[ORACLE] Alter database open resetlogs failed after restore. And the problem was my Redo logs.

Hello all

 

How are you doing?

 

I was creating a new test environment today and I tried to open a database that I had restored. 

 

Simple. Of course not.

 

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-00349: failure obtaining block size for '+REDO/FOLHAHOM/ONLINELOG/group_4a.rdo'
ORA-15001: diskgroup "REDO" does not exist or is not mounted
ORA-15001: diskgroup "REDO" does not exist or is not mounted
ORA-15001: diskgroup "REDO" does not exist or is not mounted

 

I checked the redo log files. They were wrong and I have no idea why.

set pages 120 lines 10000;
SELECT 

    l.INST_ID, l.THREAD#, l.GROUP#, 

    substr(lf.MEMBER,1,75) as MEMBER, 

    l.SEQUENCE#, l.first_change#, 

    l.BYTES/(1024 * 1024) AS "Size MB", 

    l.MEMBERS, l.ARCHIVED, l.STATUS, 

    lf.IS_RECOVERY_DEST_FILE, l.CON_ID
FROM gv$log l
JOIN gv$logfile lf ON
    l.INST_ID = lf.INST_ID AND
    l.GROUP# = lf.GROUP#
ORDER BY 1,2,3;

Maybe I forgot to set log_file_name_convert. 

 

I don't know and It doesn't matter. I didn't want to do the whole process again.

I can't drop the log file group because the status “CLEARING_CURRENT”, of course.

Kids, don't try it at home? 😜

I'm just kidding, but this solution is a workaround and should only be used in an emergency.

And remember: This is a test environment. Production can and probably will be different, so use at your own risk.

Let's go to the party:

 

-- Thread 1

SQL> ALTER DATABASE RENAME FILE '+REDO/FOLHAHOM/ONLINELOG/group_6a.rdo' TO '+REDO1/FOLHAHOM/ONLINELOG/group_6a.rdo';
Database altered.

-- Thread 2
SQL> ALTER DATABASE RENAME FILE '+REDO/FOLHAHOM/ONLINELOG/group_11a.rdo' TO '+REDO1/FOLHAHOM/ONLINELOG/group_11a.rdo';
Database altered.

set pages 120 lines 10000;
SELECT 

    l.INST_ID, l.THREAD#, l.GROUP#, 

    substr(lf.MEMBER,1,75) as MEMBER, 

    l.SEQUENCE#, l.first_change#, 

    l.BYTES/(1024 * 1024) AS "Size MB", 

    l.MEMBERS, l.ARCHIVED, l.STATUS, 

    lf.IS_RECOVERY_DEST_FILE, l.CON_ID
FROM gv$log l
JOIN gv$logfile lf ON
    l.INST_ID = lf.INST_ID AND
    l.GROUP# = lf.GROUP#
ORDER BY 1,2,3;

 

Can I rename all the members of the log file?

Yes, but I preferred to recreate all the log file groups.

 

 

 

SQL>  alter database add logfile thread 1 ('+REDO1','+REDO2') size 512M;
Database altered.

SQL> alter database add logfile thread 2 ('+REDO1','+REDO2') size 512M;
Database altered.
 

SQL>  alter database add logfile thread 1 ('+REDO1','+REDO2') size 512M;
Database altered.

SQL>  alter database add logfile thread 1 ('+REDO1','+REDO2') size 512M;
Database altered.

SQL>  alter database add logfile thread 1 ('+REDO1','+REDO2') size 512M;
Database altered.

SQL>  alter database add logfile thread 1 ('+REDO1','+REDO2') size 512M;
Database altered.

SQL> alter database add logfile thread 2 ('+REDO1','+REDO2') size 512M;
Database altered.

SQL> alter database add logfile thread 2 ('+REDO1','+REDO2') size 512M;
Database altered.

SQL> alter database add logfile thread 2 ('+REDO1','+REDO2') size 512M;
Database altered.

SQL> alter database add logfile thread 2 ('+REDO1','+REDO2') size 512M;
Database altered.

 

SQL> ALTER DATABASE DROP LOGFILE GROUP 4;
Database altered.

SQL> ALTER DATABASE DROP LOGFILE GROUP 5;
Database altered.

SQL> ALTER DATABASE DROP LOGFILE GROUP 7;
Database altered.

SQL> ALTER DATABASE DROP LOGFILE GROUP 8;
Database altered.

SQL> ALTER DATABASE DROP LOGFILE GROUP 9;
Database altered.

SQL> ALTER DATABASE DROP LOGFILE GROUP 10;
Database altered.

SQL> ALTER DATABASE DROP LOGFILE GROUP 12;
Database altered.

SQL> ALTER DATABASE DROP LOGFILE GROUP 13;
Database altered.

SQL> alter system checkpoint;
System altered.

I checked that the old groups were inactive and dropped the last two groups.

set pages 120 lines 10000;
SELECT 

    l.INST_ID, l.THREAD#, l.GROUP#, 

    substr(lf.MEMBER,1,75) as MEMBER, 

    l.SEQUENCE#, l.first_change#, 

    l.BYTES/(1024 * 1024) AS "Size MB", 

    l.MEMBERS, l.ARCHIVED, l.STATUS, 

    lf.IS_RECOVERY_DEST_FILE, l.CON_ID
FROM gv$log l
JOIN gv$logfile lf ON
    l.INST_ID = lf.INST_ID AND
    l.GROUP# = lf.GROUP#
ORDER BY 1,2,3;

SQL> ALTER DATABASE DROP LOGFILE GROUP 6;
Database altered.

SQL> ALTER DATABASE DROP LOGFILE GROUP 11;
Database altered.

 

SQL> ALTER DATABASE ENABLE thread 2;

 

 

And that's it. I hope I've helped you.

Regards

Mario