How to manage users and redo log files during routine Oracle maintenance, and routine oracle Maintenance

How to manage users and redo log files during routine Oracle maintenance, and routine oracle Maintenance

I. Managing Users and security
Various Objects in db are organized and managed as users (solutions)

    select distinct object_type from dba_objects;

Mode object, schema ----> user. objects (an object under a user)

Before creating an object and an access object, first create the object owner-user
All objects are under the user

The user has relevant attributes, some must be explicitly set, and some can use the default value.
Common attributes: User Name, password, default tablespace (default), temporary tablespace (default), account status (default), and space quota (default), profile (default), etc.
Dba_users table

Create user

1. determine the distribution of user objects in the tablespace to determine the quota of the tablespace.

2. determine the user's default tablespace and temporary tablespace to ensure the independence of user data

Create user u1 identified by u1; ---> default users tablespace and temp tablespace create user u2 identified by u2 default tablespace user_u2 temporary tablespace temp_u2; ---> specify user_u2 tablespace and temp_u2 tablespace

3. determine the user's password management rules and resource idle rules, and set the Profile
The profile file can be found in the dba_users table.

4. Create a user and set the password.

5. grant the necessary roles and other direct permissions to the user.
Generally, you need to set connect and resource to a new user:

Grant connect, resource to u1; grant creata session to u1; ---> set session Permissions

Delete a user

    drop user u1;

If the user solution contains objects, drop user u1 CASCADE;
Currently connected users cannot be deleted

Permission

System permission + object permission
Grant and revoke
Sysdba and sysoper can be granted Permissions

Select * from session_privs; ---> query the permissions of the current user select * from session_roles; ---> query the role of the current user select * from role_sys_privs; ---> query the role permissions of the current user select * from user_sys_privs; ---> query the system permissions of the current user

Ii. redo log files
Redo log files or online logs
Archived log files archive logs
Alert log files alarm log
Trace files user_dump_dest user background_dump_dest Process

Redo log files

Query

    >select * from v$logfile;   

Status column: invalid this file is not accessible (New logfile does not exist or is added to this group) | stale content is incomplete | deleted is no longer used

    >select * from v$log;    

Status column: unused not written | current group, active | active is not the current group, active, it may have been archived or not | clearing is recreating an empty log (execute alter database clear logfile), and then changes to unused status

Function

When the database fails, you can re-process the transaction.
Records all changes made to the data and provides a recovery mechanism that can be divided into at least two groups with at least one file member in each group.

Write logs cyclically in group order, g1 (f1-f2-f3 -......) -G2 (f1-f2-f3 -......) -G3 (f1-f2-f3 -......) -......
Log switching: g1-g2-g3 -...... Manual switch (alter system switch logfile)

Planning

Decentralization principle: multiple members in each group are backed up to each other and separated to different disks. Example:

Size principle: the inter-group switching time can meet the business needs of about 20 minutes.

Add/Remove

Create Group
 

    alter database add logfile [group <X0>] ('<logfile path&name>','',……) size <Y>, [group <X1>] ……

Add Member

    alter database add logfile member '<logfile path&name>' to group <X0>，'<logfile path&name>' ……

Delete Group
 

    alter database drop logfile group <X>, group……

The active and current statuses cannot be deleted.
Delete a member
 

    alter database drop logfile member '<logfile path&name>','<logfile path&name>',……;

Rename a member (logs can be moved, etc)

    ho cp <old> <new>    alter database rename file '<old>' tp '<new>';

You cannot rename the current status in archive mode. You can change the current status in non-archive mode.

[Alter database noarchivelog | archivelog ;]

Exception Handling

Log inconsistency during startup

    alter database clear logfile group <X>; 

The active and current statuses are unavailable.

    alter database clear unarchived logfile group <X>; 

Log File loss

    alter database clear logfile group <X>;

When clear is not allowed in archive mode,

    recover database using backup controlfile;alter database open resetlog;

When the non-archive mode cannot be clear,

    alter system set "_allow_resetlogs_corruption"=true scope=spfile;

Startup;

    alter system reset "_allow_resetlogs_corruption" scope=spfile sid='*';

Articles you may be interested in:

• SQL statements for creating a user, role, authorization, and tablespace in Oracle
• How to add and authorize users, change passwords, unlock, and delete users in Oracle
• How Does oracle disable archive logging?